Page 1 of 2

How to merge results from search of 2 tables into one array?

Posted: Thu Feb 03, 2005 9:36 am
by mhenke
I have been trying for days, but can't make it work to select from 2 tables and merge into one array. The data for the 'online' search is in another table with fields: username, timeout, status. I need status for each username... I hope someone can help : )

This is my code:

Code: Select all

<?
dbuser();
/* number of profiles per page */
$per_page = 9; 

$country = $_GET&#1111;'country'];
$city = $_GET&#1111;'city'];
$online = $_GET&#1111;'online'];

if(isset($country))&#123;
$lookup = "SELECT username, country, city_1, online FROM users WHERE country='".$_GET&#1111;'country']."' && profdate!='NULL' order by profdate";
&#125; elseif(isset($city))&#123;
$lookup = "SELECT username, country, city_1, online FROM users WHERE city_1='".$_GET&#1111;'city']."' && profdate!='NULL' order by profdate";
&#125; elseif(isset($online))&#123;
$lookup = "SELECT username, country, city_1, online FROM users WHERE online='1' && profdate!='NULL' order by profdate";
&#125; else &#123;
$lookup = "SELECT username, country, city_1, online FROM users WHERE profdate!='NULL' order by profdate";
&#125;

$sql_text = $lookup;

if(!isset($_GET&#1111;'page'])) &#123; 
$page = 1; 
&#125; else &#123; 
$page = $_GET&#1111;'page']; 
&#125;

$prev_page = $page - 1; 
$next_page = $page + 1; 

$query = @mysql_query($sql_text); 

$page_start = ($per_page * $page) - $per_page; 

$num_rows = @mysql_num_rows($query);

if($num_rows <= $per_page) &#123; 
$num_pages = 1; 
&#125; elseif (($num_rows % $per_page) == 0) &#123; 
$num_pages = ($num_rows / $per_page); 
&#125; else &#123; 
$num_pages = ($num_rows / $per_page) + 1; 
&#125; 
$num_pages = (int) $num_pages; 

if ($page > $num_pages || $page < 0) &#123;
echo '
You have specified an invalid page number.

'; 
&#125;

$sql_text = $sql_text." LIMIT $page_start, $per_page";
$query = mysql_query($sql_text); 

/* array is filled here */

$rows = array(); 
while ($row = mysql_fetch_row($query)) 
&#123; 
     $rows&#1111;] = $row; 
&#125; 

/* page layout starts here */

echo buildTable($rows); 

function buildTable($rows) 
&#123; 

$cols = 3; 
$numrows = ceil(count($rows) / $cols);

$counter = 0; 

$table = '<table border=0 cellspacing=5>'; 
for($i = 0; $i < $numrows; $i++) &#123;

    $table .= '<tr>'; 
     
    for($j = 0; $j < $cols; $j++) &#123;
	
				     if(!empty($rows&#1111;$counter])) &#123; 
					 
if($_SESSION&#1111;'profset'] = '0')&#123;
$link = "<a href="?login" target="_top">";
&#125; else &#123;
$link = "<a href="?id=".$rows&#1111;$counter]&#1111;0]."">";
&#125;

		        
            $table .= '<td align="center" class="td'.$rows&#1111;$counter]&#1111;3].'" width="165" height="110">'.$link.'<img src="../emb/b/'.$rows&#1111;$counter]&#1111;0].'_tn.jpg" border="0" class="prof_im"></a></td>'; 
       &#125; 
       else &#123; 
             
           $table .= '<td>&nbsp;</td>'; 
       &#125; 

        $counter++; 
    &#125; 

    $table .= '</tr>'; 

    $counter = $counter - $cols; 

    $table .= '<tr>'; 
     
    for($j = 0; $j < $cols; $j++) &#123; 
	
	        
        if(!empty($rows&#1111;$counter])) &#123; 
		
		if($_SESSION&#1111;'profset'] = '0')&#123;
$link = "<a href="?login" target="_top">";
&#125; else &#123;
$link = "<a href="?id=".$rows&#1111;$counter]&#1111;0]."">";
&#125;
		
            $table .= '<td align="center" class="td2"><strong>'.$link.$rows&#1111;$counter]&#1111;0].'</a></strong><br><a href="?country='.$rows&#1111;$counter]&#1111;1].'">'.$rows&#1111;$counter]&#1111;1].'</a><br><a href="?city='.$rows&#1111;$counter]&#1111;2].'">'.$rows&#1111;$counter]&#1111;2].'</a></td>'; 
        &#125; 
       else &#123; 
             
            $table .= '<td>&nbsp;</td>'; 
        &#125; 

      $counter++; 
 &#125; 

  $table .= '</tr><tr><td><img src="../gfx/spacer.gif" height="5"></td></tr>'; 
&#125;

$table .= '</table>'; 

return $table; 

&#125; 

/* page layout ends here & previous/next links are calculated */

$country = $_GET&#1111;'country'];
$city = $_GET&#1111;'city'];
$online = $_GET&#1111;'online'];

if(isset($country))&#123;
$search = $country;
$go = "country";
&#125; elseif(isset($city))&#123;
$search = $city;
$go = "city";
&#125; elseif(isset($online))&#123;
$search = $online;
$go = "online";
&#125;

echo ("<div align="center">");

/* this displays the "Previous" link */ 

if ($prev_page != 0) &#123;
echo '<a href="index.php?page='.$prev_page.'&'.$go.'='.$search.'">< Prev</a> |';
&#125;

/* this loops the pages and displays individual links */

for ($i = 1; $i <= $num_pages; $i++) &#123;
if ($i != $page) &#123;
echo ' <a href="index.php?page='.$i.'&'.$go.'='.$search.'">'.$i.'</a> ';
&#125; else &#123;
echo ' '.$i.' ';
&#125;
&#125;

/* this displays the "Next" link */

if ($page != $num_pages) &#123;
echo '| <a href="index.php?page='.$next_page.'&'.$go.'='.$search.'">Next ></a>';
&#125;

echo ("</div>");

?>

Posted: Thu Feb 03, 2005 9:59 am
by lostboy
run your query as a join and get all the data at one go

Posted: Thu Feb 03, 2005 10:16 am
by feyd
please post the two tables' structures and which fields "link" to the other.

Posted: Thu Feb 03, 2005 10:27 am
by mhenke
One table 'users' with username, city, etc...

One table 'online' with username, timeout, status.

The only reference between the tables is the username.

I need to get results from table 'users' and results for 'status' matching 'username' from table 'online'.

Am looking into JOIN, but can't seem to figure it out : (

Posted: Thu Feb 03, 2005 10:29 am
by feyd

Code: Select all

SELECT
  *
FROM
  users, online
WHERE
  users.username = online.username
this performs a short inner join.

Posted: Thu Feb 03, 2005 10:40 am
by mhenke
The problem is that the 'users' table has many fields, and I only need 3 of them returned.

So I need to select 3 fields from the 'users' table and 2 from 'online' and "match them up" and store in one array...

Total novice with multiple table queries as you can tell : (

Something like this:

Code: Select all

$lookup = "SELECT username, country, city_1 FROM users, status FROM online WHERE users.username = online.username && country='".$_GET&#1111;'country']."' && profdate!='NULL' order by profdate";
Of course this gives errors...

Posted: Thu Feb 03, 2005 10:46 am
by timvw
in mysql null != null, thus you should use IS NULL or NOT IS NULL to test if someting is null....

i don't know about &&, standard sql says to use AND

Posted: Thu Feb 03, 2005 10:47 am
by feyd

Code: Select all

SELECT
  u.username, u.country, u.city_1, o.status
FROM
  users u, online o
WHERE
  users.username = online.username

Posted: Thu Feb 03, 2005 10:48 am
by lostboy

Code: Select all

SELECT 
  users.username, users.country, users.city_1,
  online.timeout, online.status
FROM 
  users, online 
WHERE 
  users.username = online.username

Posted: Thu Feb 03, 2005 10:59 am
by mhenke
That looks VERY cool!!! Only one question, where do I put the country='".$_GET['country']."' && profdate!='NULL'?

Wherever I add it, it returns no results : (

Posted: Thu Feb 03, 2005 11:10 am
by feyd

Code: Select all

WHERE
  users.username = online.username
  AND users.country = '&#123;$_GET&#1111;'country']&#125;'
  AND users.profdate IS NOT NULL
....depending on how your tables are set up...

please be VERY careful when using user defined information ($_GET['country']) directly inside a query.. SQL Injection potential.

Posted: Thu Feb 03, 2005 11:21 am
by mhenke
THX I will study this too, trying to make code as secure as possible...

Posted: Fri Feb 04, 2005 2:07 am
by mhenke
It works like a charm (didn't look into the SQL Injection threat yet) but it only returns result if 'username' is available in BOTH tables.

When users close their browser, the session expires, but the db doesn't know that, so I created 2 tables (please help me out here, my logic may not be valid) in the hope of making a faster site.

One table ONLY holds the online users, and is checked by a CRON job every minute to see which sessions expired so these users can be deleted from this table.

I thought having 2 tables would speed up the site as the online table will have much less rows.

I'd like your input on this, is this a good idea? If so, how can I get results for all users, even if they are only available in one table ('users').

I will include the code that I'm using now:

Code: Select all

$lookup = "SELECT 
  users.username, users.country, users.city_1, online.username, online.status 
FROM 
  users, online 
WHERE 
   users.username = online.username AND users.country='".$_GET&#1111;'country']."' AND users.profdate IS NOT NULL order by users.profdate";

Posted: Fri Feb 04, 2005 2:12 am
by feyd
your logic is basically good, however, a cron job may be a bit much, as it could be managed entirely by active users and the scripts that work with the "online" table.

This would be done via a timestamp value indicating the last time you recieved communication from the user. When you do your look over it, remove all users who's activity timestamp is older than... 3 hours or something.

I've posted many times about this.. I believe some keywords to search for are: session, database, timestamp, delete

Posted: Fri Feb 04, 2005 3:33 am
by mhenke
I have the app now set up to do the very same thing, with a timestamp check in the online table, but the cron job takes care of the cleanup.

I will search for articles on here anyway : )

Just curious: why would a cron job be a bit much? And if I had users handle the session expiration procedure, where would I add the script? Each time a user logs in? This would mean that if there's no new users logging in for a while 'stale' users could appear online for a long time.

If I add it to each page the online table will be stressed and slow down the site I think?