Crud operations in PHP using mysqli and PHPMyAdmin

PHP MySQL CRUD involves the connection of PHP with the MySQL database and performing Create (Insert), Read (Select), Update (Modify), and Delete Operation on the table.

PHP and MySQL connectivity is a very important topic while learning PHP here step by step PHP and MySQL connectivity and creat update delete and read operations are discussed using mysqli and using PHP Data Objects (PDO) are discussed.

To create database and tables PHPMyAdmin are used.

MySQL is a freely available open-source Relational Database management System (RDBMS)**  that uses Structure Query Language (SQL).

SQL is the most popular language to accessing, adding and managing content in a database.

It is popular because its fast process, reliable, ease and flexible of use.

PHP can connect to MySQL and manipulate database. It is used on the server.

Create any PHP application need MySQL database to managing data.

MySQLis an important part of most of all PHP application like WordPress, Joomla, Magento and Drupal.

MySQLis a one kind of database system for web application with huge size of storage space (like Facebook, Twitter and Wikipedia)

MySQL are stored data in tables. A table is a collection of related data, one kind of array. 

But PHP can store data and managing data except MySQL by using another database like ODBC (Open Database Connectivity).

Note: **RDBMS is a collection of data, stored and accessed virtually.

RDBMS is used for the definition, querying, creation, update and administration of databases.

It is a software application to interact with user, other application means programming language (PHP, JAVA, .NET, Python etc.) and also database itself analyze data.

Create Database on phpMyAdmin

In the XAMPP OR WAMPP server phpMyAdmin is present for MySQL.

Open XAMPP or WAMPP control panel start Apache and MySQL. Then enter localhost/phpmyadmin on the browser url.

Open phpMyAdmin section which is the main MySQL database.

Here database, table can create and also data stored and manage.

Click on the Database option then open a text box to create new database with database name.

Enter database name in the open textbox and click the Create button.

Remember one thing never used space between two words of database name only used – (dash) or _ (underscore) and MSQL is case sensitive so database name and table name field name are use same as it is.

If user create database with same name (previous any other existing database name) then new database is not create.

Always set database name similar with application (like create school management system then create database for this application with school_management name).

When a database is created successfully then this database shown in the left side list with alphabetical order.

Click the database then open particular those database table list or if there has no table then show below image.

In MYSQL there has no limit to create database.

Create Table on phpMyAdmin

Create table in a particular database is very simple like create new database.

When any user open their database there has an option create table a blank text box there enter table name and 2,3,4 any number in the next box (Number of columns) and click the go button.

Table name rules is same as database name never using space between two words of table name.

There is a student table with 5 numbers of columns. Here columns are field name like student name, address, marks, rollnumber, phone, etc.

Entered name value in the name column like(name, address, marks, rollnumber, phone, etc.)

Then select type of particular column in the type column.

Here type means which type of data stored in this field (likename is text value so choose text, address is alphanumeric so choose varchar and phone is numeric so choose int etc)

Then enter length means how many character or digit stored in this column.

(like int support 0 to 11 here only store maximum 9 digit because + and – are by default store 2 digits so if any user store number greater than 9 then choose bigint which can store 20 digit maximum.

Varchar has 255 character limits. Text has no limit; in text type no need to set length value)

Then set primary key for this table in the index column and click in the save button.

In this way a simple table will be created in the database.

No limitation for create table in one database.

User can create many table in one database as their require.

PHP MySqli Connection : how to connect to PHPMyAdmin database in PHP

Before Performing PHP MySQL CRUD we have to connect PHP with MySql Database.

There have some method to connect PHP application with MYSQL database. Like MYSQL, MYSQLi and PDO etc.

Now days MYSQLi is mainly used for connecting database.

Syntax of MYSQLi:

connection.php : PHP MySql Connection Program Example

Explain:

MYSQLi is a library class for the MYSQL databases. Using this class manage MYSQL database in a PHP application.

There have many functions to display data, store data, delete data and update data of database.

In the above example, the first parameter is the server hostname where the database is present by default local server host name is localhost.

The second parameter is the server user name which is root in local server.

The third parameter is the password value which is null in the local server.   

Fourth parameter is the database name means which particular database is connected with this PHP application.

Here $conn returns an object of the MYSQLi class of a particular tutorial database.

mysqli_connect_error() is a pre-defined function that checks whether the connection is established successfully or not.

If the hostname user name password database name is wrong then it returns an error that stop the PHP execution.

crud operations in PHP using MySQLi

PHP MySQL Insert Data in table

First operation of PHP MySQL CRUD is Create or Insert.

Lets see How to insert Data in MySql Using PHP.

create.php PHP MySQL Insert Program example

Output

Explain:

MYSQL gives insert query for store data in the database.

Insert, into and values are keywords user can write it in any case small or caps.

Table name and column name are case sensitive so write same as define.

Execute any MYSQL query used query() function. In above example $sql variable store insert query that’s insert query is executed and one data is stored in the database table.

query() function is call by MYSQLi connection object so need to include database connection file.

Without database connection never execute any MYSQL query.

php crud operation using mysqli

PHP MySQL Select from Table

Another operation of PHP MySQL CRUD is Read or Select.

Lets see how to Select or read MySql Data in PHP.

show.php PHP MySQL Select program example

Output:

MYSQL gives select query to fetch data from database.

Here * return all columns value of this table.

If any user return only specific some column then he/she can write select query like this way, select name, phone, marks from student.

Here only name, phone and marks value return.

In above example query() function execute select query so student table selected.

Now display the student table’s data, use fetch_array() function which return at a time one row of the table.

If user show the all record of the table then call the fetch_array() in a loop.

In above example use while loop for fetch all record of the student table.fetch_array() function is return table data as a numeric array and associative array both way.

So user can print name value like $rows[name] or $rows[1] both return same result.

PHP MySql delete from table

Another operation of PHP MySQL CRUD is Delete or Remove.

MYSQL gives delete query for delete data from database.

delete from tablename.  This query deletes all record of the table. Table will be empty after execute the query.

If user want to delete particular one row then add where clause in the delete query. MYSQL support where clause and also AND OR clause.

delete.php PHP MySQL Delete Program Example

Output:

Student record deleted

Now in the student table, only one record left. 

The first row deleted from the student table. Always use the primary key column in the where clause. Because the primary key field is store unique value (no duplicate data and no null value) that’s why only exact data will be deleted from the table.

If any user use another column in where clause then maybe more than one row will be deleted if value is same in another rows.

Like user use name column then if more than one student name are same then more than one record will be deleted.

PHP MySQL Update from table

Another operation of PHP MySQL CRUD is Update or Edit.

MYSQL gives update query to update data in the table.

With this query column1, column2 and column3 value are modified by new value for all rows in the table.

If user want to update particular one row then use where clause condition in the update query.

update.php PHP MySQL Update Program Example

Output:

Student record updated

In the above example address and marks value modified in the one row where rollnumber value is 2.

PHP MySql Insert multiple rows in table

PHP MySQL Insert multiple rows program example

Note:

multiple keyword used to select multiple file at a time.

PHP MySqli Last Insert Id

PHP MySQL select last insertid program example

<?php echo $conn->insert_id;?>

Its return last auto increment column value. In the file table last inserted id value is 4 so insert_id return 4.

Call the insert_id function with the database connection object and write the code after insert query.

Set a column to auto increment value in tableto check A_I option in the table structure.

PHP PDO prepared statements

PDO (PHP Data Objects) is the one of the database connection method to connect MySQL database to PHP application.

In this method insert query needs to prepare before execute.

prepareinsert.php :PHP PDO MySql Data Insert example

Explain:

Prepared statements are very useful against SQL injections.

A prepared statement is a feature used to execute the same SQL statements repeatedly with high efficiency.

Prepare: A SQL is created query and data sent to the database. Some values are not specified, they are called parameters.

In above example INSERT INTO student VALUES (?, ?, ?, ?, ?) those are parameters.

Database parses the data then compile and finally execute the SQL query, in prepare method data stores in the server without execute.

Execute: In the execute method data combine with parameters and SQL execute the query to store data into database.

 User can execute the query as many times as they want with different values.

Compared to executing SQL statements directly, prepared statements have two main advantages

1. Prepared method reduces query parsing time for preparing a SQL query.
2. Bind param minimize bandwidth to the server so website can be load fast and only need to send parameter each time not the whole query.

With Prepared methods SQL query is secured from SQL Injection, because bind param combined the values first then send to the database so no need to be escaped correctly.

If the original values are not derived from input then SQL Injection con not occurs.

Read More

Localhost PhpMyAdmin | Localhost/Phpmyadmin/

XAMPP export database: Export & Import MySql Database in phpMyAdmin