Executing SQL queries SELECT, INSERT, UPDATE, DELETE
In PHP programming, database users can extract SQL databases using MySQLi or PDO (PHP Data Objects) methods and execute SQL command statements such as SELECT, INSERT, UPDATE, and DELETE to query database tables. Here are the steps to apply these methods and procedures using both methods in PHP programming.
Using MySQLi in PHP Programming.
Query (retrieve data) with the SELECT statement in PHP programming using object-oriented (MySQLi) methods.
<?php
$servername = “localhost”;
$username = “root”;
$password = “”;
$dbname = “test_database”;
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die(“database Connection unsuccessful”. $conn->connect_error);
}
$sql = “SELECT emp_id, emp_name, email FROM employee”;
$output = $conn->query($sql);
if ($output->num_rows > 0) {
// here this code used to Output data of each row in table
while($row = $output->fetch_assoc()) {
echo “emp_id – ” . $row[“emp_id”] . ” – Emp_Name – ” . $row[“emp_name”] . ” – Email – ” . $row[“email”] . “<br>”;
}
} else {
echo “0 output”;
}
$conn->close();
?>
Use of procedural (MySQLi) methods in PHP programming.
<?php
$servername = “localhost”;
$username = “root”;
$password = “”;
$dbname = “test_database”;
$conn = mysqli_connect($servername, $username, $password, $dbname);
if (!$conn) {
die(“database connection unsuccessful – ” . mysqli_connect_error());
}
$sql = “SELECT emp_id, emp_name, email FROM employee”;
$output = mysqli_query($conn, $sql);
if (mysqli_num_rows($output) > 0) {
while($row = mysqli_fetch_assoc($output)) {
echo “emp_id – ” . $row[“emp_id”] . ” – Emp_Name – ” . $row[“emp_name”] . ” – Email – ” . $row[“email”] . “<br>”;
}
} else {
echo “0 output”;
}
mysqli_close($conn);
?>
Insert query data from the INSERT command in PHP programming with object-oriented MySQLi methods.
<?php
$servername = “localhost”;
$username = “root”;
$password = “”;
$dbname = “test_database”;
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die(“database Connection unsuccessful ” . $conn->connect_error);
}
$sql = “INSERT INTO employee (emp_name, email) VALUES (‘Siddhi deora’, ‘siddhi@domain.com’)”;
if ($conn->query($sql) === TRUE) {
echo “New database table record created”;
} else {
echo “Display Error – ” . $ sql . “<br>”. $conn->error;
}
$conn->close();
?>
Use of Procedural MySQLi Method in PHP Programming.
<?php
$servername = “localhost”;
$username = “root”;
$password = “”;
$dbname = “test_database”;
$conn = mysqli_connect($servername, $username, $password, $dbname);
if (!$conn) {
die(“database Connection unsuccessful ” . mysqli_connect_error());
}
$sql = “INSERT INTO employee (emp_name, email) VALUES (‘Harry deora’, ‘harry@domain.com’)”;
if (mysqli_query($conn, $sql)) {
echo “New database table record created”;
} else {
echo “Display Error – ” . $ sql . “<br>”. mysqli_error($conn);
}
mysqli_close($conn);
?>
Update query (update data) in PHP programming with object-oriented MySQLi methods.
<?php
$servername = “localhost”;
$username = “root”;
$password = “”;
$dbname = “test_database”;
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die(“database Connection unsuccessful ” . $conn->connect_error);
}
$sql = “UPDATE employee SET email=’harry@domain.com’ WHERE emp_name=’harry deora'”;
if ($conn->query($sql) === TRUE) {
echo “Database table Record updated successfully”;
} else {
echo “Display Error – ” . $ sql . “<br>”. $conn->error;
}
$conn->close();
?>
Use of Procedural MySQLi Method in PHP Programming.
<?php
$servername = “localhost”;
$username = “root”;
$password = “”;
$dbname = “test_database”;
$conn = mysqli_connect($servername, $username, $password, $dbname);
if (!$conn) {
die(“database Connection unsuccessful ” . mysqli_connect_error());
}
$sql = “UPDATE employee SET email=’harry@domain.com’ WHERE emp_name=’harry'”;
if (mysqli_query($conn, $sql)) {
echo “database table Record updated successfully”;
} else {
echo “Display Error – ” . $ sql . “<br>”. mysqli_error($conn);
}
mysqli_close($conn);
?>
Delete query (delete data) using the object-oriented MySQLi method in PHP programming.
<?php
$servername = “localhost”;
$username = “root”;
$password = “”;
$dbname = “test_database”;
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die(“Database table Connection unsuccessful ” . $conn->connect_error);
}
$sql = “DELETE FROM employee WHERE emp_name=’Harry deora'”;
if ($conn->query($sql) === TRUE) {
echo “Database table Record deleted successfully”;
} else {
echo “Display Error – ” . $ sql . “<br>”. $conn->error;
}
$conn->close();
?>
Use of procedural MySQLi methods in PHP programming.
<?php
$servername = “localhost”;
$username = “root”;
$password = “”;
$dbname = “test_database”;
$conn = mysqli_connect($servername, $username, $password, $dbname);
if (!$conn) {
die(“database Connection unsuccessful ” . mysqli_connect_error());
}
$sql = “DELETE FROM employee WHERE emp_name=’Harry deora'”;
if (mysqli_query($conn, $sql)) {
echo “database table Record deleted successfully”;
} else {
echo “Display Error – ” . $ sql . “<br>”. mysqli_error($conn);
}
mysqli_close($conn);
?>
A summary of the MySQLi or PDO (PHP Data Objects) methods in PHP programming.
Both MySQLi and PDO are powerful features in PHP programming for direct backend database connections or interacting with MySQL databases in popular PHP programs.
MySQLi PHP database connectivity.
- MySQLi PHP database connection is best for MySQL database backend support only.
- MySQLi PHP database connectivity can be used in both procedural and object-oriented approaches.
- MySQLi provides support for prepared statements to prevent SQL injection processes.
PDO PHP database connectivity.
- PDO database connectivity in PHP programming is best for web applications that require database abstraction. It provides support for database software such as MySQL, PostgreSQL, SQLite, etc.
- PDO provides database connectivity with only object-oriented overview features.
- PDO’s database connectivity method is more flexible, supports named placeholders, and facilitates easier database transaction management.
