1 SQL statement - Can you have 2 while statements?

[TheDeaner]

----------------------------------
$sql_statement = "SELECT id, price, title, date, FROM table where date = \"2004/12/7\"";
$result = mysql_query($sql_statement) or die(mysql_error());

while($abc = mysql_fetch_array( $result ))

{
$id = $abcd[0];
$price = $abc[1];
$title = $abc[2];
$date = $abc2[3];

echo "$title";
}

// I want to run another while statement here

while($bob2 = mysql_fetch_array( $result ))

{
echo "Now I will list $id";
}

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


Let's say I run a SQL statement to pull pictures and titles of movies from a data base. I want the pictures to cycle through first with a while statement. When all the pictures cycle through I want to list the titles with price or whatever afterward (IE: Now I will list $id from above). I don't want to have another SQL statement if possible. Does anyone know if this is possible and how it can be done?

Thanks
Dean

 

[DRJ478]

Some hints:

1. I recommend that you use the mysql_fetch_assoc() to retrieve the data from the query result set. It gives you an associative array that is keyed by the column name. No need to do the $abc thing and re-assigning the content to named vars. That's kind of wasteful and superfluos.

2. You can rewind the resource set with mysqL-data_seek(0) and do another while loop. However, that's not efficient either.

Think about a way to capture the desired output in a variable that is constructed as you iterate the original set. Output that accumulated string later.

 

[Bastien]

store the values into array..then you can cycle thru the arrays as you like...

are the images stored in the db?

Bastien

Cat, the other other white meat

 

[TheDeaner]

DRJ478,

Let's say you had the list of all the presidents in a database with their date of birth and height.

The first SQL Statement would ask for all presidents and list their names alphabetically so they show up on the left column on a webpage.

The second SQL statement in the middle of the page would ask to order the presidents and then list their height.

You want to cycle through the presidents names in both cases but don't want to use another SQL statement. It sounds like the data_seek(0) thing would work but I am not sure how to format it. I did a little test and it didn't work.

Thanks
Dean

 

[TheDeaner]

Bastien,

The images are stores as URLs in the database. I can cycle thru the arrays, but I want to do it more than once.

Thanks
Dean

 

[DRJ478]

If you need to resort all records of the result set it seems the best to build an associative array which holds all the data. Then you have all the array functions for sorting etc at your disposal.

while ($presidents[] = mysql_fetch_assoc);

I have never tried the above statement, but theoretically it should work.

 

[DRJ478]

I strongly dislike wireless keyboards. This one makes me loose brackets.
mysql_fetch_assoc() needs the brackets.

 

[ingresman]

Create yourself a class which has all the data you want,
create an array of objects with the class and populate the objects as you loop through the result set.
One of the good things of oo programming is you don't have loads or arrays each linked by an index.