Here are the PHP functions for working with MySQL queries and retrieving data. Those highlighted in light green are considered advanced and will not be taught here, but the links are still provided for your future reference.

• mysql_query()
• mysql_num_rows()
• mysql_fetch_array()
• mysql_fetch_row()

• mysql_fetch_assoc()
• mysql_fetch_object()
• mysql_affected_rows()
• mysql_list_fields()

• mysql_num_fields()
• mysql_field_name()
• mysql_field_len()
• mysql_field_table()

• mysql_fetch_field()
• mysql_field_type()
• mysql_fetch_lengths()
• mysql_result()

Mysql_query() sends a query to the currently active database on the server that's associated with the specified link identifier ($link in our examples). If $link isn't specified, the last opened link is assumed. If no link is open, the function tries to establish a link as if mysql_connect() was called with no arguments, and use it. Note: The query string as the function parameter should not end with a semicolon. (As an alternative, set the query string equal to a string variable, then include the variable as the parameter.)

The query itself is written in SQL, just like you learned from SQL Basic Concepts.

A lot has happened here. Let's review step by step:

1. We connected to MySQL and specified $link as the link identifier.
2. We selected a database to be current, and set its Boolean status in a variable ($db).
3. We set the query in a string variable ($query). Note that since the query is not inside the function as a parameter, we do add the semi-colon at the end.
4. We ran the query and stored the returned result identifier in a variable ($result).
5. We determined the number of rows in the result set and set it in a variable ($numrows). (We already know there are only two (2) rows in this result set, since there are only two records where NAME_LAST is 'Jones'.)
6. We ran a for loop:
• Our counter ($i) starts at 0 and is incremented by one the next time the loop runs. The loop runs as many times as there are rows in the result set (in this case, two times).
• During each iteration of the loop, we call the mysql_fetch_array() function, which fetches the next row from the result set. Each time we call this function, we turn the entire row fetched into an associative array and assign it to a variable ($jones). Whatever values $jones had from the previous loop are replaced in the current loop with the newly fetched row.
• Since $jones is an associative array, we can retrieve data using the following syntax: $jones[fieldname]. Notice the field names in the code above.
• During each iteration of the loop we echo a statement to the screen.

In that one example, we covered mysql_query(), mysql_num_rows() and mysql_fetch_array(). We will categorize the remaining functions into the "fetch" functions and the "field" functions.

Mysql_fetch_row(), mysql_fetch_assoc() and mysql_fetch_object() are all limited versions of the more complete mysql_fetch_array() function.

These three all fetch the next row in the result set each time they are called. Mysql_fetch_row() returns a numerically-indexed array, so to access its values, the syntax would be $jones[0], $jones[1] and so on. The other two return associative arrays, so their syntax would be similar to the example above for mysql_fetch_array().

Mysql_fetch_array() returns both a numerically-indexed and fieldname-indexed array that can be accessed by either syntax, even in the same script.

Mysql_affected_rows() returns the number of rows affected by the last INSERT, UPDATE or DELETE query associated with link_identifier (not the result identifier, but the link identifier). If the link identifier isn't specified, the last link opened by mysql_connect() is assumed. This function is to INSERT, UPDATE or DELETE what mysql_num_rows() is to SELECT. As the name suggests, mysql_affected_rows() reports the number of rows affected by an INSERT, UPDATE or DELETE command.

Here is an example employing three of the "field" functions:

Mysql_list_fields() returns a result pointer containing the fields available from the named table in the current database. Arguments are the database name and the table name.

Mysql_num_fields() returns the number of fields in a result set. Its only parameter is a result identifier. Note, this function tells you the number of fields in the table, not the number of cells in the field.

Mysql_field_name() returns the name of the specified field index. Its parameters are a result identifier and the index value of the field sought. (Remember, field index values start at 0.)

Therefore, the above example will display a list of the names of all the fields (columns) in the designated table in the current database.

Mysql_field_len() returns the length of the specified field. Its parameters are the result identifier and the field index value (not the field name). Note, this function tells you the number of cells in a field, not the number of fields.

Mysql_field_table() returns the name of the table hosting the specified field. Its parameters are the result identifier and the field index value (not the field name).