Display image in HTML table from a mysql search

Question

I am trying to create a web page the shows competitors from past car rallys. This is the result so far when searching for "barry" as a firstname.

I would like to add a thumbnail image of the car. At the moment the image is stored in the database as blob. How do I code this to show the image.

Code so far. csvsearch.html

<html>
        
<body>

<form action="csvtable.php" method="post">
Search <input type="text" name="search"><br>

Column: <select name="column">
    <option value="DriverFirstName">Driver First</option>
    <option value="NavFirstName">Nav First</option>
    <option value="Car">Car</option>
    </select><br>
<input type ="submit">
</form>

</body>

</html>

csvtable.php

<?php

$search = $_POST['search'];
$column = $_POST['column'];

$servername = "localhost";
$username = "root";
$password = "";
$db = "csv_db";

$conn = new mysqli($servername, $username, $password, $db);

if ($conn->connect_error){
    die("Connection failed: ". $conn->connect_error);
}

$sql = "SELECT * FROM competitors WHERE $column like '%$search%'";

if($result = mysqli_query($conn, $sql)){
    if(mysqli_num_rows($result) > 0){

         echo "<table border='1' cellspacing='0' cellpadding='10'>";
            echo "<tr>";
                echo "<th>Driver</th>";
                echo "<th>Co-Driver</th>";
                echo "<th>Comp #</th>";
                echo "<th>Category</th>";               
                echo "<th>Car</th>";
                echo "<th>Event</th>";      
            echo "</tr>";
        while($row = mysqli_fetch_array($result)){
            echo "<tr>";
                echo "<td>" . $row['DriverFirstName'] .' '. $row['DriverLastName'] . "</td>";
                echo "<td>" . $row['NavFirstName'] . ' '. $row['NavLastName'] ."</td>";                 
                echo "<td align='center'>" . $row['CompNumber'] . "</td>";
                echo "<td>" . $row['Category'] . "</td>";
                echo "<td>" . $row['CarYear'] .' ' . $row['Car'] .' '. $row['Model'] ."</td>";
                echo "<td>" . $row['Event'] .' ' . $row['EventYear'] ."</td>";          
            echo "</tr>";
        }
        echo "</table>";
        // Close result set
        mysqli_free_result($result);
    } else{
        echo "No records matching your query were found.";
    }
} else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($conn);
}
 
// Close connection
$conn->close();
// code from 
?>

Any ideas please? .... even if I have to start all over again going down a different path.

Please do a bit of basic research, before you come asking here. This is not a place to come and just ask “how to”, and then expect us to give you a full-on tutorial. — CBroe, Aug 13 '20 at 10:12
_“even if Ihave to start all over again going down a different path”_ - stuffing binary image data into the database, is not a thing that makes much sense to begin with in most situations. Unless there is a very specific reason to do otherwise, large (binary) assets belong into the file system, only the file system _path_ which can be used to access them belongs into the database. — CBroe, Aug 13 '20 at 10:13
**Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Aug 13 '20 at 13:21

Goodies · Answer 1 · 2020-08-13T15:04:44.843

-1

Some tips..

For sending a binary as image you will have to send a response header with the proper mime type...

   <?php
     header("Content-type: " . image_type_to_mime_type(IMAGETYPE_PNG));
   ?>

Refer for the options https://www.php.net/manual/en/function.image-type-to-mime-type.php

You will need that mime type as a table field in your database..

Here is some php-code I used in the past to transfer blob images,

   <?php
       require_once "db.php";
       if(isset($_GET['image_id'])) {
        $sql = "SELECT imageType,imageData FROM output_images WHERE imageId=" . $_GET['image_id'];
        $result = mysqli_query($conn, $sql) or die("<b>Error:</b> Problem on Retrieving Image BLOB<br/>" . mysqli_error($conn));
        $row = mysqli_fetch_array($result);
        header("Content-type: " . $row["imageType"]);
        echo $row["imageData"];
       }
       mysqli_close($conn);
     ?>

The source I got this from was https://phppot.com/php/mysql-blob-using-php/

By the way.. have you considered NOT using a blob in the database and simply issue a link to that image, like and store your images server-side, in somepath\ on disk ?

edited Aug 13 '20 at 15:04

answered Aug 13 '20 at 15:03

Goodies

1,951
21
26

**Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) – Dharman Aug 13 '20 at 15:04
It is a very bad idea to use `die(mysqli_error($conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) – Dharman Aug 13 '20 at 15:04
@Dharman I have read your warning twice now.. but can you please provide some code using Php and MySQL that is not vulnerable for injection ? Also I'd like to see an example of SQL-injecting from this point, that could do something unintended. Keep in mind we are not looking at a database that allows syntax expanding the selection, or updating your database, starting at some position in a WHERE-condition, that is WHERE imageId = " . $_GET['image_id'] a substitution for "image id" that could do something nasty.. – Goodies Aug 13 '20 at 15:10
I am not sure I understand your comment, but if you want to learn how to include a PHP variable properly in SQL then I recommend reading https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement and tutorials on https://phpdelusions.net/pdo – Dharman Aug 13 '20 at 15:12
If you follow the proper way of binding parameters then it is very easy to write code that is safe from SQL injection. – Dharman Aug 13 '20 at 15:12

Display image in HTML table from a mysql search

1 Answers1