How to display an image in a database from a pool of images?

yihungkuo

Hi,
I posted the question in CODING HELP forum, but didn't get much feedback.. I realize I should have posted here in NEWBIES section and have narrowed down my question... somebody please help me. thanks a million ...see question below

See the table image here

http://picasaweb.google.com/lh/photo/7p6sxuquynt0STDnHdYUJQ?authkey=Gv1sRgCMiSuJLCt4PkIw&feat=directlink

I need the php script to ONLY list one of the images. For example,

Products_id 4 has 4 images, I only need to display in html the image where products_image_id = 7 (smallest image ID of the same products_id where products_special_image is NULL)

another example, only display F0101_side.jpg where products_id = 2, and T202_index.jpg where products_id = 9

coldwerturkey

SELECT products_image FROM table WHERE products_id = 9 AND products_special_image = '' ORDER BY products_image_id ASC LIMIT 1

bradgrafelman

So just put those words into logical statements, wrap a couple of SQL keywords around it, and you've got yourself a SQL query:

SELECT products_image FROM myTable WHERE products_special_image IS NULL GROUP BY products_id ORDER BY products_image_id ASC

yihungkuo

bradgrafelman;10908610 wrote:
So just put those words into logical statements, wrap a couple of SQL keywords around it, and you've got yourself a SQL query:
SELECT products_image FROM myTable WHERE products_special_image IS NULL GROUP BY products_id ORDER BY products_image_id ASC

Thanks for your reply... I tried it but nothing shows! This is what I have (the table is also named products_image)..

----------------------

$query = mysql_query("SELECT products_image FROM products_image WHERE products_special_image IS NULL GROUP BY products_id ORDER BY products_image_id ASC");

  while($row = mysql_fetch_array($query))
  {


  echo $row['products_image'];
  echo "<br /><br/>";

  }

--------------

Please forgive me if this is a stupid mistake.. I'm really a newbie. Furthermore, I need to incorporate this to my original chunk of code, any advice?

$query = mysql_query("SELECT DISTINCT * FROM products, products_description WHERE products.products_id = products_description.products_id AND products_description.language_id = '4' AND products.products_status = '1' ORDER BY products.products_last_modified desc");


  while($row = mysql_fetch_array($query))
  {


  echo "<font size=1>".$row['products_id'] . " | " .$row['products_name']. " | "  .$row['products_head_title_tag'] ." | " .$row['products_last_modified'] ." | " .$row['language_id']. "</font>";
  echo "<br /><br/>";

  }

Thanks again

yihungkuo

note that in the database, the fields are set to 'not null'.. this may be the problem? I can't change the database since I already have many product info there.. is there another approach?

yihungkuo

Ok, I tried my best, this is what i have so far

  $query = mysql_query("SELECT * FROM products, products_description WHERE products.products_id = products_description.products_id AND products_description.language_id = '4' AND products.products_status = '1' ORDER BY products.products_last_modified desc"); 

$query_image = mysql_query("SELECT products_image FROM products_image WHERE products_image LIKE '%.%' GROUP BY products_id");

$row_image = mysql_fetch_array($query_image);

  while($row = mysql_fetch_array($query)) 
  { 


  echo "<font size=1>"
  .$row['products_id'].
   " | " 
   .$row['products_name'].
    " | "  

	.$row['products_head_title_tag'] .
	" | " 
	.$row['products_last_modified'] .
	" | " 
	.$row['language_id'].
	 " | "
	  .$row_image['products_id'].
	  " | "
	  .$row_image['products_image_id'].
	  " | "
	  .$row_image['products_image'].
	  "</font>"; 
  echo "<br /><br/>"; 

  }

What I want to achieve is that each row will show it's product_id, its name, and its image. Right now I have each product listed, but I can't figure out how to match the product image to the product. help pleeease....

yihungkuo

wow I actually got it to work! Can any of you experts comment on this and maybe advise if this is the best way to do this?

  $query = mysql_query("SELECT * FROM products, products_description, products_image WHERE  products.products_id = products_description.products_id AND products.products_id = products_image.products_id AND products_image.products_image LIKE '%.%'  AND products_description.language_id = '4' AND products.products_status = '1' GROUP BY products_image.products_id ORDER BY products.products_last_modified desc"); 




  while($row = mysql_fetch_array($query)) 
  { 



  echo "<font size=1>"
  .$row['products_id'].

  " | "
   .$row['products_name'].
    " | "  

	.$row['products_head_title_tag'] .
	" | " 
	.$row['products_last_modified'] .
	" | " 
	.$row['language_id'].
	 " | "

  .$row['products_image_id'].
  " | <img src=\"/images/"
  .$row['products_image']. "\" width=\"80\" height=\"80\" /> </font>"; 
  echo "<br /><br/>"; 

  }