Jerzxu Posted July 20, 2008 Share Posted July 20, 2008 Good Morning/Afternoon (whichever applies) Okay, I am making a area on my website which users let each other know what they did. (Kinda like facebook's recent friend actions) Anyways, I ran into some problems. You see with the code I have to grab the friends that you have from one table, use them for another tables query to get the actions. So basically: Query Friends -> Use Friends to Query Actions -> Output Recent Actions The code I have is below $friends = mysql_query(sprintf("SELECT `Friend` FROM `Friends` WHERE `Username`='%s' AND `Blocked`='No' AND `Mutual`='Yes'", mysql_real_escape_string($username))); while ($friends = mysql_fetch_array($friends)){ foreach ($friends as $fri){ $actions = mysql_query("SELECT * FROM `Actions` WHERE `Username`='$fri'"); $actionrows = mysql_num_rows($actions); for ($i=0;$i=$actionrows;$i++){ while ($recs = mysql_fetch_array($actions)){ echo $recs['Username']; } } } } The code just gives me a timeout error, but does output the $recs['Username']; It just takes forever to load it. I probably can achieve this some other way, I just don't know exactly where to turn. If you could show me a sign it would help~ Regards, Jerzxu Quote Link to comment Share on other sites More sharing options...
JasonLewis Posted July 20, 2008 Share Posted July 20, 2008 Try something like this: $friends = mysql_query(sprintf("SELECT * FROM `Friends` WHERE `Username`='%s' AND `Blocked`='No' AND `Mutual`='Yes'", mysql_real_escape_string($username))); while($r = mysql_fetch_array($friends)){ $actions = mysql_query("SELECT * FROM `Actions` WHERE `Username`='{$r['Friend']}'"); while ($recs = mysql_fetch_array($actions)){ echo $recs['Username']."<br>"; } } Quote Link to comment Share on other sites More sharing options...
cooldude832 Posted July 20, 2008 Share Posted July 20, 2008 You ever hear of a join query??? Select Friends.*, Actions.* from `Friends` Left join `Actions` ON (Actions.Username = Friends.Friend) Where Friends.Username = '%s' and Friends.Blocked='No' and Friends.Mutual='Yes' GROUP BY Friends.Username Take it into phpmyadmin and play around with it Quote Link to comment Share on other sites More sharing options...
Jerzxu Posted July 20, 2008 Author Share Posted July 20, 2008 Try something like this: $friends = mysql_query(sprintf("SELECT * FROM `Friends` WHERE `Username`='%s' AND `Blocked`='No' AND `Mutual`='Yes'", mysql_real_escape_string($username))); while($r = mysql_fetch_array($friends)){ $actions = mysql_query("SELECT * FROM `Actions` WHERE `Username`='{$r['Friend']}'"); while ($recs = mysql_fetch_array($actions)){ echo $recs['Username']."<br>"; } } That seems to have worked. Thank you! You ever hear of a join query??? Select Friends.*, Actions.* from `Friends` Left join `Actions` ON (Actions.Username = Friends.Friend) Where Friends.Username = '%s' and Friends.Blocked='No' and Friends.Mutual='Yes' GROUP BY Friends.Username Take it into phpmyadmin and play around with it Never have, I will look into that though. THANKS Quote Link to comment Share on other sites More sharing options...
cooldude832 Posted July 20, 2008 Share Posted July 20, 2008 If you have a properly built database advancing your queries to draw off multiple tables will drastically improve your abilities. Also I see you are using fulltext (Yes/No) instead of BOOL variables of (1/0) Why??? Quote Link to comment Share on other sites More sharing options...
Jerzxu Posted July 20, 2008 Author Share Posted July 20, 2008 If you have a properly built database advancing your queries to draw off multiple tables will drastically improve your abilities. Also I see you are using fulltext (Yes/No) instead of BOOL variables of (1/0) Why??? That sir is a good question. Well, helps me remember what its doing, BUT now that I think about it I could probably just put comments to remind me what I am doing. Quote Link to comment Share on other sites More sharing options...
cooldude832 Posted July 20, 2008 Share Posted July 20, 2008 When you site gets amazing and you have more users than facebook saving that extra space and processing time to use a smallint(1) vs a varchar(3) is huge Quote Link to comment Share on other sites More sharing options...
Jerzxu Posted July 20, 2008 Author Share Posted July 20, 2008 When you site gets amazing and you have more users than facebook saving that extra space and processing time to use a smallint(1) vs a varchar(3) is huge That is true. Actually I was trying to debate whether or not to use ID (int 255, auto-inc.) as a primary key in the Actions table as I am going to set up a cronjob to delete 2 day old actions. So the auto-incrementing ID would be used up eventually except it would take ALONG time I would imagine. But I couldn't think of a way around, any thoughts? Quote Link to comment Share on other sites More sharing options...
cooldude832 Posted July 20, 2008 Share Posted July 20, 2008 I make my primarykeys of infinitesimally growing fields BigINT AUTOINCREMENT Storing/comparing integers is far easier than varchars/text/floats because you have 10 characters to use vs the entire ASCII library If u want I can look at your db design and tell you how to improve it. Quote Link to comment Share on other sites More sharing options...
Jerzxu Posted July 20, 2008 Author Share Posted July 20, 2008 Sure just want my email or something??? For contact? Or post it on here??? (prob not best idea) Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.