need query construction help

[jasondavis]

First bit of code is my current method to build a list of 20 user pictures

<?PHP
//this methos has 21 queries

//makes DB connection and includes all needed functions
require_once "../config/functions.inc.php";

//show 20 users who are online
$sql = 'SELECT userid FROM friend_online WHERE g="' .$g. '" ORDER BY userid ASC LIMIT 0,20';
$result= executequery($sql);

//loop through 20 results from above
while($line=mysql_fetch_array($result)){
	//show user pictures
	//this function makes new query to get picture URL	
	show_user_photo($line['userid'],$photo_type='small',$user_name='yes');
}
?>

Below is the new method I am working on but im stuck and need soem help please

<?PHP
//this methos would have only 2 queries i think

require_once "../config/functions.inc.php";
//show 20 users who are online
$sql = 'SELECT userid FROM friend_online WHERE g="' .$g. '" ORDER BY userid ASC LIMIT 0,20';
$result= executequery($sql);

//instead of looping through and making 20 more queries to get pictures let do it in 1 query
//the numbers at the end should be from the first query above
$sql = 'SELECT * FROM friend_reg_user WHERE auto_id in(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20)';
$result= executequery($sql);

//do a while statement or what here to show the picture urls in there own little table?

?>

[nemonoman]

Your 2 sql queries would (probably) select different rows. Other than that, there's no differences. You still need to loop to get the records. This loop should be very fast, however. I'd very much guess that any slowness you are seeing is in DISPLAY rather than fetching the rows.

[Kudose]

Just a thought:

SELECT info FROM table WHERE id BETWEEN 1 AND 20

[nemonoman]

In PHP/MySQL there is no documented way to get the information from a query that returns multiple records other than by looping through mysql_fetch...

[dkruythoff]

Just to be sure it's clear: The very first example seems fine to me, and it only executes one query, which returns 20 results.
Like nemonoman said, you need to loop through all 20 to read them.

[Kudose]

I only posted because I feel my query is cleaner than limiting 0,20 and using IN (1,2,3...)

I wasn't implying that it would return 20 records without a loop.

[jasondavis]

Hi I will post my code now, Im not gonna argue about it looping or not but I can say it went from like 21 queries to like 2 queries for same result and increased page load time 5x

<?PHP
require_once "../config/functions.inc.php";
//show 20 users who are online
$sql = 'SELECT userid FROM friend_online WHERE g="' .$g. '" ORDER BY userid ASC LIMIT 0,20';
$result= executequery($sql);

$ids = array();
while($id = mysql_fetch_assoc($result)){
	$ids[] = $id['userid'];
}
$in_statement = '';
if(count($ids) > 0){
	$in_statement = implode(',', $ids);
}

//instead of looping through and making 20 more queries to get pictures let do it in 1 query
$sql = 'SELECT auto_id,disp_name,online,pic_url FROM friend_reg_user WHERE auto_id in('.$in_statement.')';
$result2= executequery($sql);
while($line = mysql_fetch_array($result2)) { 
	show_pictures here();
}
?>

[laserlight]

Im not gonna argue about it looping or not but I can say it went from like 21 queries to like 2 queries for same result and increased page load time 5x

As in you compared it to your original code that involved running queries in a loop, and you found it slower by 5 times? That is strange.

If you can use subqueries, I suggest:

<?php
require_once "../config/functions.inc.php";

$sql = "SELECT auto_id, disp_name, online, pic_url
        FROM friend_reg_user
        WHERE auto_id IN (SELECT userid FROM friend_online WHERE g='$g' LIMIT 0, 20)";

$result = executequery($sql);

while ($row = mysql_fetch_assoc($result)) {
    show_pictures_here();
}

?>