Home »
PHP »
PHP programs
How to Use PHP to Insert Data Into MySQL Database?
Learn, how to insert data into MySQL database using PHP?
Submitted by Jyoti Singh, on January 31, 2018 [Last updated : March 13, 2023]
Create MySQL schema and a table
First, create a schema in a database and create table in it add following fields in it.
This table holds the data for a product supplier.
HTML form to get the data from the user
Now, create an Html form to get the data from the user. Make a file named "supplier.php" and add the following code in it:
HTML Code
<html>
<form action='supplierSubmit.php' method="post" enctype='multipart/form-data'>
<center>
<table>
<caption>
<font size='5'><b>Supplier Details</b></font>
</caption>
<tr>
<td><b>Supplier Id:</b></td>
<td><input type='text' name='sid' size=30></td>
</tr>
<tr>
<td><b>Supplier Name:</b></td>
<td><input type='text' name='sn' size=30></td>
</tr>
<tr>
<td><b>Firmname:</b></td>
<td><input type='text' name='fn' size=30></td>
</tr>
<tr>
<td><b>Gender:</b></td>
<td>
<input type=radio name='gen' value="Male">Male
<input type=radio name='gen' value='Female'>Female
</td>
</tr>
<tr>
<td><b>DOB:</b></td>
<td><input type=date name='dob' size=30></td>
</tr>
<tr>
<td><b>Amount Due:</b></td>
<td><input type='text' name='amtd' size=30></td>
</tr>
<tr>
<td><b>Amount Paid:</b></td>
<td><input type='text' name='amtp' size=30></td>
</tr>
<tr>
<td><b>City:</b></td>
<td><input type='text' name='city' size=30></td>
</tr>
<tr>
<td><b>Photo:</b></td>
<td>
<input type='file' name='photo'>
</td>
</tr>
<tr>
<td><input type=submit></td>
<td><input type=reset></td>
</tr>
</table>
</center>
</form>
</html>
Let's look how the form looks like:
Create a PHP file to write PHP code
Now make a file named "supplierSubmit.php" and add the following code in it.
PHP code to insert data into MySQL Table
<html>
<?php
$dsn="mysql:host=localhost;dbname=hpindia";
$cn=new PDO($dsn,'root','123');
$sid=$_POST['sid'];
$sn=$_POST['sn'];
$fn=$_POST['fn'];
$gender=$_POST['gen'];
$dob=$_POST['dob'];
$amtd=$_POST['amtd'];
$amtp=$_POST['amtp'];
$city=$_POST['city'];
$pic=$sid.".jpg";
//$photo=$_Files['photo']['name'];
$q="insert into supplier values($sid,'$sn','$fn','$gender','$dob',$amtd,$amtp,'$city','$pic')";
$smt=$cn->prepare($q);
$result=$smt->execute();
if($result){
move_uploaded_file($_FILES['photo']['tmp_name'],"images/$pic");
echo "<font color='green' size=5>Record Submitted</font>";
}
else{
echo "<font color='red' size=5>Fail to Submit Record</font>";
}
$nq="select * from supplier";
$res=$cn->query($nq);
if($res){
?>
<center>
<table border=1>
<caption>
<b><i>List of Suppliers</i></b>
</caption>
<tr>
<th>Supplier Id</th>
<th>Name</th>
<th>Firmname</th>
<th>Gender</th>
<th>DOB</th>
<th>Amount Due</th>
<th>Amount Paid</th>
<th>City</th>
<th>Photo</th>
<th>Update</th>
</tr>
<?php
while($row=$res->fetch()){
echo "<tr><td>$row[0]</td><td>$row[1]</td><td>$row[2]</td><td>$row[3]</td><td>$row[4]</td><td>$row[5]</td><td>$row[6]</td><td>$row[7]</td><td><img src='images/$row[8]' width=50 height=50></td><td><a href='displayById?sid=$row[0]'>Edit</a></td></tr>";
}
}
else {
echo "<font color='red' size=5>Record Not Found";
}
?>
</table>
</center>
</html>
This function is used to upload the files. Remember database will only hold the name of the image file and not the file to save the image a particular place we need make a folder named "images" inside your PHP workspace.
<?php
move_uploaded_file($_FILES['photo']['tmp_name'],"images/$pic");
?>
It is a predefined function, it takes two parameters.
$_FILES['photo']['tmp_name'] → this takes the picture and stores it in a temporary folder name tmp_name.
Second parameter has a folder named images and name of the picture in variable $pic, this transfers the picture from the temporary folder to the images folder with name as stored in $pic variable.
As the user hit's the submit button the details should be added to the database and the image must be added to images folder that u created earlier.
Note: In order to send a picture or file we always keep the request POST and enctype as multipart/form-data as we are sending picture as well as the form data with it.
PHP Database Programs »