some php/sql ?

[jonni]

Hi, I have set up osc for a client who is selling prints of his paintings and original photographic material. Apart form the 'shop' area there are other pages, in particular a gallery where users can easily browse through collections of images.. Rather than create a whole new database for this purpose i had a brainwave and decided to use the existing db created by osc as I know its good and i am totally new to sql. so the first problem i wanted to solve was how to display table of thumbnails of images from a particular collection. Notice that I se collections rather than categories since it makes more sense in terms of art. Anywa what I basicaly needed to retrieve was an array of products_id->products_image where products_id were linked to a particular categories_id. The setup of osc makes this relationship in a table called products_to_categories but try as i might i just simply could not come up with a valid sql_query that would return the desired array in one reqest to the database, in the end I had to do it like this

 

NOTE:the code prints the thmbnails in rows of 3

 

//first call

$rt_tmp = mysql_query("SELECT products_id FROM products_to_categories WHERE categories_id = 17 LIMIT 0 , 100");

$cell = 0;

echo '<table>';

while($rw_tmp = mysql_fetch_array($rt_tmp))

{

//second call

$result = mysql_query("SELECT products_id , products_image FROM products WHERE products_id = ".$rw_tmp['products_id']." LIMIT 0 , 100");

$row = mysql_fetch_array($result);

if ($cell == 0)

echo '<tr><td><a href="#" onClick="viewImage('.$row['products_id'].');"><img src="../catalog/images/'.$row['products_image'].'" width="100" height="75" border="0"></a></td>';

else

echo '<td><a href="#" onClick="viewImage('.$row['products_id'].');"><img src="../catalog/images/'.$row['products_image'].'" width="100" height="75" border="0"></a></td>';

$cell++;

if ($cell == 3){

echo '</tr>';

$cell = 0;

}

}

echo '</td></tr></table>';

 

So the question is --- if there are any php/mysql tecchies there who know what I'm on about culd you tell me how i could have achieved the same result using just one call?

J-)