MySqli Object Oriented Style
Connecting with mysqli +
This could simply be done in the same manner as mysql_connect(), but mysqli offers us an Object Oriented (OO) interface to MySQL so it would be futile to do things "the old way". Lets see how we go.
<?php
/*** mysql hostname ***/
$hostname = 'localhost';
/*** mysql username ***/
$username = 'root';
/*** mysql password ***/
$password = 'rootpassword';
/*** create a new mysqli object ***/
$mysqli = @new mysqli($hostname, $username, $password);
/* check connection */
if(!mysqli_connect_errno())
{
/*** if we are successful ***/
echo 'Connected Successfully <br />';
/*** our sql statement ***/
$sql = 'CREATE DATABASE periodic_table';
if($mysqli->query($sql) === TRUE)
{
echo 'Database created successfully <br />';
}
else
{
echo $sql.'<br />' . $mysqli->error;
}
/*** close connection ***/
$mysqli->close();
}
else
{
/*** if we are unable to connect ***/
echo 'Unable to connect';
exit();
}
?>
Should we run this code twice, we would again get an get an error like the following
Connected Successfully
CREATE DATABASE periodic_table
Cannotcreate database periodic_table; database exists
You can see from the message, as in the previous example, we have a message to tell us that we have connected successfully, then the SQL query used followed by a message from the database itself telling us the exact nature of the error.
Create a user with mysqli +
Here also there are no changes except for the SQL. All the code remains as it was to create a database.
<?php
/*** mysql hostname ***/
$hostname = 'localhost';
/*** mysql username ***/
$username = 'root';
/*** mysql password ***/
$password = 'rootpassword';
/*** create a new mysqli object ***/
$mysqli = @new mysqli($hostname, $username, $password);
/* check connection */
if(!mysqli_connect_errno())
{
/*** if we are successful ***/
echo 'Connected Successfully<br />';
/*** sql to create a user ***/
$sql = "GRANT ALL ON periodic_table.* TO username@localhost IDENTIFIED BY 'password'";
if($mysqli->query($sql) === TRUE)
{
echo 'New user created successfully<br />';
}
else
{
echo $sql.'<br />' . $mysqli->error;
}
/*** close connection ***/
$mysqli->close();
}
else
{
/*** if we are unable to connect ***/
echo 'Unable to connect';
exit();
}
?>
Creating a Table with mysqli +
As with the script above, we no longer need to use the root username and password for creating our table. An extra parameter is added to our class instantiation that contains the name of the default database to use for our script. We assign this to a variable at the top of our script with the other variables.
<?php
/*** mysql hostname ***/
$hostname = 'localhost';
/*** mysql username ***/
$username = 'username';
/*** mysql password ***/
$password = 'password';
/*** mysql database name ***/
$dbname = 'periodic_table';
/*** create a new mysqli object with default database***/
$mysqli = @new mysqli($hostname, $username, $password, $dbname);
/* check connection */
if(!mysqli_connect_errno())
{
/*** if we are successful ***/
echo 'Connected Successfully<br />';
/*** sql to create a new table ***/
$sql = "CREATE TABLE elements (
atomicnumber tinyint(3) NOT NULL default '0',
latin varchar(20) NOT NULL default '',
english varchar(20) NOT NULL default '',
abbr char(3) NOT NULL default '',
PRIMARY KEY (atomicnumber)
)";
if($mysqli->query($sql) === TRUE)
{
echo 'New table created successfully<br />';
}
else
{
echo $sql.'<br />' . $mysqli->error;
}
/*** close connection ***/
$mysqli->close();
}
else
{
/*** if we are unable to connect ***/
echo 'Unable to connect';
exit();
}
?>
INSERT data with mysqli +
<?php
/*** mysql hostname ***/
$hostname = 'localhost';
/*** mysql username ***/
$username = 'username';
/*** mysql password ***/
$password = 'password';
/*** mysql database name ***/
$dbname = 'periodic_table';
/*** create a new mysqli object with default database***/
$mysqli = @new mysqli($hostname, $username, $password, $dbname);
/* check connection */
if(!mysqli_connect_errno())
{
/*** if we are successful ***/
echo 'Connected Successfully<br />';
/*** sql to INSERT a new record ***/
$sql = "INSERT INTO elements (atomicnumber, latin, english, abbr)
VALUES ( 1, 'HYDROGENIUM', 'Hydrogen', 'H')";
if($mysqli->query($sql) === TRUE)
{
echo 'New record created successfully<br />';
}
else
{
echo $sql.'<br />' . $mysqli->error;
}
/*** close connection ***/
$mysqli->close();
}
else
{
/*** if we are unable to connect ***/
echo 'Unable to connect';
exit();
}
?>
INSERT multiple records with mysqli +
The mysqli extenstion provides an object oriented approach to the same task. The mysqli extension contains a class method named multi_query() for exactly this purpose. This class method basically takes an SQL statement, or multiple SQL statements concatenated by a semicolon ; character. Lets see it in action with a small subset of our periodic table of elements.
<?php
/*** mysql hostname ***/
$hostname = 'localhost';
/*** mysql username ***/
$username = 'username';
/*** mysql password ***/
$password = 'password';
/*** mysql database name ***/
$dbname = 'periodic_table';
/*** create a new mysqli object with default database***/
$mysqli = @new mysqli($hostname, $username, $password, $dbname);
/* check connection */
if(!mysqli_connect_errno())
{
/*** if we are successful ***/
echo 'Connected Successfully<br />';
/*** sql to INSERT a new record ***/
/*** note the semi colon on the end of each statement ***/ <font size="-1">
$sql = "INSERT INTO elements (atomicnumber, latin, english, abbr) VALUES ( 1, 'HYDROGENIUM', 'Hydrogen', 'H');";
$sql .= "INSERT INTO elements (atomicnumber, latin, english, abbr) VALUES ( 2, 'HELIUM', 'Helium', 'He');";
$sql .= "INSERT INTO elements (atomicnumber, latin, english, abbr) VALUES ( 3, 'LITHIUM', 'Lithium', 'Li')";
$sql .= "INSERT INTO elements (atomicnumber, latin, english, abbr) VALUES ( 4, 'BERYLLIUM', 'Beryllium', 'Be');";
$sql .= "INSERT INTO elements (atomicnumber, latin, english, abbr) VALUES ( 5, 'BORUM', 'Boron', 'B')";
</font> /*** run the multiple statements and check for errors ***/
if($mysqli->multi_query($sql) !== FALSE)
{
echo 'New records created successfully<br />';
}
else
{
echo $sql.'<br />' . $mysqli->error;
}
/*** close connection ***/
$mysqli->close();
}
else
{
/*** if we are unable to connect ***/
echo 'Unable to connect';
exit();
}
?>