Need help displaying an image using sql and php

[MB.ShadowFox]

Hi all,
I'm pretty new here but I do have "some" knowledge of sql and PhP, I'll start by telling you what I have and then ask what I need.

What I have:
I have a database which holds multiple tables, the one in question is called storage.sql
The table design for storage.sql is as follows:

-- ----------------------------
-- Table structure for `storage`
-- ----------------------------
DROP TABLE IF EXISTS `storage`;
CREATE TABLE `storage` (
  `owner` int(11) NOT NULL DEFAULT '0',
  `itemnum` int(11) NOT NULL DEFAULT '0',
  `itemtype` int(11) NOT NULL DEFAULT '0',
  `refine` int(11) NOT NULL DEFAULT '0',
  `durability` int(11) NOT NULL DEFAULT '40',
  `lifespan` int(11) NOT NULL DEFAULT '100',
  `slotnum` int(11) NOT NULL DEFAULT '0',
  `count` int(11) NOT NULL DEFAULT '1',
  `stats` int(11) NOT NULL DEFAULT '0',
  `socketed` int(11) NOT NULL DEFAULT '0',
  `appraised` int(11) NOT NULL DEFAULT '0',
  `gem` int(11) NOT NULL DEFAULT '0',
  `sp_value` int(11) NOT NULL DEFAULT '0',
  UNIQUE KEY `ow_slot` (`owner`,`slotnum`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

So, in the column 'itemnum' data is kept as a int, basically I run a game server and players put items into there storage then the database is updated to show the data, each item has it's own icon and each icon has its own icon number, I.e item number 1049 would be shown in the column 'itemnum' and has an icon number of 1049 either .jpg or .png

What I have done so far is query'd the sql file in PhP to show a players storage by there account i.d the code for which is below:

<?php // Database connection
$rs = mysql_connect($dbhost, $dbuser, $dbpasswd) or die('Could not establish database connection.');
mysql_select_db($dbname,$rs) or die(mysql_error()); 
if (!$rs)
  {
  die('Could not connect: ' . mysql_error());
  }
  print "Welcome:";
  echo  $_SESSION['account'];

  $result = mysql_query("SELECT * FROM storage WHERE owner ='" . $_SESSION['account_id'] . "'"); // Selecting from storage

// Make box and name the fields in html
 echo "<table border='1'>
<tr>
<th>Owner ID</th>
<th>Item#</th>
<th>Quantity</th>
</tr>";
// Fill box with data
while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['owner'] . "</td>";
  echo "<td>" . $row['itemnum'] .  "</td>";
  echo "<td>" . $row['count'] . "</td>";
  echo "</tr>";
  }
echo "</table>";
  mysql_close($con);

As you can see from the above php file, the data is called from the sql file and shown to the user in a html table using the SELECT * FROM statement.

Now that's all fine and dandy, but, I want to show the user an image instead of raw data that is got from the sql query, I have an image in my httpdocs images folder called 1049.png and I want it to be displayed in the table to the user if he has that item in his storage..

Basically what I want is this:
->User logs into the site
->User opens his storage page
->Data is grabbed from the sql table
->For the 'itemnum' column I want that data to be converted in PhP ( without changing the table design ) to an image type and point to the image location in my httdocs

I have included some images below to show exactly what I mean in case It isn't clear above :)
Image showing the data displayed so far
http://img39.imageshack.us/img39/2215/66683684.jpg
Image showing where the data is kept
http://img502.imageshack.us/img502/3876/70264013.jpg

I have been baffling my head about this one for nearly a 3 months now and it's gotten to the point where I'm just about ready to pull my hair out...

Please, please, please can somebody help me.
Any and all suggestions are welcomed

Thanks in advance,
Mark

[LastMitch]

@MB.ShadowFox

Basically what I want is this:
->User logs into the site
->User opens his storage page
->Data is grabbed from the sql table
->For the 'itemnum' column I want that data to be converted in PhP ( without changing the table design ) to an image type and point to the image location in my httdocs

I'm a bit confused. You mention that you need help to display the image from the db. I might have read it wrong but I don't see any images that need to display from your list?

Please, please, please can somebody help me.
Any and all suggestions are welcomed

I think you need to understand something about Daniweb. Daniweb is a educational forum. We can only help you in a certain extended. This is actually work. It's like you want someone to write some new code for your code to work. Right now, base on the code you provide you need some fresh code. My advice pay someone to do this.

[veedeoo]

@MB.ShadowFox, LastMitch is correct.

Since Im already typing something in response to your question, why not do it something like this. but this does not happen very often..only when my mood is good (caffeinated).

## protect your script from getting undefined index in php 5 just in case itemnumb.png does not exist.
$thisImage = '' ;

## define the directory where the image is located.. this is needed just in case you will be storing this value in the database.
$imagePath = 'SomeLocationInYourServer/images/' ;


while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['owner'] . "</td>";
## check if the image does exist
if (@file_exists($imagePath.$row['itemnum'].".png")){

## I am not really into mixing PHP and html, but I will do it this time. Maybe a templating crash course could help.

echo '<td> <img src="'.$imagePath.$row['itemnum'] .'.png"/>';
}
## if it does not exist, why not show something else put the td, to prevent the table from behaving oddly.
else{
echo '<td>No Image</td>';


}
 echo "<td>" . $row['count'] . "</td>";
echo '</tr>';
}

echo '</table>';

[MB.ShadowFox]

I'm a bit confused. You mention that you need help to display the image from the db. I might have read it wrong but I don't see any images that need to display from your list?

The images are kept in my httdocs image folder, I just wanted to know the correct syntax for pulling the image from my image folder and displaying the correct image using the data from the sql table.. I.e for row 'itemnum' if the data is 1049 I wanted the image 1049.png to be shown from my image folder.

I think you need to understand something about Daniweb. Daniweb is a educational forum. We can only help you in a certain extended. This is actually work. It's like you want someone to write some new code for your code to work. Right now, base on the code you provide you need some fresh code. My advice pay someone to do this.

I don't want someone to actually write a full code for me, I'm quite capable of doing that myself, I am however asking if there is a way to do the above and a gentle push in the right direction.

@veedeoo
You sir are legendary, I will test this now and see how it pans out, thank you very much for your help, I'll get back to you on how things go.

Thanks again,
Mark

[MB.ShadowFox]

Ok just rebuilt my storage script and it's all working perfectly fine, thank you so much to veedeoo for pushing me in the right direction, all I had to change is the location parameter and now everything is working just fine.

Thank you so much again for your help in this, I very much appreciate it.

[MB.ShadowFox]

Just one last thing..

Is there a way to change the imagepath location for multiple images of the same name in an if statement, basically a way to divert the imagepath based on an if statement, i.e

if ($row['itemnum']==1049)
{
($imagepath=="images/storage/weap");
}

This is just incase I want to have a few folders with different images in them.
Thanks again :)