Sending Queries and Retrieving Data

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

<?php $link = @mysql_connect("mysql_host", "userid", "userpassword") or die("Could not connect to MySQL"); $db = @mysql_select_db("database_name",$link) or die("Could not select database"); $query = "SELECT * FROM people WHERE NAME_LAST = 'Jones'"; $result = @mysql_query($query,$link) or die("Could not submit query"); $numrows = @mysql_num_rows($result); for ($i=0; $i<$numrows; $i++) { $jones = mysql_fetch_array($result); echo "Full name: ".$jones[NAME_FIRST]. " "; echo $jones[MI]. " " .$jones[NAME_LAST]. "<br>"); } echo ("Affected rows = " .mysql_affected_rows($link). "<br>"); ?>

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:
The result of the above script would look like so:

Full name: Robert T Jones
Full name: Tyler R Jones

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.

The "fetch" 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.

The "field" functions -

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

<?php $link = @mysql_connect("mysql_host", "userid", "userpassword") or     die("Could not connect to MySQL"); $fields = @mysql_list_fields("dbname", "tablename", $link) or     die("Could not retrieve field list"); $columns = @mysql_num_fields($fields); for ($i=0; $i<$columns; $i++) {     echo (mysql_field_name($fields, $i). "<br>"); } ?>

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


Tables   < <  PREVIOUS   Table of Contents NEXT  > >   Test Query

Developed with HTML-Kit
Sandersongs Web Tutorials
Contact the Webmasterwith comments.
©2017, by Bill Sanders, all rights reserved.
This domain had 3,975 different visits in the last 30 days.
435,541 hits on this domain since 24 Nov 2006.
http://www.sandersongs.com/PHPsqlCourse/setupDB13.php
This page was last modified on our server on 4 Jun 2008
and last refreshed on our server at 3:54 am, MST
This file took 0.01368 seconds to process.