Chapter Three

PHP AND MYSQLI

DATABASE
 Mysqli

MySQLi is one of the most popular relational database system being used
on the Web today. It is freely available and easy to install, however if you
have installed Wampserver/Xampp server it already there on your
machine. MySQL database server offers several advantages:
MySQLi is easy to use, yet extremely powerful, fast, secure, and scalable.
MySQLi runs on a wide range of operating systems, including UNIX or
Linux, Microsoft Windows, Apple Mac OS X, and others.
MySQLi supports standard SQL (Structured Query Language).
MySQLi is ideal database solution for both small and large applications.
MySQLi is developed, and distributed by Oracle Corporation.
MySQLi includes data security layers that protect sensitive data from
intruders
 …counts

MySQLi is currently the most popular open source

database server in existence. On top of that, it is very
commonly used in conjunction with PHP scripts to
create powerful and dynamic server-side
applications.
we can use either apache or IIS server to connect our
Php code to database. For now we are going to use
apache server.
 Connecting to MySqli Database

PHP provides mysqli_connect function to open a

database connection. This function takes three
parameters and returns a MySQLi link identifier on
success, or FALSE on failure.
Mysqli function parameters :
 Server , username, password

mysqli_connect(server,username,password);
 Php and MySqli

The first step for interacting with MySQL connecting to the server
requires the appropriately named mysqli_connect( ) function:
 $dbc = mysqli_connect (hostname, username, password,
db_name);
The first three arguments sent to the function (hostname,
username, and password) are based upon the users and
privileges established within MySQL.
The fourth argument is the name of the database to use. This is
the equivalent of saying USE databasename within the mysql
client.
If the connection was made, the $dbc variable, short for database
connection (but you can use any name you want, of course).
 ..count

We can connect to mysql database by using either object oriented or procedural methods.
Connecting using object oriented
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$database= " DbName";

// Create connection
$conn = new mysqli($servername, $username, $password,$database);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>
$conn->close(); // for closing connction
 Creating Database

// Create database using object oriented

$sql = "CREATE DATABASE DbName";
if ($conn->query($sql) === TRUE) {
    echo "Database created successfully";
} else {
    echo "Error creating database: " . $conn->error;
}
 ..count's

Connect mysql using procedural methods

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$database= " DbName";

// Create connection
$conn = mysqli_connect($servername, $username, $password,$database);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>
mysqli_close($conn); // for closing connection
 ..count’s

// creating database using procedural

$sql = "CREATE DATABASE DbName";
if (mysqli_query($conn, $sql)) {
    echo "Database created successfully";
} else {
    echo "Error creating database: " .
mysqli_error($conn);
}
 Creating tables

The CREATE TABLE statement is used to create a

table in MySQL.
$sql=“CREATE TABLE ItThird (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
) ”;
 …Count's

After the data type, you can specify other optional attributes for each
column:
NOT NULL - Each row must contain a value for that column, null values
are not allowed
DEFAULT value - Set a default value that is added when no other value is
passed
UNSIGNED - Used for number types, limits the stored data to positive
numbers and zero
AUTO INCREMENT - MySQL automatically increases the value of the
field by 1 each time a new record is added
PRIMARY KEY - Used to uniquely identify the rows in a table. The
column with PRIMARY KEY setting is often an ID number, and is often
used with AUTO_INCREMENT
Each table should have a primary key column (in this case: the "id"
column). Its value must be unique for each record in the table.
 Insert data to database table

After a database and a table have been created, we

can start adding data in them.
Some tips when insert data to DB
The SQL query must be quoted in PHP
String values inside the SQL query must be quoted
Numeric values must not be quoted
The word NULL must not be quoted
 …coun’t

$sql = "INSERT INTO ItThird(firstname, lastname,

email)
VALUES (‘Kim', ‘Jung', ‘kimjun@gmail.com')";

if (mysqli_query($conn, $sql)) {
    echo “ one New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
 Inserting Multiple values

Multiple SQL statements must be executed with the mysqli_multi_query() function.

$sql = "INSERT INTO MyGuests (firstname, lastname, email)

VALUES (‘abebe', ‘kebede', ‘mama@example.com');";

$sql .= "INSERT INTO MyGuests (firstname, lastname, email)

VALUES ('Mikiyas', ‘alemu', ‘myemail@example.com');";

$sql .= "INSERT INTO MyGuests (firstname, lastname, email)

VALUES (‘chala', 'Diriba', ‘cha@example.com')";

if (mysqli_multi_query($conn, $sql)) {
    echo "New records created successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
 Retrieving Data From Db

The SELECT statement is used to Extract data from one or more tables:
SELECT column_name(s) FROM table_name or
SELECT * FROM table_name
The * used to select all columns from the table.

$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    // output data of each row
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"]. " Name: " . $row["firstname"]. " " .
$row["lastname"]. "<br>";
    }
} else {
    echo "0 results";
}
 Retrieving

 MySQL provides a LIMIT clause that is used to specify the number of records
to return.
 The LIMIT clause makes it easy to code multi page results or pagination with
SQL, and is very useful on large tables.
 Returning a large number of records can impact on performance.

 $sql = "SELECT * FROM Orders LIMIT 30"; // it will return the first 30 records.
 $sql = "SELECT * FROM Orders LIMIT 10 OFFSET 15"; OR
 $sql = "SELECT * FROM Orders LIMIT 15, 10";

// return only 10 records, start on record 16 (OFFSET 15)“ OR select records 16 - 25

 Modifying data on DB Table

The UPDATE statement is used to update existing

records in a table:
UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value 
NB. the WHERE clause in the UPDATE syntax:
The WHERE clause specifies which record or records
that should be updated. If you omit the WHERE clause,
all records will be updated!
 ..count's

$sql = "UPDATE ItThird SET lastname=abebe'

WHERE id=2";

if (mysqli_query($conn, $sql)) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " .
mysqli_error($conn);
}
 Removing Data from Db Table

The DELETE statement is used to delete records

from a table:
DELETE FROM table_name
WHERE some_column = some_value
NB. the WHERE clause in the DELETE
syntax: The WHERE clause specifies which record
or records that should be deleted. If you omit the
WHERE clause, all records will be deleted!
 Database security

Database security with respect to PHP comes down to three broad

issues:
1. Protecting the MySQL access information
2. Not revealing too much about the database
3. Being cautious when running queries, particularly those
involving user submitted data
You can accomplish the first objective by securing the MySQL
connection script outside of the Web directory so that it is never
viewable through a Web browser.
The second objective is attained by not letting the user see PHP’s
error messages or your queries (in these scripts, that information is
printed out for your debugging purposes; you’d never want to do
that on a live site).
 Cont…

For the third objective, there are numerous steps you can
and should take, all based upon the premise of never trusting
user-supplied data.
 First, validate that some value has been submitted, or that it is of the
proper type (number, string, etc.).
 Second, use regular expressions to make sure that submitted data
matches what you would expect it to be.
 Third, you can typecast some values to guarantee that they’re numbers.
 A fourth recommendation is to run user-submitted data through the
mysqli_real_escape_string( ) function.
This function makes data safe to use in a query by escaping
what could be problematic characters.
 $safe = mysqli_real_escape_string ($dbc, data);
End of The Chapter