Php CRUD Operation with MySql

In this tutorial you will learn CRUD operation in PHP with MYSql.

CRUD stands for Create, Read, Update, Delete, In this tutorial you will learn how to Create, Read, Update, Delete (CRUD) data in php application, in our example we will be mysql as database, but you can use any other database, php-mysql is greate combination and popular among developers.

If don't know how to create database table in MySQL Database, take a look at this mysql table tutorial

Php MySQL Connection

First we create a connection object in php using mysqli_connect, We should always create that in a separate file, so that we can use the same code reference in all over the projects instead of writing again and again, another advantage of keeping the database connection related information on one place, when you want to change the server or database username and password, you just need to change in one place, , so let's create a file called "db_connection.php" and write following code.

<?php
$servername = "localhost";
$username = "username";
$password = "mypass123";
$db = "wtrdb";

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

// Check connection
if (!$conn) {
   die("Connection failed: " . mysqli_connect_error());
}

//echo "Connected successfully";
?>

Know more about php mysql database connection mechanism.

Now whenever we want to perform any database related operations we just include the above file and user the connection object $conn .

Let's look at the following example how to include and use the connection object.

Here we have a form-submit.php file, where we are capturing all form data and saving in database.

<?php
// Check if the form is submitted
$personName = $_POST['personName'];
$address = $_POST['address'];
$mobile = $_POST['mobile'];
$email = $_POST['email'];
$message = $_POST['message'];
$tdate=new DateTime();
// form a sql query
$sql = "INSERT INTO tbquery (name, email, mobile,address, comment, postdate)
VALUES ('". $personName."',
'". $email ."',
'". $mobile ."',
'". $address ."',
'". $message ."',
'". $tdate->format('Y-m-d') ."'
)";
if (mysqli_query($conn, $sql)) {
echo "Your query posted successfully";
} else {
echo "Error: " . $sql . "
" . mysqli_error($conn);
}

mysqli_close($conn);
 
?>

Now from above example you can understand how to execute any sql query in mysql database from php code,

Now you learn how to update existing data in mysql database from php code

$sql ="Update tbQuery
set mobile ='90000000'
where email='webtrainingroom@gmail.com'";
if (mysqli_query($conn, $sql)) {
echo "Query updated successfully";
} else {
echo "Error: " . $sql . "
" . mysqli_error($conn);
}

Be careful while executing any delete statement, if there is no where clause added in your query all data will be de deleted from specified table

$sql ="Delete from tbQuery
where email='webtrainingroom@gmail.com'";


if (mysqli_query($conn, $sql)) {
    echo "data deleted successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

Fetching data is very common functionality in any application development, and is the most simplest, only thing you need make sure that SQL query syntax is right.

$sql ="Select col1, col2, col3 from tableName";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) 
        {
            echo "<div>" . $row["col1"]. " - " . $row["col2"]. " " . $row["col3"]. "</div>";
        }
} 
else {
    echo "no record found";