mysql result fetching comparison using php

Question

Which one is better to fetch data from mysql database using PHP and why?

mysql_fetch_row() 
mysql_fetch_assoc() 
mysql_fetch_array()

Answer 1

Hi,

Actually, each of them has a different method of returning data, so the question is more like "wich one is more suitable for your needs".

mysql_fetch_row() returns arrays like this :

$row[0];

mysql_fetch_assoc() :

$row["table_field"];

mysql_fetch_array() :

$row[0];
// or
$row["table_field"];

The "heaviest" here is perhaps the fetch_array, since it accepts an optional parameter for you to specify if you want your data returned as an associative or numeric-key array.

There's also this one :

mysql_fetch_object() :

$row->table_field;

Personally, I'd use them all according to my needs in every query, but if you work with OOP in php, this last option is perhaps the "niciest".

Answer 2

None of them is inherently superior to the other - they are just different. The _assoc() and _row() functions are really just convenience functions for different ways of calling mysql_fetch_array().

From the documentation

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).

Answer 3

That depends on how you want to access the row data.

• If you want to use numerical indexes, use fetch_row
• If you want to use the field name as the index, use fetch_assoc

If you don't need to use the indexes (say, you pass the row through a foreach), or you don't care, using fetch_row should result in a slightly smaller overhead (although this is very minimal). If you don't have a preference, prefer using field names, as this tends to make the code easier to read.

Never use fetch_array, which essentially combines fetch_row and fetch_assoc. Using both numerical and associative is almost certain to make the code less clear. (You may use fetch_array if you supply the $result_type parameter as something other than MYSQL_BOTH, but you might as well use the appropriate function, as your code will be just as clear, but more concise).

Note that if you do want to use the field name, make sure that you don't have two fields with the same name in your query. If you do, you will have to provide an alias for that column in your query (and use that as the field name when accessing the array)

Answer 4

mysql_fetch_row() It returns array with numeric index/key. Usually it is the faster compare with two other methods.

mysql_fetch_assoc() It returns array with column name as key. It is slightly slower than mysql_fetch_row().

mysql_fetch_array() It returns returns essentially two arrays. One with an associative based key index and one with a numeric index. mysql_fetch_array() without specifying which method you want (either MYSQL_NUM or MYSQL_ASSOC) always returns a double array. And it is considerably more inefficient as compared to mysql_fetch_row() or mysql_fetch_assoc(). You can set the result type as second parameter.

I think, actually those method has no significant difference.