MySQL Fundamentals in PHP
::: Table of Contents :::
Introduction
Connecting to a MySQL Server
Creating a Database
Selecting a Database
Sending SQL Queries
Creating a Table
Inserting Data into a Table
Viewing Records from a Table
Updating Records in a Table
Final Thoughts
::: Introduction :::
Other than answering some common questions about MySql and PHP, we will see if we can answer the one question many of you are probably asking yourselves... Is it possible for TroPe to write an article without any graphics at all? Although I went through some serious withdrawals, I made it (this time).
I'm new to this MySql stuff, but sql is sql. As I have been working with it within PHP, I have documented my code testing, my errors, and have taken some notes. I am proud to present my first (of many?) articles on MySql. Not proud because I wrote it, but proud because I am finally 'here'. Like I said in my summary, it's a breeze. The only limitation of MySql that I have run across so far came yesterday when I attempted to execute a sub select - select name... where id in (...) - something like that. But it could just be my host is using an old version of MySql. I thought I heard somewhere that sub selects were supported now.
::: Connecting to a MySQL Server :::
PHP provides functions, which you can use to establish a connection with a database server.
Here is a list of the connectivity functions in PHP:
mysql_connect - Opens a connection to the MySQL server. This function accepts the server name, username, and password as arguments.
mysql_select_db - Selects a database on the MySQL server.
mysql_query - Issues an SQL statement.
mysql_fetch_array - Redirects an SQL statement result row to an array after fetching the row from the table.
mysql_free_result - Releases the resources used by the current connection.
mysql_close - Closes the current connection.
You use the mysql_connect() function to establish a connection with the MySQL server. The mysql_connect() function accepts optional arguments to let a user log on to a MySQL server and returns a connection identifier. PHP uses the connection identifier internally to identify a connection. In case of multiple connections at the same time, mysql_connect() uses the connection identifier of a previous connection instead of establishing a new connection. The syntax for the mysql_connect() function is:
$linkid=mysql_connect($hostname, $username, $password);
Three arguments are passed with the mysql_connect() function: $hostname, $username, and $password. The $hostname argument is the host name on which the MySQL server is running. I found out you can also provide the IP address instead of the host name. If you do not specify a value for this argument, the MySQL server is considered to be running on the local host. The $username argument is the name of the end user attempting the connection. This name should be of a valid MySQL end user with the required privileges. The $password argument is the password of the end user. The $con variable consists of the connection identifier of the connection. The value of $con will be true if the connection is successful and false if the connection is unsuccessful.
This listing shows the code to establish a connection with the MySQL server:
<?php
$host="";
$usrnm="root";
$passwrd="";
$con= mysql_connect($host,$usrnm,$passwrd);
if (!$con)
{
die("Connection failed!!!");
}
else
{
echo "The user root has successfully logged into the MySQL server.",$host;
}
?>
The declared variable, $con, holds the result of the mysql_connect() function. The host name is localhost. The values of $usrnm and $passwrd are root and blank respectively. The die() function is used to stop the script and print an error message if the connection fails. The output of the code is:
The user root has successfully logged into the MySQL server.
::: Creating a Database :::
You can create databases using PHP in MySQL using the mysql_create_db() function. The syntax for the mysql_create_db() function is:
$vardb=mysql_create_db($database_name);
The variable, $vardb, stores the result of the mysql_create_db() function and $database_name provides the name of the database to be created. If the database is created successfully, the value of the $vardb variable is true. If the database creation fails, the value of $vardb is false.
This listing shows the PHP code to create a database, myFirstDb:
<?php
$host="";
$usrnm="root";
$passwrd="";
$con= mysql_connect($host,$usrnm,$passwrd);
if (!$con)
{
die("Connection failed!!!");
}
$vardb=mysql_create_db("myFirstDb");
If (!$vardb)
{
die("Database creation failed!!!");
}
echo "The myFirstDb database is created successfully.";
?>
In the code, the connection with the MySQL server is established using the mysql_connect() function and the database, myFirstDb, is created using the mysql_create_db() function. The $vardb variable stores the result of the mysql_connect() function, which is either true or false. The output of the code is:
The myFirstDb database is created successfully.
::: Selecting a Database :::
Before you start working with a database, you need to select it. Selecting a database using PHP is similar to the use <database_name>command in MySQL. You can select a database using the mysql_select_db() function. The syntax of the mysql_select_db() function is:
$varselect=mysql_select_db($database_name)
The $varselect variable stores the result of the mysql_select_db() function, which is true if the database is selected successfully and false if the selection fails. The $database_name argument provides the name of the database to be selected.
Note: The selection might fail if the database does not exist in the server or another end user has already accessed the same database.
This listing shows the PHP code to select a database:
<?php
$host="";
$usrnm="root";
$passwrd="";
$con= mysql_connect($host,$usrnm,$passwrd);
if (!$con)
{
die("Connection failed!!!");
}
$varselect=mysql_select_db("myFirstDb");
If (!$varselect)
{
die("Database selection fails!!!");
}
echo "The database myFirstDb is ready for use.";
?>
In the code, the mysql_connect() function establishes a connection with the MySQL server and the mysql_select_db() function selects the database myFirstDb. The $varselect variable stores the result of the mysql_select_db() function, which is either true or false. If the selection fails, the die() function generates an error message. The output of the code is:
The database myFirstDb is ready for use.
::: Sending SQL Queries :::
To retrieve data from MySql, you need to use SQL queries to access and retrieve data from MySQL databases. The mysql_query() function sends SQL queries to the MySQL server. It accepts an SQL query as the argument. The syntax for the mysql_query() function is:
$varquery=mysql_query($sql_query);
The $sql_query argument is a valid SQL query. You need not end the query with a semicolon. The $varquery variable stores the result of the mysql_query() function, which is true if the SQL query is successful and false if the SQL query fails.
Note: The SQL query might fail if the query is not valid, the record that you want to retrieve does not exist, or the end user does not have the required privilege to access the data.
My next listing shows the PHP code to send a query:
<?php
$host="";
$usrnm="root";
$passwrd="";
$database="myFirstDb";
$con= mysql_connect($host,$usrnm,$passwrd)
or die("Connection failed!!!");
$varselect=mysql_select_db($database)
or die("Database selection fails!!!");
$query="drop table if exists testtable";
$varquery=mysql_query($query) or die("Query failed!!!");
?>
In the code, the mysql_connect() function establishes the connection with the MySQL server and the mysql_select_db() function selects the myFirstDb database. The $query variable stores the SQL query to drop a table, which is passed as an argument in the mysql_query() function. The mysql_query() function executes the SQL query and stores the result in the $varquery variable, which is either true or false.
::: Creating Tables :::
You can store data in MySQL tables using PHP code. This section discusses how to create a table called customers with the fields and field types shown here:
: CustID........char (5)
: Name .........varchar(30)
: age...........int
: sex...........char(1)
: address.......varchar(200)
: phone.........char(12)
: email.........varchar(40)
Here's the PHP code to create the customers table in the myFirstDb database:
<?php
$host="";
$usrnm="root";
$passwrd="";
$database="myFirstDb";
$table="customers";
$con= mysql_connect($host,$usrnm,$passwrd)
or die("Connection failed!!!");
$varselect=mysql_select_db($database)
or die("Database selection fails!!!");
$query="create table if not exists". $table ."
(custid CHAR(5) PRIMARY KEY, name VARCHAR(30) NOT NULL, age INT UNSIGNED NOT NULL,
sex CHAR(1) NOT NULL, address VARCHAR(200) NOT NULL, phone CHAR(12), email VARCHAR(40))";
$varquery=mysql_query($query);
if (!$varquery)
{
die(" Query could not be executed.");
}
else
{
echo $table ."created in the myFirstDb database.\n";
}
?>
In the above listing, take note of the following:
- The argument $table provides the name of the table that will be created in the database.
- The mysql_connect() function establishes a database connection.
- The mysql_select_db() function selects the myFirstDb database.
- The $query variable stores the SQL query to create the table.
- The SQL query is used to create a table with fields, such as custid, name, age, sex, address, phone, and email. The name, address, and email fields have variable lengths. This implies that these fields can consist of an indefinite number of characters.
- The custid field is marked as the primary key for the table because it can be used to identify a customer uniquely.
- The variable $varquery stores the value true if the operation of the query is successful. Else, the variable $varquery contains the value false. The program displays an error message if $varquery is false.
The output of the code is:
customers created in the myFirstDb database.
Note: You can view the design of the table in MySQL using the command "explain <Table_name>".
::: Inserting Date Into A Table :::
After you create a table, you need to enter records in the table. You can enter data in MySQL tables or enable end users to enter data using a HTML forms. The PHP code is embedded in HTML to communicate with the MySQL server. My next listing shows the HTML code to create the form to enter customer details:
<html>
<head>
<title>Customer Details Form</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body text="#000000">
<p> </p><form name="customerdetailsform" method="post" action="addnew.php">
<table width="250" border="0" align="center" cellpadding="3">
<tr>
<td colspan="2">
<div align="center"><b><i>Enter Customer Details</i></b></div>
</td>
</tr>
<tr>
<td width="100">
<div align="right">Customer ID</div>
</td>
<td width="150">
<input type="text" name="custID">
</td>
</tr>
<tr>
<td width="100">
<div align="right">Name</div>
</td>
<td width="150">
<input type="text" name="namel">
</td>
</tr>
<tr>
<td width="100">
<div align="right">Age</div>
</td>
<td width="150">
<input type="text" name="age">
</td>
</tr>
<tr>
<td width="100">
<div align="right">Sex</div>
</td>
<td width="150">
<input type="text" name="sex">
</td>
</tr>
<tr>
<td width="100">
<div align="right">Address</div>
</td>
<td width="150">
<input type="text" name="address">
</td>
</tr>
<tr>
<td width="100">
<div align="right">Phone</div>
</td>
<td width="150">
<input type="text" name="phone">
</td>
</tr>
<tr>
<td width="100">
<div align="right">E-mail</div>
</td>
<td width="150">
<input type="text" name="email">
</td>
</tr>
<tr>
<td colspan="2">
<div align="center">
<input type="submit" name="Submit" value="Submit">
</div>
</td>
</tr>
</table>
</form>
</body>
</html>
Typically I would show a graphic here of the screenshot, but... for now just take note of the field names.
This code creates a HTML form that accepts customer details. The form accepts data for all the fields of the customers table, such as customer id, name, age, sex, address, phone number, and e-mail. The data entered by the user is submitted to a PHP script using the POST method.
The data entered in the HTML form is to be inserted in the MySQL table as a new row. To do this, you need to establish a connection with the MySQL server using the mysql_connect() function, select the database using the mysql_select_db() function, and send the Insert query to the MySQL server using the mysql_query() function. To perform these tasks, you need to create a PHP script file. This listing shows the code for the PHP script:
<html>
<head>
<title>Inserting New Record in the customers Table</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body text="#000000">
<p> </p>
<?php
$host="";
$usrnm="root";
$passwrd="";
$database="myFirstDb";
$tablename="customers";
$con= mysql_connect($host,$usrnm,$passwrd)
or die("Connection failed!!! <br>");
$varselect=mysql_select_db($database)
or die("Database selection failed!!!");
$query="INSERT INTO customers VALUES('". $custID ."','". $name . "','". $age .
"','". $sex . "','". $address . "','". $phone . "','". $email . "')";
$varquery=mysql_query($query);
if (!$varquery)
{
die("Query execution failed!!!<br>");
}
else
{
echo "<table border=\"0\" align=\"center\" cellspacing=\"1\"
cellpadding=\"5\" width=\"300\">";
echo " <tr> ";
echo " <td colspan=\"2\"> ";
echo " <center><b>".mysql_affected_rows()."
record entered into the table</b></center>";
echo " </td>";
echo " </tr>";
echo " <tr> ";
echo " <td width=\"100\"> ";
echo " <div align=\"right\">custID</div>";
echo " </td>";
echo " <td width=\"200\">".$custID. "</td>";
echo " </tr>";
echo " <tr> ";
echo " <td width=\"100\"> ";
echo " <div align=\"right\">Name</div>";
echo " </td>";
echo " <td width=\"200\">".$name. "</td>";
echo " </tr>";
echo " <tr> ";
echo " <td width=\"100\"> ";
echo " <div align=\"right\">Age</div>";
echo " </td>";
echo " <td width=\"200\">".$age. "</td>";
echo " </tr>";
echo " <tr> ";
echo " <td width=\"100\"> ";
echo " <div align=\"right\">Sex</div>";
echo " </td>";
echo " <td width=\"200\">".$sex. "</td>";
echo " </tr>";
echo " <tr> ";
echo " <td width=\"100\"> ";
echo " <div align=\"right\">Address</div>";
echo " </td>";
echo " <td width=\"200\">".$address. "</td>";
echo " </tr>";
echo " <tr> ";
echo " <td width=\"100\"> ";
echo " <div align=\"right\">Phone</div>";
echo " </td>";
echo " <td width=\"200\">".$phone. "</td>";
echo " </tr>";
echo " <tr> ";
echo " <td width=\"100\"> ";
echo " <div align=\"right\">E-mail</div>";
echo " </td>";
echo " <td width=\"200\">".$email. "</td>";
echo " </tr>";
echo " <tr> ";
echo " <td colspan=\"2\"> </td>";
echo " </tr>";
echo " <tr> ";
echo " <td colspan=\"2\"> </td>";
echo " </tr>";
echo "</table>";
}
?>
</body>
</html>
Here's a breakdown of what is happening:
- The mysql_connect() function establishes a connection with the MySQL server.
- The mysql_select_db() function selects the myFirstDb database.
- The variable $query stores the SQL Insert query.
- The SQL query is entered as a row in the customers table with the values for the respective fields.
- The variable $varquery stores the result of the SQL query, which is either true or false. If the SQL query is successful, the value of the $varquery is true. If the SQL query fails, the value is false.
- If the value of $varquery is false, the PHP code generates an error message.
- If the value of $varquery is true, a report indicating the newly added record is displayed.
- The mysql_affected_rows() function returns the number of rows affected by the last SQL query.
::: Viewing Records From A Table :::
You can retrieve data from a MySQL table using the Select statement. To do this using PHP code, you need to establish a connection with the MySQL server, select the database using the mysql_select_db() function, and send an SQL query to the MySQL server using the mysql_query() function. The data from the table will be returned as a result identifier.
There are various functions in PHP to extract rows from a result identifier. You can return the result as an array using the mysql_fetch_row() function and the mysql_fetch_array() function. Let's take a look at both of them:
The mysql_fetch_row() Function
The mysql_fetch_row() function returns one row or record at a time from the result identifier. It returns the record in the form of an enumerated array. Each field in the record is an element of the array. The first element of the array is the first field of the record.
The syntax of the mysql_fetch_row() function is:
$record=mysql_fetch_row($result);
Here, the $result variable is the result identifier that the mysql_query() function returns by executing the SQL query. The record that is returned by the mysql_fetch_row() function is stored in the $record variable in the form of an enumerated array.
Note that the mysql_fetch_row() function returns one record at a time. If there are multiple rows returned by the SQL statement, the mysql_fetch_row() function will be called as many times as the number of records returned by the SQL statement.
My next listing shows the PHP script to retrieve data from the customers table of the myFirstDb database using the mysql_fetch_row() function:
<html>
<head>
<title>Customer Details</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<h2 align="center">Customer Details</h2>
<?php
$host="";
$usrnm="root";
$passwrd="";
$database="myFirstDb";
$tablename="customers";
$con= mysql_connect($host,$usrnm,$passwrd)
or die("<br>Connection failed!!! <br>");
$varselect=mysql_select_db($database)
or die("<br>Database selection failed!!!<br>");
$query="SELECT * from customers";
$varquery=mysql_query($query);
echo "<table width=\"500\" border=\"0\" align=\"center\" cellpadding=\"5\" cellspacing=\"3\">";
echo " <tr>";
echo " <td width=\"150\"> <center><b>Customer ID
</b></center></td>\n";
echo " <td width=\"150\"> <center><b>Name
</b></center></td>\n";
echo " <td width=\"100\"> <center><b>Age</b>
</center></td>\n";
echo " <td width=\"100\"> <center><b>Sex</b>
</center></td>\n";
echo " <td width=\"100\"> <center><b>Address</b>
</center></td>\n";
echo " <td width=\"100\"> <center><b>Phone</b>
</center></td>\n";
echo " <td width=\"100\"> <center><b>E-mail</b>
</center></td>\n";
echo " </tr>\n";
while ($record=mysql_fetch_row($varquery))
{
echo " <tr>\n";
echo " <td>$row[0]</td>\n";
echo " <td>$row[1]</td>\n";
echo " <td>$row[2]</td>\n";
echo " <td>$row[3]</td>\n";
echo " <td>$row[4]</td>\n";
echo " <td>$row[5]</td>\n";
echo " <td>$row[6]</td>\n";
echo " </tr>\n";
}
echo "</table>\n";
?>
</body>
</html>
This code enables you to view data from the MySQL table by performing the following tasks:
- The mysql_connect() function establishes a connection with the MySQL server.
- The mysql_select_db() function selects the database.
- The SQL query selects all records from the customers table.
- The $query variable stores the result of the SQL statement.
- The $varquery variable stores the result identifier, which is either true or false, depending on the success or failure of the SQL statement.
- If the value of $varquery is false, an appropriate error message is generated.
- The mysql_fetch_row() function is called to extract the first record from the $varquery result identifier and store the first record in the $record variable in the form of an enumerated array. The $record consists of a single-dimensional array of seven elements. Each element of the array contains a field of the record.
- The data in the $record variable is displayed in a Web page in the form of a table.
The mysql_fetch_array() Function
The mysql_fetch_array() function returns records from the MySQL table in the form of an associative array. Each field in the table is the label of the element in the associative array. As a result, you do not need to remember the order of the columns in the table because the records are not stored according to their position in an enumerated array. As a result, the labels of the elements in the associative array can refer to the corresponding fields.
The syntax of the mysql_fetch_array() function is:
$record=mysql_fetch_array($result);
The $result argument is the data result identifier that the mysql_query() function returns by executing the SQL query. The $record variable stores the record returned by the mysql_fetch_array() function in the form of an associative array. The values of the associative array elements are the corresponding values of the fields in the record. Similarlly, the labels of the elements in the associative array are the corresponding fields in the record.
Let's have a look at the PHP code to retrieve data from the customers table of the myFirstDb database using the mysql_fetch_array() function:
<html>
<head>
<title>Customer Details</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<h2 align="center">Customer Details</h2>
<?php
$host="";
$usrnm="root";
$passwrd="";
$database="myFirstDb";
$tablename="customers";
$con= mysql_connect($host,$usrnm,$passwrd)
or die("Connection failed!!! <br>");
$varselect=mysql_select_db($database)
or die("Database could not be selected");
$query="SELECT * from " . $tablename;
$varquery=mysql_query($query);
echo "<table width=\"500\" border=\"0\" align=\"center\" cellpadding=\"5\" cellspacing=\"3\">";
echo " <tr> ";
echo " <td width=\"150\"> <center><b>Customer ID
</b></center></td>\n";
echo " <td width=\"150\"> <center><b>Name
</b></center></td>\n";
echo " <td width=\"100\"> <center><b>Age</b>
</center></td>\n";
echo " <td width=\"100\"> <center><b>Sex</b>
</center></td>\n";
echo " <td width=\"100\"> <center><b>Address</b>
</center></td>\n";
echo " <td width=\"100\"> <center><b>Phone</b>
</center></td>\n";
echo " <td width=\"100\"> <center><b>E-mail</b>
</center></td>\n";
echo " </tr>\n";
while ($record=mysql_fetch_array($varquery))
{
echo " <tr>\n";
echo " <td>".$record["name"]."</td>\n";
echo " <td>".$record["email"]."</td>\n";
echo " <td>".$record["custid"]."</td>\n";
echo " <td>".$record["address"]."</td>\n";
echo " <td>".$record["age"]."</td>\n";
echo " <td>".$record["sex"]."</td>\n";
echo " <td>".$record["phone"]."</td>\n";
echo " </tr>\n";
}
echo "</table>\n";
?>
</body>
</html>
This code enables you to view the data from the MySQL table by performing the following tasks:
- The mysql_connect() function establishes a connection with the MySQL server.
- The mysql_select_db() function selects the myFirstDb database.
- The SQL query selects all records from the customers table.
- The $query variable stores the result of the SQL statement.
- The $varquery variable stores the result identifier, which is either true or false, depending on the success or failure of the SQL statement.
- If the value of $varquery is false, an appropriate error message is generated.
- The mysql_fetch_array() function is called to extract the first record from the $varquery result identifier and store the first record in the $record variable in the form of an associative array.
- When the mysql_fetch_array() function is called repeatedly, the $record result identifier consists of a single-dimensional array of seven elements. The values of each element of the array are the same as the corresponding value of the field of the table. Similarly, the labels of each element of the array are the same as the corresponding fields of the table.
- The data in the $record variable is displayed in a Web page in the form of a table.
::: Updating Records in a Table :::
In MySQL, you can use the Update statement to update records in a table. The following method is used to update records in a MySQL database using PHP:
1. An HTML form displays the fields in a row that needs to be updated. This form will accept the values for the fields.
2. The data entered is submitted to a PHP script.
3. The PHP script establishes a connection with the MySQL server using the mysql_connect() function.
4. The mysql_select_db() function selects the database where the records have to be updated.
5. The mysql_query() function sends a Select statement to the MySQL server. You need to specify a Where clause with the Select statement so that a single row is returned to the result identifier.
6. As the data is returned to the result identifier, the PHP script displays a second HTML form with the fields in the MySQL table. The current values of the fields are displayed on the form.
7. The end user changes the values and submits the information.
8. Another PHP script establishes a connection with the MySQL server and sends the Update query to the server using the mysql_query() function.
9. The result identifier is true or false depending on the success or failure of the Update operation.
This listing shows the HTML code to create the form that accepts the customer id and displays the record from the customers table:
<html>
<head>
<title>Customer Details</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<p> </p>
<form name="updateform" method="post" action="displayrecord.php">
<table width="250" border="0" align="center" cellpadding="3">
<tr>
<td colspan="2">
<center><b>Enter Customer ID</b></center>
</td>
</tr>
<tr >
<td width="100">
<center>Customer ID</center>
</td>
<td width="150">
<input type="text" name="custid">
</td>
</tr>
<tr>
<td colspan="2" >
<center>
<input type="submit" name="Submit" value="Submit">
</center>
</td>
</tr>
</table>
</form>
</body>
</html>
...and this shows the PHP script to display the record and accept the updates for the existing record:
<html>
<head>
<title>Customer Details</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<p> </p><form name="displayrecord" method="post"
action="displayercord.php">
<table width="250" border="0" align="center" cellpadding="3">
<tr>
<td colspan="2">
<div align="center"><b>Update Customer Details</b></div>
</td>
</tr>
<?php
$host="";
$usrnm="root";
$passwrd="";
$database="myFirstDb";
$tablename="customers";
$con= mysql_connect($host,$usrnm,$passwrd)
or die("Connection failed!!! <br>");
$varselect=mysql_select_db($database)
or die("Database selection failed!!!");
$query="SELECT * from customers WHERE custid='" .$customerid ."'";
$varquery=mysql_query($query);
if($record=mysql_fetch_array($varquery))
{
echo "<tr> ";
echo " <td width=\"100\"> ";
echo " <div align=\"right\">Customer ID </div>";
echo " </td>";
echo " <td width=\"150\"> ";
echo " <input type=\"text\" name=\"custid\" value=\"".$row["custid"]."\" >";
echo " </td>";
echo " </tr>";
echo "<tr> ";
echo " <td width=\"100\"> ";
echo " <div align=\"right\">Name </div>";
echo " </td>";
echo " <td width=\"150\"> ";
echo " <input type=\"text\" name=\"name\" value=\"".$row["name"]."\" >";
echo " </td>";
echo " </tr>";
echo " <tr> ";
echo " <td width=\"100\"> ";
echo " <div align=\"right\">Age</div>";
echo " </td>";
echo " <td width=\"150\"> ";
echo " <input type=\"text\" name=\"age\" value=\"".$row["age"]."\" >";
echo " </td>";
echo " </tr>";
echo " <tr> ";
echo " <td width=\"100\"> ";
echo " <div align=\"right\">Sex</div>";
echo " </td>";
echo " <td width=\"150\"> ";
echo "<input type=\"text\" name=\"sex\" value=\"".$row["sex"]."\" >";
echo " </td>";
echo " </tr>";
echo "<tr> ";
echo " <td width=\"100\"> ";
echo " <div align=\"right\">Address </div>";
echo " </td>";
echo " <td width=\"150\"> ";
echo " <input type=\"text\" name=\"address\" value=\"".$row["address"]."\" >";
echo " </td>";
echo " </tr>";
echo "<tr> ";
echo " <td width=\"100\"> ";
echo " <div align=\"right\">Phone </div>";
echo " </td>";
echo " <td width=\"150\"> ";
echo " <input type=\"text\" name=\"phone\" value=\"".$row["phone"]."\" >";
echo " </td>";
echo " </tr>";
echo "<tr> ";
echo " <td width=\"100\"> ";
echo " <div align=\"right\">E-mail </div>";
echo " </td>";
echo " <td width=\"150\"> ";
echo " <input type=\"text\" name=\"email\" value=\"".$row["email"]."\" >";
echo " </td>";
echo " </tr>";
echo " <tr> ";
echo " <td colspan=\"2\"> ";
echo " <div align=\"center\"> ";
echo " <input type=\"submit\" name=\"Submit\" value=\"Submit\">";
echo " </div>";
echo " </td>";
echo "</tr>";
}
?>
</table>
</form>
</body>
</html>
And this shows the PHP script that accepts the record entered by the end user and updates the record in the MySQL table:
<html>
<head>
<title>Updated Record</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<p> </p>
<?php
$host="";
$usrnm="root";
$passwrd="";
$database="myFIrstDb";
$tablename="customers";
$connection= mysql_connect($host,$usrnm,$passwrd)
or die("Connection failed!!! <br>");
$varselect=mysql_select_db($database)
or die("Database selection failed!!!");
$query="UPDATE customers SET address=\"". $address .
"\" , phone=\"".$phone."\" WHERE custid=\"".$custid."\"";
$varquery=mysql_query($query);
if (!$varquery)
{
die(" Query execution failed!!!<br>");
}
else
{
echo "<table border=\"0\" align=\"center\" cellspacing=\"1\"
cellpadding=\"5\" width=\"300\">";
echo " <tr> ";
echo " <td colspan=\"2\"> ";
echo " <center><b>".mysql_affected_rows()."record is updated successfully</b></center>";
echo " </td>";
echo " </tr>";
echo " <tr> ";
echo " <td width=\"100\"> ";
echo " <div align=\"right\">Customer ID</div>";
echo " </td>";
echo " <td width=\"200\">".$custid. "</td>";
echo " </tr>";
echo " <tr> ";
echo " <td width=\"100\"> ";
echo " <div align=\"right\">Name</div>";
echo " </td>";
echo " <td width=\"200\">".$name. "</td>";
echo " </tr>";
echo " <tr> ";
echo " <td width=\"100\"> ";
echo " <div align=\"right\">Age</div>";
echo " </td>";
echo " <td width=\"200\">".$age. "</td>";
echo " </tr>";
echo " <tr> ";
echo " <td width=\"100\"> ";
echo " <div align=\"right\">Sex</div>";
echo " </td>";
echo " <td width=\"200\">".$sex. "</td>";
echo " </tr>";
echo " <tr> ";
echo " <td width=\"100\"> ";
echo " <div align=\"right\">Address</div>";
echo " </td>";
echo " <td width=\"200\">".$address. "</td>";
echo " </tr>";
echo " <tr> ";
echo " <td width=\"100\"> ";
echo " <div align=\"right\">Phone</div>";
echo " </td>";
echo " <td width=\"200\">".$phone. "</td>";
echo " </tr>";
echo " <tr> ";
echo " <td width=\"100\"> ";
echo " <div align=\"right\">E-mail</div>";
echo " </td>";
echo " <td width=\"200\">".$email. "</td>";
echo " </tr>";
echo " <tr> ";
echo " <td colspan=\"2\"> </td>";
echo " </tr>";
echo "</table>";
}
?>
</body>
</html>
You need to run the HTML forms and PHP scripts to update records in the MySQL table.
::: Final Thoughts :::
So far I am impressed with MySql. It appears to be fast and efficient. The PHP integration, as you have just seen, is quite easy. I look forward to working with MySql in the future.
Regards,
TroPe!
| 
Copyright Open Source Institute, 2006 
