PHP Mysqli Procedural Approch
20
August
2015

1) Connecting To Database Server :::

For accesing our database we need to connect to our database server


		$server="localhost"; //Most Of The Time Database Server Will Be Localhost
		$user="root";        // Supper User. There will be some different user on your server
		$password="";        // Password For That Username
		$database="myDb";    // Our Database
		$con=mysqli_connect($server,$user,$password,$myDb);
		if(!$con){
			die("Can't Get It Connected ".mysqli_connect_error()); 
		}
		echo "Connection Successfully Completed";
	

2) Closing Connection :::

After our work is completed with the database it is always a good practice to close the connection.


	mysqli_close($con);
	

3) Script For Creating Tables

Many times in our application we have to create database for some or other purpose. So we will be creating our sample `STAFF` table for example

mysqli_query() function performs a query against the database. It will return a mysqli_result object. For other successful queries it will return TRUE. FALSE on failure


		$server="localhost"; //Most Of The Time Database Server Will Be Localhost
		$user="root";        // Supper User. There will be some different user on your server
		$password="";        // Password For That Username
		$database="myDb";    // Our Database
		$con=mysqli_connect($server,$user,$password,$myDb);
		if(!$con){
			die("Can't Get It Connected ".mysqli_connect_error()); 
		}
		$query="CREATE TABLE `staff`(
		 `id` INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
		 `firstname` VARCHAR(30) NOT NULL,
		 `lastname` VARCHAR(30) NOT NULL,
		 `email` VARCHAR(50),
		 `password` VARCHAR(50)
		)";		
		$query=mysqli_query($con,$query) or die(mysqli_error($con));
		mysqli_close($con);
	

4) Inserting Values Inside Of the Table

Tables are used for storing data, so lets start by adding some of the data to our newly created table `staff`

mysqli_real_escape_string() function escapes the special characters in our string and make it more safer to user

trim() function removes extra spaces from the begining and from the end of the string

If the column is set to AUTO_INCREMENT then mysql will automatically add its value


	
		$firstname="Lakshya";
		$firstname=mysqli_real_escape_string($con,trim($firstname));
		
		$lastname="Shah";
		$lastname=mysqli_real_escape_string($con,trim($lastname));
		
		$email="lakshya@paceinfonet.com";
		$email=mysqli_real_escape_string($con,trim($email));
		
		$password="dummypassword";
		$password=mysqli_real_escape_string($con,trim($password));
		
		$query="INSERT INTO `staff`(`firstname`,`lastname`,`email`,`password`) values('{$firstname}','{$lastname}','{$email}','{$password}')";
		$query=mysqli_query($con,$query) or die(mysqli_error($con));
		
	

You Can Also Insert Multiple Records Like this :

Mulitple Sql statements can be executed by using mysqli_multi_query() function. All the queries should be seperated by ‘;’


	$query="INSERT INTO `staff`(`firstname`,`lastname`,`email`,`password`) values('firstName1','lastName1','email1@example.com','dummypassword1');";
	$query." INSERT INTO `staff`(`firstname`,`lastname`,`email`,`password`) values('firstName2','lastName2','email2@example.com','dummypassword2');"
	$query." INSERT INTO `staff`(`firstname`,`lastname`,`email`,`password`) values('firstName3','lastName3','email3@example.com','dummypassword3');"
	$query." INSERT INTO `staff`(`firstname`,`lastname`,`email`,`password`) values('firstName4','lastName4','email4@example.com','dummypassword4');"
	$query." INSERT INTO `staff`(`firstname`,`lastname`,`email`,`password`) values('firstName5','la stName5','email5@example.com','dummypassword5');"
	$query=mysqli_multi_query($con,$query) or die(mysqli_error($con));
	

5) For Getting Last Inserted Id

`id` field is set to AUTO_INCREMENT, and it is the primary key for our table `staff`. Many times it is important to get the index of our latest inserted record.


		$index=mysqli_insert_id($con);
	

6) Selecting Data From Tables

mysqli_num_rows() function returns the number of rows in a result set.

mysqli_fetch_assoc() function fetches a result row as an associative array.

Data in table are stored for easy retrival of the data, from the table


	$query="SELECT `id`, `firstname`, `lastname` FROM `staff`";
	$result = mysqli_query($con, $query);
	

7) Updating the Record
mysqli_affected_rows() returns the number of rows affected by the last INSERT, UPDATE, REPLACE or DELETE query.


	$query = "UPDATE `staff` SET lastname='lastNameEdited' WHERE `id`='3'";
	mysqli_query($con, $query);
	if(mysqli_affected_rows($con)){
		echo "Updated";
	}else{
		echo "You Have Not Made Changes";
	}
	

8) Deleting The Record


	$query = "DELETE FROM `staff` WHERE `id`='3'";
	mysqli_query($con, $query);
	
  • 1225 View
  • Pin It