PHP and MySQL access
1. Start with a simple table in the database:
mysql > SELECT * FROM employeesIn the following exercises I used a MySQL tool called HeidiSql
Here are some screen shots of the exercise:
2. Create a web page with the following PHP:
<?php
$db = mysql_connect("farrer.csu.edu.au", "keustace", "password");
$result = mysql_query("SELECT * FROM employees", $db);
echo "First Name: ", mysql_result($result, 0, "first"), "<BR>";
echo "Last Name: ", mysql_result($result, 0, "first"), "<BR>";
echo "Address: ", mysql_result($result, 0, "first"), "<BR>";
echo "Position: ", mysql_result($result, 0, "first"), "<BR>";
?>Here is a screenshot of the code and the rendered pages:
3. This is how we can add a record and is part of a file to create called add_record.html:
<HTML>
<BODY>
<FORM METHOD="POST" ACTION="add_record.php">
First Name: <INPUT TYPE="Text" NAME="first">
Last Name: <INPUT TYPE="Text" NAME="last">
Address: <INPUT TYPE="Text" NAME="address">
Position: <INPUT TYPE="Text" NAME="position">
<INPUT TYPE="Submit" NAME="submit" VALUE="Enter information">
</FORM>
</BODY>
</HTML>
My HTML page:
And rendered:
4. The corresponding PHP file is add_record.php used with the POST method:
<?php
$db = mysql_connect("farrer.csu.edu.au", "keustace", "password");
mysql_select_db("mydatabase", $db);
$result = mysql_query("INSERT INTO employees (first, last, address, position) VALUES ('$first', '$last', '$address', '$position')");
if ($result == 1) {echo "Thank you!" Your information has been entered.";
}else{
echo "Sorry, there's a problem";
}
?>My PHP page:
Enter some data:
Display a diagnostic message after processing:
Check that the data has been added to the database:
5. The last code example shows how to get multiple records:
<?php
$db = mysql_connect("farrer.csu.edu.au", "keustace", "password");
mysql_select_db("mydatabase", $db);
$result = mysql_query("SELECT * FROM employees", $db);
echo "<table border=1>\n";
echo "<tr><td><b>Name</b></td><td><b>Position</b></td><tr>\n";
while ($myrow = $mysql_fetch_row($result)) {
echo "<tr><td>", $myrow[2], ", ", $myrow[1], "</td></tr>",
$myrow[3], "</td></tr>";
}
echo "</table><BR>";
?>
My code:
The results:
