PHP MySQL Showing image from Database with the right id

Question

Im trying to make a blog system for school. Ive almost got everything working, the only thing i need to let work is showing the images from my database with the right post. Right now i only get the image_name to show up instead of the image.

This is what the database table of the image looks like: image table

And here the blog post table:

Here is a picture of the row i got in the table post, the thing i want to accomplish is to have the image with the right image_id to be with the post with the accompanying post. post table row

The php code:

<?php
//get the posts from the database
    $query = $db->prepare("SELECT post_id, title, LEFT(body, 150) AS body, category, name FROM posts 
                            INNER JOIN categories ON categories.category_id=posts.category_id
                            INNER JOIN `blob` ON blob.image_id=posts.image_id
                            order by post_id DESC limit $start, $per_page");
    $query->execute();
    //make variabels
    $query->bind_result($post_id, $title, $body, $category, $image);

    ?>

Here the other part of the code where the image should come with the right post_id

<?php
                while($query->fetch()): 
                $lastspace = strrpos($body, ' ');
            ?>
            <article>
                <div class="image_small"><?php echo $image ?></div>
                    <div class="text_small"><h3><?php echo $title?></h3><br>
                    <p><?php echo substr($body, 0, $lastspace)?></p>
                    </div>
                <div class="vak"><h4>Vak: <?php echo $category?></h4></div>
                <div class="more_button"><?php echo "<a href='post.php?id=$post_id'><img src='images/more-button.png'  width='70' height='30' border='0'></a>"?></div>

            </article>

            <br><br>
            <?php endwhile;?>

in your query, you ask for `name` .... `SELECT post_id, title, LEFT(body, 150) AS body, category, name` if you change it to `image` do you get your blob? — RightClick, Jun 11 '15 at 15:43
now i get the scripted code of the image, like 100 lines of weird symbols and not the image itself. Do you know how i can solve this? thanks in advance! — S. Mateijsen, Jun 11 '15 at 15:49

RightClick · Accepted Answer · 2015-06-11T15:51:47.003

3

You are getting the image name instead of the image because you are querying the image name. Change your query and I think the rest will work.

$query = $db->prepare("SELECT post_id, title, LEFT(body, 150) AS body, category, image FROM posts 
                            INNER JOIN categories ON categories.category_id=posts.category_id
                            INNER JOIN `blob` ON blob.image_id=posts.image_id
                            order by post_id DESC limit $start, $per_page");

There are a couple ways to use that blob to show the image. I like this method...

echo '<img src="data:image/jpeg;base64,'.base64_encode( $image ).'"/>';

edited Jun 11 '15 at 15:51

answered Jun 11 '15 at 15:45

RightClick

1,090
7
12

now i get the scripted code of the image, like 100 lines of weird symbols and not the image itself. Do you know how i can solve this? thanks in advance! – S. Mateijsen Jun 11 '15 at 15:48
it is best to not store the images themselves in the database (for chance of encoding corruption such as it seems you are experiencing). Instead, save the image to the disk, and store the path to the image in your database. – Zerp Jun 11 '15 at 15:52
Base64 encoding is an option (as RightClick's edit shows) but be wary that it will ad an overhead ~25% depending to the size of your images. – Zerp Jun 11 '15 at 15:53
@Zerp, I don't think they have a corruption issue, they were just echoing out the raw blob on the page. I updated my answer to show how to do it. Your approach is also valid, but I've used both with success, I think this is a fine way to deal with images. – RightClick Jun 11 '15 at 15:54
WOW! thank you so much mate, it works perfect now! You made my day mate! – S. Mateijsen Jun 11 '15 at 15:56
awesome, that kind of makes my day to hear that! mark it answered please. – RightClick Jun 11 '15 at 15:57
There is not a strictly correct or incorrect way to keep images associated with your database, but this is a debate that has been around for quite a while, and most people agree that storing the image directly into the database is an unfavorable solution. http://stackoverflow.com/questions/3748/storing-images-in-db-yea-or-nay http://www.revsys.com/blog/2012/may/01/three-things-you-should-never-put-your-database/ http://stackoverflow.com/questions/815626/to-do-or-not-to-do-store-images-in-a-database @RightClick – Zerp Jun 11 '15 at 16:00
Everyone is free to choose the solution that works best for them though – Zerp Jun 11 '15 at 16:01
Zerp, thanks for those links, very informative. It does seem like there are only a few situations where db storage is preferred. Access control or multi-server environments seem to be the major ones. Or for ease of backup. You're right, probably 95% of the time it should just be saved to disk. – RightClick Jun 11 '15 at 16:06

PHP MySQL Showing image from Database with the right id

1 Answers1