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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

mhenke
Forum Newbie
Posts: 21
Joined: Thu Jan 27, 2005 2:20 am

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

Post 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>");

?>
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post by lostboy »

run your query as a join and get all the data at one go
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

please post the two tables' structures and which fields "link" to the other.
mhenke
Forum Newbie
Posts: 21
Joined: Thu Jan 27, 2005 2:20 am

Post 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 : (
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

SELECT
  *
FROM
  users, online
WHERE
  users.username = online.username
this performs a short inner join.
mhenke
Forum Newbie
Posts: 21
Joined: Thu Jan 27, 2005 2:20 am

Post 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...
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
Last edited by timvw on Thu Feb 03, 2005 10:47 am, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post 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
mhenke
Forum Newbie
Posts: 21
Joined: Thu Jan 27, 2005 2:20 am

Post 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 : (
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
mhenke
Forum Newbie
Posts: 21
Joined: Thu Jan 27, 2005 2:20 am

Post by mhenke »

THX I will study this too, trying to make code as secure as possible...
mhenke
Forum Newbie
Posts: 21
Joined: Thu Jan 27, 2005 2:20 am

Post 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";
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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
mhenke
Forum Newbie
Posts: 21
Joined: Thu Jan 27, 2005 2:20 am

Post 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?
Post Reply