Database connection and Insert Update Delete in PHP

database connection & insert update delete in php

In this tutorial, we’ll tell you how you can connect your web application to a relational database such as MYSQL and use SQL queries to fetch, insert, delete and update data. To do so, you need to have MYSQL up and running on your local system. There are any client both online and offline to manage your database but we’d recommend phpmyadmin. You are however free to use your own database management tool you’re comfortable with as long as it gets the work done.

phpMyAdmin is a free and open source tool written in PHP intended to handle the administration of MySQL with the use of a web browser. It can perform various tasks such as creating, modifying or deleting databases, tables, fields or rows; executing SQL statements or managing users and permissions.

If you have Xampp/Wamp installed on your system, visit localhost:80/phpmyadmin to access phpmyadmin. To enter phpmyadmin, you need a username and a password. The default username and password is root. In some cases, the password might be blank.

Click on the option “New” from the left navigation bar and create a database named new_db. A database consists of one or more tables. Now create tables in your new database. A database table has its own unique name and consists of columns and rows. You can either use the user friendly menu options of phpmyadmin or write SQL queries to make changes to your database.

 

Creating a table

The CREATE TABLE command is used to create a table in MySQL. Run the following query in phpmyadmin

Now get back to your editor and add the following code to make database connections. There are three ways of doing so: procedural, object-oriented and PDO. Proceduaral method is no longer used and has been depreceated. WE are going to tell you about the object-oriented method as its safe, secure and fast. PDO is the newest on the block however for sake of simplicity, let’s just use the object oriented way. Once you’re familiar with it, you can go ahead and learn PDO.

Add the following code in PHP to make database connections:

Mysqli is the class and $conn is the object. The first parameter is the server name, second is the username of database, third the password of database and last parameter is the name of the database schema.

die and echo are more or less same. The only major difference between them is that die( ) function is used to print only string messages. Value of variables cannot print with die( ) function. Another point worth noting here is that die( ) function is used to display a message and exit the script.

Next, insert some record into the users table. Add the following code in PHP to do so.

The queries used above are known as prepare statements. A prepared statement is a special feature used to execute the similar SQL statements repeatedly with high efficiency. SQL statement template is created and sent to the database. Values are not stated clearly and left with parameters (labeled “?”) so that they can be bound later on. The database then parses, compiles, and performs query optimization on the SQL statement template, and stores the result without executing it. When needed, the application binds these values to the parameters, and the database executes the statement.

The data inserted over here is static data, You can also the user input data through forms and insert it into the database. To know how -> follow our tutorial on working with forms.

In comparison to executing SQL statements directly, prepared statements have two main advantages:

  • Prepared statements reduce parsing time as the preparation on the query is done only once although the statement itself is executed multiple times
  • Prepared statements are useful against SQL injections (code injection technique, in which malicious SQL statements are inserted into an entry field for execution ), because parameter values, which are transmitted later using a different protocol, need not be correctly escaped.

Fetching data using select statements

You can verify the inserted data by going to phpmyadmin and clicking on the user table or by running the following SQL query in phpmyadmin.


Now, we’ll fetch the data we just inserted into the database and display on the screen. Add the following code in PHP

Most of the code is self-explanatory. while loop is used to fetch all the records (read rows) one by one until the last record is fetched.

Updating existing data

To modify existing data update query is used.

The UPDATE statement is used to update existing records in a table. Run the following query in phpmyadmin

The WHERE clause specifies which record(s) should be updated. If you omit the WHERE clause, all records will be updated!

Now to update the records from PHP you need to create a small form for the user to input new data. Add this PHP code to the file where you have created the new form asking the user to input new data. Do not forget to make database connections again!

Make sure you change the name of the input fields accordingly. Verify the new records by going to phpmyadmin and browsing the table user.

Deleting Data 

Now that you have fetched, inserted, updated records, you might also want to delete the records.

The DELETE statement is used to delete records from a table. Run the following SQL query in phpmyadmin.


To delete through PHP, create an external link pointing to a file delete.php Add the following code to delete.php

After deleting the record, the screen will show a message for 1.5 seconds and redirect back to the main page i.e. index page.

Great, if you made it till here. You can pat yourself for now you know how to implement basic database functions ( insert update delete in PHP ) in PHP. Stay tuned for further tuts. We’ll be back soon. Adios!

 

Leave a Reply

Your email address will not be published. Required fields are marked *