PHPのお勉強！

PHP TOP
PHP 8.4.2 Released!

    mysql_fetch_array

    (PHP 4, PHP 5)

    mysql_fetch_array — 連想配列、添字配列、またはその両方として結果の行を取得する

    警告

    この拡張モジュールは PHP 5.5.0 で非推奨になり、PHP 7.0.0 で削除されました。 MySQLi あるいは PDO_MySQL を使うべきです。詳細な情報は MySQL: API の選択 を参照ください。 この関数の代替として、これらが使えます。

    説明

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

    取得した行に対応する配列を返し、内部のデータポインタを前に進めます。

    パラメータ

    result

    評価された結果 リソース。この結果は、mysql_query() のコールにより得られたものです。

    result_type

    取得する配列の形式です。以下の定数値をとります。: MYSQL_ASSOC, MYSQL_NUM, そして MYSQL_BOTH

    戻り値

    取得した行をあらわす文字列の配列を返します。もし行が存在しない場合は false を返します。返される配列の形式は、result_type がどのように指定されているかによります。MYSQL_BOTH（デフォルト） を利用すると、連想添字と数値添字を共に持つ配列を取得します。 MYSQL_ASSOC を利用すると（ mysql_fetch_assoc() の動作と同様に）連想添字のみが取得され、 MYSQL_NUM を利用すると （mysql_fetch_row() の動作と同様に）数値添字のみが 取得されます。

    結果の中で同じフィールド名のカラムが 2 つ以上ある場合、 最後のカラムが優先されます。 同名の他のカラムにアクセスするには、そのカラムの数値インデックスを 使うかまたはカラムの別名を定義する必要があります。 カラムの別名を定義した場合は、本来の列名でそのカラムにアクセスすることは できません。

    例

    例1 重複した列名に対して別名を定義する問い合わせ

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

    例2 mysql_fetch_array() を MYSQL_NUM とともに利用する

    <?php
    mysql_connect    ("localhost", "mysql_user", "mysql_password") or
    die(    "Could not connect: " . 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 Name: %s", $row[0], $row[1]);
    }

    mysql_free_result($result);
    ?>

    例3 mysql_fetch_array() を MYSQL_ASSOC とともに利用する

    <?php
    mysql_connect    ("localhost", "mysql_user", "mysql_password") or
    die(    "Could not connect: " . 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 Name: %s", $row["id"], $row["name"]);
    }

    mysql_free_result($result);
    ?>

    例4 mysql_fetch_array() を MYSQL_BOTH とともに利用する

    <?php
    mysql_connect    ("localhost", "mysql_user", "mysql_password") or
    die(    "Could not connect: " . 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 Name: %s", $row[0], $row["name"]);
    }

    mysql_free_result($result);
    ?>

    注意

    注意: パフォーマンス

    特筆すべき点として、mysql_fetch_array() が 著しい付加価値があるにもかかわらず、 mysql_fetch_row()より それほど遅くはないということが言えます。

    注意: この関数により返されるフィー ルド名は 大文字小文字を区別 します。

    注意: この関数は、 NULL フィールドに PHPの null 値を設定します。

    参考

    Found A Problem?

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

    User Contributed Notes 12 notes

    up
    17
    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.
    up
    8
    KingIsulgard
    16 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.
    up
    -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.
    up
    -3
    info at o08 dot com
    16 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
    ?>
    up
    -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
    ?>
    up
    -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!
    up
    -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.
    up
    -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.
    up
    -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.
    up
    -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.
    up
    -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
    up
    -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