Performing Basic Tasks with MySQL 4.1 and Above, using mysqli with PHP 5 - Performing basic operations: connecting to MySQL, running queries and more
(Page 2 of 4 )
Assuming that the “mysqli” extension has been enabled in your PHP 5 distribution (for detailed installing instructions on different operating systems, please read the PHP manual), the first example that I’ll show you consists of connecting to the MySQL server in a procedural fashion. However, all the subsequent code samples will be developed by utilizing an object-based approach, so if you’re writing procedural PHP code, take a look at the online PHP documentation.
That said, here’s how to connect to MySQL via the “mysqli” extension:
// example using procedural approach
if(!$db=mysqli_connect('host','user','password','database')){
throw new Exception('Error connecting to host.
'.mysqli_connect_error());
}
// display host information
echo 'Host information: '.mysqli_get_host_info($db);
// close connection
mysqli_close($db);
The above example shows two significant things that I’d like to highlight: first, many of the functions and methods included with the “mysqli” library are similar to the “MySQL” functions that you use on a regular basis, and second, in most cases these functions also allow you to perform basic tasks, like connecting to MySQL in a much simpler way.
With reference to the prior example, notice how the “mysqli_connect()” functions take up four parameters for connecting to the server: the corresponding host, the user/password combination, and additionally the name of the database being selected.
Also, in case it fails to connect to the server, the script uses the “mysql_connect_error()” function to display an error message and eventually halt the program’s execution. On the other hand, if the connection to MySQL has been successfully established, some basic information about the host is displayed by the “mysqli_get_host_info()” function, and finally the connection is properly closed by another handy function, in this case, “mysqli_close().”
As you can see, the programming flow followed by the script you just saw is nearly identical to a typical MySQL-connecting snippet, used hundreds of times before. Nevertheless, to make things really exciting, let me show you the object-oriented version of the previous example, in conjunction with a few additional lines of code. Here is how this new example looks:
// MySQL connection example using an OOP approach
$mysqli=new mysqli('host','user','password','database');
if(mysqli_connect_errno()){
trigger_error('Error connecting to host. '.$mysqli-
>error,E_USER_ERROR);
}
if(!$result=$mysqli->query('SELECT * FROM users')){
trigger_error('Error running query. '.$mysqli-
>error,E_USER_ERROR);
}
// display number of returned rows
echo 'Number of returned rows: '.$result->num_rows.'<br />';
// display rows
while($row=$result->fetch_array(MYSQLI_ASSOC)){
echo $row['name'].'<br />';
}
// close result set
$result->close();
// close connection
$mysqli->close();
If you examine the above script, you’ll realize that there are several things worth noting. To begin with, connecting to MySQL is performed via the “mysqli” class, which accepts the typical connection parameters –- host, user and password respectively -- along with the database being used, while eventual connection errors are displayed by the “error” class property.
With regard to running regular queries (in this case a simple SELECT statement), the script uses the “query()” method, which as you see returns a MySQL result set represented by the $result object.
From this point onward, database rows can be easily fetched by using the “fetch_array()” method, which surely will be familiar to you, since it looks very similar to the regular “mysql_fetch_array()” function available within the MySQL library.
Finally, after traversing the pertinent result set and displaying rows as an associative array (note the implementation of the MySQLI_ASSOC constant), the data set is first closed prior to closing the corresponding MySQL connection. In both cases, to perform these tasks, the script uses the intuitive “close()” method.
As you can see, connecting to MySQL, fetching a few database rows and finally closing the connection is indeed a straightforward process, which should be quite familiar, if you’ve been working with PHP and MySQL for a while.
So far, the example you learned before is quite illustrative for understanding how the “mysqli” extension works. Most MySQL-related tasks are performed by using a combination of properties and methods that belongs to the “mysqli” class, which is very similar to many of the functions included within the regular MySQL library (for instance the “mysql_fetch_array()”, “mysql_fetch_row()”, “mysql_fetch_object()” functions, and so on).
Also, the implementation of result set objects (when applicable) is very convenient for separating the logic to connecting to MYSQL and selecting databases, from the logic required for handling rows. Of course, in this series I’ll be showing you how to use the most important methods and properties, so if you want to learn more about additional methods, again make sure to check out the PHP manual.
At this point, you learned how to connect to MySQL, display the returned rows after performing a simple SELECT query, and close the corresponding server connection, with the help of some handy methods and properties. Therefore, the next step consists of setting up another practical example, which will show how to execute multiple queries in only one step. Sounds pretty good, right? Thus, continue reading to learn how this will be achieved.
Next: Leveraging the real power of the “mysqli” extension: running multiple queries >>
More MySQL Articles
More By Alejandro Gervasio
/ 16 
