PHP 101 (part 8): Databases and Other Animals - Part 2
PHP 101 (part 8): Databases and Other Animals - Part 1
Surgical Insertion
Wiping Out
Looking Inside
Oops!
Surgical Insertion
So now you know how to execute a SELECT query to retrieve a
result set from the database. However, you can also use PHP's MySQL API for
queries that don't return a result set - for example, an INSERT
or UPDATE query. Consider the following example, which demonstrates
this by asking for user input through a form and then INSERT-ing
that data into the database:
<html>
<head>
<basefont face="Arial">
</head>
<body>
<?php
if (!isset($_POST['submit'])) {
// form not submitted
?>
<form action="<?=$_SERVER['PHP_SELF']?>" method="post">
Country: <input type="text" name="country">
National animal: <input type="text" name="animal">
<input type="submit" name="submit">
</form>
<?php
}
else {
// form submitted
// set server access variables
$host = "localhost";
$user = "test";
$pass = "test";
$db = "testdb";
// get form input
// check to make sure it's all there
// escape input values for greater safety
$country = empty($_POST['country']) ? die ("ERROR: Enter a country") : mysql_escape_string($_POST['country']);
$animal = empty($_POST['animal']) ? die ("ERROR: Enter an animal") : mysql_escape_string($_POST['animal']);
// open connection
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");
// select database
mysql_select_db($db) or die ("Unable to select database!");
// create query
$query = "INSERT INTO symbols (country, animal) VALUES ('$country', '$animal')";
// execute query
$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());
// print message with ID of inserted record
echo "New record inserted with ID ".mysql_insert_id();
// close connection
mysql_close($connection);
}
?>
</body>
</html>
Here, the user is first presented with a form asking for a country and its national animal.
Once the form is submitted, the form input is used inside to create
an INSERT query, which is then sent to the database with
the mysql_query() method. Since mysql_query()
returns a Boolean value indicating whether the query was successful or
not, it is possible to check whether the INSERT took place
and return an appropriate message:
There are two new functions in the example above. The
mysql_escape_string() function escapes special characters
(like quotes) in the user input so that it can be safely entered into the
database; while the mysql_insert_id() returns the ID
generated by the previous INSERT query (useful only if the
table into which the INSERT occurs contains an
AUTO_INCREMENT field). Both these functions are also available
in ext/mysqli.
Wiping Out
Obviously, you can also do the same thing with other data manipulation
statements. This next example demonstrates how to use a DELETE
statement with PHP to selectively delete items from the table. For variety,
I'm going to use ext/mysqli this time around:
<html>
<head>
<basefont face="Arial">
</head>
<body>
<?php
// set server access variables
$host = "localhost";
$user = "test";
$pass = "test";
$db = "testdb";
// create mysqli object
// open connection
$mysqli = new mysqli($host, $user, $pass, $db);
// check for connection errors
if (mysqli_connect_errno()) {
die("Unable to connect!");
}
// if id provided, then delete that record
if (isset($_GET['id'])) {
// create query to delete record
$query = "DELETE FROM symbols WHERE id = ".$_GET['id'];
// execute query
if ($mysqli->query($query)) {
// print number of affected rows
echo $mysqli->affected_rows." row(s) affected";
}
else {
// print error message
echo "Error in query: $query. ".$mysqli->error;
}
}
// query to get records
$query = "SELECT * FROM symbols";
// execute query
if ($result = $mysqli->query($query)) {
// see if any rows were returned
if ($result->num_rows > 0) {
// yes
// print them one after another
echo "<table cellpadding=10 border=1>";
while($row = $result->fetch_array()) {
echo "<tr>";
echo "<td>".$row[0]."</td>";
echo "<td>".$row[1]."</td>";
echo "<td>".$row[2]."</td>";
echo "<td><a href=".$_SERVER['PHP_SELF']."?id=".$row[0].">Delete</a></td>";
echo "</tr>";
}
}
// free result set memory
$result->close();
}
else {
// print error message
echo "Error in query: $query. ".$mysqli->error;
}
// close connection
$mysqli->close();
?>
</body>
</html>
Here's what it looks like:
Notice my usage of the affected_rows property of the
mysqli object here - this returns the total number of rows
affected by the last operation. It's available in ext/mysql
as well, as the function mysql_affected_rows().
Looking Inside
PHP comes with a bunch of functions designed to tell you everything you would ever want to know about the MySQL client and server, their version numbers, the total number of databases available, the tables inside each database, the processes running... you name it, and it's probably there. Here's an example which uses them to give you a big-picture view of what's going on inside your MySQL RDBMS:
<html>
<head>
<basefont face="Arial">
</head>
<body>
<?php
// set server access variables
$host = "localhost";
$user = "root";
$pass = "guessme";
$db = "testdb";
// open connection
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");
// get database list
$query = "SHOW DATABASES";
$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());
echo "<ul>";
while ($row = mysql_fetch_array($result)) {
echo "<li>".$row[0];
// for each database, get table list and print
$query2 = "SHOW TABLES FROM ".$row[0];
$result2 = mysql_query($query2) or die ("Error in query: $query2. ".mysql_error());
echo "<ul>";
while ($row2 = mysql_fetch_array($result2)) {
echo "<li>".$row2[0];
}
echo "</ul>";
}
echo "</ul>";
// get version and host information
echo "Client version: ".mysql_get_client_info()."<br />";
echo "Server version: ".mysql_get_server_info()."<br />";
echo "Protocol version: ".mysql_get_proto_info()."<br />";
echo "Host: ".mysql_get_host_info()."<br />";
// get server status
$status = mysql_stat();
echo $status;
// close connection
mysql_close($connection);
?>
</body>
</html>
Here's what the output might look like:
The first part of this script is fairly simple: it runs the SHOW
DATABASES query to get a list of databases, then iterates over the
list and runs the SHOW TABLES command to retrieve the list
of tables inside each. Next, the mysql_get_*_info() functions
provide the client version number, the MySQL version number, the version
number of the special MySQL client-server protocol used for communication
between the two, the current host name, and how it is connected to the MySQL
server. Finally, new in PHP 4.3.0 is the mysql_stat() function,
which returns a string containing status information on the MySQL server
(including information on server uptime, open tables, queries per second
and other statistical information).
Oops!
All done? Nope, not quite yet - before you go out there and start
building cool data-driven Web sites, you should be aware that both
MySQL extensions come with powerful error-tracking functions which can
speed up development time. Take a look at the following example, which
contains a deliberate error in the SELECT query string:
<?php
// connect
$connection = mysql_connect("localhost", "test", "test") or die("Invalid server or user");
mysql_select_db("testdb", $connection) or die("Invalid database");
// query
$query = "SELECT FROM symbols";
// result
$result = mysql_query($query,$connection);
// look for errors and print
if(!$result) {
$error_number = mysql_errno();
$error_msg = mysql_error();
echo "MySQL error $error_number: $error_msg";
}
// disconnect
mysql_close($connection);
?>
Here's an example of the output:
The mysql_errno() function displays the error code returned
by MySQL if there's an error in your SQL statement, while the
mysql_error() function returns the actual error message. Turn
these both on, and you'll find that they can significantly reduce the time
you spend fixing bugs.
The ext/mysqli code tree includes two additional functions for
connection errors, mysqli_connect_errno() and
mysqli_connect_error(), which contain information on connection
(not query) errors only. Use these to debug errors in your MySQL connections,
as in the example below:
<?php
// create mysqli object
// open connection
$mysqli = new mysqli("localhost", "test", "test", "testdb");
// check for connection errors
if (mysqli_connect_errno()) {
die("Unable to connect: ".mysqli_connect_error());
}
// query
$query = "SELECT FROM symbols";
// execute query
$result = $mysqli->query($query);
// look for errors and print
if(!$result) {
$error_number = $mysqli->errno;
$error_msg = $mysqli->error;
echo "MySQL error $error_number: $error_msg";
}
// disconnect
$mysqli->close();
?>
And in case you were wondering why I haven't used object syntax for
these two functions in the script above, it's actually very simple: I
can't. You see, if there is an error in connecting to the server, the
mysqli() object will not be created, and so methods and
properties related to that object will not exist. For this reason, to
debug connection errors in ext/mysqli, you must always use
the procedural, rather than the object, notation.
And that's about all I have for this issue of PHP 101. In Part Nine I'm going to tell you all about PHP 5's built-in DBMS alternative, the very cool SQLite database engine. Don't miss it!
Copyright Melonfire, 2004 (http://www.melonfire.com). All rights reserved.
Comments
Again, this link to the next section works from the ToC.
WELL i MANAGED TO ADD RECORD TO MY DATABASE BUT WHEN I WANT TO ADD NEW RECORD, IT SAYS I HAVE SUCCESFULLY ENTERED THE RECORD BUT IN THE DATABASE THE NEW RECORD IS NOT SEEN.
gHASIA,
<?php
$host = "localhost";
$user = "root";
$pass = "";
$dbname = "cdco";
$connection=mysql_connect($host,$user,$pass);
$db=mysql_select_db($dbname);
//kupokea data toka kwenye form
$Staffid=$_POST['staffid'];
$Fname=$_POST['fname'];
$Mname=$_POST['mname'];
$Sex=$_POST['sex'];
$City=$_POST['city'];
$Officeno=$_POST['officeno'];
//tunataka kuondoa mgogoro wa mtu kutoingiza data hapa.//baadae natakiwa kucontrol office no ilingane na foreign key
IF ($Staffid==false||$Fname==false||$Mname==false||$City==false||$Sex==false||$Officeno==false)
{
echo "please make sure you fill all the fields of the form";
}
else
{
//tunaconnect na database kabla ya kuingiza data. nadhani connection ya mwanzo ni feki nitajaribu kuondoa nione
$connection=mysql_connect($host,$user,$pass);
$db=mysql_select_db($dbname);
$sql = "INSERT INTO staff (staffid,fname,mname,sex,city,officeno) values ('Staffid','$Fname','$Mname','$City','$Sex','$Officeno')";
$result=mysql_query($sql);
echo "you have succesfully entered the record to the table staff";
// close connection
mysql_close($connection);
};
//nadhani natakiwa kufunga connection kabla sijafunga php
?>
<form action="<?=$_SERVER['PHP_SELF']?>" method="post">.
The "<?" and "?>" should be replaced with "<?php" and "?>." The so-called "short tag" is a bad idea, and only works if you change Short_tags to "on" in your php.ini , which is not recommended. I couldn't get this code to work on my installation until I changed
"<?=$_SERVER['PHP_SELF']?>"
to
"<?php $_SERVER['PHP_SELF'] ?>".
Peace.