Using MySQL DML command In PHP

When data is put into a MySQL table it is referred to as inserting data. When inserting data it is important to remember the exact names and types of the table’s columns. If you try to place a 500 word essay into a column that only accepts integers of size three, you will end up with a nasty error!
 
Note: As stated in the previous sub-section, we are going to use the table created in the previous sub-section for examples coming in this chapter.
 
Inserting records
 
Now that you have created your table, let’s put some data into that table! Here is the PHP/MySQL code for inserting data into the table we created in the previous lesson [since we have the interface ready we can create the table anytime and I assume that you’ve already created one].
 
<?php
class dbUtils{	
	private $flag;
	private $sql;//="create database ";
	private $msg;//="";*/
	#function createDB
	public function __construct(){
		$this->flag=0;
		$this->sql="";
		$this->msg="";
	}
	public function connect(){
		$con=@mysql_connect("localhost","root","");
		if(!$con)
			return NULL;
		else 
			return $con;		
	}
	public function createDB($db_to_create){		
	}
	public function createTable($db_to_use,$tbl_name,$command){
	}
	public function insert_record($db_to_use, $command){
		$con;
		$msg1="Result=";
		#echo $db_to_use.$command;
		if(!$db_to_use||!$command)	{
		return "Either Database name of Command is empty";				
		}
		else{
		$con=$this->connect();		
			
			if($con){
				
				mysql_select_db($db_to_use);
				$result=mysql_query($command,$con) or die(mysql_error());
					if(!$result)
						return "<b> Unable to insert records i
n the specified table".mysql_error()."</b>";
					else
						return "<b>Record(s) updated successfully</b>"; 
						
			}
			else 
			{
				return  " Unable to connect to DB";
			}
		}		
	}
	public function update_record($db_to_use, $commnad){
	}
	public function delete_record($db_to_use, $commnad){
	}
}
?>
<HTML>
<HEAD>
<TITLE> PHP Tutorial : Workign with MySQL[Insert Record] </TITLE>
</HEAD>
<BODY>
<TABLE border="1"  style="border: 1px solid blue;">
<form name="insert" action="<?php $PHP_SELF;?>" method="post">
<TR>
	<TD>Enter Database to Use</td>
	<TD><INPUT TYPE="text" NAME="db_name" size="20" maxlength="20"></td>
</tR>
<TR>
	<TD>Enter Table Name</td>
	<TD><INPUT TYPE="" NAME="tbl" size="30" maxlength="30"></td>	
</tR>
<TR>
	<TD>Enter Name</td>
	<TD><INPUT TYPE="text" NAME="name" size="30" maxlength="30"></td>
</tR>
<TR>
	<TD>Enter Address</td>
	<TD><textarea cols="20" rows="4" name="address"></textarea></td>	
</tR>
<TR>
	<TD>Enter Email Address</td>
	<TD><INPUT TYPE="text" NAME="email" size="50" maxlength="90"></td>	
</tR>
<TR>
	<TD><INPUT TYPE="Submit" NAME="submit" value="Insert Record"></td>
	<TD><INPUT TYPE="Reset" NAME="Reset" value="Reset"></td>	
</tR>
</form>
</TABLE>
<?php
	if(isset($_POST["submit"])){
?>
<div style="border:thin #FF0000 solid; width:80%; height:auto">

<?php
	$db=$_POST["db_name"];
	$tbl=trim($_POST["tbl"]);
	$name=$_POST["name"];
	$addr=$_POST["address"];
	$email=$_POST["email"];
	if($db&&$tbl)	{
			$sql="insert into ".$tbl." values 
('null','".$name."','".$addr."','".$email."');";
			$dbutil=new dbUtils();
			$msg2=$dbutil->insert_record($db,$sql);
			if($msg2)
				echo $msg2;
			else 
				echo "Unable to insert record i
nto $tbl an error has occured ".$msg2;
		}
	else{	
		echo "Please enter Valid Database and/or table name to process";	
	}
?>
	</div>
<?php	
	}
?>
</BODY>
</HTML>
 
About Script should produce the following HTML form
 
img
 
After you fill data in the form and click on Insert record button it should display the following output
 
img
 
img
 
Explanation of the above script:
 
1. In the above script, at first we check if the form has been submitted
 
<?php
	if(isset($_POST["submit"])){
?>
 
2. If the form has been submitted then we retrieve the form field values and construct the query
 
$db=$_POST["db_name"]; //Retrieve the database name 
$tbl=trim($_POST["tbl"]);// retrieve table name from form
$name=$_POST["name"];//Retrieve the Name from form
$addr=$_POST["address"];//Retrieve the Address from form
$email=$_POST["email"];//Retrieve the email id from form
if($db&&$tbl){
 $sql="insert into ".$tbl." values ('null','".$name."','".$addr."','".$email."');";
 
3. Now, we create an object of dbUtils class
 
$dbutil=new dbUtils();
 
4. Call the insert_record function of dbUtils class
 
$msg2=$dbutil->insert_record($db,$sql);
 
5. In the insert_record function change the database to use user specified database and execute the query to insert the record into the database table, besides other required rituals
 
mysql_select_db($db_to_use);				
$result=mysql_query($command,$con) or die(mysql_error());	
		if(!$result)

	return "<b> Unable to insert records i
n the specified table".mysql_error()."</b>";
        else
	return "<b>Record(s) updated successfully</b>";
 
Updating Records
 
Following PHP script update the name of the user whose id is 2:
 
<html>
<head>
<title>PHP Tutorial :Working with MySQL</title>

</head><body>
<?php
$command="update test_tbl set name='Neeraj Bhardwaj' where id=2;";
$con=mysql_connect("localhost","root","") or die();
mysql_select_db("test", $con) or die(mysql_error());

		#echo $sql;
		$result=mysql_query($command,$con)or die (mysql_error());
		if($result)
		{
			echo "Record Updated  Successfully <br>.";
		}
		
?>
</body>
</html>
 
This example is like the previous example, except that we are updating the record(s), and we’ve used update command instead of insert command.
 
Deleting Records
 
<html>
<head>
<title>PHP Tutorial :Working with MySQL</title>

</head><body>
<?php
$command="delete from test_tbl where id=2;";
$con=mysql_connect("localhost","root","") or die();
mysql_select_db("test", $con) or die(mysql_error());

		#echo $sql;
		$result=mysql_query($command,$con)or die (mysql_error());
		if($result)
		{
			echo "Record Deleted Successfully <br>.";
		}
		
?>
</body>
</html>
 
Above PHP script delete a record from table test_tbl whose id is 2.
 
All three examples above are same except the SQL command used. As you can see it is quite easy to play with MySQL using PHP. Of course, you can use HTML forms to perform these operations. By doing so you can perform SQL operations dynamically.
Scroll to Top