mysql_fetch_array

(PHP 4, PHP 5)

mysql_fetch_array — Retourne une ligne de résultat MySQL sous la forme d'un tableau associatif, d'un tableau indexé, ou les deux

Avertissement

Cette extension était obsolète en PHP 5.5.0, et a été supprimée en PHP 7.0.0. À la place, vous pouvez utiliser l'extension MySQLi ou l'extension PDO_MySQL. Voir aussi MySQL : choisir une API du guide. Alternatives à cette fonction :

Description

mysql_fetch_array(resource $result, int $result_type = MYSQL_BOTH): array

mysql_fetch_array() retourne un tableau qui contient la ligne demandée dans le résultat result et déplace le pointeur de données interne d'un cran.

Liste de paramètres

result: La ressource de résultat qui vient d'être évaluée. Ce résultat vient de l'appel à la fonction mysql_query().
result_type: Le type de tableau à récupérer. C'est une constante qui peut prendre les valeurs suivantes : MYSQL_ASSOC, MYSQL_NUM, et MYSQL_BOTH.

Valeurs de retour

Retourne un tableau de chaînes qui correspond à la ligne récupérée ou false s'il n'y a plus de lignes. Le type de tableau retourné dépend de la définition du paramètre result_type. En utilisant MYSQL_BOTH (défaut), vous récupérerez un tableau contenant des indices associatifs et numériques. En utilisant MYSQL_ASSOC, vous ne récupérerez que les indices associatifs (comme le fonctionnement de la fonction mysql_fetch_assoc()), en utilisant MYSQL_NUM, vous ne récupérerez que les indices numériques (comme le fonctionnement de la fonctionmysql_fetch_row()).

Si plusieurs colonnes portent le même nom, la dernière colonne aura la priorité. Pour accéder aux autres colonnes du même nom, vous devez utiliser l'index numérique, ou faire un alias pour chaque colonne. Pour les alias de colonnes, vous ne pourrez pas accéder aux contenus avec les noms originaux des colonnes.

Exemples

Exemple #1 Requête avec duplication de nom de colonnes

SELECT table1.field AS foo, table2.field AS bar FROM table1, table2

Exemple #2 mysql_fetch_array() avec MYSQL_NUM

<?php
mysql_connect("localhost", "mysql_user", "mysql_password") or
die("Impossible de se connecter : " . mysql_error());
mysql_select_db("mydb");

$result = mysql_query("SELECT id, name FROM mytable");

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
   printf("ID : %s  Nom : %s", $row[0], $row[1]);
}

mysql_free_result($result);
?>

Exemple #3 mysql_fetch_array() avec MYSQL_ASSOC

<?php
mysql_connect("localhost", "mysql_user", "mysql_password") or
die("Impossible de se connecter : " . mysql_error());
mysql_select_db("mydb");

$result = mysql_query("SELECT id, name FROM mytable");

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
   printf("ID : %s  Nom : %s", $row["id"], $row["name"]);
}

mysql_free_result($result);
?>

Exemple #4 mysql_fetch_array() avec MYSQL_BOTH

<?php
mysql_connect("localhost", "mysql_user", "mysql_password") or
die("Impossible de se connecter : " . mysql_error());
mysql_select_db("mydb");

$result = mysql_query("SELECT id, name FROM mytable");

while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
   printf ("ID : %s  Nom : %s", $row[0], $row["name"]);
}

mysql_free_result($result);
?>

Notes

Note: Performance

Une chose importante à noter est que l'utilisation de mysql_fetch_array() n'est pas significativement plus lent que l'utilisation de mysql_fetch_row(), alors qu'il fournit des valeurs significatives ajoutées.

Note: Les noms des champs retournés par cette fonction sont sensibles à la casse.

Note: Cette fonction définit les champs NULL à la valeur PHP null.

Voir aussi

mysql_fetch_row() - Retourne une ligne de résultat MySQL sous la forme d'un tableau
mysql_fetch_assoc() - Lit une ligne de résultat MySQL dans un tableau associatif
mysql_data_seek() - Déplace le pointeur interne de résultat MySQL
mysql_query() - Envoie une requête à un serveur MySQL

Improve This Page

Learn How To Improve This Page • Submit a Pull Request • Report a Bug

＋add a note

User Contributed Notes 12 notes

down

robjohnson at black-hole dot com ¶

22 years ago

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.

down

KingIsulgard ¶

15 years ago

I have found a way to put all results from the select query in an array in one line.

// Read records
$result = mysql_query("SELECT * FROM table;") or die(mysql_error());
    
// Put them in array
for($i = 0; $array[$i] = mysql_fetch_assoc($result); $i++) ;
    
// Delete last empty one
array_pop($array);

You need to delete the last one because this will always be empty.

By this you can easily read the entire table to an array and preserve the keys of the table columns. Very handy.

down

-4

puzbie at facebookanswers dot co dot uk ¶

13 years ago

<?php
 while($r[]=mysql_fetch_array($sql));
 ?>
 
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.

down

-3

info at o08 dot com ¶

15 years ago

As opposite of mysql_fetch_array:



<?php

function mysql_insert_array ($my_table, $my_array) { 

    $keys = array_keys($my_array); 

    $values = array_values($my_array); 

    $sql = 'INSERT INTO ' . $my_table . '(' . implode(',', $keys) . ') VALUES ("' . implode('","', $values) . '")';

    return(mysql_query($sql)); 

}

#http://www.weberdev.com/get_example-4493.html

?>

down

-5

final at skilled dot ch ¶

13 years ago

I ran into troubles with MySQL NULL values when I generated dynamic queries and then had to figure out whether my resultset contained a specific field.

First instict was to use isset() and is_null(), but these function will not behave as you probably expect.

I ended up using array_key_exists, as it was the only function that could tell me whether the key actually existed or not.

<?php
$row = mysql_fetch_assoc(mysql_query("SELECT null as a"));
var_dump($row); //array(1) {  ["a"]=>  NULL }
var_dump(isset($row['a'])); //false
var_dump(isset($row['b'])); //false
var_dump(is_null($row['a'])); //true
var_dump(is_null($row['b'])); //true + throws undefined index notice
var_dump(array_key_exists('a', $row)); // true
var_dump(array_key_exists('b', $row)); // false
?>

down

-4

john at skem9 dot com ¶

18 years ago

my main purpose was to show the fetched array into a table, showing the results side by side instead of underneath each other, and heres what I've come up with. 

just change the $display number to however many columns you would like to have, just dont change the $cols number or you might run into some problems.

<?php
$display = 4;
$cols = 0;
echo "<table>";
while($fetched = mysql_fetch_array($result)){
    if($cols == 0){
        echo "<tr>\n";
    }
    // put what you would like to display within each cell here
    echo "<td>".$fetched['id']."<br />".$fetched['name']."</td>\n";
    $cols++;
    if($cols == $display){
        echo "</tr>\n";
        $cols = 0;
    }
}
// added the following so it would display the correct html
if($cols != $display && $cols != 0){
    $neededtds = $display - $cols;
    for($i=0;$i<$neededtds;$i++){
        echo "<td></td>\n";
    }
     echo "</tr></table>"; 
    } else {
    echo "</table>";
}
?>

Hopefully this will save some of you a lot of searching.

any kind of improvements on this would be awesome!

down

-4

kunky at mail dot berlios dot de ¶

19 years ago

This is very useful when the following query is used:

`SHOW TABLE STATUS`

Different versions of MySQL give different responses to this.

Therefore, it is better to use mysql_fetch_array() because the numeric references given my mysql_fetch_row() give very different results.

down

-5

Anonymous ¶

13 years ago

If I use 

<?php
while($r[]=mysql_fetch_array($sql));
?>

so in array $r is one more entry then rows returned from the database.

down

-5

barbieri at NOSPAMzero dot it ¶

22 years ago

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.

down

-5

joelwan at gmail dot com ¶

19 years ago

Try Php Object Generator: http://www.phpobjectgenerator.com

It's kind of similar to Daogen, which was suggested in one of the comments above, but simpler and easier to use.

Php Object Generator generates the Php Classes for your Php Objects. It also provides the database class so you can focus on more important aspects of your project. Hope this helps.

down

-5

tim at wiltshirewebs dot com ¶

19 years ago

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

down

-5

some at gamepoint dot net ¶

22 years ago

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.

＋add a note