fetch images from multiple mysql tables using php

Question

I've been trying for the last couple of days to try and understand how to fetch data from multiple tables, but I cant say that I fully understood how it works. Anyway :)

What I am trying to do is to fetch the images stored in multiple tables using a search bar to define the parameters i.e artist name (I know that storing an image in a database could cause many problems but its an exercise for the university so I cant really do much about it)

So to be specific now. I have 5 tables called animaldata , cardata , landscapedata , peopledata and otherdata. Each of the table has the following fields : 1 id 2 artist_name 3 details 4 photo 5 phototype and 6 date. Where artist_name is the name of logged person who uploads the image and details is the caption , photo is the blob item to be saved and phototype is the .. well type of the image :P

So what I want to do is when someone uses the search bar to search for an artist the code to fetch and display all the images from all the tables the artist has uploaded.

Here's the code that I use to fetch the data from 1 table but I don't know how to alter it in order to search the other tables as well.

That is the search.php

<?php
mysql_connect ("localhost","root","")  or die (mysql_error());
mysql_select_db ("photo_album_db");

$term = $_POST['term'];

$sql = mysql_query("select * from animaldata where artist_name like '%$term%'");

while ($row = mysql_fetch_array($sql)){
    echo $row['details'];
echo "</br>";
echo "<img src=getan.php?id=".$row['id']." width=250 height=200/>";
echo "</br>";
}
?>

The Form of the search bar is the following : <form action="search.php" method="post"><input type="text" name="term" /><input type="submit" name="search" value="Search" /></form>

Thanks in advance for any suggestion , if you need more "clues" let me know :) :) :)

**Your code is vulnerable to SQL injection.** You *really* should be using prepared statements, into which you pass your variables as parameters that do not get evaluated for SQL. If you don't know what I'm talking about, or how to fix it, read the story of [Bobby Tables](http://bobby-tables.com). — eggyal, May 20 '12 at 10:15
If his professors try an SQL inject on him, I swear I'll fly up there and hi-five those bad-asses! — Mihai Stancu, May 20 '12 at 10:23

Mihai Stancu · Answer 1 · 2012-05-21T12:49:41.663

2

For changing the table if they have the same columns then use:

$sql = mysql_query("select animaldata.artist_name AS animaldata_artist_name, cardata.artist_name AS cardata_artist_name /* do this for all columns in all tables, or mor specifically for the columns you need  */ from animaldata AS a, cardata AS c, landscapedata AS l, peopledata AS p where a.artist_name like '%$term%' OR c.artist_name like '%$term%' OR l.artist_name like '%$term%' OR p.artist_name like '%$term%'");

Try this for displaying the images:

<img src="data:image/jpeg;base64,".base64_encode($row['image_blob'])." width...

This will not make a separate request for the photo. It will embed the contents of the image into the HTML.

The technique is supported by the data:uri protocol.

edited May 21 '12 at 12:49

answered May 20 '12 at 10:19

Mihai Stancu

15,848
2
33
51

thanks , Ive changed those things but when i search for the artist name,just a blank page appears with nothing in it (but no errors as well) – ekptwtos May 20 '12 at 11:15
if you're running linux try "php -l /path/to/file.php" to check for syntax/parse errors. – Mihai Stancu May 20 '12 at 11:22
Syntax/parse errors are not shown if the file that you are directly visiting (http://example.com/page.php) has them. They are only shown if the file is included. – Mihai Stancu May 20 '12 at 11:23
I do not currently have linux on my computer. Do you think that I should make a separate php page to fetch the data and images out of the DB instead of the – ekptwtos May 21 '12 at 09:57
ok , it works now.. BUT there seems to be another problem.. lets say i search for 'ekptwtos' and there are 3 images in animals and 2 on people , it only shows the 2 photos of the last table (in this case people) – ekptwtos May 21 '12 at 10:11
Multiple table select queries act the same way as JOIN queries do, you should expand the * selector, naming each column with an alias. – Mihai Stancu May 21 '12 at 12:47
Since you're doing this for school I don't think you should make a separate page for fetching images unless you have some special requirements you need to address regarding the images. It is a performance fault but so is storing the images in the database in the first place. – Mihai Stancu May 21 '12 at 12:51
I did this but then I get this Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in c:// blah blah . It is pointing at the while loop. – ekptwtos May 21 '12 at 14:36
when resource is expected and boolean is given it mean that the output of mysql_query is **false** as in "parsing the query failed", before you use the response of a mysql_query you should check if it's not false and output the result of mysql_error(). – Mihai Stancu May 21 '12 at 21:42
The previous comment means that the script has a syntax error. Normally I would test code before writing it in an answer. But since you're it's *for homework* and you're in a learning process i think it's best you find the cause. – Mihai Stancu May 21 '12 at 21:50

score 2 · Accepted Answer · answered May 21 '12 at 13:19

2

Try this:

// set up database connection ... 

$term = my_awesome_sanitizer($_POST['term']);

$tables = array("animaldata", "cardata", "landscapedata", "peopledata", "otherdata");
foreach ($tables as $table)
{
    $sql = mysql_query("select * from $table where artist_name like '%$term%'");
    while ($row = mysql_fetch_array($sql))
    {
        echo $row['details'];
        // show stuff ....
    }
}

answered May 21 '12 at 13:19

Jannie Theunissen

28,256
21
100
127

this makes 5 individual queries to the database, at least **union join** all the queries so that they would be sent all at once. – Mihai Stancu May 21 '12 at 21:47
1

@Mihai Valid point, but the OP is trying to understand the basics and this solution is easier to understand and maintain at a small performance penalty. – Jannie Theunissen May 22 '12 at 07:15

fetch images from multiple mysql tables using php

2 Answers2