mysql_fetch_array

(PHP 4, PHP 5)

mysql_fetch_array — Fetch a result row as an associative array, a numeric array, or both

Warning

This extension is deprecated as of PHP 5.5.0, and will be removed in the future.
Instead, the MySQLi or PDO_MySQL extension should be used.
See also MySQL: choosing an API guide and
related FAQ for more information.
Alternatives to this function include:

Parameters

The type of array that is to be fetched. It's a constant and can
take the following values: MYSQL_ASSOC,
MYSQL_NUM, and
MYSQL_BOTH.

Return Values

Returns an array of strings that corresponds to the fetched row, or FALSE
if there are no more rows. The type of returned array depends on
how result_type is defined. By using
MYSQL_BOTH (default), you'll get an array with both
associative and number indices. Using MYSQL_ASSOC, you
only get associative indices (as mysql_fetch_assoc()
works), using MYSQL_NUM, you only get number indices
(as mysql_fetch_row() works).

If two or more columns of the result have the same field names,
the last column will take precedence. To access the other column(s)
of the same name, you must use the numeric index of the column or
make an alias for the column. For aliased columns, you cannot
access the contents with the original column name.

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.

Here's a quicker way to clone a record. Only 3 lines of code instead of 4. But the table must have an auto-incremented id.
I took the code from Tim and altered it. Props to Tim.

<?php
// copy content of the record you wish to clone
$entity = mysql_fetch_array(mysql_query("SELECT * FROM table_name WHERE id='$id_to_be cloned'"), MYSQL_ASSOC) or die("Could not select original record");

// set the auto-incremented id's value to blank. If you forget this step, nothing will work because we can't have two records with the same id
$entity["id"] = "";

// insert cloned copy of the original record
mysql_query("INSERT INTO table_name (".implode(", ",array_keys($entity)).") VALUES ('".implode("', '",array_values($entity))."')");

// if you want the auto-generated id of the new cloned record, do the following
$newid = mysql_insert_id();
?>

If you use implode() with the return value by mysql_fetch_array, if you use MYSQL_BOTH on parameter 2, the result is not really what you're expecting.
For example :
my sql database contains "Amine, Sarah, Mohamed";

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.

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.

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

<?phpmysql_data_seek($result, 0);?>

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

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.

Yes, that will generate a dummy array element containing the false of the final mysql_fetch_array. You should either truncate the array or (more sensibly in my mind) check that the result of mysql_fetch_array is not false before adding it to the array.

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.

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.

Just thought I'd share these helper functions that I use to simplify processing of query results a bit:
<?php
// For a simple query that should return a single value, this returns just that value (or FALSE) so you can process it immediately
function db_result_single($result) {
return ($row = mysql_fetch_row($result)) && isset($row[0]) ? $row[0] : false;
}

Here's a quick way to duplicate or clone a record to the same table using only 4 lines of code:

// first, get the highest id number, so we can calc the new id number for the dupe// second, get the original entity// third, increment the dupe record id to 1 over the max// finally insert the new record - voila - 4 lines!

$id_max = mysql_result(mysql_query("SELECT MAX(id) FROM table_name"),0,0) or die("Could not execute query"); $entity = mysql_fetch_array(mysql_query("SELECT * FROM table." WHERE id='$id_original'),MYSQL_ASSOC) or die("Could not select original record"); // MYSQL_ASSOC forces a purely associative array and blocks twin key dupes, vitally, it brings the keys out so they can be used in line 4$entity["id"]=$id_max+1; mysql_query("INSERT INTO it_pages (".implode(", ",array_keys($Entity)).") VALUES ('".implode("', '",array_values($Entity))."')");

Really struggled in cracking this nut - maybe there's an easier way out there? Thanks to other posters for providing inspiration. Good luck - Tim

mob AT stag DOT ru has a nice function for getting simple arrays from MySQL but it has a serious bug. The MySQL link being set as an argument is NULL when no link is supplied meaning that you're passing NULL to the mysql funcctions as a link, which is wrong. I am not using multitple connections so I removed the link and using the global link. If you want to support multiple links check to see if its set first.

/** to support multiple links add the $link argument to function then* test it before you use the link** if(isset($link))* if($err=mysql_errno($link))return $err;* else* if($err=mysql_errno())return $err;*/

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]