[RESOLVED] query within a query, please help!

bastienvans

I will try to describe my problem as accurately as possible.

This is a simplified version of my query.

$query = "SELECT * FROM listings";
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
$location = $row['location'];
$propertyID = $row['propertyID'];

$query = "SELECT min(day) from rates where propertyid=$propertyID";
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_array($result)) {

echo $row[0];
}}

Table 'rates' resembles to something like this.

PropertyId | Season | Day | Week | Month

1234 | December | 50 | 100 | 1000
1234 | January | 60 | 110 | 1100
1234 | March | 70 | 120 | 1200
1234 | April |80 | 140 | 1300
1234 | June |90 | 150 | 1400
3142 | December |40 | 110 | 1500
3142 | January |20 | 120 | 1300
3142 | March | 90 | 130 | 1400
3142 |April | 40 | 160 | 1600
3142 |June | 50 | 150 | 1800

I need to output all my listings while querying the min(day) for each properties. My current code outputs 1 listing and stops, instead of outputting all my listings, and query min(day) for each.

Thanks for your help. This is driving me nuts.

halojoy

$result
will be the same as $result
if you use same var name for 2 different query results
then the other will mess up the first one

$a = 2;
$a = 1;
echo $a;
and the value of '2' will be lost, destroyed

So, use $result2 for the second time query result

<?php

$query = "SELECT * FROM listings";
$result = mysql_query($query) or die(mysql_error());

while ($row = mysql_fetch_array($result)) {
	//loop result1
	$location = $row['location'];
	$propertyID = $row['propertyID'];

$query = "SELECT min(day) from rates where propertyid=$propertyID";
// result number 2
$result2  = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_array( $result2 )) {
	// loop result2	
	echo $row[0];
}

}
?>

bastienvans

thank you mate, can't believe I missed that!

cheers

igorek

its because of the pointer correct? while loop relies on the $result as being the pointer on which row in the recordset comes next $row = mysql_fetch_array($result); and if $result is reset it looses its position in the array.

halojoy

igorek;10946047 wrote:
its because of the pointer correct? while loop relies on the $result as being the pointer on which row in the recordset comes next $row = mysql_fetch_array($result); and if $result is reset it looses its position in the array.

Yes, you are right, igorek
But not only the pointer position is lost,
but the very $result itself.
If we overwrite any $variable with a new contents for $variable
The previous is lost. ALL OF IT!

In this case it is just like a simple standard loop in loop thing
where we use $i for outer loop and $j for inner loop

for($i=0; $i<5; $i++)
{
    for($j=0; $j<5; $j++)
    {
        echo $i.' - '.$j .'<br />';
    }
}

will work nice and well as we want. No loop interfere with the other.

.... but I do not even want to bother with trying this
:eek::eek
but I can imagine some interesting results or ERRORS

for($i=0; $i<5; $i++)
{
    for($i=0; $i<5; $i++)
    {
        echo $i.' - '.$i .'<br />';
    }
}

igorek

mysql_fetch_assoc(); i think is better cuz man that 0 got me off track. i mean you are using implicit grouping with the min(day), we only getting back 1 row and 1 field of data might as will call it by name of field in database otherwise might get lost later on.

while($row_rsrates = mysql_fetch_assoc($result)){
 echo($row_rsrates['day']);
}

easier on readability no?