How to Store Images in MySQL Database Using PHP

How to store images in mysql database using php

I found the answer, For those who are looking for the same thing here is how I did it.
You should not consider uploading images to the database instead you can store the name of the uploaded file in your database and then retrieve the file name and use it where ever you want to display the image.

HTML CODE

<input type="file" name="imageUpload" id="imageUpload">

PHP CODE

if(isset($_POST['submit'])) {

    //Process the image that is uploaded by the user

    $target_dir = "uploads/";
    $target_file = $target_dir . basename($_FILES["imageUpload"]["name"]);
    $uploadOk = 1;
    $imageFileType = pathinfo($target_file,PATHINFO_EXTENSION);

    if (move_uploaded_file($_FILES["imageUpload"]["tmp_name"], $target_file)) {
        echo "The file ". basename( $_FILES["imageUpload"]["name"]). " has been uploaded.";
    } else {
        echo "Sorry, there was an error uploading your file.";
    }

    $image=basename( $_FILES["imageUpload"]["name"],".jpg"); // used to store the filename in a variable

    //storind the data in your database
    $query= "INSERT INTO items VALUES ('$id','$title','$description','$price','$value','$contact','$image')";
    mysql_query($query);

    require('heading.php');
    echo "Your add has been submited, you will be redirected to your account page in 3 seconds....";
    header( "Refresh:3; url=account.php", true, 303);
}

CODE TO DISPLAY THE IMAGE

while($row = mysql_fetch_row($result)) {
    echo "<tr>";
    echo "<td><img src='uploads/$row[6].jpg' height='150px' width='300px'></td>";
    echo "</tr>\n";
}

How can I store and retrieve images from a MySQL database using PHP?

First you create a MySQL table to store images, like for example:

create table testblob (
    image_id        tinyint(3)  not null default '0',
    image_type      varchar(25) not null default '',
    image           blob        not null,
    image_size      varchar(25) not null default '',
    image_ctgy      varchar(25) not null default '',
    image_name      varchar(50) not null default ''
);

Then you can write an image to the database like:

/***
 * All of the below MySQL_ commands can be easily
 * translated to MySQLi_ with the additions as commented
 ***/ 
$imgData = file_get_contents($filename);
$size = getimagesize($filename);
mysql_connect("localhost", "$username", "$password");
mysql_select_db ("$dbname");
// mysqli 
// $link = mysqli_connect("localhost", $username, $password,$dbname); 
$sql = sprintf("INSERT INTO testblob
    (image_type, image, image_size, image_name)
    VALUES
    ('%s', '%s', '%d', '%s')",
    /***
     * For all mysqli_ functions below, the syntax is:
     * mysqli_whartever($link, $functionContents); 
     ***/
    mysql_real_escape_string($size['mime']),
    mysql_real_escape_string($imgData),
    $size[3],
    mysql_real_escape_string($_FILES['userfile']['name'])
    );
mysql_query($sql);

You can display an image from the database in a web page with:

$link = mysql_connect("localhost", "username", "password");
mysql_select_db("testblob");
$sql = "SELECT image FROM testblob WHERE image_id=0";
$result = mysql_query("$sql");
header("Content-type: image/jpeg");
echo mysql_result($result, 0);
mysql_close($link);

How do I store an image in MySQL database using php?

You can store the base64 string of the image in your table.

$img_file = "/tmp/my_image.jpg";
// get base64 string
$imgData = base64_encode(file_get_contents($img_file));

// insert into DB
$dbh->prepare("INSERT INTO my_image_table (img_base64) VALUES(?)");
$dbh->execute(array($imgData));

Make sure your data type of the column is big enough for the images. LONGTEXT can save up to 4GB.

Alternative: use BLOB or file-paths like others suggested.

PHP to store images in MySQL or not?

Always depends of context, but usually, I store a user image on the filesystem in a folder called /content/user/{user_id}.jpg and try to bother the database as little as possible.

Can I store images in MySQL

Yes, you can store images in the database, but it's not advisable in my opinion, and it's not general practice.

A general practice is to store images in directories on the file system and store references to the images in the database. e.g. path to the image,the image name, etc.. Or alternatively, you may even store images on a content delivery network (CDN) or numerous hosts across some great expanse of physical territory, and store references to access those resources in the database.

Images can get quite large, greater than 1MB. And so storing images in a database can potentially put unnecessary load on your database and the network between your database and your web server if they're on different hosts.

I've worked at startups, mid-size companies and large technology companies with 400K+ employees. In my 13 years of professional experience, I've never seen anyone store images in a database. I say this to support the statement it is an uncommon practice.

Saving images to MySQL Database? Yes? Or not?

A few comments:

You seem to be using date('d.m.Y') I guess to get the current date. You're using it as a PHP function, but it's inside an SQL statement. I suggest you use CURDATE() if you put it inside the SQL statement. If you use the PHP function, do it outside the SQL statement and bind the result as a parameter.

Your code stores both the img content and the path to the tmpfile for the upload. I don't see the need for this. Pick one or the other. An uploaded tmpfile is removed at the end of the PHP request, so I don't know why you would store that path.

Don't use addslashes() for content you're binding to a query parameter. This will store the literal slashes in your content. Since your image file is not text, it will add slashes before binary bytes that happen to be the same as ASCII character \ (0x5C). You don't need slashes anyway when using bound parameters, even if it's for text.

See https://www.php.net/manual/en/mysqli-stmt.send-long-data.php for the right way to store larger data content via bound parameter. This allows files that are larger than MySQL's max_allowed_packet buffer, because it sends the content in chunks.

$query = "INSERT INTO `produkt` (`id`, `date`, `img`)
          VALUES('', CURDATE(), ?)";

// prepare query 
$stmt = $conn->prepare($query);

//  bind  params
$null = NULL;
$stmt->bind_param("b", $null);
$fp = fopen($_FILES['img']['tmp_name'], "r");
while (!feof($fp)) {
    $stmt->send_long_data(0, fread($fp, 8192));
}
fclose($fp);

// execution of the prepared statement
$stmt->execute();

Are you asking whether it's better to store the image or the path? There is no universal rule about this. Many developers would say, "never store big images and other files in the database" because it makes the database larger.

But there are good reasons to do that sometimes, such as:

• If you delete a row, it will atomically delete the image stored on that row. Whereas if you store it outside the database, then you need to remember to delete any orphaned images when you delete a row in the database.

• Data in the database obeys transaction semantics. If you insert/update/delete a row that references an image, the change is isolated until you commit. You may also choose to rollback the transaction. You can't do that with files on the filesystem.

• A database backup will collect all the image data along with row data, and this will be atomic. If you keep image files outside the database, a data backup must be done in two steps, and you always worry if you got the images that are true for that database snapshot.

Storing and displaying images from MySQL with PHP

<?php

# getting the uploaded image and storing it
if ( isset($_FILES['image']['tmp_name']) ) {
    // open mysqli db connection
    $mysqli = new mysqli($mysqliHost,$mysqliUsername,$mysqliPassword,$mysqliDatabase);

    // get image data
    $binary = file_get_contents($_FILES['image']['tmp_name']);

    // get mime type
    $finfo = new finfo(FILEINFO_MIME);
    $type = $finfo->file($_FILES['image']['tmp_name']);
    $mime = substr($type, 0, strpos($type, ';'));

    $query = "INSERT INTO `images` 
                    (`data`,`mime`,`name`) 
    VALUES('".$mysqli->real_escape_string($binary)."',
            '".$mysqli->real_escape_string($mime)."',
            '".$mysqli->real_escape_string($_FILES['image']['name'])."')";
    $mysqli->query($query);
}

# viewing the uploaded image
if ( isset($_GET['imageName']) ) {
    // open mysqli db connection
    $mysqli = new mysqli($mysqliHost,$mysqliUsername,$mysqliPassword,$mysqliDatabase);

    // query for the image in the db
    $query = "SELECT `data`,`mime` FROM `images` WHERE `name`='".$mysqli->real_escape_string($_GET['imageName'])."'";
    $result = $mysql->query($query);

    if ( $result->num_rows ) {
        // grab the query result from the db select
        $assoc = $result->fetch_assoc();

        // let the client browser know what type of data you're sending
        header('Content-type: '.$assoc['mime']);

        // dump the binary data to the browser
        echo $assoc['data'];
        exit;
    } else {
        header('HTTP/1.1 404 Not Found');
        exit;
    }
}

?>

My script does not account for images with the same name, you can swap out the part where it says $_FILES['image']['name'] to another variable that has/creates a unique name for it, or use the inserted ID (PRIMARY AUTO_INCREMENT MySQL key).

Here is a sample table schema:

CREATE TABLE `images` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` longblob NOT NULL,
  `mime` varchar(50) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Record and Read an image file in the database . Using PHP and MYSQL

Here goes a very basic example:

MySQL

CREATE TABLE 'picture' (
  'id' INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  'image' LONGBLOB NOT NULL,
  PRIMARY KEY ('id')
)

PHP

$image = file_get_contents('myimage.jpg');
// your db connection code here..
mysql_query("INSERT INTO picture (image) VALUES ('$image');

Bare in mind storing images in the database is not a very good plan. It is better to keep the images in a directory and just save the path in the database.

---

Related Topics