CRUD Operation by MySqli procedural way in PHP

0
591

Download Source Code

Introduction:

Now a day each dynamic website cannot image without the database storage. Many website are used in MySQL database when using PHP. Here i want to guide you through what you should be doing – using PHP’s MySQLi class.

At first introduce earlier, I want to know to you that, MySQLi classes functions allows to access MySQL database server. Especially I want to say that, the library of MySQLi is designed to work with MySQL version 4.1.13 or newer where the (i) stands for improved the name of MySQLi. MySQLi functions can be used with PHP in the Object Oriented way but it also allows procedural usage as well.

So in this tutorial, I am trying to present Fetch-Insert-Update-Delete Operation (basically it is called CRUD Operation) by procedural way MySqli database connection in the PHP. Here you no need to create database and table manually from the localhost.

MySqli Connection:

The MySQL server allows using of different transport layers for connections. Connections use TCP/IP, UNIX domain sockets or Windows named pipes. The hostname localhost is bound to the use of UNIX domain sockets. It is not possible to open a TCP/IP connection using the hostname localhost you must use 127.0.0.1 instead.

Before access data in the MySQLi database, we need to be able to connect to the server. Here I have using the username as root with a password blank (“”) connecting to the test database on the server localhost

Obviously, the database name is optional and can be omitted. If you omit the database name you must be sure to prefix your tables with the database in all of your queries.

Here we want to check the connection MySQLi, if not connected successfully then and error is thrown or else show successfully connected to MySQLi is shown.

Create Database

Here we need to create database using the sql without go to localhost. I have used the database name test. And according to the sql, a database “test” will be created if it is not available in the localhost or else database ”test” will not be created.

By the mysqli_query() function perform queries against the database name “test” will be created if this name is available in the localhost or else an error is thrown to error creating database.

Next we need to changes the default database for the connection by the mysqli_select_db() if database “test” is created successfully.

Create Table

Now time to create the table within the “test” database using the query. You can use any table name, but I have used table name “Person”. Make sure there is no table that you select as $tablename.

Before write the query to create the table make sure how many fields Is required and what‘s type of data will be content within each field. Here I have created a table name “Person” and which has three fields FirstName, LastName and Age. FirstName and LastName both are contain string data which character length is 30 and Age field that is contains Integer data.

Here, mysqli_query() perform queries against the database to create table “Person” within the database “test”. If table “Person’ is created successfully then “Table person created successfully” is shown or else an error is thrown that is error to creating table.

Inset Operation

After a database and a table have been created, we can start adding data in them. To insert data into the table, I have assigned an associative array which array index is table column and value is data for the table column.

Here are some syntax rules to follow for the value of associative array:

  • String values must be quoted
  • Numeric values must not be quoted
  • The word NULL must not be quoted

As FirstName and LastName fields are declare as string so its corresponding value must be quoted and Age field is declare as integer so its corresponding value must not be quoted.

Now time to call insert function which accept three argument; first is connection string($con), second is table name($tablename) and third is table column value’s associative array($ColumnVal) that you declare in previous.

The definition of the insert function is a following,

Here, if happen failed to connect MySQLi then an error code is thrown by mysqli_connect_errno() and display an error message.

Delete Operation

To delete the table data row your can assign an associative array that is to be which table row you want to delete. For example here I’m using an associative array which LastName is Cake and Age is 32; its means delete the data row which LastName is Cake and Age is 32. Here same syntax rule is allowed for the associative array that is described earlier in the Insert Operation section.

Now time to call delete function to delete the data row in the table. In this function is accepted three arguments is that, one is connection string ($con), second is table name ($tablename) on which table this operation will be performed and third is column value ($ColumnVal) that you are declare earlier.

Following is the definitions of the delete function which can’t return anything only perform the delete operation.

N.B: In this delete function has restriction is that sql query WHERE clause only contains AND expression.

Update Operation

To update table data row you need to declare two associative arrays one is $set array that accepted set operation value against columns fields and other is $condition array that accepted conditional operation value against the column fields. Here same syntax rule is allowed for the associative array that is described earlier in the Insert Operation section.

Now time to call update function which accepted four arguments. First is connection string variable ($con), Second is table name ($tablename) on this which table you want to perform the operation, Third is $set associative array that you declare previous and Fourth is $condition associative array that also declare previous.

Following is the definition of update function; this function can’t perform anything only perform the update operation.

N.B: In this delete function has restriction is that sql query WHERE clause only contains AND expression.

Fetch Operation

To fetch the data from table you can call fetch function which accept three argument one is connection string variable ($con), second is table name ($tablename) on which table you want to perform this operation, third is an array which contains the name of columns which value you want to fetch.

Following is the definition o f fetch function which fetches all rows and returns the result-set as an associative array

After perform the fetch function an associative array is return and you can shown under the print_r() function.

Close Connection

Although PHP automatically closes your mysqli connection upon script termination, Instead of you want to close the connection before your script is complete, you can do so by just invoking the close function. This is done by doing the mysqli_close() function closes a previously opened database connection.

Conclusion

MySQLi is the improved version of MySQL database server. In this version of MySQL many security features and function has been added and you can use it with PHP for the better web storage database. So let’s start and hope for better coding….:)

SHARE
Previous articleVideo Creation Software Video Maker Fx with Crack
I am a professional software developer from last 4 years. I have experience in analysis, design, development, testing and implementation of desktop (using C, C++, C#) and web (using HTML5, CSS3, JavaScript, PHP, MySql) platform using. I have good exposure to object-oriented design, software architectures, design patterns, test-driven development and Project Management.

LEAVE A REPLY