Sponsors

Friday, 1 November 2013

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

12:58 By No comments


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 errorsif (mysqli_connect_errno()) {

    die("Unable to connect!");

}
// if id provided, then delete that recordif (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 queryif ($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 informationecho "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 connectionmysql_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 printif(!$result) {

    $error_number = mysql_errno();

    $error_msg = mysql_error();

    echo "MySQL error $error_number: $error_msg";    
}
// disconnectmysql_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 errorsif (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 printif(!$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!

0 Comments:

Post a Comment