You have gone through MySQL to understand MySQL Basics.
Downloaded and installed a latest version of MySQL.
Created database user guest with password guest123.
If you have not created a database then you would need root user and its password to create a database.

We have divided this chapter in the following sections −

Connecting to MySQL database − Learn how to use PHP to open and close a MySQL database connection.
Create MySQL Database Using PHP − This part explains how to create MySQL database and tables using PHP.
Delete MySQL Database Using PHP − This part explains how to delete MySQL database and tables using PHP.
Insert Data To MySQL Database − Once you have created your database and tables then you would like to insert your data into created tables. This session will take you through real example on data insert.
Retrieve Data From MySQL Database − Learn how to fetch records from MySQL database using PHP.
Using Paging through PHP − This one explains how to show your query result into multiple pages and how to create the navigation link.
Updating Data Into MySQL Database − This part explains how to update existing records into MySQL database using PHP.
Deleting Data From MySQL Database − This part explains how to delete or purge existing records from MySQL database using PHP.
Using PHP To Backup MySQL Database − Learn different ways to take backup of your MySQL database for safety purpose.

MySQL Database Connection

Opening Database Connection

PHP provides mysql_connect function to open a database connection. This function takes five parameters and returns a MySQL link identifier on success, or FALSE on failure.

Syntax

connection mysql_connect(server,user,passwd,new_link,client_flag);

Sr.No	Parameter & Description
1	server Optional − The host name running database server. If not specified then default value is localhost:3306.
2	user Optional − The username accessing the database. If not specified then default is the name of the user that owns the server process.
3	passwd Optional − The password of the user accessing the database. If not specified then default is an empty password.
4	new_link Optional − If a second call is made to mysql_connect() with the same arguments, no new connection will be established; instead, the identifier of the already opened connection will be returned.
5	client_flags Optional − A combination of the following constants − · MYSQL_CLIENT_SSL − Use SSL encryption · MYSQL_CLIENT_COMPRESS − Use compression protocol · MYSQL_CLIENT_IGNORE_SPACE − Allow space after function names · MYSQL_CLIENT_INTERACTIVE − Allow interactive timeout seconds of inactivity before closing the connection

NOTE − You can specify server, user, passwd in php.ini file instead of using them again and again in your every PHP scripts. Check php.ini file configuration.

Closing Database Connection

Its simplest function mysql_close PHP provides to close a database connection. This function takes connection resource returned by mysql_connect function. It returns TRUE on success or FALSE on failure.

Syntax

bool mysql_close ( resource $link_identifier );

If a resource is not specified then last opend database is closed.

Example

Try out following example to open and close a database connection −

<?php      

$dbhost = 'localhost:3036';   

$dbuser = 'guest';   

$dbpass = 'guest123';   

$conn = mysql_connect($dbhost, $dbuser, $dbpass);     

if(! $conn ) 

{      

die('Could not connect: ' . mysql_error());   

}      

echo 'Connected successfully';   

mysql_close($conn);

?>

Create MySQL Database Using PHP

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 );

Sr.No

Parameter & Description

sql

Required – SQL query to create a database

connection

Optional – if not specified then last opend connection by mysql_connect will be used.

Example

Try out following example to create a database −

<?php   $dbhost = 'localhost:3036';   

$dbuser = 'root';   

$dbpass = 'rootpassword';   

$conn = mysql_connect($dbhost, $dbuser, $dbpass);      

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";   

mysql_close($conn);

?>

Selecting a Database

Once you establish a connection with a database server then it is required to select a particular database where your all the tables are associated.

This is required because there may be multiple databases residing on a single server and you can do work with a single database at a time.

PHP provides function mysql_select_db to select a database.It returns TRUE on success or FALSE on failure.

Syntax

bool mysql_select_db( db_name, connection );

Sr.No

Parameter & Description

db_name

Required – Database name to be selected

connection

Optional – if not specified then last opend connection by mysql_connect will be used.

Example

Here is the example showing you how to select a database.

<?php   

$dbhost = 'localhost:3036';   

$dbuser = 'guest';   

$dbpass = 'guest123';   

$conn = mysql_connect($dbhost, $dbuser, $dbpass);      

if(! $conn )

 {       

die('Could not connect: ' . mysql_error());   

}      

echo 'Connected successfully';      

mysql_select_db( 'test_db' );   

mysql_close($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

Try out following example to create a table −

<?php      

$dbhost = 'localhost:3036';   

$dbuser = 'root';   

$dbpass = 'rootpassword';   

$conn = mysql_connect($dbhost, $dbuser, $dbpass);      

if(! $conn ) {      die('Could not connect: ' . mysql_error());   

}      

echo 'Connected successfully';      

$sql = 'CREATE TABLE employee( '.      'emp_id INT NOT NULL AUTO_INCREMENT, '.      'emp_name VARCHAR(20) NOT NULL, '.      'emp_address  VARCHAR(20) NOT NULL, '.      'emp_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";      

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 excute those commands.

Consider the following content in sql_query.txt file

CREATE TABLE employee(   emp_id INT NOT NULL AUTO_INCREMENT,   emp_name VARCHAR(20) NOT NULL,   emp_address  VARCHAR(20) NOT NULL,   emp_salary   INT NOT NULL,   join_date    timestamp(14) NOT NULL,   primary key ( emp_id ));

<?php   

$dbhost = 'localhost:3036';   

$dbuser = 'root';   

$dbpass = 'rootpassword';   

$conn = mysql_connect($dbhost, $dbuser, $dbpass);      

if(! $conn ) 

{      

die('Could not connect: ' . mysql_error());   

}      

$query_file = 'sql_query.txt';      

$fp = fopen($query_file, 'r');   

$sql = fread($fp, filesize($query_file));   

fclose($fp);       

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”; mysql_close($conn);?>

Deleting MySQL Database Using PHP

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

Try out following example to drop a database.

<?php   

$dbhost = 'localhost:3036';   

$dbuser = 'root';   

$dbpass = 'rootpassword';   

$conn = mysql_connect($dbhost, $dbuser, $dbpass);      

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";      

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

Try out following example to drop a table −

<?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);

?>

Insert Data into MySQL Database

Data can be entered into MySQL tables by executing SQL INSERT statement through PHP function mysql_query. Below a simple example to insert a record into employee table.

Example

Try out following example to insert record into employee table.

<?php   

$dbhost = 'localhost:3036';   

$dbuser = 'root';   

$dbpass = 'rootpassword';   

$conn = mysql_connect($dbhost, $dbuser, $dbpass);      

if(! $conn ) 

{      

die('Could not connect: ' . mysql_error());   

}      $sql = 'INSERT INTO employee '.      '(emp_name,emp_address, emp_salary, join_date) '.      'VALUES ( "guest", "XYZ", 2000, NOW() )';         

mysql_select_db('test_db');   

$retval = mysql_query( $sql, $conn );      

if(! $retval )

 {      

die('Could not enter data: ' . mysql_error());   

}      

echo "Entered data successfully\n";      

mysql_close($conn);

?>

In real application, all the values will be taken using HTML form and then those values will be captured using PHP script and finally they will be inserted into MySQL tables.

While doing data insert its best practice to use function get_magic_quotes_gpc() to check if current configuration for magic quote is set or not. If this function returns false then use function addslashes() to add slashes before quotes.

Example

Try out this example by putting this code into add_employee.php, this will take input using HTML Form and then it will create records into database.

<html>     

<head>      

<title>Add New Record in MySQL Database</title>  

 </head>      

<body>      

<?php         

if(isset($_POST['add'])) 

{            

$dbhost = 'localhost:3036';            

$dbuser = 'root';            

$dbpass = 'rootpassword';            

$conn = mysql_connect($dbhost, $dbuser, $dbpass);                        

if(! $conn ) 

{               

die('Could not connect: ' . mysql_error());            

}                        

if(! get_magic_quotes_gpc() ) 

{               

$emp_name = addslashes ($_POST['emp_name']);               

$emp_address = addslashes ($_POST['emp_address']);            

}

else 

{               

$emp_name = $_POST['emp_name'];               

$emp_address = $_POST['emp_address'];            

}                        

$emp_salary = $_POST['emp_salary'];                        

$sql = "INSERT INTO employee ". "(emp_name,emp_address, emp_salary,                join_date) ". "VALUES('$emp_name','$emp_address',$emp_salary, NOW())";                           

mysql_select_db('test_db');            

$retval = mysql_query( $sql, $conn );                        

if(! $retval )

 {               

die('Could not enter data: ' . mysql_error());            

}                        

echo "Entered data successfully\n";                        

mysql_close($conn);         

}

else

 {            

?>                           

<form method = "post" action = "<?php $_PHP_SELF ?>">                  

<table width = "400" border = "0" cellspacing = "1"                      cellpadding = "2">                                       

<tr>      

<td width = "100">Employee Name</td>                        

<td><input name = "emp_name" type = "text"           id = "emp_name"></td>                    

 </tr>                                       

<tr>                        

<td width = "100">Employee Address</td>                        

<td><input name = "emp_address" type = "text"           id = "emp_address"></td>                    

</tr>                                      

 <tr>                        

<td width = "100">Employee Salary</td>                       

 <td><input name = "emp_salary" type = "text"  id = "emp_salary"></td>                     

</tr>                                       

<tr>                        

<td width = "100"> </td>                        

<td> </td> 

  </tr>                                     

<tr>                        

<td width = "100"> </td>                       

 <td>  <input name = "add" type = "submit" id = "add"   value = "Add Employee">   </td>                     

</tr>                                    

</table>               

</form>                        

<?php         

}      

?>      

</body>

</html>

Getting Data From MySQL Database

Data can be fetched from MySQL tables by executing SQL SELECT statement through PHP function mysql_query. You have several options to fetch data from MySQL.

The most frequently used option is to use function mysql_fetch_array(). This function returns row as an associative array, a numeric array, or both. This function returns FALSE if there are no more rows.

Below is a simple example to fetch records from employee table.

Example

Try out following example to display all the records from employee table.

<?php   

$dbhost = 'localhost:3036';   

$dbuser = 'root';   

$dbpass = 'rootpassword';      

$conn = mysql_connect($dbhost, $dbuser, $dbpass);      

if(! $conn ) 

{      

die('Could not connect: ' . mysql_error());  

 }      

$sql = 'SELECT emp_id, emp_name, emp_salary FROM employee';   

mysql_select_db('test_db');   

$retval = mysql_query( $sql, $conn );      

if(! $retval ) 

{      

die('Could not get data: ' . mysql_error());   

}      

while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) 

{      

echo "EMP ID :{$row['emp_id']

} 

<br> ".         "EMP NAME : {$row['emp_name']} <br> ".         "EMP SALARY : 

{

$row['emp_salary']

} 

<br> ".         "--------------------------------<br>";   

}      

echo "Fetched data successfully\n";      

mysql_close($conn);

?>

The content of the rows are assigned to the variable $row and the values in row are then printed.

NOTE − Always remember to put curly brackets when you want to insert an array value directly into a string.

In above example the constant MYSQL_ASSOC is used as the second argument to mysql_fetch_array(), so that it returns the row as an associative array. With an associative array you can access the field by using their name instead of using the index.

PHP provides another function called mysql_fetch_assoc() which also returns the row as an associative array.

Example

Try out following example to display all the records from employee table using mysql_fetch_assoc() function.

<?php   

$dbhost = 'localhost:3036';   

$dbuser = 'root';  

$dbpass = 'rootpassword';      

$conn = mysql_connect($dbhost, $dbuser, $dbpass);     

 if(! $conn ) 

{      

die('Could not connect: ' . mysql_error());   

}      

$sql = 'SELECT emp_id, emp_name, emp_salary FROM employee';   

mysql_select_db('test_db');   

$retval = mysql_query( $sql, $conn );      

if(! $retval ) 

{      

die('Could not get data: ' . mysql_error());   

}     

while($row = mysql_fetch_assoc($retval)) 

{      

echo "EMP ID :

{

$row['emp_id']}  <br> ".         "EMP NAME : 

{

$row['emp_name']} <br> ".         "EMP SALARY : {

$row['emp_salary']

} <br> ".         "--------------------------------<br>";   

}      

echo "Fetched data successfully\n";      mysql_close($conn);

?>

You can also use the constant MYSQL_NUM, as the second argument to mysql_fetch_array(). This will cause the function to return an array with numeric index.

Example

Try out following example to display all the records from employee table using MYSQL_NUM argument.

<?php   $dbhost = 'localhost:3036';  

$dbuser = 'root';  

$dbpass = 'rootpassword';      

$conn = mysql_connect($dbhost, $dbuser, $dbpass);      

if(! $conn ) 

{      

die('Could not connect: ' . mysql_error());   

}      

$sql = 'SELECT emp_id, emp_name, emp_salary FROM employee';   

mysql_select_db('test_db');   $retval = mysql_query( $sql, $conn );      

if(! $retval ) 

{      

die('Could not get data: ' . mysql_error());   

}      

while($row = mysql_fetch_array($retval, MYSQL_NUM))

 {     

echo "EMP ID :{

$row[0]

}  

<br> ".         

"EMP NAME : {

$row[1]

} 

<br> ".         "EMP SALARY : {

$row[2]

} 

<br> ".         "--------------------------------<br>";   

}      

echo "Fetched data successfully\n";     

 mysql_close($conn);

?>

All the above three examples will produce same result.

Releasing Memory

Its a good practice to release cursor memory at the end of each SELECT statement. This can be done by using PHP function mysql_free_result(). Below is the example to show how it has to be used.

Example

Try out following example

<?php   

$dbhost = 'localhost:3036';   

$dbuser = 'root';  

$dbpass = 'rootpassword';      

$conn = mysql_connect($dbhost, $dbuser, $dbpass);    

if(! $conn )

 {      

die('Could not connect: ' . mysql_error());   

}     

$sql = 'SELECT emp_id, emp_name, emp_salary FROM employee';   mysql_select_db('test_db');  

 $retval = mysql_query( $sql, $conn );     

 if(! $retval ) 

{      

die('Could not get data: ' . mysql_error());   

}      

while($row = mysql_fetch_array($retval, MYSQL_NUM))

 {      

echo "EMP ID :{$row[0]} 

 <br> ".         

"EMP NAME : {$row[1]} <br> ".         "EMP SALARY : {$row[2]

}

 <br> ".         "--------------------------------<br>";   

}      

mysql_free_result($retval);   

echo "Fetched data successfully\n";      

mysql_close($conn);

?>

While fetching data you can write as complex SQL as you like. Procedure will remain same as mentioned above.

So, this brings us to the end of blog. This Tecklearn ‘Operations in MySQL DB using PHP’ blog helps you with commonly asked questions if you are looking out for a job in PHP Programming. If you wish to learn Java and build a career Java Programming domain, then check out our interactive, Java and JEE Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/java-and-jee-training/

Java and JEE Training

About the Course

Java and JEE Certification Training is designed by professionals as per the industrial requirements and demands. This training encompasses comprehensive knowledge on basic and advanced concepts of core Java & J2EE along with popular frameworks like Hibernate, Spring & SOA. In this course, you will gain expertise in concepts like Java Array, Java OOPs, Java Function, Java Loops, Java Collections, Java Thread, Java Servlet, and Web Services using industry use-cases and this will help you to become a certified Java expert.

Why Should you take Java and JEE Training?

Java developers are in great demand in the job market. With average pay going between $90,000/- to $120,000/- depending on your experience and the employers.
Used by more than 10 Million developers worldwide to develop applications for 15 Billion devices.
Java is one of the most popular programming languages in the software world. Rated #1 in TIOBE Popular programming languages index (15th Consecutive Year)

What you will Learn in this Course?

Introduction to Java

Java Fundamentals
Introduction to Java Basics
Features of Java
Various components of Java language
Benefits of Java over other programming languages
Key Benefits of Java

Installation and IDE’s for Java Programming Language

Installation of Java
Setting up of Eclipse IDE
Components of Java Program
Editors and IDEs used for Java Programming
Writing a Simple Java Program

Data Handling and Functions

Data types, Operations, Compilation process, Class files, Loops, Conditions
Using Loop Constructs
Arrays- Single Dimensional and Multi-Dimensional
Functions
Functions with Arguments

OOPS in Java: Concept of Object Orientation

Object Oriented Programming in Java
Implement classes and objects in Java
Create Class Constructors
Overload Constructors
Inheritance
Inherit Classes and create sub-classes
Implement abstract classes and methods
Use static keyword
Implement Interfaces and use it

Polymorphism, Packages and String Handling

Concept of Static and Run time Polymorphism
Function Overloading
String Handling –String Class
Java Packages

Exception Handling and Multi-Threading

Exception handling
Various Types of Exception Handling
Introduction to multi-threading in Java
Extending the thread class
Synchronizing the thread

File Handling in Java

Input Output Streams
io Package
File Handling in Java

Java Collections

Wrapper Classes and Inner Classes: Integer, Character, Boolean, Float etc
Applet Programs: How to write UI programs with Applet, Java.lang, Java.io, Java.util
Collections: ArrayList, Vector, HashSet, TreeSet, HashMap, HashTable

Java Database Connectivity (JDBC)

Introduction to SQL: Connect, Insert, Update, Delete, Select
Introduction to JDBC and Architecture of JDBC
Insert/Update/Delete/Select Operations using JDBC
Batch Processing Transaction
Management: Commit and Rollback

Java Enterprise Edition – Servlets

Introduction to J2EE
Client Server architecture
URL, Port Number, Request, Response
Need for servlets
Servlet fundamentals
Setting up a web project in Eclipse
Configuring and running the web app with servlets
GET and POST request in web application with demo
Servlet lifecycle
Servlets Continued
Session tracking and filter
Forward and include Servlet request dispatchers

Java Server Pages (JSP)

Fundamentals of Java Server Page
Writing a code using JSP
The architecture of JSP
JSP Continued
JSP elements: Scriptlets, expressions, declaration
JSP standard actions
JSP directives
Introduction to JavaBeans
ServletConfig and ServletContext
Servlet Chaining
Cookies Management
Session Management

Hibernate

Introduction to Hibernate
Introduction to ORM
ORM features
Hibernate as an ORM framework
Hibernate features
Setting up a project with Hibernate framework
Basic APIs needed to do CRUD operations with Hibernate
Hibernate Architecture

POJO (Plain Old Java Object)

POJO (Plain Old Java Object)
Persistent Objects
Lifecycle of Persistent Object

Spring

Introduction to Spring
Spring Fundamentals
Advanced Spring

Got a question for us? Please mention it in the comments section and we will get back to you.

476