Quakenet/#php Tutorial

Note: If you opened this page from an external URL pay attention that all chapters are linked together. Be sure you also read all prior chapters of this tutorial, otherwise you will miss relevant content explained before.

MySQL / Access via php

  1. MySQLi

1. MySQLi

To get back to php: we wants to access the database via php. As we cannot simply write the sql query inside the php code we need something to access the mysql database. We use the mysqli extension in the OOP style.

The mysqli extension provide us with some classes to work with mysql. First we use the MySQLi class. We login into the database with the constructor.

<?php
$db 
= new mysqli('localhost''username''password''database');
?>

If no connection could be established an warning is generated. We suppress this error with a @ before the new operator. This way no error messages from this line are shown. This doesn't mean you should write @ everywhere. Its just the error message which isn't show, the error still exists. Accordingly to the manual we should use mysqli_connect_errno to check if the connection is established. If this function returns 0 the connection is established, otherwise the mysqli_connect_error tells us why not.

<?php
$db 
= @new mysqli('localhost''username''password''database');
if (
mysqli_connect_errno()) {
    die (
'Could not open a mysql connection: '.mysqli_connect_error().'('.mysqli_connect_errno().')');
}
?>

As we got our MySQLi object now we can work with it. We use the query() method (MySQLi::query) to send a query, but we can also use prepare() (MySQLi::prepare) to create complex queries. The parameter is always a sql query which you want to send to the database. If you use a SELECT query an MySQLi_Result object is returned.

<?php
$db 
= @new mysqli('localhost''username''password''database');
if (
mysqli_connect_errno()) {
    die (
'Could not open a mysql connection: '.mysqli_connect_error().'('.mysqli_connect_errno().')');
}
$sql 'SELECT
    title,
    added
FROM
    news'
;
$result $db->query($sql);
?>

As every query can fail we always check for errors. You can check the return value of query(). If it is false there is an error and the error message is saved in the error field of the MySQLi object.

<?php
$db 
= @new mysqli('localhost''username''password''database');
if (
mysqli_connect_errno()) {
    die (
'Could not open a mysql connection: '.mysqli_connect_error().'('.mysqli_connect_errno().')');
}
$sql 'SELECT
    title,
    added
FROM
    news'
;
$result $db->query($sql);
if (!
$result) {
    die (
'Something is wrong with the query: '.$db->error);
}
var_dump($db$result);    
?>

We check what kind of errors we have with the var_dump function.

object(mysqli)#1 (0) {
}
object(mysqli_result)#2 (0) {
}

The first one is our MySQLi object, the second one is the created MySQLi_Result object we are now working with. We show first how big the result set is. The number of rows is saved in the num_rows field. (MySQLi_Result::num_rows).

<?php
$db 
= @new mysqli('localhost''username''password''database');
if (
mysqli_connect_errno()) {
    die (
'Could not open a mysql connection: '.mysqli_connect_error().'('.mysqli_connect_errno().')');
}
$sql 'SELECT
    title,
    added
FROM
    news'
;
$result $db->query($sql);
if (!
$result) {
    die (
'Something is wrong with the query: '.$db->error);
}
echo 
'The result set got '.$result->num_rows." rows<br />\n";
?>

In our case we have 2 rows in our table. To access one row we use the fetch_assoc() method (MySQLi_Result::fetch_assoc). This method returns an array which contains all data from a row. Each call of fetch_assoc() returns the next row. If the end of the result set is reached this method returns NULL.

<?php
$db 
= @new mysqli('localhost''username''password''database');
if (
mysqli_connect_errno()) {
    die (
'Could not open a mysql connection: '.mysqli_connect_error().'('.mysqli_connect_errno().')');
}
$sql 'SELECT
    title,
    added
FROM
    news'
;
$result $db->query($sql);
if (!
$result) {
    die (
'Something is wrong with the query: '.$db->error);
}
echo 
'The result set got '.$result->num_rows." rows<br />\n";
var_dump($result->fetch_assoc());
var_dump($result->fetch_assoc());
var_dump($result->fetch_assoc());
var_dump($result->fetch_assoc());
?>

This code creates the following output:

The result set got 2 rows<br />
array(2) {
  ["title"]=>
  string(0) ""
  ["added"]=>
  string(19) "0000-00-00 00:00:00"
}
array(2) {
  ["title"]=>
  string(16) "My first news"
  ["added"]=>
  string(19) "2008-08-17 14:02:08"
}
NULL
NULL

As you see the first to calls returns the rows from the result set. After then the method returns only NULL values. This allowes you to use fetch_assoc() in a while loop.

<?php
$db 
= @new mysqli('localhost''username''password''database');
if (
mysqli_connect_errno()) {
    die (
'Could not open a mysql connection: '.mysqli_connect_error().'('.mysqli_connect_errno().')');
}
$sql 'SELECT
    title,
    added
FROM
    news'
;
$result $db->query($sql);
if (!
$result) {
    die (
'Something is wrong with the query: '.$db->error);
}
echo 
'The result set got '.$result->num_rows." rows<br />\n";
while (
$row $result->fetch_assoc()) {  // NULL is equivalent to false
    // $row is now the array with the values.
    
echo 'The news "'.$row['title'].'" was written at "'.$row['added']."\"<br />\n";
}
?>

If we output all rows we can close the result set with close() (MySQLi_Result::close).

<?php
$db 
= @new mysqli('localhost''username''password''database');
if (
mysqli_connect_errno()) {
    die (
'Could not open a mysql connection: '.mysqli_connect_error().'('.mysqli_connect_errno().')');
}
$sql 'SELECT
    title,
    added
FROM
    news'
;
$result $db->query($sql);
if (!
$result) {
    die (
'Something is wrong with the query: '.$db->error);
}
echo 
'The result set got '.$result->num_rows." rows<br />\n";
while (
$row $result->fetch_assoc()) {  // NULL is equivalent to false
    // $row is now the array with the values.
    
echo 'The news "'.$row['title'].'" was written at "'.$row['added']."\"<br />\n";
}
unset(
$result); // you can delete the variable with unset() as it isn't needed anymore.
?>

If you want you can close the connection to the database with the close() method (MySQLi::close) but this happends at the end of your script anyway.

Questions about the chapter

No questions

Back to Next to
Copyright © to the OPs of #php/QuakeNet Valid XHTML 1.0 Strict Valid CSS!