Home » Labs » PHP MySQL create, Insert, select, print, update and delete  

PHP MySQL create, Insert, select, print, update and delete


Filed under Labs

PHP is a well known server side programming language which is used for web development. PHP language development began in 1994 by Rasmus Lerdorf, a Greenland programmer. On 28 August 2014 PHP 5.6 version has been released.

MySQL is one of the most popular database which is developed as open source by Oracle Corporation and it is written in C and C++. Using PHP with MySQL is a good combination for web apps and today most of the web applications are using this combination.

In this topic we cover the basics of PHP language that is Create, Insert, Select and Delete. This post is especially for the beginner in web development.

How to create MySQL database

We can create a MySQL database using ‘CREATE DATABASE‘ statement. Below example will show how to create a database using query.

 
CREATE DATABASE max_demo

the above query will create a MySQl database called “max_demo”

How to create a MySQL table in database

The CREATE TABLE statement is used to create a table in MySQL.

In our example we are going to create a table in database. Below query will create a table named “users”, with five columns: “id”, “firstname”, “lastname”, “email” and “reg_date”.

 
CREATE TABLE users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(80),
reg_date TIMESTAMP
)

How to insert data Into MySQL

For inserting we can use INSERT INTO statement. Below is an example of inserting data into MySQL.

INSERT INTO table_name (column1, column2, column3,…)
VALUES (value1, value2, value3,…)

 
$sql = "INSERT INTO users (firstname, lastname, email)
VALUES ('Ajay', 'AK', 'ajay@vishmax.com')";

How to select data from a MySQL Database

The SELECT statement is used to select data from one or more tables.

SELECT column_name(s) FROM table_name

or you can select entair columns by using * character.

SELECT * FROM table_name

In our working example we can select the data by following way.

 
$sql = "SELECT id, firstname, lastname FROM users";
$result = $conn->query($sql);

How to print data from a MySQL Database

For printing data we can use echo statement.

 
// Add your connection details here
 
$sql = "SELECT id, firstname, lastname FROM users";
$result = mysqli_query($conn, $sql);
 
if (mysqli_num_rows($result) > 0) {
    // output data of each row
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
    }
} else {
    echo "0 results found";
}

How to update data from MySQL database

The UPDATE statement is used to update existing records in a table. Below query will update the last name of the user record with id=1

 
// Add your connection details here
 
$sql = "UPDATE users SET lastname='Rao' WHERE id=1";

How to delete data from MySQL database

The DELETE statement is used to delete records from a table. Below query will delete the data from database, where user record with id=1

 
// Add your connection details here
 
$sql = "DELETE FROM users WHERE id=1";
It's only fair to share...Share on Google+Tweet about this on TwitterShare on Facebook

Posts you may like: