Creating a Search Application -
(Page 6 of 29 )
At this point, our table, named introduction, is completely empty. The only way it is going to get any data in it is for us to populate it. We will accomplish this by sending an SQL query to the database via the mysql_query() function.
The mysql_query() function will allow us to send any SQL query to the database. So, not only can we insert data into the database with it, but we can also select, delete, and update information. Before we can do any of the last three operations, we need to insert something into the database. So, in order to insert a single row into the database, we would use the following mysql_query() statement.
<?php
$result = mysql_query("INSERT INTO `introduction` "
. "VALUES ('', 'Smith', 'John')", $db)
or die("Invalid query: " . mysql_error());
?> |
This statement will insert a row into the table with the value of 'Smith' for the last_name column and 'John' for the first_name column. You will notice that we passed an empty string for the id column as the database server will automatically assign this value. The mysql_query() function will return TRUE on success and FALSE on error. We will see in a moment that it can also return a resource identifier under certain circumstances. However, it will always return either TRUE or FALSE for insert, update, and delete statements. Please pay special attention to the fact that the characters on either side of the table name, introduction, are back tick characters. Using single quotes will not work!
There is another new function introduced in the statement above. The mysql_error() function returns the last error message from the database server. In the case that our query fails, the die function will stop execution of the script and display the error message as returned by the mysql_error() function.
That was a simple insert statement that stored one row of data in the table. What if we need to store multiple rows of data in the table at one time? Well, we could run several insert queries like the one we just demonstrated, or we can actually expand the insert statement to store more than one row at a time. This is done by adding more values to the end of the statement. Each row of data is placed within parentheses and added to the end of the insert statement, with each set of values separated by a comma. The following is an example of how we would insert three rows at one time.
<?php
$result = mysql_query("INSERT INTO `introduction` "
. "VALUES ('', 'Harkey', 'Rob'), "
. "('', 'Wade', 'Michelle'), "
. "('', 'Gary', 'Amanda')", $db)
or die("Invalid query: " . mysql_error());
?> |
Now that we have some data in the table, let's see how we would retrieve it from the database. We will use the mysql_query() function as before, but we will now have to introduce some other functions to work in conjunction with it. First, we will issue a basic select statement to the database like so:
<?php
$result = mysql_query ("SELECT * FROM introduction", $db)
or die ("Invalid query: " . mysql_error());
?> |
The SQL statement issued will select all rows and columns from the table introduction. This SQL statement will have mysql_query() return a resource identifier associated with the result set of the query which will be stored in the variable $result. We then can use another function named mysql_fetch_assoc() to retrieve a row from the result set as an associative array. The mysql_fetch_assoc() function will return an associative array with the column names serving as the keys of the array. Basic usage of this function would be as follows:
<?php
$row = mysql_fetch_assoc ($result);
?> |
After this statement executes, the variable $row would contain an associative array with three elements. An element would correspond to each of the columns in the table introduction. Now, we can display the data from this row by simply echoing out each element.
<?php
echo "{$row['id']}<br />\n";
echo "{$row['last_name']}, ";
echo "{$row['first_name']}<br />\n";
?> |
This would display:
Commonly, you will see the mysql_fetch_assoc() function used as the condition in a while loop. This enables us to process all the rows in a result set. The following example shows how this is done.
<?php
while ($row = mysql_fetch_assoc ($result)) {
echo "{$row['id']}<br />\n";
echo "{$row['last_name']}, ";
echo "{$row['first_name']}<br />\n";
}
?> |
It is worth noting that it is not necessary to retrieve the columns in the order in which they are stored in the table. We could have displayed the id, last name, and first name in any order that we wished.
The mysql_fetch_assoc is just one function that you can use to retrieve rows from a result set. Others include mysql_fetch_array, which will return an array with both enumerated and associative indexed, and mysql_fetch_row, which returns only a enumerated array.
Next: >>
More Database Articles Articles
More By Matt Wade
