PHP & MySQL
( Summary
of Functions Used )
Topic Summary
Note: First-pass overview links are between [ ], more detailed pass have †, and the rest is for greater exemplification.
Summary:
Recall (noted above), so far our process has been:
- Open the database connection and "use" the database:
$connection = mysql_connect("$hostName",$username,$password);
mysql_select_db($databaseName, $connection);- Run the query (obtain a "result set"):
$result = mysql_query ("SELECT * FROM wine", $connection);- While there are rows in the result set fetch the current row into an array ($row, here):
while ($row = mysql_fetch_row($result))
{...
OR
while ($row = @ mysql_fetch_array($result))
{...- Print the values in each row of the result set:
for ($i=0; $i<mysql_num_fields($result); $i++)
echo $row[$i] . " ";
..}
OR if mysql_fetch_array(...) was used, possibly:
... echo $row["field_name"]; ...- Close the database connection:
mysql_close($connection);
One more example to exemplify the small set of functions used to accomplish queries (as just noted above):
Recall the FY '04 Faculty and Administrator Salary Data, noted in our discussion of MySQL and example tables:
- [Description of Data]
- [Data] (initial data set had leading space before first name and commas in some titles, e.g. "VP, Academic Affairs."
- Forms (incomplete)
- For last name/first name search:
- Most basic version: [Form] and [PHP Listing] (recall [db5.inc])
- For better formatting (using the [number_format()] or the [money_format()] function: [Form] and [PHP Listing] (recall [db5.inc])
- A "finished" form for searches by name and department: [Form] using [PHP Listing for name search] and [PHP Listing for department search]
$sql .= ")";
Deletes the last comma and concatenates the terminal ")"
in the CREATE TABLE ... statement
| Field Name | Field Type | Field Length |
| id | int | 5 |
| format | char | 2 |
| title | char | 150 |
| artist_fn | char | 100 |
| artist_ln | char | 100 |
| rec_label | char | 50 |
| my_notes | text | |
| date_acq | date |
If you are testing the work with command-line MySQL,
Recall: DROP TABLE table_name; is the MySQL command to delete a table from your database.
Skip to Authentication or [Sessions] sections in first pass.
<BR>
<A HREF="<?php echo "$PHP_SELF?order_by=username";
?>">
User Name
</A>
This means that when the first link
(for "User ID") the current (table2b.php via
$PHP_SELF)
PHP script will be called again
and $order_by
will be set at 'userid' (instead of being empty as it
was on the first call to table2b.php) via the query
string (see address field in browser).
Recall, the PHP Web-Based Interface for MySQL: http://mysql.cs.txstate.edu/ .