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:
mysql_connect(server,user,passwd);Parameter Description
server Optional - The host name running database server. If not specified then default value is localhost.
user Optional - The username accessing the database. If not specified then default is root.
passwd Optional - The password of the user accessing the database. If not specified then default is an empty password.
Selecting a Database:
Once you estblish 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:
mysql_select_db( db_name, connection );
Example :( connection and database selection)
<?php
define("db_hostname"."localhost");
define("db_user"."root");
define("db_pwd"."");
define("db_name"."database name");
$sql=mysql_connect(db_hostname,db_user,db_pwd);
mysql_select_db(db_name,$sql);
?>
Inserting data into MYSQL Database:
Data can be entered into MySQL tables by executing SQL INSERT statement through PHP function mysql_query. Below a simle example to insert a record into employee table.SYNTAX :
mysql_query("insert into table_name values('v1','v2','v3','v4','v5')");
Example :
Try out following example to insert record into profile table.
<?php
$fname = 'rex';
$lname = 'singh';
$id = 'rex@gmail.com';
$pwd = '111';
$city = 'aligarh';
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' . mysql_error());
}
$sql = "insert into profile values('$fname','$lname','$id','$pwd','$city')";
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);
Note :
PHP In abve example we insert values into table(profile) of database(test_db).
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.SYNTAX :
mysql_query("select * from table_name where
[WHERE Clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause]
");
Example :
Try out following example to display all the info from profile table.
<?php
define("db_hostname"."localhost");
define("db_user"."root");
define("db_pwd"."");
define("db_name"."test_db");
$sql=mysql_connect(db_hostname,db_user,db_pwd);
mysql_select_db(db_name,$sql);
$sql = mysql_query(select fname,lname,id from profile);
while($data=mysql_fetch_array($sql))
{
echo "$data[fname] - $data[lname] - $data[$id] <br>";
}
Note :
PHP In abve example we fetch values from table(profile) of database(test_db).
SYNTAX :
mysql_query("UPDATE table_name
SET column_name1 = value1,
column_name2 = value2, ...
[WHERE condition]
");
Example :
Try out following example to understand update operation. You need to provide an employee ID to update an employee salary.
<html>
<head>
<title>Update a Record in MySQL Database</title>
</head>
<body>
<?php
if(isset($_POST['update']))
{
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' . mysql_error());
}
$emp_id = $_POST['emp_id'];
$emp_salary = $_POST['emp_salary'];
$sql = "UPDATE employee ".
"SET emp_salary = $emp_salary ".
"WHERE emp_id = $emp_id" ;
mysql_select_db('test_db');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die('Could not update data: ' . mysql_error());
}
echo "Updated 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 ID</td>
<td><input name="emp_id" type="text" id="emp_id"></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="update" type="submit" id="update" value="Update">
</td>
</tr>
</table>
</form>
<?php
}
?>
</body>
</html>
SYNTAX :
mysql_query("DELETE FROM table_name [WHERE condition]");
Example :
Try out following example to understand delete operation. You need to provide an employee ID to delete an employee record from employee table.
<html>
<head>
<title>Delete a Record from MySQL Database</title>
</head>
<body>
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
mysql_select_db('test_db',$conn);
if(isset($_POST['delete']))
{
$emp_id = $_POST['emp_id'];
$sql = mysql_query("DELETE employee WHERE emp_id = '$emp_id'") ;
echo "Deleted data successfully\n";
mysql_close($conn);
}
?>
<form method="post" action="<?php $_PHP_SELF ?>">
<table width="400" border="0" cellspacing="1" cellpadding="2">
<tr>
<td width="100">Employee ID</td>
<td><input name="emp_id" type="text" id="emp_id"></td>
</tr>
<tr>
<td width="100"> </td>
<td> </td>
</tr>
<tr>
<td width="100"> </td>
<td>
<input name="delete" type="submit" id="delete" value="Delete">
</td>
</tr>
</table>
</form>
</body>
</html>
SQL TRUNCATE Statement
The SQL TRUNCATE command is used to delete all the rows from the table and free the space containing the table.
Syntax :
TRUNCATE TABLE table_name;
For Example: To delete all the rows from employee table, the query would be like,
TRUNCATE TABLE employee;
Difference between DELETE and TRUNCATE Statements:
DELETE Statement: This command deletes only the rows from the table based on the condition given in the where clause or deletes all the rows from the table if no condition is specified. But it does not free the space containing the table.
TRUNCATE statement: This command is used to delete all the rows from the table and free the space containing the table.
SQL DROP Statement:
The SQL DROP command is used to remove an object from the database. If you drop a table, all the rows in the table is deleted and the table structure is removed from the database. Once a table is dropped we cannot get it back, so be careful while using DROP command. When a table is dropped all the references to the table will not be valid.
Syntax :
DROP TABLE table_name;
For Example: To drop the table employee, the query would be like
mysql_query("DROP TABLE employee");
Difference between DROP and TRUNCATE Statement:
If a table is dropped, all the relationships with other tables will no longer be valid, the integrity constraints will be dropped, grant or access privileges on the table will also be dropped, if want use the table again it has to be recreated with the integrity constraints, access privileges and the relationships with other tables should be established again. But, if a table is truncated, the table structure remains the same, therefore any of the above problems will not exist.
Updating data in MYSQL Database:
Data can be updated into MySQL tables by executing SQL UPDATE statement through PHP function mysql_query.mysql_query("UPDATE table_name
SET column_name1 = value1,
column_name2 = value2, ...
[WHERE condition]
Example :
Try out following example to understand update operation. You need to provide an employee ID to update an employee salary.
<html>
<head>
<title>Update a Record in MySQL Database</title>
</head>
<body>
<?php
if(isset($_POST['update']))
{
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' . mysql_error());
}
$emp_id = $_POST['emp_id'];
$emp_salary = $_POST['emp_salary'];
$sql = "UPDATE employee ".
"SET emp_salary = $emp_salary ".
"WHERE emp_id = $emp_id" ;
mysql_select_db('test_db');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die('Could not update data: ' . mysql_error());
}
echo "Updated 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 ID</td>
<td><input name="emp_id" type="text" id="emp_id"></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="update" type="submit" id="update" value="Update">
</td>
</tr>
</table>
</form>
<?php
}
?>
</body>
</html>
Deleting data from MYSQL Database:
Data can be deleted from MySQL tables by executing SQL DELETE statement through PHP function mysql_query.mysql_query("DELETE FROM table_name [WHERE condition]");
Example :
Try out following example to understand delete operation. You need to provide an employee ID to delete an employee record from employee table.
<html>
<head>
<title>Delete a Record from MySQL Database</title>
</head>
<body>
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
mysql_select_db('test_db',$conn);
if(isset($_POST['delete']))
{
$emp_id = $_POST['emp_id'];
$sql = mysql_query("DELETE employee WHERE emp_id = '$emp_id'") ;
echo "Deleted data successfully\n";
mysql_close($conn);
}
?>
<form method="post" action="<?php $_PHP_SELF ?>">
<table width="400" border="0" cellspacing="1" cellpadding="2">
<tr>
<td width="100">Employee ID</td>
<td><input name="emp_id" type="text" id="emp_id"></td>
</tr>
<tr>
<td width="100"> </td>
<td> </td>
</tr>
<tr>
<td width="100"> </td>
<td>
<input name="delete" type="submit" id="delete" value="Delete">
</td>
</tr>
</table>
</form>
</body>
</html>
SQL TRUNCATE Statement
The SQL TRUNCATE command is used to delete all the rows from the table and free the space containing the table.
Syntax :
TRUNCATE TABLE table_name;
For Example: To delete all the rows from employee table, the query would be like,
TRUNCATE TABLE employee;
Difference between DELETE and TRUNCATE Statements:
DELETE Statement: This command deletes only the rows from the table based on the condition given in the where clause or deletes all the rows from the table if no condition is specified. But it does not free the space containing the table.
TRUNCATE statement: This command is used to delete all the rows from the table and free the space containing the table.
SQL DROP Statement:
The SQL DROP command is used to remove an object from the database. If you drop a table, all the rows in the table is deleted and the table structure is removed from the database. Once a table is dropped we cannot get it back, so be careful while using DROP command. When a table is dropped all the references to the table will not be valid.
Syntax :
DROP TABLE table_name;
For Example: To drop the table employee, the query would be like
mysql_query("DROP TABLE employee");
Difference between DROP and TRUNCATE Statement:
If a table is dropped, all the relationships with other tables will no longer be valid, the integrity constraints will be dropped, grant or access privileges on the table will also be dropped, if want use the table again it has to be recreated with the integrity constraints, access privileges and the relationships with other tables should be established again. But, if a table is truncated, the table structure remains the same, therefore any of the above problems will not exist.
No comments:
Post a Comment