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&nbsp;&nbsp;
            <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:

PHP - Insert record in MySQL - HTML form

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 »

PHP Invoice Example

PHP - MySql Connection Example

Related Programs

Comments and Discussions!

Load comments ↻

Top MCQs

Top Programs/Examples