How to upload images in MySQL using PHP PDO ?

In this article, we will upload images to the MySQL database using PHP PDO and display them on the webpage. 

Approach: Make sure you have a XAMPP server or WAMP server installed on your machine. In this tutorial, we will be using the WAMP server.

Article content:

1. Table Structure
2. Database configuration using PDO.
3. HTML & PHP Files
4. Working Procedure
5. Conclusion

To upload images to the MySQL database using PDO-PHP and display them on the webpage, follow the steps given below:

1. Create Database: Create a database using PHPMyAdmin, the database is named “geeksforgeeks” here. You can give any name to your database. You can also use your existing database or create a new one.

create database “geeksforgeeks”

2. Create Table: We now create a table named ‘images‘. The table contains three fields:

• id – int(11) – primary key – auto increment
• name – varchar(100)
• image – varchar(255)

Your table structure should look like this:

create table “images”

Or copy and paste the following code into the SQL panel of your PHPMyAdmin.

CREATE TABLE `images`(
    `id` int(11) NOT NULL AUTO_INCREMENT, 
     `name` varchar(100) NOT NULL,
     `image` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

To do this from the SQL panel refer to the following screenshot:

create a table from SQL panel

3. Creating folders and files:

We will now create a folder named “uploads“. The files uploaded by the client on the server will be stored in this folder. Create index.php, database_connection.php and view.php files. Keep your main project folder (for example.. GeeksforGeeks) in the “C://xampp/htdocs/”, if you are using XAMPP or “C://wamp64/www/” folder if you are using the WAMP server respectively. The folder structure should look like this:

folder structure

Database configuration using PHP PDO:

• database_connection.php: Create a “database_connection.php“ file for the database connection.

PHP

<?php

$server = "localhost";
$username = "root";
$password = "";
$dbname = "geeksforgeeks";

try {
    $conn = new PDO(
        "mysql:host=$server; dbname=$dbname",
        "$username", "$password"
    );

    $conn->setAttribute(
        PDO::ATTR_ERRMODE,
        PDO::ERRMODE_EXCEPTION
    );
}
catch(PDOException $e) {
    die('Unable to connect with the database');
}

?>

index.php: Below is the PHP source code for uploading the images using HTML form. Let us first understand the PHP part.

In the below code, the first block verifies that the ‘submit’ button from the form has been clicked using the PHP isset() function. And the second if block verifies that the image file exists with a valid extension. $_FILES is a two-dimensional superglobal associative array of items that are being uploaded via the HTTP POST method. The move_uploaded_file() function is used to upload the pdf file to the server. We are passing 2 values, the temporary file name and the folder where the file will be stored. The files will be stored in the “GeeksForGeeks/uploads/ ” folder which we created earlier.

In the HTML <form> tag, we are using “enctype=’multipart/form-data” which is an encoding type that allows files to be sent through a POST method. Without this encoding, the files cannot be sent through the POST method. We must use this enctype if you want to allow users to upload a file through a form. The multiple attributes in the input file element are used to enable selecting multiple files.

PHP

<?php
include "database_connection.php";

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

    // Count total files
    $countfiles = count($_FILES['files']['name']);

    // Prepared statement
    $query = "INSERT INTO images (name,image) VALUES(?,?)";

    $statement = $conn->prepare($query);

    // Loop all files
    for($i = 0; $i < $countfiles; $i++) {

        // File name
        $filename = $_FILES['files']['name'][$i];

        // Location
        $target_file = './uploads/'.$filename;

        // file extension
        $file_extension = pathinfo(
            $target_file, PATHINFO_EXTENSION);

        $file_extension = strtolower($file_extension);

        // Valid image extension
        $valid_extension = array("png","jpeg","jpg");

        if(in_array($file_extension, $valid_extension)) {

            // Upload file
            if(move_uploaded_file(
                $_FILES['files']['tmp_name'][$i],
                $target_file)
            ) {

                // Execute query
                $statement->execute(
                    array($filename,$target_file));
            }
        }
    }

    echo "File upload successfully";
}
?>

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content=
        "width=device-width, initial-scale=1.0">
    <title>Geeks for geeks Image Upload</title>
</head>

<body>
    <h1>Upload Images</h1>

    <form method='post' action=''
        enctype='multipart/form-data'>
        <input type='file' name='files[]' multiple />
        <input type='submit' value='Submit' name='submit' />
    </form>

    <a href="view.php">|View Uploads|</a>
</body>

</html>

view.php: Code to display the uploaded images.

PHP

<?php
include "database_connection.php";
?>

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content=
    "width=device-width, initial-scale=1.0">
</head>

<body>
    <?php

    $stmt = $conn->prepare('select * from images');
    $stmt->execute();
    $imagelist = $stmt->fetchAll();

    foreach($imagelist as $image) {
    ?>

    <img src="<?=$image['image']?>"
        title="<?=$image['name'] ?>"
        width='200' height='200'>
    <?php
    }
    ?>
</body>

</html>

4. Working Procedure:

1. Run the local server or any server and redirect to your index.php page.

2. Select your image file to upload and click on submit button to upload the image to the database.

3. Click on the view upload link to check the uploaded file.

5. Conclusion: You can add CSS and change HTML as per the application requirement. In the above, the working of this upload image feature in PHP is implemented.

PHP is a server-side scripting language designed specifically for web development. You can learn PHP from the ground up by following this PHP Tutorial and PHP Examples.