| 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 |
| |
 |
| |
| After you fill data in the form and click on Insert record button it should display the following output |
| |
 |
| |
 |
| |
| 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. |