Executing SQL Queries using PHP

The first step for Connecting to MySQL is to call mysqli_connect() function The Syntax for this function is as follows.

$conn = mysqli_connect (hostname or servername, username, password, db_name);

The first three arguments sent to the function (hostname, username, and password) are based upon the users of MySQL database.

The hostname value will be localhost. The fourth argument is the name of the database to use.

This is the equivalent of saying use database name within the MySQL client. If the connection was made, the $conn variable, short for database connection, will become a reference point for all of your subsequent database interactions.

Most of the PHP functions for working with MySQL will take this variable as its first argument.

Create a Connection to a MySQL Database

Before you can access data in a database, you must create a connection to the database. In PHP, this is done with the mysqli_connect() function.

Servername – Specifies the server to connect to. Default value is "localhost"
Username - Specifies the username to log in with. Default value is the name of the user that owns the server process
Password - Specifies the password to log in with. Default is ""
DatabaseName- to provide database name created in PhpMyAdmin.

Example:

<?php
	$conn = mysqli_connect("localhost","root","","my_db");
	if (!$con){
		Print 'Could not connect: ' . mysqli_error();
	}
	else{
		Print 'Connect';
	}
	// some code
?>

Closing a Connection

The connection will be closed automatically when the script ends. To close the connection before, use the mysqli_close() function.

Syntax:

mysqli_close(connection variable);

Example:

mysqli_close($conn);

Creating a Database

To create and delete a database you should have admin privilege. Its very easy to create a new MySQL database. PHP uses mysql_query() function to create a MySQL database.

This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax:

bool mysql_query(sql, connection);

Sql - Required. SQL query to create a database.
Connection - Optional. if not specified then last opend connection by mysql_connect() will be used.

Example:

<?php
	$dbhost = 'localhost:3036';
	$dbuser = 'root';
	$dbpass = 'rootpassword';
	// Create Connection
	$conn = mysql_connect($dbhost, $dbuser, $dbpass);
	// Check Connection
	if(!$conn ) {
		die('Could not connect: ' . mysql_error());
	}
	echo 'Connected successfully';
	$sql = 'CREATE Database test_db';
	$retval = mysql_query( $sql, $conn );
	if(! $retval ) {
		die('Could not create database: ' . mysql_error());
	}
	echo "Database test_db created successfully\n";
	// Close Connection
	mysql_close($conn);
?>

In case you need to create many tables then its better to create a text file first and put all the SQL commands in that text file and then load that file into $sql variable and execute those commands.

Deleting a Database

If a database is no longer required then it can be deleted forever. You can use pass an SQL command to mysql_query() to delete a database.

Example:

<?php
	$dbhost = 'localhost:3036';
	$dbuser = 'root';
	$dbpass = 'rootpassword';
	// Create Connection
	$conn = mysql_connect($dbhost, $dbuser, $dbpass);
	// Ccheck Connection
	if(!$conn ) {
		die('Could not connect: ' . mysql_error());
	}
	$sql = 'DROP DATABASE test_db';
	$retval = mysql_query($sql, $conn);
	if(!$retval ) {
		die('Could not delete database db_test: ' . mysql_error());
	}
	echo "Database deleted successfully\n";
	// Close Connection
	mysql_close($conn);
?>

WARNING − its very dangerous to delete a database and any table. So before deleting any table or database you should make sure you are doing everything intentionally.

Deleting a Table

Its again a matter of issuing one SQL command through mysql_query() function to delete any database table.

But be very careful while using this command because by doing so you can delete some important information you have in your table.

Example:

<?php
	$dbhost = 'localhost:3036';
	$dbuser = 'root';
	$dbpass = 'rootpassword';
	$conn = mysql_connect($dbhost, $dbuser, $dbpass);
	if(!$conn ) {
		die('Could not connect: ' . mysql_error());
	}
	$sql = 'DROP TABLE employee';
	$retval = mysql_query( $sql, $conn );
	if(!$retval ) {
		die('Could not delete table employee: ' . mysql_error());
	}
	echo "Table deleted successfully\n";
	mysql_close($conn);
?>

Selecting Database

PHP uses mysqli_select_db() function to select the database on which queries are to be performed. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax:

mysqli_select_db(connection variable , string $dbname ) : bool

Example:

$db = mysqli_select_db($conn, 'stud' ); 
if(!$db) { 
	die('Could not select database: ' . mysqli_error($conn)); 
}

Creating Database Tables

To create tables in the new database you need to do the same thing as creating the database.

First create the SQL query to create the tables then execute the query using mysql_query() function.

Example:

<?php
	$dbhost = 'localhost:3036';
	$dbuser = 'root';
	$dbpass = 'rootpassword';
	// Create Connection
	$conn = mysql_connect($dbhost, $dbuser, $dbpass);
	// Check Connection
	if(!$conn ) {
		die('Could not connect: ' . mysql_error());
	}
	echo 'Connected successfully';
	$sql = 'CREATE TABLE users( '.
		'id INT NOT NULL AUTO_INCREMENT, '.
		'name VARCHAR(20) NOT NULL, '.
		'address VARCHAR(20) NOT NULL, '.
		'salary INT NOT NULL, '.
		'join_date timestamp(14) NOT NULL, '.
		'primary key (emp_id)
	)';
	mysql_select_db('test_db');
	$retval = mysql_query( $sql, $conn );
	if(! $retval ) {
		die('Could not create table: ' . mysql_error());
	}
	echo "Table employee created successfully\n";
	// Close Connection
	mysql_close($conn);
?>

Executing Simple Queries

Once you have successfully connected to and selected a database, you can start performing queries.

These queries can be as basic as inserts, updates, and deletions or as involved as complex joins returning numerous rows.

In any case, the PHP function for executing a query is mysqli_query().

Syntax:

mysqli_query(connection variable , string $query);

Example:

$result = mysqli_query($conn, $sql);

The mysqli_query() function takes the database connection as its argument and the query itself.

For simple queries like INSERT, UPDATE, DELETE, etc. (which do not return records), the $result variable short for result will be either TRUE or FALSE, depending upon whether the query executed successfully.

Inserting Record into Database

After a database and a table have been created, we can start adding data in them.

The SQL query must be quoted in PHP String values inside the SQL query must be quoted Numeric values must not be quoted The INSERT INTO statement is used to add new records to a MySQL table.

Syntax:

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

Example:

<?php
	$servername = "localhost";
	$username = "username";
	$password = "password";
	$dbname = "myDB";
	// Create Connection
	$conn = mysqli_connect($servername, $username, $password, $dbname);
	// Check Connection
	if (!$conn) {
		die("Connection failed: " . mysqli_connect_error());
	}
	$sql = "INSERT INTO users (firstname, lastname, email)
	VALUES ('Sujan', 'Nepal', 'gopal@gmail.com')";
	if (mysqli_query($conn, $sql)) {
		echo "New record created successfully";
	} else {
		echo "Error: " . $sql . "<br>" . mysqli_error($conn);
	}
	
	// Close Connection
	mysqli_close($conn);
?>

Multiple SQL statements must be executed with the mysqli_multi_query() function.

Retrieving Query Results

First, we set up an SQL query that selects the column name from table. The SELECT statement is used to select data from one or more tables.

Syntax:

// for specific column from table
SELECT column_name(s) FROM table_name;

// for all character from table
SELECT * FROM table_name;

or we can use the * character to select ALL columns from a table.

The next line of code runs the query and puts the resulting data into a variable called $result.

Then, the function num_rows() checks if there are more than zero rows returned.

If there are more than zero rows returned, the function fetch_assoc() puts all the results into an associative array that we can loop through.

The while() loop loops through the result set and outputs the data from the no of column name.

Example:

<?php
	// Create connection
	$conn = mysqli_connect("localhost", "root", "", "myDB");
	// Check connection
	if (!$conn) {
 		die("Connection failed: " . mysqli_connect_error());
	}
	$sql = "SELECT id, firstname, lastname FROM MyTable";
	$result = mysqli_query($conn, $sql);
	if (mysqli_num_rows($result) > 0) {
 	// output data of each row
 		while($row = mysqli_fetch_assoc($result)) {
 		echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"].
		"<br>";
 		}
	} else { 
		echo "No results found!";
	}
	// Close connection
	mysqli_close($conn);
?>

Counting Returned Records

We can get the total number of rows in a table by using the MySQL mysqli_num_rows() function.

Syntax:

mysqli_num_rows(result);

The result is to specify the result set identifier returned by mysqli_query() function.

Example:

<?php
	$sql = "SELECT * from users";
	if ($result = mysqli_query($con, $sql)) {
		// Return the number of rows in result set
		$rowcount = mysqli_num_rows( $result );
		// Display result
		printf("Total rows in this table : %d\n", $rowcount);
	}
?>

Output:

Total rows in this table : 5

We count the table rows using MySQL count() function. It’s an aggregate function used to count rows.

Syntax:

select count (*) from table;

Updating Records with PHP

The UPDATE statement is used to update existing records in a tabl.

Syntax:

UPDATE table_name SET column1=value, column2=value2,... WHERE some_column=some_value

Example:

<?php
	$servername = "localhost";
	$username = "username";
	$password = "password";
	$dbname = "myDB";
	// Create connection
	$conn = mysqli_connect($servername, $username, $password, $dbname);
	// Check connection
	if (!$conn) {
 		die("Connection failed: " . mysqli_connect_error());
	}
	$sql = "UPDATE users SET lastname='Shah' WHERE id=1";
	if (mysqli_query($conn, $sql)) {
 		echo "Record updated successfully";
	}
	else {
 		echo "Error updating record: " . mysqli_error($conn);
	}
	// Close connection
	mysqli_close($conn);
?>

Deleting Data from MySQL Database

Data can be deleted from MySQL tables by executing SQL DELETE statement through PHP function mysql_query(). Below is a simple example to delete records into users table.

To delete a record in any table it is required to locate that record by using a conditional clause. Below example uses primary key to match a record in users table.

Example:

<?php
	$servername = "localhost";
	$username = "username";
	$password = "password";
	$dbname = "myDB";
	// Create connection
	$conn = mysqli_connect($servername, $username, $password, $dbname);
	// Check connection
	if (!$conn) {
 		die("Connection failed: " . mysqli_connect_error());
	}
	$sql = "DELETE FROM users WHERE id = $user_id";
	if (mysqli_query($conn, $sql)) {
 		echo "Record Deleted successfully";
	}
	else {
 		echo "Error deleting record: " . mysqli_error($conn);
	}
	// Close connection
	mysqli_close($conn);
?>

Ensuring Secure SQL

Database security with respect to PHP comes down to three broad issues.

Protecting the MySQL access information
Not display too much about the database.
Being careful when running queries, particularly those involving user submitted data.

You can accomplish the first objective by securing the MySQL connection script outside of the Web directory so that it is never viewable through a Web browser.

The second objective is achieved by not letting the user see PHP’s error messages or your queries (in these scripts, that information is printed out for your debugging purposes; you’d never want to do that on a live site).

For the third objective, there are numerous steps you can and should take, all based upon the premise of never trusting user supplied data.

First, validate that some value has been submitted, or that it is of the proper type (number, string, etc.).

Second, use regular expressions to make sure that submitted data matches what you would expect.

Third, you can typecast some values to guarantee that they’re numbers.

Fourth recommendation is to run user submitted data through the mysql_real_ escape_string() function.

This function cleans data by escaping what could be problematic characters.

$clean = mysql_real_escape_string($dbc, data);

For security purposes, mysql_real_escape_string() should be used on every text input in a form.

Menu

Executing SQL Queries using PHP

Create a Connection to a MySQL Database

Closing a Connection

Creating a Database

Deleting a Database

Deleting a Table

Selecting Database

Creating Database Tables

Executing Simple Queries

Inserting Record into Database

Retrieving Query Results

Counting Returned Records

Updating Records with PHP

Deleting Data from MySQL Database

Ensuring Secure SQL

Introduction to Algorithm

Recursion Algorithm

Introduction

Control Structures and Loop

Array and Function

Form Handling and Data Validation

File Handling, Sessions, and Error Handling

Working with Database

Advanced PHP Concepts

PHP Framework

Executing SQL Queries using PHP

Create a Connection to a MySQL Database

Closing a Connection

Creating a Database

Deleting a Database

Deleting a Table

Selecting Database

Creating Database Tables

Executing Simple Queries

Inserting Record into Database

Retrieving Query Results

Counting Returned Records

Updating Records with PHP

Deleting Data from MySQL Database

Ensuring Secure SQL

Introduction to Algorithm

Recursion Algorithm