PHP MySQLi Create, Read, Update and Delete – 1
For dynamic website, web application MySQL play an important role. The term MySQLi means MySQL Improved. MySQLi work with MySQL version 4.1.13 or newer and PHP version 5.0.0. Today I will share how to create, read, update and delete (CRUD) data in database using PHP MySQLi.
In this tutorial. How to,
- Opening Database Connection
- Closing Database Connection
- Create Database
- Selecting Database
- Creating Table
OPENING DATABASE CONNECTION
Using mysqli_connect() to establish the database connection.
Syntax
<?PHP connection mysqli_connect(server,user,password); ?>
Server − The host name running database server.
User − The username accessing the database.
Password − The password of the user accessing the database.
CLOSING DATABASE CONNECTION
Using mysqli_close() to close the database connection
Syntax
<?PHP bool mysqli_close($conn); ?>
The following examples shows how to close a database connection:
<?PHP $dbhost = 'localhost'; $dbuser = 'root'; $dbpass = ''; $conn = mysqli_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die('Could not connect: ' . mysqli_error()); } echo 'Connected successfully'; mysqli_close($conn); ?>
CREATING A DATABASE
The CREATE DATABASE statement is used to create a database.
Syntax
<?PHP bool mysqli_query(connection, sql ); ?>
The following examples shows how to create a database:
<?PHP $dbhost = 'localhost'; $dbuser = 'root'; $dbpass = ''; $conn = mysqli_connect($dbhost, $dbuser, $dbpass); if(! $conn ) die('Could not connect: ' . mysqli_error()); echo 'Connected successfully<br>'; $sql = 'CREATE Database test_db'; $result = mysqli_query( $conn, $sql ); if(! $result ) die('Could not create database: ' . mysqli_error()); echo "Database test_db created successfully..."; mysqli_close($conn); ?>
SELECTING A DATABASE
Once connect with a database server then it is required to select a particular database where your all the tables are associated. mysqli_select_db() to select a database.
Syntax
<?PHP bool mysqli_select_db(connection , db_name); ?>
The following examples shows how to select a database:
<?PHP $dbhost = 'localhost'; $dbuser = 'root'; $dbpass = ''; $conn = mysqli_connect($dbhost, $dbuser, $dbpass); if(! $conn ) die('Could not connect: ' . mysqli_error()); echo 'Connected successfully<br>'; $result = mysqli_select_db( $conn, 'test_db'); mysqli_close($conn); ?>
CREATING A TABLE
The CREATE TABLE statement is used to create a table.
We will create a table named “account”, with five columns: “acc_id”, “acc_name”, “acc_email”, “password” and “status”:
The following examples shows how to create the table:
<?PHP $dbhost = 'localhost'; $dbuser = 'root'; $dbpass = ''; $conn = mysqli_connect($dbhost, $dbuser, $dbpass); if(! $conn ) die('Could not connect: ' . mysqli_error()); $db = mysqli_select_db( $conn, 'test_db'); $sql = ' CREATE TABLE account ( acc_id INT(20) NOT NULL AUTO_INCREMENT, acc_name VARCHAR(155) NOT NULL, acc_email VARCHAR(155) NOT NULL, password VARCHAR(155) NOT NULL, created_dt DATETIME NOT NULL, status INT(1) NOT NULL DEFAULT "1", primary key ( acc_id )) '; $result = mysqli_query( $conn, $sql ); if(! $result ) die('Could not create table : ' . mysqli_error()); echo "Table Created Successfully..."; mysqli_close($conn); ?>
For MySQLi Manipulation will be in next tutorials.
PHP MySQLi Create, Read, Update and Delete – 2
[…] tutorial how to creating a connection, creating a database, creating a table using PHP MySQLi. Click here to […]