How to dispaly image from MySQL table to PHP page in table format ?

Question

I am working on image uploader in which the user uploads the image to the mysql database via a simple HTML/PHP form.

It works, and now I want to retrieve the saved images from MySQL in a HTML table format. But I am not successful. I am very much a novice in PHP. I have attached the currently used code... can someone please help me create a solution which displays the result as pretty looking table?

I also want to allow the user to upload multiple files/images at a time and store those in the database. How would that be possible?

Here is my code:

$query = "SELECT * FROM ae_gallery";
$result = mysql_query($query) or die('Error, query failed');
 if(mysql_num_rows($result) == 0)
 {
echo "Database is empty <br>";
 }
else
{
 echo "<center><b><h1><u>WV Images</u></h1></b></center><br>";
 echo "E_Name &ndash; Job_Num &ndash; Reason &ndash; Time &ndash;Image<br><hr>";
while(list($E_Name,$Job_num,$ext,$image_time,$data,$Remarks,)=mysql_fetch_array($result))
  {
   echo "
   {$E_Name}
    &ndash;
   {$Job_num}
    &ndash;
    {$Model}
    &ndash;
    {$Reason}
    &ndash;
    {$image_time}
    &ndash;
    <a href='download.php?id={$id}'><img src='http://pagename/wv-images   /download.php?id={$id}' width='80' height='80' alt='{$title}' border='0'></a> 
   <br><hr>" ;
    }
    }
   ?>
  </body>
  </html>

Answer 1

Normally what you'll do is upload the image to the filesystem, typically under a filename like 123.jpeg, where 123 is the primary key of the database row associated with the uploaded file. (Don't trust the user's submitted filename to store the file under.)

You can upload images into a BLOB column in the database itself, and then use a download.php script to retrieve and spit it out:

<?php
    $result= mysql_query('SELECT imagedata FROM ae_gallery WHERE id='.intval($_GET['id']))
    if (mysql_num_rows($result)==0) {
        header('Status: 404 Not Found');
        exit();
    }
    $row= mysql_fetch_assoc($result);
    header('Content-Type: image/jpeg');
    echo $row['imagedata'];
// no PHP end-tag to avoid accidental whitespace being included in the output

However doing it this way means you'll get no caching by default. You have to add a load of quite complicated caching header handling to stop it re-fetching the image each time. The web server is in general a more efficient way of serving static data.

For the page you quoted, you'll really want to display in a <table>. You will also need to use htmlspecialchars() every time you echo a text value into HTML, otherwise you're going to have HTML-injection issues and potential cross-site-scripting security holes.

<?php
    function h($s) {
        echo htmlspecialchars($s);
    }

    $result= mysql_query('SELECT * FROM ae_gallery') or die('Error, query failed');
?>

<?php if (mysql_num_rows($result)==0) { ?>
    Database is empty <br/>
<?php } else { ?>
    <table>
        <tr>
            <th>E_Name</th>
            <th>Job_Num</th>
            <th>Model</th>
            <th>Reason</th>
            <th>Time</th>
            <th>Image</th>
        </tr>
        <?php while ($row= mysql_fetch_assoc($result)) { ?>
            <tr>
                <td><?php h($row['E_Name']); ?></td>
                <td><?php h($row['Job_Num']); ?></td>
                <td><?php h($row['Model']); ?></td>
                <td><?php h($row['Reason']); ?></td>
                <td><?php h($row['image_time']); ?></td>
                <td>
                    <a href="/uploaded-images/<?php h($row['id']); ?>.jpeg">
                        <img src="/uploaded-images/<?php h($row['id']); ?>.jpeg" alt="<?php h($row['title']); ?>"/>
                    </a>
                </td>
            </tr>
        <?php } ?>
    </table>
<?php  } ?>

Notes:

• I've defined a function with a short name to avoid having to type out echo htmlspecialchars() so much.

• I've assumed that id and title are columns in the table. (They don't seem to come from anywhere in the example code.)

• I've assumed that the variable names you used are the column names in the table, so used the same names to access $row. It's better to access the results of a query through named columns rather than relying on their order (which may change if you start altering the schema).