Retrieving Images from Database via Query

darren_plehn

Hey all, I'm new to php and have a question. Any help would be appreciated. I am able to get images into my database using the help from http://www.phpbuilder.com/columns/flori ... hp3?page=1 but I'm having trouble implementing the retrieval of the image in my script below.
<html>

<head>
<title>Custom Foam Interior Templates</title>
</head>

<body>

<?php

$myvar = $POST['formVar'];
$myvar2 = $POST['formVar2'];
$myvar3 = $_POST['formVar3'];

$con = mysql_connect('host','username','password');
if (!$con){
die('Could not connect: ' . mysql_error());
}

mysql_select_db("caseclu_foaminterior", $con);

if(isset($POST["q"]))
$selection = $POST["q"];
else
$selection = null;
switch ($selection)
{
case "all":
$query = "SELECT FROM interiors";
break;
case "id":
$query = "SELECT FROM interiors WHERE id like '%$myvar3%'";
break;
case "custom":
$query = $_POST["cq"];
break;
case "category":
$query = "SELECT FROM interiors WHERE category='$myvar2'";
break;
case "search":
$query = "SELECT FROM interiors WHERE description like '%$myvar%'";
break;
default:
echo 'Nothing to do here';
}

$q = mysql_query($query)
or die("This is not a valid query: " . $query);

$fieldCount = mysql_num_fields($q);

echo "You selected the query <i>$query</i>.<br />
Here are your results: <br /><br />
<table border='1'>
<tr>";

// print table headers
for($i=0; $i<$fieldCount; $i++)
{
$field = mysql_fetch_field($q);
echo "<th>{$field->name}</th>";
}
echo "</tr>\n";

// Print table rows
while($record = mysql_fetch_row($q))
{
echo "<tr>";
foreach($record as $cell)
echo "<td>$cell</td>";
echo "</tr>";
}
echo "</table>";

mysql_close($con);

</body>

</html>

Does anyone know how to display the image field with the actual images rather than all the random characters. I am able to follow Florian Dittmer's tutorial http://www.phpbuilder.com/columns/flori ... hp3?page=2 on the next page to view the image, but no luck implementing it into my pre-exsisting queries. Any help is appreciated!

johanafm

assuming there is a field called image in interiors and that it contains path to image from web root

echo '<img src="/'.$record['image'].'" alt=""/>;

darren_plehn

There is a field called image in interiors, but is this solution for if the picture stored in there is just the file path to the picture? Because in my situation there isn't a file path stored in images. It's the actual image made up of a bunch of random characters. Here is the script that gets the picture into the table.

<html>
<head><title>Store binary data into SQL Database</title></head>
<body>

<?php

if ($submit) {

// connect to the database
// (you may have to adjust the hostname,username or password)

MYSQL_CONNECT("hostname","username","password");
mysql_select_db("caseclu_foaminterior");

$data = addslashes(fread(fopen($form_data, "r"), filesize($form_data)));

$result=MYSQL_QUERY("INSERT INTO interiors (id,category,description,image,filename,filesize,filetype) ".
    "VALUES ('$id','$category','$description','$data','$form_data_name','$form_data_size','$form_data_type')");

$id= mysql_insert_id();
print "<p>This file has the following Database ID: <b>$id</b>";

MYSQL_CLOSE();

} else {

// else show the form to submit new data:

?>
<form method="post" action="<?php echo $PHP_SELF; ?>" enctype="multipart/form-data">

<TABLE>

<TR>
<TD><font size="2" face="Verdana, Arial, Helvetica, sans-serif">ID:</font></TD>
<TD> <input type=text name="id"></TD>
</TR>
<TR>
<TD><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Category:</font></TD>
<TD><input type=text name="category"></TD>
</TR>
<TR>

<TR>
<TD><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Description:</font></TD>
<TD><input type=text name="description" size="27"></TD>
</TR>
<TR>
<TR>
<TD><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Image:</font></TD>
<TD><input type="hidden" name="MAX_FILE_SIZE" value="1000000">
<input type="file" name="form_data" size="40"></TD>
</TR>
<TR>
<TD><input type="submit" value="submit" name="submit"></TD>
<TD><input type="reset" value="Reset" name="Reset"></TD>
</TR>
</TABLE>
</form>

<?php

}

</body>
</html>

(I do have a script that can simply view the image) Let me know if that would help in modifying to what I need.

johanafm

darren_plehn;10930156 wrote:
(I do have a script that can simply view the image) Let me know if that would help in modifying to what I need.

Does that script handle $_GET to retrieve a single image?

<img src="scriptToShowImage.php?id=12" alt="" />

So all you need to do in step one is retrieve the ids for the images you want to show. Then you put the ids in the query string so your script knows what image to show.

The problem with this approach is that your web server gets called once for each and every image on your page. This means your script is called once per image, and furthermore that you create one db connection per image (costly) and perform not one query to show all images, but actually one query per image plus the initial query.

darren_plehn

I appreciate the help, but also appreciate the explanation on how costly this approach can be. I'm going to play around with it a little bit and consider keeping the images outside the database. thanks!