anyway to do this loop?without repeating..

[MDanz]

$getrest =  mysql_query("SELECT * FROM block WHERE sid='$id' ORDER BY `id` DESC",$this->connect);

while($row1 = mysql_fetch_assoc($getrest)) {

$idz = $row1['id'];

$getmore =  mysql_query("SELECT * FROM block WHERE sid='$idz' ORDER BY `id` DESC",$this->connect);					
						 while ($row2 = mysql_fetch_assoc($getmore)) {

}

}

 

you see i'm using the result from the first query in the where clause of the second query.  This isn't good practice as what i'm trying to accomplish could lead me doing this up to ten times.  what would be a better way to do this than while loops inside of while loops?

 

 

example?

[KevinM1]

Do you need the outer loop?

[MDanz]

yes.

 

you see the next results 'sid' is the same as the previous results id.

 

e.g.

[id=5][id=95,sid=5][id=59,sid=95] etc

 

i was thinking of doing a self-join but i'm not sure how to implement it

 

 

 

[ManiacDan]

Use a JOIN query instead of nested loops.

 

SELECT b1.* 
FROM block b1
  JOIN block b2 ON b1.sid = b2.sid
WHERE b2.sid = {$id}
ORDER BY b1.`id` DESC

-Dan

[MDanz]

Use a JOIN query instead of nested loops.

 

SELECT b1.* 
FROM block b1
  JOIN block b2 ON b1.sid = b2.sid
WHERE b2.sid = {$id}
ORDER BY b1.`id` DESC

-Dan

 

thanks but this only queries two results, when there is up to 10. when i do mysql_num_rows it only returns two results.

 

e.g. 4 results

id  name sid
1   test1 0
45 test2 1
32 test3 45
12 test4 32

 

 

[Muddy_Funster]

Try using a RIGHT LEFT or a LEFT JOIN.

[PFMaBiSmAd]

If I'm not mistaken, the join condition would need to be -

ON b1.sid = b2.id

[MDanz]

i tried that.. it still only displays 2 results... when there are 10.  i thought this would be pretty easy but i was wrong.  would an array be easier?

[PFMaBiSmAd]

Everything that we have been posting is based on the code in your first post.

 

Perhaps if you showed your database table definition, some data, and what result you expect for that data, someone could directly help. Edit: And no, what you have shown as the expected result so far in the thread is not sufficient to help you.

[ManiacDan]

If I'm not mistaken, the join condition would need to be -

ON b1.sid = b2.id

This is correct, the existence of the poorly named "id" and "sid" fields (as well as filtering by "id" on both queries) got me confused.  The proper query, based on your first code, is:

SELECT b1.*
FROM block b1
  JOIN block b2 ON b1.sid = b2.id
WHERE b2.sid = {$id}
ORDER BY b1.`id` DESC

This is still confusing, and is only a guess based on the queries you pasted.

 

-Dan

[ManiacDan]

Also, this question is database only.  Perform all these queries inside your database software (substituting for $id, obviously) until you get a query that functions the way you expect it to, THEN insert it into your PHP script.  For all we know, something inside your loop (which you haven't shown us) is ruining your output.

 

-Dan