PHP MySQL Connectivity, Mysqli Connectivity,MySQL Create Database,MySQL Insert, MySQL Update, MySQL Delete

PHP Mysql connectivity

Use the mysql_connect( ) function to established connection to the MySQL server.

To access the database functionality we have to make a connection to database using Php.
mysql_connect() function is used to establish the connection to mysql server.
four arguments need to be passed to mysql_connect() function.

hostname : if you are working on local system , you can use localhost or you can also provide ip address or server name
username : if there is a existing user , you can provide username. default username is ‘root’.
password : by default password is blank or null.
dbname : it is a optional field . it is basically a name of the database that need to be connected.

1 2	mysql_connect(host, username, password, dbname);

Parameter	Description
host(Server name)	Either a host name(server name) or an IP address
username	The MySQL user name
password	The password to log in with
dbname	Optional. The database to be used when performing queries

Note : There are more available parameters, but the ones listed above are the most important.

In the following example we store the connection in a variable ($con) for later use in the script

1 2 3 4 5 6 7

<?php   // Create connection $con=mysql_connect("localhost","root","") or die(mysql_error());   ?>

Here localhost is server name. root is MySQL default user name. default password is blank and database name is my_db. mysql_error( ) function provides mysql connectivity error message.

MySQL Close Connection

1 2 3 4 5 6 7 8 9 10

<?php // Create connection $con=mysql_connect("localhost","root","","my_db") or die(mysql_error()); //code to be executed...   // Close connection mysql_close($con);    ?>

after work with the database is done we have to close the connection using mysql_close() function
in which the connection to the database is passed.

Mysqli PHP Connectivity

How to connect PHP through mysqli.
Create a registration form with 4 four fields name,email id, mobile number and address.
Fill all the fields click on save data submit button to save your data inside “users” table using mysqli prepared statements insert.
To display all the values from “users” table click on disp data button it will display the values using mysqli prepared statements select.

Create Database and Table

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

//create a database CREATE DATABASE `demo`;   USE demo;   //create a table CREATE TABLE `demo`.`users` (   `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,   `name` CHAR( 50 ) NOT NULL ,   `email` VARCHAR( 50 ) NOT NULL ,   `mobile` BIGINT NOT NULL ,   `address` TEXT NOT NULL ,   UNIQUE ( `email`   ) ) ENGINE = MYISAM ;

PHP Script(PHP mysqli connection )

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67

<?php   //mysqli connectivity $connect= new mysqli("localhost","root","","demo") or die("ERROR:could not connect to the database!!!!");   extract($_POST);   if(isset($save)) {   $query="insert into users values('','$n','$e','$m','$add')";   if($connect->query($query))   {   echo 'congrates!! data saved';   }   else   {   echo 'data not saved '.$connect->error;   }   }   //display data if(isset($disp)) {   $query="select * from users";   $result=$connect->query($query);   echo "<table border=1>";   echo "<tr><th>Name</th><th>Email</th><th>Mobile< /th><th>Address</th></tr>";   while($row=$result->fetch_array())   {   echo "<tr>";   echo "<td>".$row['name']."</td>";   echo "<td>".$row['email']."</td>"; echo "<td>".$row['mobile']."</td>";   echo "<td>".$row['address']."</td>";   echo "</tr>";   }   echo "</table>";   }   ?>

HTML Form

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54

<style>   input,textarea{width:200px}   input[type=submit]{width:100px}   </style>   <form method="post">   <table width="218" border="0">     <tr>       <td width="208"><input type="text" name="n" placeholder="Enter your name"/></td>     </tr>     <tr>       <td><input type="text" name="e" placeholder="Enter your email"/></td>     </tr>     <tr>       <td><input type="text" name="m" placeholder="Enter your mobile"/></td>     </tr>     <tr>       <td><textarea name="add" placeholder="Enter your address"></textarea></td>     </tr>     <tr>       <td>   <input type="submit" name="save" value="Save Data"/>   <input type="submit" name="disp" value="Display Data"/>   </td>     </tr>   </table>   </form>

PHP MySQL Create Database

Create Database

The CREATE DATABASE statement is used to create a MySQL database.

Create database is used to create a MySQL database.

We must add the CREATE DATABASE statement to the mysql_query() function to execute the command.

Ex i(Create database)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

<?php $con = mysql_connect("localhost","root","") or die(mysql_error()); // Check connection if (mysql_connect_errno($con))   {   echo "Failed to connect to MySQL: " .mysql_connect_error($con);   } // Create database $sql="CREATE DATABASE Employee"; if (mysql_query($con,$sql))   { echo "Database Employee created successfully";   } else   { echo "Error creating database: " .mysql_error();   } ?>

In the above example first we create the connection with the database then we write statement to create a database of name employee after that this statement is passed to mysql_query() along with the connection variable of the database.

Create a table

The CREATE TABLE statement is used to create a table

Create table is used to create a table. while creating a table we can define several constraints such as primary key, unique key,auto_increment etc.

We must add the CREATE TABLE statement to the mysql_query() function to execute the command.
Ex (Create Table)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

<?php $con=mysql_connect("localhost","root","") or die(mysql_error()); // select database mysql_select_db("Employee",$con);    // Create table $sql="CREATE TABLE empInfo ( emp_id int auto_increment primary key, name char(50) not null, email varchar(50) not null, mobile bigint not null )"; if (mysql_query($sql))   {   echo "Table empInfo created successfully";   } else   { echo  "Error creating database: " .mysql_error();   } ?>

In the above example first we create the connection with the database then we select a database of name employee
then we write the statement to create a table,and this statement is passed to mysql_query().

PHP MySQL Insert

PHP MySQL Insert Query

The INSERT INTO statement is used to add new records to a database table.

The insert statement is used to add new records to a database table.
each time a new record is to be added we use INSERT INTO statement for that purpose.
there are two ways of inserting records either explicitly providing the column name
with values respectively or simply by providing values of table but doesn’t specify the
column name.

Syntax

1 2 3 4 5 6

INSERT INTO table_name (column1, column2, column3,...)             VALUES (value1, value2, value3,...)

The second way doesn’t specify the column names where the data will be inserted, only their values

Syntax

1 2 3 4 5

INSERT INTO table_name VALUES (value1, value2, value3,...)

Ex

In the previous chapter we created a table named “empInfo”, with four columns; “emp_id”, “name”, “emailid” and “mobile”. Use the same table to insert values inside empInfo table.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

<?php   $con=mysql_connect("localhost","root","") or die(mysql_error()); // select database mysql_select_db("Employee",$con);    //get data from html form $name=$_GET['name'];   $eid=$_GET['eid'];   $mob=$_GET['mob'];    //Insert values in empInfo table with column name $query="INSERT INTO empInfo(emp_id, name, email, mobile)    VALUES ('', '$name','$eid','$mob')";   mysql_query($query); //OR //Insert values in empInfo table directly $query="INSERT INTO empInfo VALUES ('', '$name','$eid','$mob')";   mysql_query($query); ?>

HTML Form

1 2 3 4 5 6 7 8 9 10 11 12 13

<form>   Enter your name<input type="text" name="name"/><hr/>   Enter your email<input type="text" name="eid"/><hr/>   Enter your mobile<input type="text" name="mob"/><hr/>   <input type="submit" value="INSERT"/><hr/>   </form>

In the above example the values such as name , eid , mobile number are fetched from the
form and inserted into empInfo table using INSERT INTO query.

Insert data from HTML form directly in database

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

<?php   $con=mysql_connect("localhost","root","") or die(mysql_error());   // select database mysql_select_db("Employee",$con<font color="blue">);   //Insert values into empInfo table directly using form $query="INSERT INTO empInfo VALUES ('', '{$_GET['name']}','{$_GET['eid']}','$_GET['mob']')";   mysql_query($query); ?>

HTML Form

1 2 3 4 5 6 7 8 9 10 11 12 13

<form> Enter your name<input type="text" name="name"/><hr/>   Enter your email<input type="text" name="eid"/><hr/>   Enter your mobile<input type="text" name="mob"/><hr/>   <input type="submit" value="INSERT"/><hr/>   </form>

In the above example values such as name , eid , mobile number are fetched from the
form and inserted into empInfo table without providing the column names

PHP MySQL Update

PHP MySQL Update Query

The update keyword is basically used to modify or edit the existing records in the database table.
it usually need a where clause to find out in which record change is to be done.

It is must to specify the where clause otherwise all records of that table got modify.
Syntax

1 2 3

UPDATE table_name SET column1=value1,column2=value2... WHERE column = value

Note : Notice the WHERE clause in the UPDATE syntax is must otherwise all records will be updated!

Ex

Previous records in empInfo table are:

Emp_id	Name	Email	Mobile
1	devesh	devesh@gmail.com	9910099100
2	deepak	deepak@gmail.com	9210053520
3	ravi	ravi@gmail.com	9810098100

This example updates some data in the “empInfo” table

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

<?php   //connect database $con=mysql_connect("localhost","root","") or die(mysql_error()); // select database mysql_select_db("Employee",$con);    //update values of empInfo table $data="UPDATE empInfo SET name='dev',mobile=9015501256 WHERE email='devesh@gmail.com'";   $val=mysql_query($data); ?>

In the above example ,
empInfo table has 3 records of devesh,deepak, and ravi.
we need updation in this table as
devesh change to dev and his mobile number also need to be changed.

here first we create connection with the database,then database is selected using mysql_select_db(),
then update query is passed to the mysql_query( ) and the database table is updated.

PHP MySQL Delete

PHP MySQL Delete

DELETE keyword is used basically to delete 1 or more than one records from the database.
even if we delete each and every row of a table then also the schema of the table remain un deleted.
it,s necessary to use where clause in delete query, otherwise all the records will be deleted.

Syntax

1 2 3 4 5

DELETE FROM table_name WHERE some_column = some_value

Note : Notice the WHERE clause in the DELETE syntax is must otherwise all records will be deleted!

Eg 1

Previous records in empInfo table are:

Emp_id	Name	Email	Mobile
1	devesh	devesh@gmail.com	9910099100
2	deepak	deepak@gmail.com	9210053520
3	ravi	ravi@gmail.com	9810098100

This example deleted one row from “empInfo” table

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

<?php   //connect database $con=mysql_connect("localhost","root","") or die(mysql_error()); // select database mysql_select_db("Employee",$con);    //update values of empInfo table $data="delete from empInfo WHERE email='devesh@gmail.com'";   $val=mysql_query($data); ?>

After deletion , the “empInfo” table will look like this:

Emp_id	Name	Email	Mobile
2	deepak	deepak@gmail.com	9210053520
3	ravi	ravi@gmail.com	9810098100

In the above example ,
empInfo table has 3 records of devesh,deepak, and ravi.
we need to delete a record from this table.
The record of devesh here is to be deleted.

here first we create connection with the database,then database is selected using mysql_select_db(),
then delete query is passed to the mysql_query and the table row is deleted.