How to Insert image In MySQL Using PHP


In some registration php form and application we need image upload. Example: For Profile picture upload, gallery photo upload, product image etc

There are two ways to insert images in mysql.

  1. Using binary format insert
  2. Using image upload in folder

Using binary format

In this type we directly insert the image in mysql table using binary format.

Using image upload in folder

In this type we upload the image in a folder and store the image name in MySql table.

For insert image in MySQL first we have to create a table in data base.

CREATE TABLE `image` (
`id` int(11) NOT NULL,
`image` varchar(55) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  

index.php


<!DOCTYPE html>
<html>
<head>
    <title>Insert Image in MySql using PHP</title>
</head>
<body>

<form action="upload.php" method="post" enctype="multipart/form-data">
    <input type="file" name="image[]" />
    <button type="submit">Upload</button>
</form>
</body>
</html>

upload.php

<?php
$cn=mysqli_connect("localhost","root","","image") or die("Could not Connect My Sql");
$output_dir = "upload/";/* Path for file upload */
	$RandomNum   = time();
	$ImageName      = str_replace(' ','-',strtolower($_FILES['image']['name'][0]));
	$ImageType      = $_FILES['image']['type'][0];
 
	$ImageExt = substr($ImageName, strrpos($ImageName, '.'));
	$ImageExt       = str_replace('.','',$ImageExt);
	$ImageName      = preg_replace("/\.[^.\s]{3,4}$/", "", $ImageName);
	$NewImageName = $ImageName.'-'.$RandomNum.'.'.$ImageExt;
    $ret[$NewImageName]= $output_dir.$NewImageName;
	
	/* Try to create the directory if it does not exist */
	if (!file_exists($output_dir))
	{
		@mkdir($output_dir, 0777);
	}               
	move_uploaded_file($_FILES["image"]["tmp_name"][0],$output_dir."/".$NewImageName );
	     $sql = "INSERT INTO `img`(`image`)VALUES ('$NewImageName')";
		if (mysqli_query($cn, $sql)) {
			echo "successfully !";
		}
		else {
		echo "Error: " . $sql . "" . mysqli_error($cn);
	 }

?>
CREATE TABLE `image` (
`id` int(11) NOT NULL,
`image` LONGBLOB NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  

Here we using 1 file for insert image in MySQL:

Using binary format

<!DOCTYPE html>
<html>
<head>
    <title>Insert Image in MySql using PHP</title>
</head>
<body>
<?php
$msg = '';
if($_SERVER['REQUEST_METHOD']=='POST'){
    $image = $_FILES['image']['tmp_name'];
    $img = file_get_contents($image);
    $con = mysqli_connect('localhost','root','','admin') or die('Unable To connect');
    $sql = "insert into images (image) values(?)";

    $stmt = mysqli_prepare($con,$sql);

    mysqli_stmt_bind_param($stmt, "s",$img);
    mysqli_stmt_execute($stmt);

    $check = mysqli_stmt_affected_rows($stmt);
    if($check==1){
        $msg = 'Image Successfullly UPloaded';
    }else{
        $msg = 'Error uploading image';
    }
    mysqli_close($con);
}
?>
<form action="" method="post" enctype="multipart/form-data">
    <input type="file" name="image" />
    <button>Upload</button>
</form>
<?php
    echo $msg;
?>
</body>
</html>