mysql help for multiple queries

jasondavis

Ok sorry if this is confusing, what my code below does is it is on a social networking site. It gets a list of users ID numbers that I am friends with, it then takes that list and checks it against a bulletin table and fetches every bulletin that is from one of the friend ID's in my list. Once it fetches the bulletinID it has to cycle trough all of them and fetch a user's photo url and other data. I am sure there has to be a better way to do this with joins maybe? I have been trying to figure a better query for a long time now but I am not very good, any help would be very appreciated

<?PHP
//find who I am friends with, this can return several thousand friend user ID's
____________________________

function findfriend() {
	$sql_friend = "select friendid from friend_friend where userid=" .$_SESSION['info']['auto_id'] . " and status=1";
	$result_friend = executeQuery($sql_friend);
	while ($line_friend = mysql_fetch_assoc($result_friend)) {
		$friend[] = $line_friend['friendid'];
	}
//	$cnt = count($friend);
//	if ($cnt > 0) {
//		$str_friends_ids = implode(",", $friend);
//	} else {
//		$str_friends_ids = '';
//	}
//	return $str_friends_ids;
return((sizeof($friend) > 0) ? implode(',', $friend) : ''); 
}


-----------------------------

// gets string of friend ID's like 1,2,3,4,5
$str_friend_ids=findfriend();


// get total number of results
$reccnt = mysql_result(mysql_query("SELECT COUNT(uid) FROM friend_updates where (uid=$userid or uid in ($str_friend_ids))"),0);



//use the result from above for this query
//build query
$sql ="select id,uid,type,typeid,privacy,date from friend_updates where (uid=$userid or uid in ($str_friend_ids)) order by date DESC limit 20";
$result= executequery($sql);


-----------------------------

// now with the results from the above we loop through them and run 2 other querys, 1 to get the bulletin data and 1 to get a users name and photo url

while($row=mysql_fetch_assoc($result)){

$sql = 'select subject from friend_bulletin where auto_id=' .$row['typeid']. ' limit 1';
	$result_blog = executequery($sql);
	while ($line_b = mysql_fetch_assoc($result_blog)) {



//another query here to get users photo and username

$sql_buname = 'select auto_id,disp_name,pic_url from friend_reg_user where auto_id=' .$row['uid'];
$result_check = executeQuery($sql_buname);

//end while statement

}

?>

Weedpacket

If you're making queries solely for the sake of getting values to use in other queries then it is almost certain that you can do the job with a single query. And it is almost certain that if you're using a query inside a loop then you can do it with one query without the loop.

Select b.subject as subject, r.auto_id as auto_id, r.disp_name as disp_name, r.pic_url as pic_url
From friend_bulletin b, friend_reg_user r, friend_updates u,
Where
    b.auto_id = r.auto_id
and b.auto_id = u.typeid
and (u.uid = $userid or u.uid in ($str_friend_ids))
Order by u.date desc
Limit 20

Something vaguely along those lines anyway. Suffice it to say that you can use more than one table at a time in a query.

jasondavis

Thank you for taking the time to post that. I am trying to give it a try, I just manually inserted a few friedn ID's to test so it looks like this

SELECT b.user_id AS user_id, b.subject AS subject, r.auto_id AS auto_id, r.disp_name AS disp_name, r.pic_url AS pic_url
FROM friend_bulletin b, friend_reg_user r, friend_updates u, 
WHERE b.auto_id = r.auto_id
AND b.auto_id = u.typeid
AND (
u.uid =  "1"
OR uid
IN ( 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 34, 54, 675, 3243 )
)

I am getting this error though can you help

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Where b.auto_id = r.auto_id and b.auto_id = u.typeid and (u.uid ="1" or u' at line 3

1 more thing, my friend_friend table which keeps track of who is who's friend on the network currently has around 600,000 rows so this Query has to go through all of them, and the string of friedn ID's which I have posted above only has 16 friedn ID's, in reality this string can consist with up to 10,000 user ID's if the user has 10,000 friends on the site. So you can see improving performance is a must to make this site work. Now that I have told you about the friend table, do you think that using the

uid
IN ( 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 34, 54, 675, 3243, up to 10,000 numbers here

Is this still a good method?

Thank you so much for the help this site has been an amazing learning experience while trying to keep performance up to par with the large userbase of the site

Weedpacket;10920375 wrote:
If you're making queries solely for the sake of getting values to use in other queries then it is almost certain that you can do the job with a single query. And it is almost certain that if you're using a query inside a loop then you can do it with one query without the loop.
Select b.subject as subject, r.auto_id as auto_id, r.disp_name as disp_name, r.pic_url as pic_url
From friend_bulletin b, friend_reg_user r, friend_updates u,
Where
    b.auto_id = r.auto_id
and b.auto_id = u.typeid
and (u.uid = $userid or u.uid in ($str_friend_ids))
Order by u.date desc
Limit 20
Something vaguely along those lines anyway. Suffice it to say that you can use more than one table at a time in a query.

Bjom

first of all you need a JOIN and that works like this:

SELECT ... FROM t1 INNER JOIN t2 ON t1.id = t2.id INNER JOIN ....

Next there is one excess comma in front of WHERE

Have a look here

Randy_Jackson

Correct me if I'm wrong, but I believe that in your IN clause, there should not be any spaces between the arguments.

laserlight

Randy Jackson wrote:
Correct me if I'm wrong, but I believe that in your IN clause, there should not be any spaces between the arguments.

The spaces are not significant in that context.