Download as PPTX, PDF, TXT or read online from Scribd
Download as pptx, pdf, or txt
You are on page 1/ 20
CHAPTER - 4
Connecting to Databases PHP & MySQL DATABASE MySQL - is the most popular database system used with the PHP language • It is freely available and easy to install, (if you have installed Wamp server it already there on your machine )
• MySQL database server offers several advantages:
MySQL is easy to use, yet extremely powerful, fast, secure, and scalable. MySQL runs on a wide range of operating systems, including UNIX or Linux, Microsoft Windows, Apple Mac OS X,
and others. MySQL supports standard SQL (Structured Query Language).
MySQL is ideal database solution for both small and large applications.
MySQL is developed, and distributed by Oracle Corporation.
MySQL includes data security layers that protect sensitive data from intruders.
• MySQL database stores data into tables like other relational database PHP Connect to MySQL Server
In order to store or access the data inside a MySQL database, you first need to connect to the MySQL database server
PHP offers two different ways to connect to MySQL server:
1. MySQLi extension (Improved MySQL) and 2. PDO extension(PHP Data Objects)
MySQLi extension - provides an easier way to connect to, and execute queries on a MySQL database server (supports MySQL database only) • It is a better choice for MySQL-specific projects.
• Both PDO and MySQLi offer an object-oriented API, but MySQLi also offers a procedural API which is relatively easy for beginners to understand.
PDO extension - is more portable and supports more than twelve different databases, Cont.… Connecting to MySQL Database Server
o In PHP you can easily do this using the mysqli_connect() function.
o All communication between PHP and the MySQL database server takes place through this connection.
Basic syntaxes for connecting to MySQL using MySQLi and PDO extensions
◦ MySQLi, Procedural way
$link = mysqli_connect("hostname", "username", "password", "database"); ◦ MySQLi, Object Oriented way $mysqli = new mysqli("hostname", "username", "password", "database"); ◦ PHP Data Objects (PDO) way $pdo = new PDO("mysql:host=hostname; dbname=database", "username", "password");
hostname - specify the host name (e.g. localhost), or IP address of the MySQL server username and password - specifies the credentials to access MySQL server, and database - if provided specifies the default database to be used when performing queries Cont.… MySQLi - Procedural way
<?php /* Assuming you are running MySQL server with default setting (user 'root' with no password) */ $link = mysqli_connect("localhost", "root", "");
// Check connection if($link === false) { die("ERROR: Could not connect. " . mysqli_connect_error()); } // Print host information echo "Connect Successfully. Host info: " . mysqli_get_host_info($link); ?>
• The above code is connecting our page with the MYSQL server – • To connect with a specific database inside the MySQL server add a database name
Note: The default username for MySQL database server is root and there is no password.
• The die() function
o is a built-in function used for error handling in PHP. Mainly it is used to terminate script execution immediately. Cont.… Closing the MySQL Database Server Connection
• Connection to the MySQL server will be closed automatically as soon as the execution of the script ends. • However, if you want to close it earlier you can do this by simply calling the PHP mysqli_close() function. <?php /* Assuming you are running MySQL server with default setting (user 'root' with no password) */ $link = mysqli_connect("localhost", "root", "");
// Check connection if($link === false) { die("ERROR: Could not connect. " . mysqli_connect_error()); }
// Close connection mysqli_close($link); ?> Cont.… Creating Databases
<?php /* Assuming you are running MySQL server with default setting (user 'root' with no password) */ $link = mysqli_connect("localhost", "root", "");
// Check connection if($link === false) { die("ERROR: Could not connect. " . mysqli_connect_error()); }
// Attempt create database query execution To execute the SQL query – it should pass as argument $sql = "CREATE DATABASE demo"; to the PHP mysqli_query() function if(mysqli_query($link, $sql)) { echo "Database created successfully"; } else { echo "ERROR: Could not able to execute $sql. " . mysqli_error($link); } // Close connection mysqli_close($link); ?> Cont.… Creating Tables • The SQL CREATE TABLE statement is used to create a table in database.
<?php /* Assuming you are running MySQL server with default setting (user 'root' with no password) */ $link = mysqli_connect("localhost", "root", "", "demo");
// Check connection if($link === false) { die("ERROR: Could not connect. " . mysqli_connect_error()); }
// Attempt create table query execution
$sql = "CREATE TABLE persons( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(30) NOT NULL, last_name VARCHAR(30) NOT NULL, email VARCHAR(70) NOT NULL UNIQUE )"; if(mysqli_query($link, $sql)) { echo "Table created successfully."; } else { echo "ERROR: Could not able to execute $sql. " . mysqli_error($link); } // Close connection mysqli_close($link); ?> Cont.… Inserting Values • The SQL INSERT INTO statement is used to insert data in to the table in the database.
<?php /*Assuming you are running MySQL server with default setting (user 'root' with no password) */ $link = mysqli_connect("localhost", "root", "", "demo");
// Check connection if($link === false) { die("ERROR: Could not connect. " . mysqli_connect_error()); }
// Attempt create table query execution
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES ('Abebe', 'Petros', 'abepeter@mail.com')"; if(mysqli_query($link, $sql)) { echo "Records inserted successfully."; } else { echo "ERROR: Could not able to execute $sql. " . mysqli_error($link); } // Close connection mysqli_close($link); ?> Cont.… Insert Data into a Database from an HTML Form
<!DOCTYPE html> <html lang="en"> Index.html <head> <meta charset="UTF-8"> <title>Add Record Form</title> </head> <body> <form action="insert.php" method="post"> <p> <label for="firstName">First Name:</label> <input type="text" name="first_name" id="firstName"> </p> <p> <label for="lastName">Last Name:</label> <input type="text" name="last_name" id="lastName"> </p> <p> <label for="emailAddress">Email Address:</label> <input type="text" name="email" id="emailAddress"> </p> <input type="submit" value="Submit"> </form> </body> </html> Cont.… insert.php <?php /* Assuming you are running MySQL server with default setting (user 'root' with no password) */ $link = mysqli_connect("localhost", "root", "", "demo"); if($link === false) // Check connection { die("ERROR: Could not connect. " . mysqli_connect_error()); }
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES ('$first_name', '$last_name', '$email')"; if(mysqli_query($link, $sql)) { echo "Records added successfully."; } else { echo "ERROR: Could not able to execute $sql. " . mysqli_error($link); } // Close connection mysqli_close($link); ?> Cont.… mysqli_real_escape_string() function
o Replaces special characters like backslashes (\), apostrophes (‘) with their escaped equivalents (preceded by a backslash \\ and \’)
o This makes the string safe for inclusion within an SQL query and prevent SQL injection vulnerabilities.
o Accepts Two Arguments:
• $mysqli: A valid MySQLi connection resource obtained using mysqli_connect().
• $unescaped_string: The string containing potentially unsafe characters.
Best Practices: • Always Escape User Input: Make mysqli_real_escape_string() a habit whenever you use user input in
SQL queries. • Consider Prepared Statements: For enhanced security, especially when dealing with sensitive data. Cont.… PHP MySQL Prepared Statements
A prepared statement (aka. parameterized statement) is simply a SQL query template containing placeholder instead of the actual parameter values. MySQLi supports the use of anonymous positional placeholder (?), as shown below:
INSERT INTO persons (first_name, last_name,
email) VALUES (?, ?, ?);
Placeholders are replaced by the actual values at the time of execution of the statement.
The prepared statement execution consists of two stages: prepare and execute.
• Prepare — SQL statement template is created and sent to the database server. The server performs a syntax check and query optimization, and stores it for later use.
• Execute — During execute the parameter values are sent to the server to execute it. Cont.… Prepared statements - very useful, particularly in situations when you execute a particular statement multiple times with different values
for example, a series of INSERT statements
A prepared statement can execute the same statement repeatedly with high efficiency because the statement is parsed only once, while it can be executed multiple times.
o It also minimize bandwidth usage, since upon every execution only the placeholder values need to be transmitted to the database server instead of the complete SQL statement. <?php /*Assuming you are running MySQL server with default setting (user 'root' with no password) */ $link = mysqli_connect("localhost", "root", "", "demo"); // Check connection if($link === false) { die("ERROR: Could not connect. " . mysqli_connect_error()); } // Prepare an insert statement $sql = "INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)"; if($stmt = mysqli_prepare($link, $sql)) { // Bind variables to the prepared statement as parameters mysqli_stmt_bind_param($stmt, "sss", $first_name, $last_name, $email);
/* Set the parameters values and execute the statement again to insert another row */ $first_name = "Hermione"; $last_name = "Granger"; Repeat this one to $email = "hermionegranger@mail.com"; insert different rows mysqli_stmt_execute($stmt);
echo "Records inserted successfully.";
} else { echo "ERROR: Could not prepare query: $sql. " . mysqli_error($link); } // Close statement mysqli_stmt_close($stmt); // Close connection insert.php mysqli_close($link); ?> Cont.… The mysqli_prepare() - function in PHP is a powerful tool for creating prepared statements • Accepts Two Arguments: o $mysqli: A valid MySQLi connection resource obtained using mysqli_connect()
o $sql: The SQL statement with placeholders instead of directly embedded values
• Analyzes the SQL statement for syntax errors and parses it
• Allocates resources for the prepared statement on the MySQL server • If successful, returns a MySQLi prepared statement object • If there's an error, it returns FALSE
The mysqli_stmt_bind_param() function in PHP
• Accepts Three Arguments: o $stmt: A valid MySQLi prepared statement object returned by mysqli_prepare() o $types: A string that specifies the data types of the variables being bound. ( i: Integer d: Double s: String ) • Binds variables to the corresponding placeholders placeholders (?), allowing for dynamic data insertion Cont.… PHP MySQL SELECT Query <?php while($row = mysqli_fetch_array($result)) $link = mysqli_connect("localhost", "root", "", "demo"); { echo "<tr>"; // Check connection echo "<td>" . $row['id'] . "</td>"; if($link === false) echo "<td>" . $row['first_name'] . "</td>"; { echo "<td>" . $row['last_name'] . "</td>"; die("ERROR: Couldt connect. " . echo "<td>" . $row['email'] . "</td>"; mysqli_connect_error()); echo "</tr>"; } } echo "</table>"; // Attempt select query execution $sql = "SELECT * FROM persons"; // Free result set if($result = mysqli_query($link, $sql)) mysqli_free_result($result); { } if(mysqli_num_rows($result) > 0) { else echo "<table>"; { echo "<tr>"; echo "No records matching your query were found."; echo "<th>id</th>"; } echo "<th>first_name</th>"; } echo "<th>last_name</th>"; else echo "<th>email</th>"; { echo "</tr>"; echo "ERROR: Could not execute $sql.". mysqli_error($link); } mysqli_fetch_array() – a function fetches a single row of data from the $result variable // Close connection mysqli_close($link); $sql = "SELECT * FROM persons WHERE ?> first_name='abebe'"; Cont.… PHP MySQL Update Query <?php $link = mysqli_connect("localhost", "root", "", "demo");
// Check connection if($link === false) { die("ERROR: Could not connect. " . mysqli_connect_error()); }
// Attempt update query execution
$sql = "UPDATE persons SET email='abebe@mail.com' WHERE id=1"; if(mysqli_query($link, $sql)) { echo "Records were updated successfully."; }
else { echo "ERROR: Could not able to execute $sql. " . mysqli_error($link); }
// Close connection mysqli_close($link); ?> Cont.… PHP MySQL Delete Query
// Check connection if($link === false) { die("ERROR: Could not connect. " . mysqli_connect_error()); }
// Attempt delete query execution
$sql = "DELETE FROM persons WHERE first_name= 'hana'”; if(mysqli_query($link, $sql)) { echo "Records were deleted successfully."; } else { echo "ERROR: Could not able to execute $sql. " . mysqli_error($link); } // Close connection mysqli_close($link); ?> The end
