PHP 101 (part 8): Databases and Other Animals – Part 2

November 30, -0001

Tutorials

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.

Surgical Insertion - request form

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:

Surgical Insertion - submitted form

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:

Wiping Out

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:

Looking Inside

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:

Oops!

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.

13 Responses to “PHP 101 (part 8): Databases and Other Animals – Part 2”

  1. darklighterz Says:

    Nice tutorials, However just thought i would mention that both the insert and delete scripts use $_SERVER['PHP_SELF'] which has a major XSS vulnerability, as this is a tutorial i feel its important all those reading know the risks and get in the habbit of being security conscious.

    To demonstrate this attack load up the deletion script in a browser and paste this into the end of your url

    /%22%3E%3Cscript%3Ealert(‘XSS INJECTION VULNERABLE’)%3C
    /script%3E%3Cfoo%22

    Fixes for this are either using htmlentities

    i.e. replace

    $_SERVER['PHP_SELF']

    with

    htmlentities($_SERVER['PHP_SELF'])

    Or by using $_SERVER['SCRIPT_NAME'] for example in the deletion script

    replace

    echo "<td><a href=".$_SERVER['PHP_SELF']."?id=".$row[0].">Delete</a></td>";

    with

    echo "<td><a href=".$_SERVER['SCRIPT_NAME']."?id=".$row[0].">Delete</a></td>";

  2. j_smith1981 Says:

    Here’s an example of my title for this comment, good tutorial though, just worked out I could use multiple connections from mysqli, wonderful work!

    <html>
    <head>
    <title></title>
    </head>
    <body>
    <?php

    if(!isset($_POST['submit'])) {
    $message = ‘Please enter the details below, your list will be added to the database’;
    ?>
    <?=(isset($message)) ? "<p>$message:<p>" : ” ?>
    <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST">
    <p><label for="country">Country: </label><input type="text" id="country" name="country" />*<br />
    <label for="animal">Animal: </label><input type="text" id="animal" name="animal" />*</p>
    <input type="submit" id="submit" name="submit" value="Add" />
    </form>
    <?php
    } else {

    if(strlen(trim($_POST['country'])) > 0 &&
    strlen(trim($_POST['animal'])) > 0) {

    $host = ‘localhost’;
    $user = ‘myuser’;
    $password = ‘mypassword’;
    $database = ‘mydatabase’;

    $connection = mysql_connect($host, $user, $password) or die("<p>Error with system, please try again later</p>");
    mysql_select_db($database, $connection) or die("<p>Error with system, please try again later</p>");

    // if all is fine:
    $country = mysql_real_escape_string($_POST['country']);
    $animal = mysql_real_escape_string($_POST['animal']);

    $sql = "INSERT INTO zend_hello_database (country, animal) VALUES (‘$country’, ‘$animal’)";
    $result = mysql_query($sql);

    if($result) {
    echo ‘<p>You successfully added:<br />’."\n".’
    Country: ‘."$country<br />\n";
    echo "Animal: $animal</p>";

    echo "\n<p>Click <a href=\"05_Mysqli_Function_OOP.php\">here</a> to see the result\n";
    }

    } else {
    echo ‘<p>You missed out some required fields, please try again <a href="06_Surgical_Insertion.php">here</a></p>’;
    }

    }
    ?>
    </body>
    </html>

  3. sconnall Says:

    Excellent tutorials!!

    I’d also like to add that it appears that the mysqli_connect_errno() and mysqli_connect_error() now have object access formats.

    Here are the PHP manual links:

    http://us2.php.net/manual/en/mysqli.connect-errno.php
    http://us2.php.net/manual/en/mysqli.connect-error.php

    The following code in the tutorial can be replaced with the object access representations:

    OLD format:

    // check for connection errors
    if (mysqli_connect_errno()) {
    die("Unable to connect: ".mysqli_connect_error());
    }

    NEW format:

    // check for connection errors
    if ($mysqli->connect_errno) {
    die("Unable to connect: ".$mysqli->connect_error);

  4. jen-jay Says:

    First I must say these tutorials rock! I’m just winding down a 600 some page book on php and it’s excruciatingly dry. I zipped through your tutorials and found them very helpful and easy to read.

    I’m using a packaged download of a popular testing server on my Mac which includes all I need to run through these tutorials. I’m also using Dreamweaver which I’ve connected to the server and the database.

    My question or suggestion:

    When I did the Wiping Out section using the ext/mysqli I got an unable to connect to server error. The previous example worked fine.

    This is what I had to change and I don’t know why:
    // set server access variables
    $host = "localhost"; in the wiping out example worked fine instead of
    $host = "localhost:8888"; which worked in the first tutorial on this page and previous tutorials.

  5. akrynski Says:

    After executing the very first code from this page (adding new fields to table) I got an error 404 – Object not found.- Requested URL not found on this server. On address strip (after executing php script in browser)I have:
    http://localhost/galeria/pierwszy/&lt;?php=$_SERVER['PHP_SELF']?>
    Confusing…

  6. walldaima Says:

    Patoue, the page cannot be displayed because php doesn’t have shorttags set. See unregistered andy, above.

    Short tags shouldn’t be used. <? marks the start of a script, which could be in any number of scripting languages. Anyone who programs regularly should know that <?php is just good practice, as it determines which scripting processor to use explicitly. Depending on the hosting site, using <? might get you perl or python or some other flavor. Being explicit just removes a possible headache for you, and more importantly, provides a courtesy for those who follow in your footsteps and might have to care for your creation.

  7. Patoue Says:

    Great tutorial!

    I get the following error when running the Insert query
    Access forbidden!

    You don’t have permission to access the requested object. It is either read-protected or not readable by the server.

    If you think this is a server error, please contact the webmaster.
    Error 403

    Thanks for your support

  8. _____anonymous_____ Says:

    Why is setting shorttags on in your php not a good idea then to the above poster?

  9. _____anonymous_____ Says:

    In the form part of the first script (the part about using INSERT) there is a line that contains short tags:

    <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.

  10. _____anonymous_____ Says:

    pLEASE HELP!!!!!

    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
    ?>

  11. _____anonymous_____ Says:

    These tutorials are great, but i just thought i should point out that some of that code doesn’t work (and will return a 403 error [at least in my case]) unless you have short_open_tags set to ‘on’ in your php.ini

  12. Sageguru Says:

    I did not find anything on Updating Records, there were great stuffs on Retrieve, Insert and Delete, but not on update, but overall you truely have a good stuff its a one stop area for all levels of php developers i believe. Kudos. Kindly mail me something on <update> or the URL if its available but has escaped me. THANKS.

  13. dixon_pete Says:

    In Part Nine I’m going..

    Again, this link to the next section works from the ToC.