Mysql fetch binds a mysql fetch array

Question

The following are two methods that are commonly used in most php codes to retrieve mysql data.

• mysql_fetch_array()
• mysql_fetch_assoc()

Now I have a large database. To get data in loops ( while ), which is faster and better?

I found this test .

What is your choice?

Answer 1

It depends on how your tables are configured:

mysql_fetch_array() essentially returns two arrays with a numerical index, one with an associative row index.

Thus, using mysql_fetch_array() without specifying MYSQL_ASSOC or MYSQL_NUM , or specifying MYSQL_BOTH , you will return two arrays (basically that will return mysql_fetch_assoc() and mysql_fetch_row() ), so mysql_fetch_assoc() will be faster.

If you have the correct table setup and the correctly written mysql_fetch_assoc() query is the way to go, the readability of the code $result['username'] easier to understand than $result[0] .

Answer 2

I prefer fetch_assoc. It returns a "associative array" (for example, a python dictionary). This means that your array is indexed by a string (in my usual case).

The default value for fetch_array gives both numbered indexes and associative indexes.

But I never use numbered indexes, so I like it a little faster with fetch_assoc

Answer 3

The "size" of the query results does not matter in this case.

mysql_fetch_array() usually creates overhead by returning an associative array plus indexed results.

You need to select the type of query and the desired results on whether to use mysql_fetch_array() or mysql_fetch_assoc() .

If the overhead is "not taken into account", and I am sure that the request completes successfully, and I know only one result, which I sometimes do:

 $q = mysql_query('SELECT `column1`,`column2` FROM `table` WHERE `id` = 123'); list($column1,$column2) = mysql_fetch_array($q); mysql_free_result($q); 

For an iterative process (like a while ) this just doesn't shorten it. If there is no need for "quickly retrieved" results (via the list operator), and the result must be further processed in the code, I always use mysql_fetch_assoc() *.

---

* When forced to actually use the obsolete procedural functions for finding PHP data. There are alternatives .

Answer 4

In your example, which is shown in your example, mysql_fetch_array () is used without any arguments to indicate MYSQL_ASSOC or MYSQL_NUM, so it defaults to MYSQL_BOTH ... this will more likely distort the result, since it needs to load twice as many elements an array than mysql_fetch_assoc (), so I would suggest that this is not a valid test.

In fact, there should be very few differences between mysql_fetch_array () with MYSQL_ASSOC and mysql_fetch_assoc (), and this will not be the biggest overhead in your script.

Answer 5

This is the result of mysql_fetch_array ()

 $row['fieldname'] = 'some value'; 

or

 $row[0] = some value'; 

This is the result of mysql_fetch_assoc () will only return

 $row['fieldname'] = 'some value'; 

Answer 6

mysql_fetch_assoc() is probably the best choice. There is a slight performance hit (the mysql extension should look for column names, but this should only happen once, no matter how many rows you have), but probably not enough to justify using mysql_fetch_array() . mysql_fetch_array() can be useful if you select only one column.

Answer 7

I suppose it depends on your definition of big. From this test, you can see that only before you make more than 1,000,000,000 samples do you actually get a measurable difference? Is your database so big? If not, then go with something that is easier for you to use (probably to get an associative array).

Another point to consider, if it is so large that there is a measurable difference, then I get the opportunity to use all the functions together. Think about how to use MySQLi or even better to use PDO!

Answer 8

Good http://php.net/manual/en/function.mysql-fetch-assoc.php states

Note. Performance

It is important to note that using mysql_fetch_assoc () is not significantly slower than using mysql_fetch_row (), while it provides significant added value.

Therefore, the difference should not bother.

Answer 9

Another point to keep in mind is that fetch_assoc() may not return every column that you expect. If 2 output columns have the same name, only the last instance of this column will be obtained using fetch_assoc() . For example, the following query:

 SELECT * FROM orders LEFT JOIN line_items ON orders.id = line_items.order_id 

Assuming both tables have a column named id , the resulting associative array will have a key named id whose value is line_items.id , and you cannot extract orders.id from the result!

You can work around this problem with duplicate column names and continue to use fetch_assoc() by manually aligning SELECT columns, giving each column a unique alias:

 SELECT o.id AS order_id, #unique alias i.id AS line_item_id, #unique alias o.date, i.qty, i.price FROM orders o LEFT JOIN line_items i ON i.order_id = o.id 

Or you can switch to using fetch_array() , which will allow you to access id by index rather than by key

Answer 10

mysql_fetch_array () vs mysql_fetch_assoc ()

mysql_fetch_array (): returns the array corresponding to the selected row and moves the internal data pointer forward. for better documentation click here! learn more mysql_fetch_assoc (): equivalent to calling mysql_fetch_array () with MYSQL_ASSOC for an optional second parameter. It returns only an associative array.