PHP Bulk Image Download from MySQL Table

Question

I'm trying to download all the image files (.jpg format) stored as mediumblobs in a MySQL table.

The following PHP code allows the individual images to be displayed and saved individually without a problem.

blob2img.php

<td>
<form method="post" action="download_img.php">
<input type='submit' name='submit' <?php $imgCode = $row['IMAGEID']; echo "value='View imgId: " . $imgId . "'"; ?> />
<input type='hidden' name='imgId' <?php echo "value='" . $imgId . "'"; ?> />
</form>
</td>

download_img.php

<?php
    $img_id = $_REQUEST['imgId'];
    require_once "db3.php";
    if(isset($img_id)) {
        $myCode = $img_id;
        $sql = "select * from images where IMAGEID='" . $myCode . "'";

        if (!$result=mysqli_query($con, $sql)){
        echo mysqli_error($con);
        } else {
            $row = mysqli_fetch_array($result);
        header('Cache-Control: no-cache');
        header('Accept-Ranges: bytes');
        header('Content-Type: image/jpeg');
                header('Content-Transfer-Encoding: binary');
        header('Content-Disposition: attachment; filename="'. basename($row["IMAGEID"]) . '_' . basename($row["LOCATION"]) . '"');
            echo $row["IMAGEDATA"];
        }
    }
?>

The issue is that there are thousands of images stored in the table and it would far more preferable to be able to download the whole lot in one go. When the download_img.php file is dispensed with and the above ... block in the blob2img.php file is replaced with the following code:

<?php
    require_once 'db3.php';
    $result = mysqli_query($con,"SELECT IMAGEID, LOCATION FROM images");
    if (mysqli_num_rows($result) > 0) {
?>
<table class='table table-bordered table-striped'>
    <tr>
        <td>ImageId</td>
        <td>Location</td>
        <td>ImageData</td>
    </tr>
    <?php
        $i=0;
        while($row = mysqli_fetch_array($result)) {
    ?>
    <tr>
        <td><?php echo $row["IMAGEID"]; ?></td>
        <td><?php echo $row["LOCATION"]; ?></td>
        <td>
            <?php
                header('Content-Type: text/html; charset=utf-8');
                if (!file_exists($file)) {
                    $basedir = '/home/user/Documents/retrieved-images/';
                    $id = $row["IMAGEID"];
                    $loc = $row["LOCATION"];
                    $data = $row["IMAGEDATA"];
                    $file = $basedir . $id . "_" . $loc;
                    $fp=fopen($file,"wb");
                        echo "Opening File: $file<br>";
                        fwrite($fp,$data);
                        echo "Writing File: $file<br>";
                        fclose($fp);
                        echo "Closing File: $file<br>";
                        sleep(1);
                } else {
                        echo "Skipping File: $file<br>";
                }
            ?>
        </td>
    </tr>
    <?php
        $i++;
        }
    ?>
</table>

... the table is loaded row by row and the file downloads appear to be processed as all the opening file, writing file and closing file messages are returned as expected, however none of the image files get written to the target directory. It cannot be a permissions issue as user has full read/write priveleges for the the target directory specified for $basedir.

If the following line of code:

    $result = mysqli_query($con,"SELECT IMAGEID, LOCATION FROM images");

is replaced with:

    $result = mysqli_query($con,"SELECT IMAGEID, LOCATION, IMAGEDATA FROM images");

only the page title is returned. For some reason if IMAGEDATA is included in the select process at that stage it prevents the table from being loaded.

If the following lines of code:

                    $id = $row["IMAGEID"];
                    $loc = $row["LOCATION"];
                    $data = $row["IMAGEDATA"];

are replaced with:

                    $sql = "select * from images where IMAGEID='" . $row["IMAGEID"] . "'";
                    $id = $sql["IMAGEID"];
                    $loc = $sql["LOCATION"];
                    $data = $sql["IMAGEDATA"];

data is returned in a random order and $sql["LOCATION"] returns the actual image file name, but again does not result in any image files being saved to the target directory.

Further to siride's and Robert's initial feedback I've tried replacing the following lines of code:

                if (!file_exists($file)) {
                    $basedir = '/home/user/Documents/retrieved-images/';
                    $id = $row["IMAGEID"];
                    $loc = $row["LOCATION"];
                    $data = $row["IMAGEDATA"];
                    $file = $basedir . $id . "_" . $loc;

with:

                    $basedir = '/tmp/retrieved-images/';
                    $id = $row["IMAGEID"];
                    $loc = $row["LOCATION"];
                    $data = "select IMAGEDATA from images where IMAGEID='" . $id . "'";
                    $file = $basedir . $id . "_" . $loc;
                    if (!file_exists($file)) {

... but that change has made no difference either.

$basedir = '/tmp/'; to /tmp/ actually results in the "Skipping file: ..." message whereas $basedir = '/tmp/retrieved-images/'; does not.

Any assistance would be greatly appreciated.

Answer 1

The issue, as pointed out by Robert and Rick James, was a memory limit issue. Robert's reference to mysqli_use_result and link pointed me in the right direction to resolve the issue.

Below is the code that allowed all the images to be downloaded from the database in one go:

<table class='table table-bordered table-striped'>
    <tr>
        <td>ImageId</td>
        <td>Location</td>
        <td>ImageData</td>
    </tr>
    <?php
        require_once 'db3.php';
        mysqli_real_query($con, 'SELECT * FROM images');
        $result = mysqli_use_result($con);
        header('Content-Type: text/html; charset=utf-8');
        while ($row = mysqli_fetch_row($result)) {
    ?>
    <tr>
        <td><?php echo $row[0]; ?></td> 
        <td><?php echo $row[5]; ?></td>
        <td>
            <?php
                if (!file_exists($file)) {
                    $basedir = '/tmp/';
                    $id = $row[0];          // 0 here and above refers to the location where the IMAGEID data is stored in the returned row array.
                    $loc = $row[5];         // 5 here and above refers to the location where the LOCATION data is stored in the returned row array.
                    $data = $row[9];        // 9 refers to the location where the IMAGEDATA data is stored in the returned row array.
                    $file = $basedir . $id . "_" . $loc;
                    $fp=fopen($file,"wb");
                        echo "Opening File: $file<br>";
                        fwrite($fp,$data);
                        echo "Writing File: $file<br>";
                        fclose($fp);
                        echo "Closing File: $file<br>";
                        sleep(1);
                } else {
                        echo "Skipping File: $file<br>";
                }
            ?>
        </td>
    </tr>
    <?php
        }
        mysqli_free_result($result);
    ?>
</table>

Many thanks again to everyone for their input.