PHP: mysql_fetch_array

User Contributed Notes
mysql_fetch_array

romans at servidor dot unam dot mx
13-May-2005 12:31


Regarding duplicated field names in queries, I wanted some way to retrieve rows without having to use alias, so I wrote this class that returns rows as 2d-arrays



<?

  $field = $drow['table']['column'];

?>



Here is the code:



<?

  class mysql_resultset

  {

    var $results, $map;



    function mysql_resultset($results)

    {

      $this->results = $results;

      $this->map = array();



      $index = 0;

      while ($column = mysql_fetch_field($results))

      {

        $this->map[$index++] = array($column->table, $column->name);

      }

    }



    function fetch()

    {

      if ($row = mysql_fetch_row($this->results))

      {

        $drow = array();



        foreach ($row as $index => $field)

        {

          list($table, $column) = $this->map[$index];

          $drow[$table][$column] = $row[$index];

        }



        return $drow;

      }

      else

        return false;

    }

  }

?>



The class is initialized with a mysql_query result:



<?

  $resultset = new mysql_resultset(mysql_query($sql));

?>



The constructor builds an array that maps each field index to a ($table, $column) array so we can use mysql_fetch_row and access field values by index in the fetch() method. This method then uses the map to build up the 2d-array.



An example:



<?

  $sql =

    "select orders.*, clients.*, productos.* ".

    "from orders, clients, products ".

    "where join conditions";



  $resultset = new mysql_resultset(mysql_query($sql));



  while ($drow = $resultset->fetch())

  {

    echo 'No.: '.$drow['orders']['number'].'<br>';

    echo 'Client: '.$drow['clients']['name'].'<br>';

    echo 'Product: '.$drow['products']['name'].'<br>';

  }

?>



I hope others find this useful as it has been to me.

mob AT stag DOT ru
24-Jan-2005 01:16


I wrote some utility functions to improve usability and readability, and use them everywhere in my code. I suppose they can help.



function mysql_fetch_all($query,$MySQL=NULL){

 $r=@mysql_query($query,$MySQL);

 if($err=mysql_errno($MySQL))return $err;

 if(@mysql_num_rows($r))

  while($row=mysql_fetch_array($r))$result[]=$row;

 return $result;

}

function mysql_fetch_one($query,$MySQL=NULL){

 $r=@mysql_query($query,$MySQL);

 if($err=mysql_errno($MySQL))return $err;

 if(@mysql_num_rows($r))

 return mysql_fetch_array($r);

}



Example use:

if(is_array($rows=mysql_fetch_all("select * from sometable",$MySQL))){

 //do something

}else{

 if(!is_null($rows)) die("Query failed!");

}

joey at clean dot q7 dot com
18-Apr-2004 09:47


The issue of NULL fields seems to not be an issue anymore (as of 4.2.2 at least).  mysql_fetch_* now seems to fully populate the array and put in entries with values of NULL when that is what the database returned.  This is certainly the behaviour I expected, so I was concerned when i saw the notes here, but testing shows it does work the way I expected.

Ben
06-Apr-2004 01:59


One of the most common mistakes that people make with this function, when using it multiple times in one script, is that they forget to use the mysql_data_seek() function to reset the internal data pointer.



When iterating through an array of MySQL results, e.g.



<?php

while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {

    foreach ($line as $col_value) {

        echo $col_value . '<br />';

    }

}

?>



the internal data pointer for the array is advanced, incrementally, until there are no more elements left in the array. So, basically, if you copy/pasted the above code into a script TWICE, the second copy would not create any output. The reason is because the data pointer has been advanced to the end of the $line array and returned FALSE upon doing so.



If, for some reason, you wanted to interate through the array a second time, perhaps grabbing a different piece of data from the same result set, you would have to make sure you call



<?php

mysql_data_seek($result, 0);

?>



This function resets the pointer and you can re-iterate through the $line array, again!

sigit at djpkpd dot go dot id, harris at djpkpd dot go dot id
25-Mar-2003 12:28


if you have to use the field name with number like 1,2,..etc, it cause a problem when you fetch it with mysql_fetch_array.

An index array will contain a field name. 

The solusion is:

1. Use mysql_fetch_assoc to escape the result to html;

2. Use alias and choose another name of field in mysql_query

loskutak at users dot sourceforge dot net
18-Mar-2003 07:10


Following technique I am using when selecting from more tables with duplicates column names in result.

As mentioned in manual mysql_fetch_array does not support fetching these duplicate column names in associative array, even they are referenced by table name. There is also suggested to use "col AS name" technique to solve that behavior.

So instead of:

"select table1.id, table1.text, table2.id, table2.text 

from table1, table2

where table1.id=table2.id"



I am using:

"select table1.id as 'table1.id', table1.text as 'table1.text', table2.id as 'table2.id', table2.text as 'table2.text'

from table1, table2

where table1.id=table2.id"



And now mysql_fetch_array works well.

hanskrentel at yahoo dot de
08-Jan-2003 03:25


for the problem with fields containing null values in an associated array, feel free to use this function. i've got no more problems with it, just drop it in your script:



/*

*    mysql_fetch_array_nullsafe

*

*

*    get a result row as an enumerated and associated array

*    ! nullsafe !

*

*    parameter:    $result

*                    $result:    valid db result id

*

*    returns:    array | false (mysql:if there are any more rows)

*

*/

function mysql_fetch_array_nullsafe($result) {

    $ret=array();



    $num = mysql_num_fields($result);

    if ($num==0) return $ret;



    $fval = mysql_fetch_row ($result);

     if ($fval === false) return false;



    $i=0;

     while($i<$num)

        {

            $fname[$i] = mysql_field_name($result,$i);            

            $ret[$i] = $fval[$i];            // enum

            $ret[''.$fname[$i].''] = $fval[$i];    // assoc

            $i++;

        }



    return $ret;

}

juancri at tagnet dot org
12-Nov-2002 03:41


An example with mysql_fetch_array():



    $result = mysql_query("SELECT name FROM table WHERE id=8");

    $array = mysql_fetch_array($result);



$array will be:



    array ([0] => "John", ['name'] => "John")



Then you can access to the results:



    echo "The name is " . $array[0];

    // or

    echo "The name is " . $array['name'];



But the array is not referential. $array[0] is not a reference to $array['name'] or $array['name'] to $array[0], they are not relationed between. Because of that, the system will use excesive memory. With large columns, try to use mysql_fetch_assoc() or mysql_fetch_row() only.

dkantha at yahoo dot com
10-Nov-2002 06:27


I did find 'jb at stormvision's' code useful above, but instead of the number of rows you need the number of fields; otherwise you get an error. 



So, it should read like the following: 



$result=mysql_query("select * from mydata order by 'id'")or die('died');

$num_fields = mysql_num_fields($result); 

$j=0;

$x=1;

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

  for($j=0;$j<$num_fields;$j++){

   $name = mysql_field_name($result, $j);

   $object[$x][$name]=$row[$name];

  }$x++;

}



For Later in the script you may use the below array to gain access to your data



$i=1;

$ii=count($object);        //quick access function

for($i=1;$i<=$ii;$i++){

echo $object[$i]['your_field_name'];

}



I have tested this in my apps and it works great! :-)

glenn dot hoeppner at yakhair dot com
09-Oct-2002 04:04


Just another workaround for columns with duplicate names...



Modify your SQL to use the AS keyword.



Instead of:

$sql = "SELECT t1.cA, t2.cA FROM t1, t2 WHERE t1.cA = t2.cA";

 

Try:

$sql = "SELECT t1.cA AS foo1, t2.cA AS foo2 FROM t1, t2 WHERE t1.cA = t2.cA";



Then you can reference the results by name in the array:

  $row[foo1], $row[foo2]

tslukka at cc dot hut dot fi
25-Sep-2002 04:20


If you think MySQL (or other) database 

handling is difficult and requires lot's of 

code, I recommend that you try http://titaniclinux.net/daogen/



DaoGen is a program source code generator 

that supports PHP and Java. It makes database

programming quick and easy. Generated sources

are released under GPL.

robjohnson at black-hole dot com
14-Jun-2002 05:22


Benchmark on a table with 38567 rows:



mysql_fetch_array

MYSQL_BOTH: 6.01940000057 secs

MYSQL_NUM: 3.22173595428 secs 

MYSQL_ASSOC: 3.92950594425 secs 



mysql_fetch_row: 2.35096800327 secs 

mysql_fetch_assoc: 2.92349803448 secs 



As you can see, it's twice as effecient to fetch either an array or a hash, rather than getting both.  it's even faster to use fetch_row rather than passing fetch_array MYSQL_NUM, or fetch_assoc rather than fetch_array MYSQL_ASSOC.  Don't fetch BOTH unless you really need them, and most of the time you don't.

barbieri at NOSPAMzero dot it
31-May-2002 08:21


Here is a suggestion to workaround the problem of NULL values:



// get associative array, with NULL values set

$record = mysql_fetch_array($queryID,MYSQL_ASSOC);



// set number indices

if(is_array($record))

{

    $i = 0;

    foreach($record as $element)

        $record[$i++] = $element;

}



This way you can access $result array as usual, having NULL fields set.

mjm at porter dot appstate dot edu
12-Mar-2002 09:48


If you perform a SELECT query which returns different columns with duplicate names, like this:





--------


$sql_statement = "SELECT tbl1.colA, tbl2.colA FROM tbl1 LEFT JOIN tbl2 ON tbl1.colC = tbl2.colC";





$result = mysql_query($sql_statement, $handle);





$row = mysql_fetch_array($result);


--------





Then





$row[0] is equivalent to $row["colA"]





but





$row[1] is not equivalent to $row["colA"].





Moral of the story: You must use the numerical index on the result row arrays if column names are not unique, even if they come from different tables within a JOIN. This would render mysql_fetch_assoc() useless.





[Ed. note - or you could do the usual 'select tbl1.colA as somename, tbl2.colA as someothername. . .' which would obviate the problem. -- Torben]

techquest at onebox dot com
21-Jan-2002 07:19


Hi to enumerate a result set with out worrying about null values just use below code.





 while($myrow = mysql_fetch_row($result))


 {


 print " <tr> ";


  for($x=0; $x <= count($myrow); $x++)


  {


   print "<td>myrow[$x]</td>";


  }


 print "</tr>";


}





[ed note: tidied code so it actually works]

some at gamepoint dot net
10-Jan-2002 01:13


I never had so much trouble with null fields but it's to my understanding that extract only works as expected when using an associative array only, which is the case with mysql_fetch_assoc() as used in the previous note.



However a mysql_fetch_array will return field values with both the numerical and associative keys, the numerical ones being those extract() can't handle very well.

You can prevent that by calling mysql_fetch_array($result,MYSQL_ASSOC) which will return the same result as mysql_fetch_assoc and is extract() friendly.

25-Jun-2001 12:54


actually with php 4.0.5 mysql_fetch_assoc DOES set NULL fields, so you can safely overwrite existing values with 





$result = mysql_query($some_query);


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


extract($row);


// ... 


}





extract will do the job of walking through the associative array and setting the variables $$key to $value for you even when $value is NULL





mysql_fetch_row also consistently retrieves NULL values with the correct indices





Ivan

jpeterso at moody dot edu
14-Jun-2001 01:26


The code above is great to write the variable's one time and refer to them when you need to then loop through the next item.  





In a controled environment, I used this code in a for loop to create columns.  You still need to put it in a loop for multiple items.  





$ary = mysql_fetch_assoc($query_variable);


if(is_array($ary)){


while (list($key,$val) = each($ary)) { 


            $$key = $val; 


} 


} else {


  break 1;


  }





I continuely got errors without the else break clause.  This breaks me out of my for loop.  Hope it helps.

chuck at eric dot uoregon dot edu
01-May-2001 05:53


An easier way to accomplish what Jean.Francois did with his code is to use the mysql_fetch_assoc() function to retrieve the associative array and feed this into a while loop:





<?


. . .


$q_result = mysql_query($select_stmt);


while ($ary = mysql_fetch_assoc($q_result)) {


  while (list($key,$val) = each($ary)) {


    $$key = $val;


  }


  #  You can now use your column names


  #    as variables.


}


. . .


?>





The above approach is suggested by Rasmus Lerdorf in the PHP Pocket Reference.  It eliminates a query to the database to retrieve column names (SHOW COLUMNS) and makes prettier code, IMO.





I might add that the (mis)behavior of mysql_fetch_array() in not producing column name hash elements had my stymied for a while.  I find it more intuitive to use column names rather than indices to access field data.  Frankly, I don't see the rationale for not producing column name hash elements when the value is NULL.  Luckily, mysql_fetch_assoc() exists, making my life easier and my code more readily understood.

Robert dot C dot Haskell at rolls-royce dot com
17-Apr-2001 07:09


I'm running php 4.04pl1 - As mentioned in the previous notes, when using the default result_type parameter MYSQL_BOTH, any array element that has a NULL value can only be accessed through the numeric indicies.





If I use the MYSQL_ASSOC result_type, then all of the array elements, including those with NULL values, can be accessed by the name indicies.

sean at kungfudesign dot com
19-Jan-2001 02:18


If NULL fields are not returned then the hash will not be set. You can just check it like so:





if (isset($row["FirstName"})) {





     $first_name = $row["FirstName"];





} else {





     // FirstName is NULL


     ...





}

iblden at yahoo dot com
18-Jan-2001 12:17


I also found that if the first record in my query contained NULL values in the cooresponding cells, "nothing" is saved to the variables being used in the loop.  That is, the NULL value is stored in the variable used to present the column's value.  If the first record contains NULL then (as mentioned in a previous post) as the loop continues, the variables associated with the next row will also be set to NULL.  So, if your Select statement returns 79 records and the first record contains all NULL values, You will be presented with a "blank" table when you actually expect 79 records posted.





hmmm.  i think i represented that correcty.





So how do i test for NULL values in this While loop --->





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


             $first_name = $row["FirstName"];


             $last_name = $row["LastName"];


             $home_phone = $row["HomePhone"];


             $email = $row["EmailName"]; 





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





iblden.

dhosek at quixote dot com
02-Jan-2001 04:29


The solution to the extract(mysql_fetch_array()) problem is to simply initialize all the variables to 0 or "" before the extract (or use unset if you really want to preserve NULL-ness).

arlo at typea dot net
30-Nov-2000 03:47


The behavior Sasha describes becomes problematic if your mysql_fetch_array() is followed by extract() in a loop that writes out results from a query. If a field value is null, then extract() won't recreate the corresponding variable, and the variable will remain set to the value from the previous row. This gave some unexpected results until I figured out what was going on.





I'll post a workaround here if I can figure out a good one.





Arlo Leach


www.typea.net

marcin dot wachocki at wp dot pl
21-Nov-2000 07:57


Alright, but since you can use the column number to reach it, it gets kinda problem, doesn't it. I was expecting, that a set returned as a result of "SELECT *" query will always consist of a constant number of fields.


Now I know I was wrong, but I know it only accidentally.

siamy at hotmail dot com
19-Nov-2000 10:09


Well said, but it doesn't really pose a big problem, as you can use the field names to reference data.

sasha at mathforum dot com
03-Sep-1999 09:03


Note that mysql_fetch_* will not set any columns whose value is NULL.  so, if you have a row with 10 columns, and 5 of them are NULL, then mysql_fetch_row/array will return 5-element


array (mysql_fetch_array being a special case)!





This is very misleading if you expect the number


of elements in the array returned by mysql_fetch_row to always match the number of columns in your table.





(Ed. Note: many people will vehemently argue that using NULLs in the first place is a bad idea.)

mysql_fetch_array

Description

Parameters

Return Values

Examples

Notes

See Also