Deprecated: Function ereg() is deprecated in /home3/vvaswani/db-mysql.php on line 174

Deprecated: Function split() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 60
The Melonfire Community - Trog
Logo         Community
  Trog


Copyright notice:

This article is copyright Melonfire,
Strict Standards: mktime(): You should be using the time() function instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 119
2017. All rights reserved.

All source code, brand names, trademarks and other content contained herein is proprietary to Melonfire,
Strict Standards: mktime(): You should be using the time() function instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 121
2017. All rights reserved.

Source code within this article is provided with NO WARRANTY WHATSOEVER. It is meant for illustrative purposes only, and is NOT recommended for use in production environments.

Copyright infringement is a violation of law.

Printed from http://www.melonfire.com/community/columns/trog/article.php?id=263



PHP 101 (part 8): Databases And Other Animals
Hook your PHP scripts up to a MySQL database.


Deprecated: Function eregi_replace() is deprecated in /home3/vvaswani/sql.php on line 301

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 150

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 152

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 167

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 168

Deprecated: Function split() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 170
Mix and Match

One of the most compelling things PHP has going for it is its support for a variety of database management systems, including MySQL, PostgreSQL, Oracle and Microsoft Access. By virtue of this support, PHP developers can create sophisticated data-driven Web applications at a fraction of the time and cost required by competing alternatives. And nowhere is this more clear than in PHP's longtime support of MySQL, the very fast, very reliable and very feature-rich open-source RDBMS.

By using PHP and MySQL together, developers can benefit from huge savings on the licensing costs of commercial alternatives, and also leverage off the tremendous amount of thought PHP and MySQL developers have put into making sure that the two packages work together seamlessly and smoothly. And since both PHP and MySQL are open-source projects, when you use the two of them together you know you're getting the most up-to-date technology available. And that's always a good thought to go to bed with.

OK. Enough of the marketing talk. Let's get down to business.

In this issue of PHP 101, I'm going to show you how to use PHP to extract data from a database, and use that data to dynamically build a Web page. In order to try out the examples in this tutorial, you'll need a working MySQL installation, which you can obtain from the MySQL Web site at http://www.mysql.com/. If you have some knowledge of SQL (Structured Query Language, the language used to interact with a database server) you'll find it helpful, but it's not essential.


Deprecated: Function eregi_replace() is deprecated in /home3/vvaswani/sql.php on line 301

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 150

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 152

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 167

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 168

Deprecated: Function split() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 170
Building Blocks

In order to use MySQL and PHP together, your PHP build must include support for MySQL. On UNIX, this is accomplished by adding the --with-mysql option to the configure script when building PHP on UNIX, and pointing PHP to the MySQL client libraries. On Windows, the MySQL client libraries are built in to PHP 4.x and activated by default. In PHP 5.x, pre-built .dll files are included with the Windows distribution. Read more about this at http://www.php.net/manual/en/ref.mysql.php.

Unix users should note that PHP 4.x ships with a set of MySQL client libraries, which are activated by default; however, PHP 5.x no longer bundles these libraries due to licensing issues, so you need to obtain, install and activate them yourself. They're included with the MySQL distribution, and are installed automatically when you install MySQL. To activate the MySQL extension, ext/mysql, add the --with-mysql option to PHP's configure script. For more information on this change, read http://www.php.net/manual/en/faq.databases.php#faq.databases.mysql.php5.

And finally (as if all that wasn't quite confusing enough) PHP 5.x also comes with a new MySQL extension, called ext/mysqli (MySQL Improved). You can use this new extension to access the new features in MySQL 4.1.2 or better, and to gain the benefits of improved speed and security. To activate this extension on UNIX, add the --with-mysqli option to PHP's configure script, and point PHP to the mysql_config program that comes with MySQL 4.1 and above. For Windows users, a pre-built version of ext/mysqli is included in the Win32 PHP distribution. Read more about this at http://www.php.net/manual/en/ref.mysqli.php.

To figure out which extension you need, use the following rule of thumb:

- If you need the new features in MySQL 4.1.2 or better, or if you're using an older version of MySQL but still want to benefit from the speed/security improvements in the new extension, use ext/mysqli.

- If you don't fall into either of the categories above, or don't know what I'm talking about, use regular ext/mysql.

In case you were wondering, this tutorial covers both ext/mysql and ext/mysqli, so you actually get two for the price of one. Keep reading, and let me introduce you to MySQL.


Deprecated: Function eregi_replace() is deprecated in /home3/vvaswani/sql.php on line 301

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 150

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 152

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 167

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 168

Deprecated: Function split() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 170
Animal Magnetism

Every MySQL database is composed of one or more tables. These tables, which structure data into rows and columns, are what lend organization to the data.

Here's an example of what a typical table looks like:


+----+-----------+----------+
| id | country   | animal   |
+----+-----------+----------+
|  1 | America   | eagle    |
|  2 | China     | dragon   |
|  3 | England   | lion     |
|  4 | India     | tiger    |
|  5 | Australia | kangaroo |
|  6 | Norway    | elk      |
+----+-----------+----------+


As you can see, a table divides data into rows, with a new entry (or record) on every row. The data in each row is further broken down into cells (or fields), each of which contains a value for a particular attribute of the data. For example, if you consider the record for the country "India", you'll see that the record is clearly divided into separate fields for record number, country name and national animal.

The rows within a table are not arranged in any particular order - they can be sorted alphabetically, by number, by name, or by any other criteria you choose to specify. It is therefore necessary to have some method of identifying a specific record in a table. In the example above, each record is identified by a unique number; this unique field is referred to as the primary key for that table.

You use the Structured Query Language, SQL, to interact with the MySQL server and tell it to create a table, mark a field as primary, insert records, edit records, retrieve records... basically, anything that involves manipulating the data or the database. To see how this works, examine the following SQL, which creates the table above:


CREATE DATABASE testdb;

CREATE TABLE `symbols` (
    `id` int(11) NOT NULL auto_increment,
    `country` varchar(255) NOT NULL default '',
    `animal` varchar(255) NOT NULL default '',
    PRIMARY KEY  (`id`)
) TYPE=MyISAM;

INSERT INTO `symbols` VALUES (1, 'America', 'eagle');
INSERT INTO `symbols` VALUES (2, 'China', 'dragon');
INSERT INTO `symbols` VALUES (3, 'England', 'lion');
INSERT INTO `symbols` VALUES (4, 'India', 'tiger');
INSERT INTO `symbols` VALUES (5, 'Australia', 'kangaroo');
INSERT INTO `symbols` VALUES (6, 'Norway', 'elk');


You can enter these commands either interactively or non-interactively through the MySQL client program. Read http://dev.mysql.com/doc/mysql/en/mysql.html for more information on how to use the MySQL client, and the tutorial at http://www.melonfire.com/community/columns/trog/article.php?id=39 to understand what each of the SQL commands above does. SQL is a lot like spoken English, so it won't take you very long to pick it up.

Once the data has been imported, run a quick SELECT query to check that everything is working as it should be:


mysql> SELECT * FROM `symbols`;
+----+-----------+----------+
| id | country   | animal   |
+----+-----------+----------+
|  1 | America   | eagle    |
|  2 | China     | dragon   |
|  3 | England   | lion     |
|  4 | India     | tiger    |
|  5 | Australia | kangaroo |
|  6 | Norway    | elk      |
+----+-----------+----------+
6 rows in set (0.06 sec)


In English, the query above means "show me all the records from the table named symbols". If you saw the same output as above, you're good to go!


Deprecated: Function eregi_replace() is deprecated in /home3/vvaswani/sql.php on line 301

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 150

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 152

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 167

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 168

Deprecated: Function split() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 170


Deprecated: Function eregi_replace() is deprecated in /home3/vvaswani/sql.php on line 301

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 150

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 152

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 167

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 168

Deprecated: Function split() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 170
Different Strokes...

You can also use PHP's mysql_fetch_row() and list() functions to obtain a simple array of values, and then assign these values to different variables - a variation of the technique in the previous section. Take a look (only the while() loop changes):


<html>
<head>
<basefont face="Arial">
</head>
<body>

<?php

// set server access variables
$host = "localhost";
$user = "test";
$pass = "test";
$db = "testdb";

// 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 = "SELECT * FROM symbols";

// execute query
$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());

// see if any rows were returned
if (mysql_num_rows($result) > 0) {
     // yes
     // print them one after another
     echo "<table cellpadding=10 border=1>";
     while(list($id, $country, $animal)  = mysql_fetch_row($result)) {
          echo "<tr>";
          echo "<td>$id</td>";
          echo "<td>$country</td>";
          echo "<td>$animal</td>";
          echo "</tr>";
     }
     echo "</table>";
}
else {
     // no
     // print status message
     echo "No rows found!";
}

// free result set memory
mysql_free_result($result);

// close connection
mysql_close($connection);

?>

</body>
</html>


In this case, the list() function is used to assign different elements of the result set to PHP variables, which are then used when rendering the page.

You can use PHP's mysql_fetch_assoc() function to represent each row as an associative array of field-value pairs - a minor variation of the technique used above:


<html>
<head>
<basefont face="Arial">
</head>
<body>

<?php

// set server access variables
$host = "localhost";
$user = "test";
$pass = "test";
$db = "testdb";

// 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 = "SELECT * FROM symbols";

// execute query
$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());

// see if any rows were returned
if (mysql_num_rows($result) > 0) {
    // yes
    // print them one after another
    echo "<table cellpadding=10 border=1>";
    while($row = mysql_fetch_assoc($result)) {
        echo "<tr>";
        echo "<td>".$row['id']."</td>";
        echo "<td>".$row['country']."</td>";
        echo "<td>".$row['animal']."</td>";
        echo "</tr>";
    }
    echo "</table>";
}
else {
    // no
    // print status message
    echo "No rows found!";
}

// free result set memory
mysql_free_result($result);

// close connection
mysql_close($connection);

?>

</body>
</html>


Notice that in this case, field values are accessed using the field name instead of the index.

Of all the alternatives, however, the function I like the most is the mysql_fetch_object() function, which returns each row as an object (remember them from last time?) with properties corresponding to the field names:


<html>
<head>
<basefont face="Arial">
</head>
<body>

<?php

// set server access variables
$host = "localhost";
$user = "test";
$pass = "test";
$db = "testdb";

// 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 = "SELECT * FROM symbols";

// execute query
$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());

// see if any rows were returned
if (mysql_num_rows($result) > 0) {
    // yes
    // print them one after another
    echo "<table cellpadding=10 border=1>";
    while($row  = mysql_fetch_object($result)) {
        echo "<tr>";
        echo "<td>".$row->id."</td>";
        echo "<td>".$row->country."</td>";
        echo "<td>".$row->animal."</td>";
        echo "</tr>";
    }
    echo "</table>";
}
else {
    // no
    // print status message
    echo "No rows found!";
}

// free result set memory
mysql_free_result($result);

// close connection
mysql_close($connection);

?>

</body>
</html>


Here, each $row object is created with properties corresponding to the field names in that row. Row values can thus be accessed using standard object->property notation.

If you're the type that likes to have your cake and eat it too, you will probably enjoy the mysql_fetch_array() function, which returns both an associative array and a numerically-indexed array, a combination of the mysql_fetch_row() and mysql_fetch_assoc() functions. Read about it at http://www.php.net/manual/en/function.mysql-fetch-array.php.

PRBK * ...For Different Folks

If you're using PHP 5.x, you can do the same thing using the new ext/mysqli extension, which offers a number of new features. This extension can be used in two ways: procedural (using functions), and object-oriented (using class methods and properties). Consider the next script, which uses ext/mysqli in a procedural manner:


<html>
<head>
<basefont face="Arial">
</head>
<body>

<?php

// set server access variables
$host = "localhost";
$user = "test";
$pass = "test";
$db = "testdb";

// open connection
$connection = mysqli_connect($host, $user, $pass, $db) or die ("Unable to connect!");

// create query
$query = "SELECT * FROM symbols";

// execute query
$result = mysqli_query($connection, $query) or die ("Error in query: $query. ".mysqli_error());

// see if any rows were returned
if (mysqli_num_rows($result) > 0) {
    // yes
    // print them one after another
    echo "<table cellpadding=10 border=1>";
    while($row = mysqli_fetch_row($result)) {
        echo "<tr>";
        echo "<td>".$row[0]."</td>";
        echo "<td>".$row[1]."</td>";
        echo "<td>".$row[2]."</td>";
        echo "</tr>";
    }
    echo "</table>";
}
else {
    // no
    // print status message
    echo "No rows found!";
}

// free result set memory
mysqli_free_result($result);

// close connection
mysqli_close($connection);

?>

</body>
</html>


As you can see, this looks a lot like the code written for ext/mysql. The only real difference - at least to the naked eye - is the fact that function names now begin with mysqli_* instead of mysql_*. Of course, there are a whole bunch of differences under the hood: ext/mysqli is faster, more secure and more powerful than regular ext/mysql, and also includes support for prepared statements, bound result sets, multiple simultaneous queries, transactions and a whole bunch of other cool stuff.

You can also use ext/mysqli in an object-oriented way, where each task - connecting, querying, fetching - is actually a method of the mysqli() object:


<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!");
}

// create query
$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 "</tr>";
        }
        echo "</table>";
    }
    else {
        // no
        // print status message
        echo "No rows found!";
    }

    // free result set memory
    $result->close();
}
else {
    // print error message
    echo "Error in query: $query. ".$mysqli->error;
}
// close connection
$mysqli->close();

?>

</body>
</html>


Here, the new keyword is used to instantiate an object of class mysqli, and pass the object constructor connection information (including the database name). The resulting object, stored in the variable $mysqli, then exposes methods and properties to perform the tasks of querying, fetching and processing rows, and handling errors.

If you look closely at the two scripts above, you'll notice the numerous similarities between the function and method names, and the structure of the script. Of the two, though, the object-oriented method is recommended, especially in light of the new object model in PHP 5.x.

A couple of other important differences to keep in mind:

* With ext/mysqli, you can include the database name in the arguments passed to the mysqli_connect() function or to the mysqli()constructor.

* When calling mysqli_query() or the mysqli object's query() method, the link identifier is mandatory, not optional.


Deprecated: Function eregi_replace() is deprecated in /home3/vvaswani/sql.php on line 301

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 150

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 152

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 167

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 168

Deprecated: Function split() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 170
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.

Output image

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:

Output image

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.


Deprecated: Function eregi_replace() is deprecated in /home3/vvaswani/sql.php on line 301

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 150

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 152

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 167

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 168

Deprecated: Function split() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 170
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:

Output image

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


Deprecated: Function eregi_replace() is deprecated in /home3/vvaswani/sql.php on line 301

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 150

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 152

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 167

Deprecated: Function ereg_replace() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 168

Deprecated: Function split() is deprecated in /home3/vvaswani/public_html/community/columns/trog/print.php on line 170
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:

Output image

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

PBK * 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:

Output image

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. Next time, I'm going to tell you all about PHP 5.x's built-in DBMS alternative, the very cool SQLite database engine. Don't miss it!


Copyright notice:

This article is copyright Melonfire,
Strict Standards: mktime(): You should be using the time() function instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 193
2017. All rights reserved.

All source code, brand names, trademarks and other content contained herein and proprietary to Melonfire,
Strict Standards: mktime(): You should be using the time() function instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 195
2017. All rights reserved.

Source code within this article is provided with NO WARRANTY WHATSOEVER. It is meant for illustrative purposes only, and is NOT recommended for use in production environments.

Copyright infringement is a violation of law.

Printed from http://www.melonfire.com/community/columns/trog/article.php?id=263



Copyright © 1998-
Strict Standards: mktime(): You should be using the time() function instead in /home3/vvaswani/public_html/community/columns/trog/print.php on line 211
2017 Melonfire. All rights reserved
Terms and Conditions | Feedback