MySQL link, PLEASE help!?!?

[Will Clifton]

After googling and reading my eyeballs out I am no closer to a solution, I
hope someone will be kind enough to help.

I have a simple MySQL inventory db where the relevant fields are ID (pri,
auto-inc), image (med-blob), description and price. All I want to be able
to do is to store a link to the image in the image field, and have that
image be shown on the returning page. I just can't seem to get it to work
right. It is a 2000 row db, and each item has an image.

Relevant code on the inventory page (I'm sorry if it wraps funny):

$result = @mysql_query("SELECT ID, image, description, price FROM table");

echo ("<table border='0' width='90%' align=center>");

while ( $row = mysql_fetch_array($result, MYSQL_ASSOC) ) {

echo("<tr><td width='10%'><a
href=\"showPhoto.php?ID={$row[ID]}\">Photo</a> </td>" .

"<td width='70%'>" . $row[description] . "</td>" .
"<td width='20%'align=right>" . $row[price] . "</td></tr> ");
}
echo "</table>"; }

---Everything displays correctly and when you hover over the photo link
you see the URL "showPhoto.php?ID=1"

In the returning (showPhoto) page, I have the following:

//connect stuff
$result = @mysql_query("SELECT image FROM table"); while ( $row =
mysql_fetch_array($result, MYSQL_ASSOC) ) {

echo $row["image"]; }

But on the return (showPhoto) page I get all 2000 photos loading! I don't
know how to tell the array that I only want to return the photo pertaining
to that row's ID. I have tried MANY other alternatives but I either get no
data on the return page or all 2000 photos.

The entry in the image field of the db is <img src="/path/image_name.jpg">

Will some kind soul PLEASE look at this code and tell me how to do this? I
will do your dishes for life!

Thank you,

Will

[Sundial Services]

Will Clifton wrote:

In the returning (showPhoto) page, I have the following:

//connect stuff
$result = @mysql_query("SELECT image FROM table"); while ( $row =
mysql_fetch_array($result, MYSQL_ASSOC) ) {
echo $row["image"]; }

But on the return (showPhoto) page I get all 2000 photos loading! I don't
know how to tell the array that I only want to return the photo pertaining
to that row's ID. I have tried MANY other alternatives but I either get no
data on the return page or all 2000 photos.

Gorsh, Will... isn't that what your code is _asking_ PHP to do? It looks to
me like the code quoted above is specifically querying the entire table and
generating every single image!

It seems to me that your code should:

$result = mysql_query("SELECT image FROM table WHERE id=$id")
....

[Will Clifton]

On Tue, 23 Nov 2004 09:40:17 -0500, Sundial Services wrote:

It seems to me that your code should:

$result = mysql_query("SELECT image FROM table WHERE id=$id")
...

THANK YOU for reading, however that's precisely what I've been trying to
do. I even had that exact same code at one point and it returns a MySQL
syntax error where I put the variable in the query. Sigh. Still lost, any
other ideas?

Will

[Ken Robinson]

Will Clifton wrote (in part):
[snip]

href=\"showPhoto.php?ID={$row[ID]}\">Photo</a> </td>" . [snip]
//connect stuff
$result = @mysql_query("SELECT image FROM table"); while ( $row =
mysql_fetch_array($result, MYSQL_ASSOC) ) {

echo $row["image"]; }

You are passing the ID field, but not using it, so you are getting all
of your pictures returned. Use:

$result = @mysql_query("select image from table where id='" .
$_GET['id'] . "'");

Of course you really should do some sanity checking on what's in
$_GET['id'] before using it in your query.

Ken Robinson

[Steve]

showphoto.php is doing exactly what you asked it to do: write out one
<img> tag for every row in your database table.

The first of many solutions... Your query should only fetch the one row
that corresponds to the ID that your user selects on the inventory
page, thus:

// get the ID passed from inventory page...
$lngID = $_GET[ "ID" ];

// fetch one row: the <img> text for that ID only...
$result = @mysql_query( "SELECT image FROM table WHERE ID=$lngID" );

// get the result into an array...
$row = mysql_fetch_array( $result, MYSQL_ASSOC );

echo $row[ "image" ];

I'll courier my dirty dishes over shortly. Please can I have them back
in time for tea?

---
Steve

[Ken Robinson]

Will Clifton wrote:

On Tue, 23 Nov 2004 09:40:17 -0500, Sundial Services wrote:

It seems to me that your code should:

$result = mysql_query("SELECT image FROM table WHERE id=$id")
...
THANK YOU for reading, however that's precisely what I've been trying

to do. I even had that exact same code at one point and it returns a MySQL syntax error where I put the variable in the query. Sigh. Still lost, any other ideas?

Please post the code that gives the error and the error messsage.

Ken

[Will Clifton]

On Tue, 23 Nov 2004 07:30:17 -0800, Ken Robinson wrote:

THANK YOU for reading, however that's precisely what I've been trying

to

do. I even had that exact same code at one point and it returns a

MySQL

syntax error where I put the variable in the query. Sigh. Still lost,

any

other ideas?

Please post the code that gives the error and the error messsage.

Ken,

Your other post did the trick, it was exactly what I couldn't figure out
how to do. You were very kind to an obvious newbie.

Will

[Will Clifton]

On Tue, 23 Nov 2004 07:04:15 -0800, Steve wrote:

showphoto.php is doing exactly what you asked it to do: write out one
<img> tag for every row in your database table.

The first of many solutions... I'll courier my dirty dishes over shortly. Please can I have them back
in time for tea?

Steve,

Thank you very much, I used your solution and Ken's solution and they both
worked wonderfully. However, as I live in the central part of the US we
might have to overnight your dishes!

Will

[Pedro Graca]

Will Clifton wrote:

In the returning (showPhoto) page, I have the following:

//connect stuff
$result = @mysql_query("SELECT image FROM table"); while ( $row =
mysql_fetch_array($result, MYSQL_ASSOC) ) {

echo $row["image"]; }

You don't want a loop here; you want to display a single image.

/* validate $_GET['id'] */
$id = (int)$_GET['id'];

/* fetch *one* image from the database */
$sql = "select image from table where id=" . $id;
$result = mysql_query($sql) or die('Error querying the database.');

$row = mysql_fetch_array($result, MYSQL_ASSOC);
echo $row['image'];

--
Mail sent to my "From:" address is publicly readable at http://www.dodgeit.com/
== ** ## !! !! ## ** ==
TEXT-ONLY mail to the complete "Reply-To:" address ("My Name" <my@address>) may
bypass the spam filter. I will answer all pertinent mails from a valid address.

[Geoff Berrow]

I noticed that Message-ID: <pa****************************@cox.net> from
Will Clifton contained the following:

I have a simple MySQL inventory db where the relevant fields are ID (pri,
auto-inc), image (med-blob), description and price.

A couple of asides here since you main q has been answered.

Why is the image field set to med-blob if it is just a short text
string?

Are you really storing <img src="/path/image_name.jpg"> in this field?
(assuming this is a relative path)

Personally I'd simply store the image name, allowing whatever script
calls the data to prepend the path and add the html. More flexibility
this way.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/