mysql_fetch

add a note User Contributed Notes

m dot s at programmers-online dot net
17-Nov-2005 05:56


The following function to read all data out of a mysql-resultset, is may be faster than Rafaels solution:



<?

function mysql_fetch_all($result) {

   while($row=mysql_fetch_array($result)) {

       $return[] = $row;

   }

   return $return;

}

?>

topera at gmail dot com
04-Aug-2005 09:02


If you want a function like pg_fetch_all(), you may use this...



<?

function mysql_fetch_all($result) {

    $i = 0;

    for ($i=0; $i<mysql_num_rows($result); $i++) {

        $return[$i] = mysql_fetch_array($result);

    }

    return $return;

}

?>



Rafael Pereira dos Santos

programmer at bardware dot de
08-Dec-2004 05:52


Hi,



I repeatedly saw samples where mysql_fetch_row(...) was called if the result of mysql_query(...) was !==false

According to my experiences mysql_fetch_row(...) shouldn too not be called if the result of mysql_query(...) ===true

That is when you call mysql_query(...) with something like "FLUSH QUERY CACHE" etc.

I wrote a script that allows me to monitor multiple MySQL-Servers. I simply select a bunch of queries (SHOW VARIABLES etc.) from a ListBox which are executed after another. I added "FLUSH QUERY CACHE" to the list of queries to execute and got

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource

When calling mysql_fetch_row(...) after the call to mysql_query("FLUSH QUERY CACHE") and only checking the result for boolean false. 



Bernhard

girish dot sonawane at gmail dot com
12-Oct-2004 08:50


Here's a modified version of Jame's set_variables_after_query()

The modified version returns an array of hashes containing all the records...



function set_array_from_query($qry) {



    $arr = array();



    $selection = mysql_query($qry);

    if (!$selection) {

        die('Invalid query: ' . mysql_error());

    }



    if (mysql_num_rows($selection) > 0)

    {

        $x=0;

           // iterate through resultset

           while($row = mysql_fetch_row($selection))

           {

                foreach($row as $i => $value) {

                    $column = mysql_field_name($selection,$i);

                    $data["$column"] = $value;

                    $arr[$x] = $data;

                }

                $x++;



           }

     }

     

   return $arr;

}



//Examples

$array = set_array_from_query("select * from SomeTable");

25-Aug-2004 01:53


"Maybe worth pointing out that all the fields returned by this (and other?) calls are returned with type string."



So is there a more efficient implementation for this:



<?php

$Fields = mysql_fetch_assoc($Result);

$Date = strtotime($Fields["date"]);

?>

wizzkidy at hotmail dot com
09-Mar-2004 02:05


Here's a little code snipped I used to make dynamic HTML even if the table changed its rows. Hope somebody can use it.



<?PHP

#Find all columns in the table



$sql_a = "select * FROM table_a";

$exc_a = mysql_query($sql_a) or die(mysql_error());

$fields = mysql_list_fields($DBname, $DBTableName);

$columns = mysql_num_fields($fields);



for ($i = 0; $i < $columns; $i++){

  $fieldnames[] = mysql_field_name($exc_a, $i);

}



#by referencing the values inserting it in the array



IF($rst_a = mysql_fetch_array($exc_a)){

  $num_fields = count($fieldnames);

  for ($ii = 0; $aantal_fields > $ii; $ii++){

    $fieldValues[$fieldnames[$ii]] = $rst_a[$fieldnames[$ii]];

  }

}

?>

$fieldValues[] = array of column names with their corresponding values, you can even make it multi dimensional by adding $fieldValues[$fieldnames[$ii]][] instead (so if youre wanting to have your complete database table in this array its possible :)

11-Sep-2003 01:22


<?php





function mysql_buffered_query($data_select, $data_connection)


{


  $sql_query = mysql_query($data_select, $data_connection);


  while($tmp = mysql_fetch_row($sql_query))


  {


    $info_elements[]=$tmp;


  }


  return $info_elements;


}





?>

mysql at polyzing dot com
13-Jul-2003 06:05


It is probably worth pointing out that the array elements will actually be of type string, OR NULL if the field is null in the database.



Thus, either use a double equal comparison to look for empty or null



Or, use a triple equal comparison to be able to distinguish the two cases



e.g.



if ($field === '') echo "Empty, not NULL\n";



if ($field === NULL) echo "NULL\n";



if ($field == '') echo "Empty or NULL\n";

dsluchin at ya hoo dot com
15-Jun-2003 12:44


In this code:



   while($sql_fetch_rows = mysql_fetch_row($sql_query)) { 



      $info_offset   = 1;

     $info_columns  = 0;

     while ($info_offset <= $sql_num_fields) {



        $info_elements[$info_rows][$info_columns] = $sql_fetch_rows[$info_columns];

        $info_offset++; $info_columns++;

     }

     $info_rows++;

  }



You're making these harder on yourself than they need to be with the including of the $info_offset variable, which you're always making be ($info_columns + 1).  This is cleaner:



while($sql_fetch_rows = mysql_fetch_row($sql_query)) {

  $info_columns = 0;

  while ($info_columns < $sql_num_fields) {

    $info_elements[$info_rows][$info_columns] = sql_fetch_rows[$info_columns] ;

    $info_colunns++;

  }

  $info_rows++ ;

}

08-May-2003 07:10


Fetch Work Around



As a possible work around to the earlier problem, you could buffer the result of the query into a 2D array. This could be used to similar a table, to an extence and would allow you, to use the results of the query as often as you like in any custom function.



function mysql_buffered_query($data_select, $data_connection) {



   $sql_query      = mysql_query($data_select, $data_connection);

   $sql_num_fields = mysql_num_fields($sql_query);



   $info_rows = 0;



   while($sql_fetch_rows = mysql_fetch_row($sql_query)) { 



       $info_offset   = 1;

      $info_columns  = 0;

      while ($info_offset <= $sql_num_fields) {



         $info_elements[$info_rows][$info_columns] = $sql_fetch_rows[$info_columns];

         $info_offset++; $info_columns++;

      }

      $info_rows++;

   }

   return $info_elements;

}



ACCESSED WITH

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



$sql_table  = "atable";

$sql_where  = 908;

$sql_select = "SELECT * FROM $sql_table WHERE field_id ='$sql_where'";



$myarray = mysql_buffered_query($sql_select, $sql_connection);



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



You can the use $myarray (or whatever u wanna call it) in any array pre-built or custom function.

michael and then an at sign wassupy.com
08-Apr-2003 03:09


to print an array, simply use print_r(array name)



like this:

    $myrow = mysql_fetch_row($result);

echo "<pre>";

print_r($myrow);

echo "</pre>";



this will output the array in a readable form, with the index, too. Don't forget the 'pre' tags or the output will be on a single line.

james at unifiedmind dot com
16-Mar-2003 03:01


function set_variables_after_query($selection) {



  // set variables in the caller's environment from a database query

  // by James Thornton, http://jamesthornton.com



 $row = mysql_fetch_row($selection);

    

  foreach($row as $i => $value) {

    

    $column = mysql_field_name($selection,$i);

     $GLOBALS["$column"] = $value;

    

    }

  

}



// Example: 

$selection = mysql_query($sql) or die(mysql_error());  

set_variables_after_query($selection);

a at simongrant dot org
06-Feb-2002 09:10


Maybe worth pointing out that all the fields returned by this (and other?) calls are returned with type string. This had me puzzled for quite some time.

doubREDHATtme at hotmail dot comNODISTRO
06-Feb-2002 12:07


This took me a few minutes to suss out, so I thought I'd share it:



If your doing a select count(*) statement, the simplest syntax I have come up with to actually get access to the value is:



// stores the result in an array:

$count = mysql_fetch_array(mysql_query($query_string, $db));



// the actual count is stored in the first index (0):

print("row count: $count[0]");



NB I've ignored error checking here :)



Christo Fogelberg

jpaulo at fischer dot med dot br
16-Jun-2001 05:18


Here is a simple and very fast way I use to return results using the foreach construct:



$results = mysql_query($query);

$results_array = mysql_fetch_row($results);

foreach($results_array as $i => $data) {

print "<li>$i: $data";

}



If you don't need the element's key just use:

foreach ($results_array as $data) {

print $data;

}

Regards,

Joao Paulo M. Fischer

php dot net at sury dot cz
07-Jun-2001 02:40


You could also use this language construct with mysql_fetch_row:





while (list($first, $second) = mysql_fetch_row($resource)) {


[...]


}

doug at melonn dot com
13-May-2001 04:57


you can also use mysql_fetch_row() like this if you want to display inumerable results the same way:





#connect to the server and select db


mysql_connect("host","username","pass");


mysql_select_db("dbname");





#query the db


$query = "SELECT * FROM table";


$result = mysql_query($query)


or die(mysql_error());





#display results


while($i = mysql_fetch_row($result)) {


echo $i[0];


echo $i[1];


.....


}





and so forth...

add a note

mysql_fetch_row

说明