PHP checks and filters user input, connects to the database, runs a query, receives the results, and displays them in a web application. Data is added, retrieved, updated, and removed from databases. PHP communicates with MySQL and Oracle using SQL.
Sending SQL Queries with PHP
PHP functions or methods offered by the selected database extension or abstraction layer can be used to send SQL instructions to a database after you have established a connection. Functions like mysqli_query() and the query() method for object-oriented interfaces like PDO and mysqli are examples of common querying techniques.
The SQL query string and the database connection identifier are the two inputs that the mysqli_query() function accepts. A result or FALSE upon failure are returned if the query is a SELECT statement. A boolean indicating success or failure is returned for INSERT, UPDATE, and DELETE statements. Likewise, for SELECT queries, PDO’s query() method runs a SQL statement and delivers a result set as a PDOStatement object.
Basic SQL: INSERT (adding data)
Similar to retrieving data, adding new information to a database involves connecting, sending a query, and then checking the results. An INSERT statement is usually the query used to add data.
INSERT INTO tables is the fundamental syntax for an INSERT statement in SQL. VALUES….
Here’s an example using mysqli_query():
<?php
// create and execute INSERT query
$sql = "INSERT INTO products (id, name) VALUES ('5', 'pears')";
mysqli_query($connection, $sql) or die ("ERROR: " . mysqli_error($connection) . " (query was $sql)"); //
?>
Checking the results after an INSERT can involve using mysqli_affected_rows() to see how many rows were changed9.
Basic SQL: SELECT (retrieving data)
The pick statement in SQL selects rows from a table that meet criteria to retrieve data from a database. The Data Manipulation Language (DML) “workhorse” command of SQL.
A SELECT statement’s fundamental form is: FROM tables, SELECT [options] items [CONDITIONS WHERE] [ GROUP BY type of group] [ WHERE_definition is present] [ORDER BY type of order] Limit limit criterion [LIMIT];
This is an illustration utilising mysqli_query():
<?php
$sql = "SELECT id, name FROM products"; //
$result = mysqli_query($connection, $sql) //
or die ("ERROR: " . mysqli_error($connection) . " (query was $sql)"); //
?>
Another example using the object-oriented mysqli interface:
<?php
$sql = "SELECT * FROM books WHERE available = 1 ORDER BY title"; //
$result = $db->query($sql); //
?>Handling SELECT Result Sets and Fetching Modes
You usually need to get the returned data, which is returned as a result set or object, after running a SELECT query. The individual rows and columns can then be accessed by processing this result set.
Functions such as mysqli_num_rows() and mysql_num_rows() can be used to determine the number of returned rows. The object-oriented method for SQLite makes use of $result->numRows(). For certain databases, the number of rows for SELECT statements can be obtained using PDO’s rowCount() function.
Retrieving rows from the result set using multiple functions or methods which may return the data in different formats or “fetching modes”—is how you access the real data. Typical modes consist of:
- Numerically indexed arrays: Column index numbers, such as $row, are used to access data. This format is frequently returned by functions like mysqli_fetch_row() and mysql_fetch_row().
- Associative arrays: Column names serve as keys to retrieve data (e.g., $row[‘column_name’]). This format is returned by functions like mysql_fetch_assoc(). Associative arrays, numeric arrays, or both may be returned by mysqli_fetch_array().
- Both numeric and associative arrays: Associative and numeric arrays can both be returned by mysqli_fetch_array(). Each entry is retrieved by sqlite_fetch_array() as an associative array and a numerically indexed array.
- Objects: Column names become object properties when data is fetched into objects. A row is retrieved as an object using PDO’s fetch(PDO::FETCH_OBJ) mode.
- Fetching the whole result set: Certain techniques enable getting the complete set of results into a two-dimensional array. The entire result set is returned as a two-dimensional array by sqlite_fetch_all().
Usually, a loop is used to process each row as you iterate through the result set.
Here’s an example showing how to use mysqli_fetch_row() in a while loop to retrieve rows:
Here’s an example demonstrating fetching rows using a while loop with mysqli_fetch_row():
<?php
// Assuming $result holds the result of a SELECT query
if (mysqli_num_rows($result) > 0) { //
while($row = mysqli_fetch_row($result)) { //
echo $row . " = " . $row . "\n"; // Example usage
}
}
// Remember to free the result set when done (optional but good practice)
// mysqli_free_result($result); // Similar to mysql_free_result($result)
?>
And an example using a while loop with mysql_fetch_array():
<?php
// Assuming $result holds the result of a SELECT query
while ($row = mysql_fetch_array ($result)) { //
echo "user_id: ".$row["user_id"]."<br>\n"; //
echo "user_id: ".$row."<br>\n"; //
echo "fullname: ".$row["fullname"]."<br>\n"; //
echo "fullname: ".$row[53]."<br>\n"; //
}
?>Basic SQL: UPDATE, DELETE
SQL offers instructions to edit and delete existing data in addition to entering and choosing data. These consist of the DELETE and UPDATE statements. They belong to the DML, just like SELECT and INSERT.
- UPDATE: A table’s current data can be changed using this method. Although updates can also be based on other fields, it’s crucial to have a unique reference to the row, such a primary key, when making changes. The table name, the fields that need to be updated, and the updated values are necessary for a UPDATE statement.
- DELETE: This function eliminates entries from a table.
PEAR DB uses DB::query() to send these queries to the database, just as INSERT and SELECT.
Here are examples using PEAR DB’s DB::query():
<?php
// Modifying data with UPDATE
$dbh->query("UPDATE family SET is_naive = 1 WHERE name LIKE 'Kay'"); //
// Removing data with DELETE
$dbh->query("DELETE FROM family WHERE name LIKE 'Fredo'"); //
?>Mysqli_affected_rows() and mysql_affected_rows() can count UPDATE and DELETE-impacted rows.
Introduction to Prepared Statements
Prepared statements are a method of running queries in which the database server receives a template of the query and parses, saves, and validates it. After then, the data is transferred separately.
This makes it possible to directly bind PHP variables for input and output. Data retrieval and data modification are the two categories. For queries that must be run more than once, prepared statements are especially helpful.
Preventing SQL injection is one of the main advantages of utilising prepared statements. An attack known as SQL injection occurs when a malevolent person adds undesired commands to the SQL query. Prepared statements keep data with dangerous code from being treated as SQL commands by separating the SQL logic from the data.
When using prepared statements, the query template where data values will be inserted uses placeholders (usually question marks? or named parameters like :searchterm). It is not appropriate to enclose these placeholders in quote marks.
The standard procedure for creating statements with MySQL includes:
- Using $db->prepare() or mysqli_prepare() to prepare the query template.
- Bind variables to placeholders with $stmt->bind_param() or mysqli_stmt_bind_param(). The binding function requires the data types of each variable:’s’ for string, ‘i’ for integer, ‘d’ for double, and ‘b’ for blob.
- Executing the prepared statement with $stmt->execute() or mysqli_stmt_execute().
- If necessary, using functions like mysqli_stmt_fetch() or $stmt->fetch(), retrieve results for SELECT statements.
This is an illustration of a SELECT query in MySQL that uses prepared statements:
<?php
$query = "SELECT ISBN, Author, Title, Price FROM Books WHERE $searchtype = ?"; //
$stmt = $db->prepare($query); //
$stmt->bind_param('s', $searchterm); // 's' indicates the variable is a string
$stmt->execute(); //
// To fetch results after execution, you would typically use methods
// like $stmt->get_result() and then fetch rows from the result object.
// Or, if using bound output variables (less common with mysqli for SELECT),
// you would bind output variables and use $stmt->fetch() .
?>And an example of an INSERT query using prepared statements with mysqli:
<?php
$query = "insert into books values(?, ?, ?, ?)"; //
$stmt = $db->prepare($query); //
$stmt->bind_param("sssd", $isbn, $author, $title, $price); // 'sssd' specifies the types of the bound variables
$stmt->execute(); //
?>Using the prepare() and execute() methods, PDO additionally enables prepared statements; named arguments, such as :searchterm, are frequently used in place of?
Because prepared statements provide security against SQL injection, it is advised to use them for database interaction in PHP, particularly when working with user-provided data.