putting mysql query into array and using more than once error

I have a real big problem that normally doesn't happen. For some reason on this occasion it errors...

Ok I put in a mysql query IE

$query = mysql_query("SELECT * from tablename");

Then i say

$row = mysql_fetch_array($query,MYSQL_BOTH)

When I put (even a blank loop in it gives me an error) a loop in... IE

foreach ($row as $rowme){
 // do nothing
   echo $rowme['id'] . "<br />";
}

I get invalid argument supplied for foreach()

Never before has this happened - any ideas why it is now?

Running PHP 5.2.11 on the server

while($row = mysql_fetch_assoc) = works fine, but I can use this only once on the page... another question why can't I use the same loop again somewhere else? (Coldfusion you can use the same loop from a query repeatedly? why does not PHP d开发者_运维问答o this using mysql_fetch_assoc???)

I'm using a jQuery photo gallery, which populates some repeated javascript to write the descriptions on the images via the class and repeats the list items they are in, hence I need two loops of the same query. So obviously putting the query results into an array and using the array repeatedly should work right?

The only other option is to create two queries exactly the same. Seems inefficient however.

Can anyone answer these questions or shed some light. I'm desperate and very very behind putting in this gallery, (why I'm still here at work at 1.22 am) - Would be most grateful.

Thanks

A.

while($row = mysql_fetch_assoc) = works fine, but I can use this only once on the page...

Not so; you could write if ($row = mysql_fetch_assoc(...)) as many times as you like.

However in your case, you probably want to use the "seek" functions that let you go back to the start of the resultset if you want to iterate it again.

I get invalid argument supplied for foreach()

You never checked that $row is an array. If there are no results, $row will be FALSE.

There are two options to your "dilemma". You cannot query the results twice in PHP without telling explicitly.

But a simple option is to store them away into an array:

$query = mysql_query("SELECT * from tablename");
while ($row = mysql_fetch_array($query,MYSQL_BOTH)) {
    $saved[] = $row;
}

This allows you to use the result set twice, and also makes your example foreach construct work:

foreach ($saved as $rowme){
     echo $rowme['id'] . "<br />";

Obviously only advisable if it's few result rows.

The second option is to "rewind" the results. So you can actually query with _fetch_assoc twice:

$query = mysql_query("SELECT * from tablename");

// first time
while ($row = mysql_fetch_array($query,MYSQL_BOTH)) {
    ...
}


// other code here
...


// second iteration over results
mysql_data_seek($query, 0);   // rewind
while ($row = mysql_fetch_array($query,MYSQL_BOTH)) {
    ...
}

See http://php.net/mysql_data_seek in the manual.

If you want an array there it the way that you build the array with your stream.

$data = array();

while($row = mysql_fetch_assoc($res)) {
    $data[] = $row;
}

But when you do this you need a lot of memory and when your array is to big and you come over the the momory_limit which you can define in the php.ini you get an error like

"Fatal error: Allowed memory size of xxxxxxx bytes exh..."

I think you slightly misunderstand what each mysql function does. both the names you've chosen and the way you try to use them show this.

I'll try to show an example with descriptive variable names.

$query = "SELECT * FROM tablename";   //$query is sql query
$res = mysql_query($quwey);           //$res is resource

A resource encapsulates the results of the query. You use mysql_fetch functions to get data out. I'll use mysql_fetch_assoc to get one row at a time but mysql_fetch_array, mysql_fetch_object are fine too. Every time it gets called you get one row. while loops are a good choice for this bc they will fetching and re-assigning the new rows until there are none left.

while($row = mysql_fetch_assoc($res)) //$row is an associative array for single row.  
                                      //one element for every field of table
{
   var_dump($row); //this will show you what $row looks like
}

You can iterate on $row with foreach, it will step thru every element of $row. That is, every field for a single row of the table. This will NOT visit more than one row.

while($row = mysql_fetch_assoc($res)) 
{
   //$row['id'] makes sense
   foreach($row as $fieldName=>$fieldValue)
   {
       echo "$fieldName -> $fieldValue <br />;
       //$fieldName['id'] and $fieldValue['id'] do not make sense. they are not arrays
   }
}

You were concerned that you can only use that while/fetch thing once, but thats not the case. You can reset the resource's pointer back to the top and do the while loop all over again:

 while($row = mysql_fetch_assoc($res)) {//do stuff}
 mysql_data_seek($res, 0); //reset pointer back to top
 while($row = mysql_fetch_assoc($res)) {//do stuff again}    

Or you can store all of the rows into one (multidimensional) array you can work with later as many times as you like.

$arrayOfRows = array();
while($row = mysql_fetch_assoc($res)) //$row is a single row.  one element for every field of table
{
   $arrayOfRows[] = $row;
}

var_dump($arrayOfRows); //see what this looks like

And this is how you can loop thru this $arrayOfRows and use it later.

foreach($arrayOfRows as $oneRow)
{
    foreach($oneRow as $fieldName=>$fieldValue)
    {
         //code here
    }
}

I suspect the error is directly linked to the number of results. From the manual you can see the return type is either an array or FALSE. The latter might give you the error you are describing.

Fix it with either mysql_num_rows() or

if($row){
    foreach ($row as $rowme){
        // do nothing
        echo $rowme['id'] . "<br />";
    }
}