PHP MySQL Database
PHP MySQL Database
What is MySQL:
MySQL is a popular open-source relational database management system (RDBMS) that uses Structured Query Language (SQL) to manage data. It is widely used for web-based applications, including e-commerce sites, content management systems, and other types of web applications. MySQL is known for its speed, reliability, and scalability, and it can run on a variety of operating systems, including Windows, Linux, and macOS. MySQL is owned by Oracle Corporation, which provides commercial support and enterprise solutions for MySQL. However, there is also a vibrant open-source community that contributes to MySQL development and provides free support and resources for users.
What is MySQL:
MySQL is a popular open-source relational database management system (RDBMS) that uses Structured Query Language (SQL) to manage data. It is widely used for web-based applications, including e-commerce sites, content management systems, and other types of web applications. MySQL is known for its speed, reliability, and scalability, and it can run on a variety of operating systems, including Windows, Linux, and macOS. MySQL is owned by Oracle Corporation, which provides commercial support and enterprise solutions for MySQL. However, there is also a vibrant open-source community that contributes to MySQL development and provides free support and resources for users.
SELECT * FROM customers;
This query retrieves all rows and columns from the “customers” table.
SELECT name, email FROM customers WHERE country = 'USA';
This query retrieves the “name” and “email” columns from the “customers” table, but only for rows where the “country” column is equal to “USA”.
- INSERT: This query is used to insert new data into a database. It is typically used in conjunction with a form or other input method to add new records to a table. For example:
INSERT INTO customers (name, email, country) VALUES ('John Smith', 'john@example.com', 'USA');
This query inserts a new row into the “customers” table with the values “John Smith”, “john@example.com“, and “USA” for the “name”, “email”, and “country” columns, respectively.
- UPDATE: This query is used to modify existing data in a database. It can be used to update specific rows based on certain conditions, or to update all rows in a table. For example:
UPDATE customers SET country = 'Canada' WHERE name = 'John Smith';
This query updates the “country” column in the “customers” table to “Canada” for the row where the “name” column is equal to “John Smith”.
- DELETE: This query is used to delete data from a database. It can be used to delete specific rows based on certain conditions, or to delete all rows in a table. For example:
DELETE FROM customers WHERE country = 'USA';
This query deletes all rows from the “customers” table where the “country” column is equal to “USA”.
PHP Connect to MySQL:
here’s an example of how to connect to MySQL using PHP:
<?php
// Set database access credentials
$host = 'localhost'; // database host (usually localhost)
$user = 'yourusername'; // database username
$pass = 'yourpassword'; // database password
$dbname = 'yourdbname'; // database name
// Create a connection to the database
$conn = mysqli_connect($host, $user, $pass, $dbname);
// Check the connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>
This code uses the mysqli_connect() function to create a connection to the MySQL database. It takes four parameters: the database host, the database username, the database password, and the database name.
After creating the connection, the code checks if the connection was successful using the mysqli_connect_error() function. If the connection was unsuccessful, it outputs an error message and stops execution using the die() function. If the connection was successful, it outputs a success message.
Note that this code uses the mysqli extension, which is the recommended way to interact with MySQL databases in PHP.
PHP Create a MySQL Database:
here’s an example of how to create a MySQL database using PHP:
<?php
// Set database access credentials
$host = 'localhost'; // database host (usually localhost)
$user = 'yourusername'; // database username
$pass = 'yourpassword'; // database password
// Create a connection to the MySQL server
$conn = mysqli_connect($host, $user, $pass);
// Check the connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// Create a new database
$sql = "CREATE DATABASE mydatabase";
if (mysqli_query($conn, $sql)) {
echo "Database created successfully";
} else {
echo "Error creating database: " . mysqli_error($conn);
}
// Close the connection
mysqli_close($conn);
?>
This code uses the mysqli_connect() function to create a connection to the MySQL server, and then uses the mysqli_query() function to execute a CREATE DATABASE SQL statement.
Note that you must have appropriate permissions to create a new database on the MySQL server. If you do not have sufficient permissions, you will see an error message when you try to create the database.
PHP Create a MySQL Database:
Create a MySQL Database Using MySQLi and PDO
here are examples of how to create a MySQL database using MySQLi and PDO:
MySQLi
<?php
// Set database access credentials
$host = 'localhost'; // database host (usually localhost)
$user = 'yourusername'; // database username
$pass = 'yourpassword'; // database password
// Create a connection to the MySQL server
$conn = new mysqli($host, $user, $pass);
// Check the connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Create a new database
$sql = "CREATE DATABASE mydatabase";
if ($conn->query($sql) === TRUE) {
echo "Database created successfully";
} else {
echo "Error creating database: " . $conn->error;
}
// Close the connection
$conn->close();
?>
This code uses the mysqli object-oriented interface to create a connection to the MySQL server, and then uses the query() method to execute a CREATE DATABASE SQL statement.
PDO
<?php
// Set database access credentials
$host = 'localhost'; // database host (usually localhost)
$user = 'yourusername'; // database username
$pass = 'yourpassword'; // database password
// Create a connection to the MySQL server using PDO
$conn = new PDO("mysql:host=$host", $user, $pass);
// Set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Create a new database
$sql = "CREATE DATABASE mydatabase";
if ($conn->exec($sql)) {
echo "Database created successfully";
} else {
echo "Error creating database";
}
// Close the connection
$conn = null;
?>
This code uses the PDO interface to create a connection to the MySQL server, and then uses the exec() method to execute a CREATE DATABASE SQL statement. Note that in this example, the setAttribute() method is used to set the PDO error mode to exception, which will cause PDO to throw an exception if an error occurs. This can be useful for handling errors in a more structured way.
PHP MySQL Create Table:
Create a MySQL Table Using MySQLi and PDO
Sure, here are examples of how to create a MySQL table using MySQLi and PDO:
MySQLi
<?php
// Set database access credentials
$host = 'localhost'; // database host (usually localhost)
$user = 'yourusername'; // database username
$pass = 'yourpassword'; // database password
$dbname = 'mydatabase'; // database name
// Create a connection to the MySQL server
$conn = new mysqli($host, $user, $pass, $dbname);
// Check the connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// SQL to create table
$sql = "CREATE TABLE mytable (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";
if ($conn->query($sql) === TRUE) {
echo "Table created successfully";
} else {
echo "Error creating table: " . $conn->error;
}
// Close the connection
$conn->close();
?>
This code uses the mysqli object-oriented interface to create a connection to the MySQL server, and then uses the query() method to execute a CREATE TABLE SQL statement.
PDO
<?php
// Set database access credentials
$host = 'localhost'; // database host (usually localhost)
$user = 'yourusername'; // database username
$pass = 'yourpassword'; // database password
$dbname = 'mydatabase'; // database name
// Create a connection to the MySQL server using PDO
$conn = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
// Set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// SQL to create table
$sql = "CREATE TABLE mytable (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";
if ($conn->exec($sql)) {
echo "Table created successfully";
} else {
echo "Error creating table";
}
// Close the connection
$conn = null;
?>
This code uses the PDO interface to create a connection to the MySQL server, and then uses the exec() method to execute a CREATE TABLE SQL statement. Note that in this example, the setAttribute() method is used to set the PDO error mode to exception, which will cause PDO to throw an exception if an error occurs. This can be useful for handling errors in a more structured way.
PHP MySQL Insert Data:
Insert Data Into MySQL Using MySQLi and PDO
Sure, here are examples of how to insert data into a MySQL table using MySQLi and PDO:
MySQLi
<?php
// Set database access credentials
$host = 'localhost'; // database host (usually localhost)
$user = 'yourusername'; // database username
$pass = 'yourpassword'; // database password
$dbname = 'mydatabase'; // database name
// Create a connection to the MySQL server
$conn = new mysqli($host, $user, $pass, $dbname);
// Check the connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// SQL to insert data into table
$sql = "INSERT INTO mytable (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";
if ($conn->query($sql) === TRUE) {
echo "Data inserted successfully";
} else {
echo "Error inserting data: " . $conn->error;
}
// Close the connection
$conn->close();
?>
This code uses the mysqli object-oriented interface to create a connection to the MySQL server, and then uses the query() method to execute an INSERT INTO SQL statement.
PDO
<?php
// Set database access credentials
$host = 'localhost'; // database host (usually localhost)
$user = 'yourusername'; // database username
$pass = 'yourpassword'; // database password
$dbname = 'mydatabase'; // database name
// Create a connection to the MySQL server using PDO
$conn = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
// Set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// SQL to insert data into table
$sql = "INSERT INTO mytable (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";
if ($conn->exec($sql)) {
echo "Data inserted successfully";
} else {
echo "Error inserting data";
}
// Close the connection
$conn = null;
?>
This code uses the PDO interface to create a connection to the MySQL server, and then uses the exec() method to execute an INSERT INTO SQL statement. Note that in this example, the setAttribute() method is used to set the PDO error mode to exception, which will cause PDO to throw an exception if an error occurs. This can be useful for handling errors in a more structured way.
PHP MySQL Get Last Inserted ID:
Get ID of The Last Inserted Record
Sure! Here are examples of how to get the ID of the last inserted record using MySQLi and PDO:
MySQLi
<?php
// Set database access credentials
$host = 'localhost'; // database host (usually localhost)
$user = 'yourusername'; // database username
$pass = 'yourpassword'; // database password
$dbname = 'mydatabase'; // database name
// Create a connection to the MySQL server
$conn = new mysqli($host, $user, $pass, $dbname);
// Check the connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// SQL to insert data into table
$sql = "INSERT INTO mytable (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";
if ($conn->query($sql) === TRUE) {
$last_id = $conn->insert_id;
echo "Data inserted successfully. Last inserted ID is: " . $last_id;
} else {
echo "Error inserting data: " . $conn->error;
}
// Close the connection
$conn->close();
?>
This code uses the mysqli object-oriented interface to create a connection to the MySQL server, and then uses the insert_id property of the mysqli object to retrieve the ID of the last inserted record.
PDO
<?php
// Set database access credentials
$host = 'localhost'; // database host (usually localhost)
$user = 'yourusername'; // database username
$pass = 'yourpassword'; // database password
$dbname = 'mydatabase'; // database name
// Create a connection to the MySQL server using PDO
$conn = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
// Set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// SQL to insert data into table
$sql = "INSERT INTO mytable (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";
if ($conn->exec($sql)) {
$last_id = $conn->lastInsertId();
echo "Data inserted successfully. Last inserted ID is: " . $last_id;
} else {
echo "Error inserting data";
}
// Close the connection
$conn = null;
?>
This code uses the PDO interface to create a connection to the MySQL server, and then uses the lastInsertId() method of the PDO object to retrieve the ID of the last inserted record. Note that in this example, the setAttribute() method is used to set the PDO error mode to exception, which will cause PDO to throw an exception if an error occurs. This can be useful for handling errors in a more structured way.
PHP MySQL Insert Multiple Records:
Insert Multiple Records Into MySQL Using MySQLi and PDO
Sure! Here are examples of how to insert multiple records into MySQL using MySQLi and PDO:
MySQLi
<?php
// Set database access credentials
$host = 'localhost'; // database host (usually localhost)
$user = 'yourusername'; // database username
$pass = 'yourpassword'; // database password
$dbname = 'mydatabase'; // database name
// Create a connection to the MySQL server
$conn = new mysqli($host, $user, $pass, $dbname);
// Check the connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Prepare the SQL statement with placeholders
$sql = "INSERT INTO mytable (firstname, lastname, email) VALUES (?, ?, ?)";
// Prepare the statement
$stmt = $conn->prepare($sql);
// Bind parameters to the placeholders
$stmt->bind_param("sss", $firstname, $lastname, $email);
// Set parameters and execute the statement multiple times
$firstname = "John";
$lastname = "Doe";
$email = "john@example.com";
$stmt->execute();
$firstname = "Jane";
$lastname = "Doe";
$email = "jane@example.com";
$stmt->execute();
$firstname = "Bob";
$lastname = "Smith";
$email = "bob@example.com";
$stmt->execute();
// Close the statement and connection
$stmt->close();
$conn->close();
?>
This code uses the mysqli object-oriented interface to create a connection to the MySQL server and then prepares a SQL statement with placeholders for the values to be inserted. The bind_param() method is used to bind the parameters to the placeholders in the SQL statement. Then, the parameters are set and the statement is executed multiple times to insert the records.
PDO
<?php
// Set database access credentials
$host = 'localhost'; // database host (usually localhost)
$user = 'yourusername'; // database username
$pass = 'yourpassword'; // database password
$dbname = 'mydatabase'; // database name
// Create a connection to the MySQL server using PDO
$conn = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
// Set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Prepare the SQL statement with placeholders
$sql = "INSERT INTO mytable (firstname, lastname, email) VALUES (?, ?, ?)";
// Prepare the statement
$stmt = $conn->prepare($sql);
// Set parameters and execute the statement multiple times
$firstname = "John";
$lastname = "Doe";
$email = "john@example.com";
$stmt->execute([$firstname, $lastname, $email]);
$firstname = "Jane";
$lastname = "Doe";
$email = "jane@example.com";
$stmt->execute([$firstname, $lastname, $email]);
$firstname = "Bob";
$lastname = "Smith";
$email = "bob@example.com";
$stmt->execute([$firstname, $lastname, $email]);
// Close the statement and connection
$stmt = null;
$conn = null;
?>
This code uses the PDO interface to create a connection to the MySQL server and then prepares a SQL statement with placeholders for the values to be inserted. The execute() method is used to execute the statement multiple times with different parameter values to insert the records. Note that in this example, the setAttribute() method is used to set the PDO error mode to exception, which will cause PDO to throw an exception if an error occurs. This can be useful for handling errors in a more structured way.
PHP MySQL Prepared Statements:
Prepared Statements and Bound Parameters
Prepared statements and bound parameters are important for preventing SQL injection attacks and improving the performance of your database queries. Prepared statements allow you to separate the SQL code from the data that it operates on, and bound parameters ensure that any user input is treated as data rather than as part of the SQL code.
Here are examples of how to use prepared statements and bound parameters with both MySQLi and PDO.
MySQLi
<?php
// Set database access credentials
$host = 'localhost'; // database host (usually localhost)
$user = 'yourusername'; // database username
$pass = 'yourpassword'; // database password
$dbname = 'mydatabase'; // database name
// Create a connection to the MySQL server
$conn = new mysqli($host, $user, $pass, $dbname);
// Check the connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Prepare the SQL statement with placeholders for the data
$sql = "SELECT * FROM mytable WHERE name = ? AND email = ?";
// Prepare the statement
$stmt = $conn->prepare($sql);
// Bind parameters to the statement
$stmt->bind_param("ss", $name, $email);
// Set the parameter values
$name = "John Doe";
$email = "john.doe@example.com";
// Execute the statement
$stmt->execute();
// Bind the results to variables
$stmt->bind_result($id, $name, $email);
// Fetch the results
while ($stmt->fetch()) {
echo "ID: " . $id . " - Name: " . $name . " - Email: " . $email . "<br>";
}
// Close the statement and connection
$stmt->close();
$conn->close();
?>
In this example, the SQL statement includes placeholders (?) for the data. The prepare() method is used to prepare the statement, and the bind_param() method is used to bind the parameters to the statement. The execute() method is used to execute the statement, and the bind_result() method is used to bind the results to variables. The fetch() method is used to fetch each row of results.
PDO
<?php
// Set database access credentials
$host = 'localhost'; // database host (usually localhost)
$user = 'yourusername'; // database username
$pass = 'yourpassword'; // database password
$dbname = 'mydatabase'; // database name
// Create a connection to the MySQL server
$conn = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
// Prepare the SQL statement with placeholders for the data
$sql = "SELECT * FROM mytable WHERE name = :name AND email = :email";
// Prepare the statement
$stmt = $conn->prepare($sql);
// Bind parameters to the statement
$stmt->bindParam(':name', $name);
$stmt->bindParam(':email', $email);
// Set the parameter values
$name = "John Doe";
$email = "john.doe@example.com";
// Execute the statement
$stmt->execute();
// Fetch the results
while ($row = $stmt->fetch()) {
echo "ID: " . $row['id'] . " - Name: " . $row['name'] . " - Email: " . $row['email'] . "<br>";
}
// Close the statement and connection
$stmt = null;
$conn = null;
?>
In this example, the SQL statement includes named placeholders (:name and :email) for the data. The prepare() method is used to prepare the statement, and the bindParam() method is used
PHP MySQL Select Data:
Select Data From a MySQL Database
To select data from a MySQL database using PHP, you can use either MySQLi or PDO.
MySQLi
<?php
// Set database access credentials
$host = 'localhost'; // database host (usually localhost)
$user = 'yourusername'; // database username
$pass = 'yourpassword'; // database password
$dbname = 'mydatabase'; // database name
// Create a connection to the MySQL server
$conn = new mysqli($host, $user, $pass, $dbname);
// Check the connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Execute the SQL statement
$sql = "SELECT * FROM mytable";
$result = $conn->query($sql);
// Output the results
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
}
} else {
echo "0 results";
}
// Close the connection
$conn->close();
?>
In this example, the SQL statement selects all data from the mytable table. The query() method is used to execute the statement, and the fetch_assoc() method is used to fetch each row of results as an associative array. The num_rows property is used to check if there are any results.
PDO
<?php
// Set database access credentials
$host = 'localhost'; // database host (usually localhost)
$user = 'yourusername'; // database username
$pass = 'yourpassword'; // database password
$dbname = 'mydatabase'; // database name
// Create a connection to the MySQL server
$conn = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
// Execute the SQL statement
$sql = "SELECT * FROM mytable";
$stmt = $conn->query($sql);
// Output the results
while ($row = $stmt->fetch()) {
echo "ID: " . $row['id'] . " - Name: " . $row['name'] . " - Email: " . $row['email'] . "<br>";
}
// Close the connection
$conn = null;
?>
In this example, the SQL statement selects all data from the mytable table. The query() method is used to execute the statement, and the fetch() method is used to fetch each row of results as an associative array. The while loop is used to iterate through each row of results.
PHP MySQL Use The WHERE Clause:
Select and Filter Data From a MySQL Database
To select and filter data from a MySQL database using PHP, you can use either MySQLi or PDO. You can filter data using the WHERE clause in the SQL statement.
MySQLi
<?php
// Set database access credentials
$host = 'localhost'; // database host (usually localhost)
$user = 'yourusername'; // database username
$pass = 'yourpassword'; // database password
$dbname = 'mydatabase'; // database name
// Create a connection to the MySQL server
$conn = new mysqli($host, $user, $pass, $dbname);
// Check the connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Execute the SQL statement with a WHERE clause
$sql = "SELECT * FROM mytable WHERE age > 25";
$result = $conn->query($sql);
// Output the results
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Age: " . $row["age"] . "<br>";
}
} else {
echo "0 results";
}
// Close the connection
$conn->close();
?>
In this example, the SQL statement selects all data from the mytable table where the age is greater than 25. The query() method is used to execute the statement, and the fetch_assoc() method is used to fetch each row of results as an associative array. The num_rows property is used to check if there are any results.
PDO
<?php
// Set database access credentials
$host = 'localhost'; // database host (usually localhost)
$user = 'yourusername'; // database username
$pass = 'yourpassword'; // database password
$dbname = 'mydatabase'; // database name
// Create a connection to the MySQL server
$conn = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
// Execute the SQL statement with a WHERE clause
$sql = "SELECT * FROM mytable WHERE age > :age";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':age', $age);
$age = 25;
$stmt->execute();
// Output the results
while ($row = $stmt->fetch()) {
echo "ID: " . $row['id'] . " - Name: " . $row['name'] . " - Age: " . $row['age'] . "<br>";
}
// Close the connection
$conn = null;
?>
In this example, the SQL statement selects all data from the mytable table where the age is greater than 25. The prepare() method is used to prepare the statement, and the bindParam() method is used to bind the value of $age to the :age parameter in the statement. The execute() method is used to execute the statement, and the fetch() method is used to fetch each row of results as an associative array. The while loop is used to iterate through each row of results.
PHP MySQL Use The ORDER BY Clause:
Select and Order Data From a MySQL Database
To select and order data from a MySQL database using PHP, you can use either MySQLi or PDO. You can order the data using the ORDER BY clause in the SQL statement.
MySQLi
<?php
// Set database access credentials
$host = 'localhost'; // database host (usually localhost)
$user = 'yourusername'; // database username
$pass = 'yourpassword'; // database password
$dbname = 'mydatabase'; // database name
// Create a connection to the MySQL server
$conn = new mysqli($host, $user, $pass, $dbname);
// Check the connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Execute the SQL statement with an ORDER BY clause
$sql = "SELECT * FROM mytable ORDER BY age DESC";
$result = $conn->query($sql);
// Output the results
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Age: " . $row["age"] . "<br>";
}
} else {
echo "0 results";
}
// Close the connection
$conn->close();
?>
In this example, the SQL statement selects all data from the mytable table and orders it by the age column in descending order using the DESC keyword. The query() method is used to execute the statement, and the fetch_assoc() method is used to fetch each row of results as an associative array.
PDO
<?php
// Set database access credentials
$host = 'localhost'; // database host (usually localhost)
$user = 'yourusername'; // database username
$pass = 'yourpassword'; // database password
$dbname = 'mydatabase'; // database name
// Create a connection to the MySQL server
$conn = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
// Execute the SQL statement with an ORDER BY clause
$sql = "SELECT * FROM mytable ORDER BY age DESC";
$stmt = $conn->prepare($sql);
$stmt->execute();
// Output the results
while ($row = $stmt->fetch()) {
echo "ID: " . $row['id'] . " - Name: " . $row['name'] . " - Age: " . $row['age'] . "<br>";
}
// Close the connection
$conn = null;
?>
In this example, the SQL statement selects all data from the mytable table and orders it by the age column in descending order using the DESC keyword. The prepare() method is used to prepare the statement, and the execute() method is used to execute the statement. The fetch() method is used to fetch each row of results as an associative array, and the while loop is used to iterate through each row of results.
PHP MySQL Delete Data:
Delete Data From a MySQL Table Using MySQLi and PDO
To delete data from a MySQL table using PHP, you can use either MySQLi or PDO. You can delete data by executing the SQL statement DELETE FROM table_name with a WHERE clause to specify the conditions that determine which rows to delete.
MySQLi
<?php
// Set database access credentials
$host = 'localhost'; // database host (usually localhost)
$user = 'yourusername'; // database username
$pass = 'yourpassword'; // database password
$dbname = 'mydatabase'; // database name
// Create a connection to the MySQL server
$conn = new mysqli($host, $user, $pass, $dbname);
// Check the connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Execute the SQL statement with a WHERE clause
$sql = "DELETE FROM mytable WHERE id = 1";
if ($conn->query($sql) === TRUE) {
echo "Record deleted successfully";
} else {
echo "Error deleting record: " . $conn->error;
}
// Close the connection
$conn->close();
?>
In this example, the SQL statement deletes the row from the mytable table where the id column is equal to 1. The query() method is used to execute the statement, and the === operator is used to check if the query was executed successfully.
PDO
<?php
// Set database access credentials
$host = 'localhost'; // database host (usually localhost)
$user = 'yourusername'; // database username
$pass = 'yourpassword'; // database password
$dbname = 'mydatabase'; // database name
// Create a connection to the MySQL server
$conn = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
// Execute the SQL statement with a WHERE clause
$sql = "DELETE FROM mytable WHERE id = :id";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':id', $id);
// Set the parameter values and execute the statement
$id = 1;
if ($stmt->execute()) {
echo "Record deleted successfully";
} else {
echo "Error deleting record";
}
// Close the connection
$conn = null;
?>
In this example, the SQL statement deletes the row from the mytable table where the id column is equal to the parameter :id. The prepare() method is used to prepare the statement, and the bindParam() method is used to bind the parameter to the statement. The execute() method is used to execute the statement, and the if statement is used to check if the query was executed successfully.
PHP MySQL Update Data:
Update Data In a MySQL Table Using MySQLi and PDO
To update data in a MySQL table using PHP, you can use either MySQLi or PDO. You can update data by executing the SQL statement UPDATE table_name SET column1 = value1, column2 = value2, … WHERE condition with a WHERE clause to specify the conditions that determine which rows to update.
MySQLi
<?php
// Set database access credentials
$host = 'localhost'; // database host (usually localhost)
$user = 'yourusername'; // database username
$pass = 'yourpassword'; // database password
$dbname = 'mydatabase'; // database name
// Create a connection to the MySQL server
$conn = new mysqli($host, $user, $pass, $dbname);
// Check the connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Execute the SQL statement with a WHERE clause
$sql = "UPDATE mytable SET column1 = 'newvalue1', column2 = 'newvalue2' WHERE id = 1";
if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}
// Close the connection
$conn->close();
?>
In this example, the SQL statement updates the values of the column1 and column2 columns in the mytable table where the id column is equal to 1. The query() method is used to execute the statement, and the === operator is used to check if the query was executed successfully.
PDO
<?php
// Set database access credentials
$host = 'localhost'; // database host (usually localhost)
$user = 'yourusername'; // database username
$pass = 'yourpassword'; // database password
$dbname = 'mydatabase'; // database name
// Create a connection to the MySQL server
$conn = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
// Execute the SQL statement with a WHERE clause
$sql = "UPDATE mytable SET column1 = :newvalue1, column2 = :newvalue2 WHERE id = :id";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':newvalue1', $newvalue1);
$stmt->bindParam(':newvalue2', $newvalue2);
$stmt->bindParam(':id', $id);
// Set the parameter values and execute the statement
$newvalue1 = 'newvalue1';
$newvalue2 = 'newvalue2';
$id = 1;
if ($stmt->execute()) {
echo "Record updated successfully";
} else {
echo "Error updating record";
}
// Close the connection
$conn = null;
?>
In this example, the SQL statement updates the values of the column1 and column2 columns in the mytable table where the id column is equal to the parameter :id. The prepare() method is used to prepare the statement, and the bindParam() method is used to bind the parameters to the statement. The execute() method is used to execute the statement, and the if statement is used to check if the query was executed successfully.
PHP MySQL Limit Data Selections:
Limit Data Selections From a MySQL Database
When selecting data from a MySQL database using PHP, you can use the LIMIT keyword to limit the number of rows returned by the query. This can be useful when you only need to retrieve a certain number of records or when you want to implement pagination on a website.
Here’s an example of how to use LIMIT with both MySQLi and PDO:
MySQLi
<?php
// Set database access credentials
$host = 'localhost';
$user = 'yourusername';
$pass = 'yourpassword';
$dbname = 'mydatabase';
// Create a connection to the MySQL server
$conn = new mysqli($host, $user, $pass, $dbname);
// Check the connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Execute the SQL statement with LIMIT
$sql = "SELECT * FROM mytable LIMIT 10";
$result = $conn->query($sql);
// Output the results
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo "id: " . $row["id"] . " - Name: " . $row["name"] . "<br>";
}
} else {
echo "0 results";
}
// Close the connection
$conn->close();
?>
In this example, the SQL statement selects all columns from the mytable table and limits the results to 10 rows using the LIMIT keyword.
PDO
<?php
// Set database access credentials
$host = 'localhost';
$user = 'yourusername';
$pass = 'yourpassword';
$dbname = 'mydatabase';
// Create a connection to the MySQL server
$conn = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
// Execute the SQL statement with LIMIT
$sql = "SELECT * FROM mytable LIMIT :limit";
$stmt = $conn->prepare($sql);
$stmt->bindValue(':limit', 10, PDO::PARAM_INT);
$stmt->execute();
// Output the results
if ($stmt->rowCount() > 0) {
while($row = $stmt->fetch()) {
echo "id: " . $row["id"] . " - Name: " . $row["name"] . "<br>";
}
} else {
echo "0 results";
}
// Close the connection
$conn = null;
?>
In this example, the SQL statement selects all columns from the mytable table and limits the results to 10 rows using the LIMIT keyword with a bound parameter. The bindValue() method is used to set the value of the :limit parameter to 10, and the PARAM_INT constant is used to specify the data type of the parameter. The rowCount() method is used to check if the query returned any rows.