Displaying Images

darren_plehn

What code do you need to allow pictures to come up in a query? This tutorial works perfect, but I need a modification so that I can use a select statement and have all the pictures show.
http://www.phpbuilder.com/columns/florian19991014.php3

johanafm

Things I don't like about that tutorial

A sample php3 script (...)

Not having looked closely at it, I've no idea if it would work or not, but the current PHP version is 5.3, and 6 is in the making.

bin_data LONGBLOB,

This would store the actual image in the database, whereas I prefer to store a filename in the db and keep the data as files.

@MYSQL_CONNECT("localhost","root","password");

@ is the error supression operator. Bad and nasty.
Use of uppercase in function names. Well, it does work since PHP is case insensitive when it comes to functions (but not variables). I'd stick to lowercase for PHP's built in functions (as they're written in the documentation at www.php.net, and any other function the same way it is declared. For member methods that is often camelCasingForMemberMethods().

MYSQL_QUERY(

Use mysqli (i stands for improved).

If you keep the files on disk instead of in the db, all you need is

// probably want to rename the table from bin_data to image since it no longer contains binary data.
$result = $mysqli->query("SELECT filename from bin_data");

// output doctype, head and body tags here

if ($result) (
	while ($row = $result->fetch_assoc())
		echo '<img src="/path/to/images/' . $row['filename'] .'" alt="" />';
}

Else you need to instead first select the id, and then make your image tags to look like

echo '<img src="getImage.php?id=' . $row['id'] . '" alt="" />';

And then you'd need a new script, getImage.php to access the database again, not just once, but once per image (the browser sends one request per image). And that script could look like the one in the tutorial. You'd just need to use $_GET['id'] in your sql where clause.

darren_plehn

I'm starting to get a clear picture on why it's not effecient to keep images in a database. Especially doing it this way. I'm going to give the file path method a try. Thanks for your examples!

darren_plehn

I took your advice about not putting images directly in the database (thank you). I have been able to create a script that uploads the picture into a folder called "interiors" in the root directory. The script also puts the file name into the database under the field "path". I'm have trouble retrieving the image in a query though. Can you help me modify the script below so that the image will show up within the query table?

<html>

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

<body>

<?php

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

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

mysql_select_db("caseclu_foaminterior", $con); //Replace with your MySQL DB Name

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';
}

$result = mysql_query($query) or die('Error, query failed');
if(mysql_num_rows($result) == 0)
{
    echo "Database is empty <br>";
} 


$fieldCount = mysql_num_fields($result);

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($result);
    echo "<th>{$field->name}</th>";
}
echo "</tr>\n";

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

mysql_close($con);

?>


</body>

</html>

I know it has something to do with

<?php
$con = mysql_connect('root','username','password'); //Replace with your actual MySQL DB Username and Password
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("caseclu_foaminterior", $con); //Replace with your MySQL DB Name


$query  = "SELECT path, id, size FROM interiors";

$result = mysql_query($query) or die('Error, query failed');
if(mysql_num_rows($result) == 0)
{
    echo "Database is empty <br>";
} 
else
{
    while(list($path) = mysql_fetch_array($result))
    {
?>

<img src="<?=$path;?>">
<?php        

    }
}

?>

Because I can use this code to view the pictures (they just appear 1 after the other though)

johanafm

In the first code you

echo "<td>$cell</td>";

In the second

<img src="<?=$path;?>">

You will need something like

foreach($record as $field => $cell) {
	if ($field == 'path')
		echo "<td><img src="$cell"></td>";
	else
		echo "<td>$cell</td>";
}

Also this part of your code is unsafe[code=php]
$myvar = $_POST['formVar'];
$myvar2 = $_POST['formVar2'];
$myvar3 = $_POST['formVar3'];

Instead

// assuming you need a string value - requires a connection link, so connect to db first
$myvar = mysql_real_escape_string($_POST['formVar']);

// assuming you need an integer
$myvar2 = intval($_POST['formVar2']);

And when you perform queries always follow them by

if ($result == false) {
	// error handling
}
else {
	/* without the above if check, $result may be boolean false instead of result set resource
	   which means that this would fail */
	while ($row = mysql_fetch_array($result))
}

darren_plehn

Thanks for the input. I'll take care of the security issues once I get the query working.
For the pictures I used the code you suggested, but am still having issues. After analyzing the code it seems like it should work perfectly, but I don't know why its not. Basically it's trying to display the pictures in the "id" field rather than the "path" field. I have a feeling if it was to display the pictures in the right field my problems would be solved.

<html>

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

<body>
<?php

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

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

mysql_select_db("caseclu_foaminterior", $con); //Replace with your MySQL DB Name

if(isset($_POST["q"]))
  $selection = $_POST["q"];
else
  $selection = null;
switch ($selection)
{
case "":
  $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 $field => $cell) {
                if ($field == 'path') { 
                        echo "<td><img src='http://www.caseclub.com/<?=$cell;?>>'</td>"; 
                }
                else { 
                        echo "<td>$cell</td>";
                }
        }
}		
    echo "</tr>";
}
echo "</table>";

mysql_close($con);

?>


</body>

</html>

I attached a screen capture of what the query looks like. Thanks again for all your help!!!!

darren_plehn

Rather than trying to figure out why the images keep trying to show up in the first column and not the "path" column, I just switched the "path" column to the first. Problem solved.

johanafm

The row array is ordered in the same way as the fields are retrieved from the db. If you specify "SELECT id, path" then id is first, path second. If you "SELECT *", then first is whatever is first in your table etc.

darren_plehn

Thank you so much for your help. Everything is working perfectly now.

darren_plehn

Problem Solved