PHP MYSQL Example
Below is an example of how to use the mysql functions supported by PHP to access a MySQL database. Here is the file "dblist.php".
<?php
session_start();
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta name="description" content="Database List">
<title>Database List</title>
</head>
<body>
<?php
if ($location1=="database")
{
$chandle = mysql_pconnect("localhost", $username, $password)
or die("Connection Failure to Database");
session_register("database");
$database=$QUERY_STRING; // The database name is passed using QUERY_STRING
mysql_select_db($database, $chandle) or die ("Database not found.");
$tablelist=mysql_list_tables($database);
echo "<H3>Available Tables in the ", $database, " Database:</H3>";
echo "<UL>";
$table = 0;
while ($table < mysql_num_rows($tablelist))
{
$tablename=mysql_tablename($tablelist, $table);
echo "<LI><A href=\"dblist.php?", $tablename, "\">", $tablename, "</A><BR>";
$table++;
}
echo "</UL>";
mysql_close($chandle);
$location1="table";
}
elseif ($location1=="table")
{
$chandle = mysql_pconnect("localhost", $username, $password)
or die("Connection Failure to Database");
mysql_select_db($database, $chandle) or die ("Database not found.");
$query1="select * from " . $QUERY_STRING;
$result = mysql_db_query($database, $query1) or die("Failed Query");
$i=0;
echo "<table rules=\"all\"><tr>";
while ($i < mysql_num_fields($result))
{
$field_name=mysql_fetch_field($result, $i);
echo "<th>", $field_name->name, "</th>";
$i++;
}
echo "</tr>";
while ($thisrow=mysql_fetch_row($result)) //get one row at a time
{
echo "<tr>";
$i=0;
while ($i < mysql_num_fields($result)) //print all items in the row
{
echo $thisrow[$i], "<br>";
$i++;
}
echo "</tr>";
}
echo "</table>";
mysql_free_result($result);
mysql_close($chandle);
}
?>
</body>
</html>
The value of location1 is initialized in the file in section 9 called "Using PHP with Forms". It is passed using sessions, and is used to tell if we are showing the available databases or tables. Its value is initally "database" then it is changed to "table". In this example one feature is that two strings are added using the period sign. Here's the line that adds the strings:
$query1="select * from " . $QUERY_STRING;
Mysql functions that are used include:
- $chandle = mysql_pconnect("localhost", $username, $password) - Used to connect to the server.
- mysql_select_db($database, $chandle) - Used to select the default database to query.
- $tablelist=mysql_list_tables($database) - Used to get a list of tables that are available in the database. This list is a dimensioned string and the following two functions are used to list the names of the tables:
- mysql_num_rows($tablelist) - Indicates the number of items in the tablelist string.
- $tablename=mysql_tablename($tablelist, $table); - This is used to get the name of the table from the string of availabel tables using the table list reference and the index value.
- $result = mysql_db_query($database, $query1) - Used to send a query to the database.
- mysql_num_fields($result) - Used to determine the number of fields in the query result.
- field_name=mysql_fetch_field($result, $i); - Used to get the field object (not the name) for the query with the specified index.
- $thisrow=mysql_fetch_row($result); - Used to get the entire row. This is a string array.
- mysql_free_result($result); - Frees the memory occupied by the $result string.
- mysql_close($chandle); - Closes the connection to the mysql database server.
Although it is functional, this file is not nearly complete. It does not allow for multiple queries based on one login. Additional controls should be added to make this example completely functional
No comments:
Post a Comment