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.
