[SOLVED] PHP Multiloop Problem

[Jerzxu]

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

[JasonLewis]

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>";
	       }
  }

[cooldude832]

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

[Jerzxu]

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

[cooldude832]

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

[Jerzxu]

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.

[cooldude832]

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

[Jerzxu]

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?

[cooldude832]

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.

[Jerzxu]

Sure just want my email or something???

For contact?

 

Or post it on here??? (prob not best idea)