Home » Programming » php » PHP and MySQL

PHP and MySQL

PHP and MySQL connectivity is a very important topic while learning PHP here step by step PHP and MySQL connectivity and create 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. MYSQL is an important part of most of all PHP application like WordPress, Joomla, Magento and Drupal. MYSQL is 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 Server

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.

PhpMyAdmin creating table

Create Table in a database MYSQL

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.

Creating table in PHPMyAdmin

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.

Table Structure

Connect PHP with 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

Explain:

MYSQLi is a library class for MYSQL database. 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 first parameter is server host name where the database is present by default local server host name is localhost.

Second parameter is server user name which is root in local server.

Third parameter is password value which is null in local server.   

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

Here $conn return object of MYSQLi class of particular tutorial database.

mysqli_connect_error() is a pre-define function which check the connection is established successfully or not. If host name user name password database name are wrong then it returns error the stop the PHP execution.

MySQL Insert Data in table

create.php

Output

Add Student Recored (create.php)
PHPMyAdmin Showing student record

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.

Show Data from MySQL Table

show.php

Output:

Showing all students record in show.php

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.

Delete Data from MySql Table

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

Output:

Student record deleted

Showing record after deleting first record

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.

Update Data in MySQL table

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

Output:

Student record updated

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

Inserting Multiple Data

File uplaod form

Uploaded files 

Note:

multiple keyword used to select multiple file at a time.

Getting Last Auto increment ID

<?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.

Column with auto increment 
Select A_I to make column auto increment

Prepare statement to insert data

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

Data inserted in table

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.

Managed by MochaHost - Fast & Easy WordPress Hosting