Foreach / MySql Select from...

Basil_Brush

Hi, could anyone please help me or point me in the right direction for this.

$thumbArray = array($thumbnail);
foreach($thumbArray as $thumbid) {
$result = mysql_query("SELECT * FROM pp_photos WHERE id = '$thumbid'") or die(mysql_error());
while($row = mysql_fetch_array( $result )) {

do stuff

}

It only works for the one of variables in $thumbArray?

coreyp_1

What is $thumbnail?

Basil_Brush

Thumbnail is a number, a unique id of a particular image. So for example $thumbArray = 100, 200 .... and foreach of these numbers I want to do a Mysql query but for the above it only does it for 100

coreyp_1

Please pardon me if I am pressing the issue, but I think your problem lies in the declaration $thumbArray = array($thumbnail);
If $thumbnail is 100, then $thumbArray[0]=100. There is only 1 value in the array. Consider this:

$thumbArray = array();
$thumbArray[] = 100;
$thumbArray[] = 257;
$thumbArray[] = 299;

Now you have an array that looks like this:

 $thumbArray[0] = 100;
 $thumbArray[1] = 257;
 $thumbArray[2] = 299;

In other words, it looks like you are only specifying an array with one element. Does this make sense? If not, could you share a little more of your code so that I may better understand what you are trying to do?

Basil_Brush

Hmm you are right, changed $thumbArray = array($thumbnail); to $thumbArray = array(100, 200); and it worked. Ok here is more code.

$result = mysql_query("SELECT * FROM pp_categories WHERE description='$town' AND parent = '533'") or die(mysql_error());

while($row = mysql_fetch_array( $result )) {
$id = $row['id'];
$parent = $row['parent'];
$catname= $row['catname'];
$thumbnail=$row['thumbnail'];

$thumbArray = array($thumbnail);

foreach($thumbArray as $thumbid) {
$result = mysql_query("SELECT * FROM pp_photos WHERE id = '$thumbid'") or die(mysql_error());

while($row = mysql_fetch_array( $result )) {
print "<a href=/photos/showgallery/cat/" . $id . ".php><img src=/photos/data/" . $cat . "/thumbs/" . $bigimage . " border=0 class=relatedthumb></a>";
}

Thanks alot for the help Corey.

coreyp_1

I'm beginning to see a bit more clearly your problem, but I need to know more about your situation.
1. What is your database structure for pp_photos and pp_categories?
2. Where do the variables $cat and $bigimage come from?
I have to leave the house for a little while, but I will come back tonight and check on this thread.

Basil_Brush

$cat and $bigimage are in pp_photos, I am just playing around with the script and forgot to add $bigimage=$row['bigimage']; above print "<a href=/photos/showgallery/cat ....

pp_photos has the following fields (it's photopost)

and pp_categories

cat in pp_photos links id in pp_categories

coreyp_1

Here goes nothing...

$result = mysql_query("SELECT id, parent, catname FROM pp_categories
  WHERE description='$town' AND parent = '533'") or die(mysql_error());

while($row = mysql_fetch_array( $result )) {
  $id = $row['id'];
  $parent = $row['parent'];
  $catname = $row['catname'];
  $query = "SELECT id, cat, bigimage FROM pp_photos WHERE cat=$id";
  $resource = mysql_query($query) or die(mysql_error());
  while ($result = mysql_fetch_array($resource)) {
    print "<a href=/photos/showgallery/cat/" .
      $result['id'] . ".php><img src=/photos/data/" . $result['cat'] . "/thumbs/" .
      $result['bigimage'] . " border=0 class=relatedthumb></a>";
  }
}

You'll notice that I cut down your query by only requesting certain fields. This reduces the load on your server. After all, your tables contain quite a few columns! Just add whatever else you need.

P.S. - I didn't test this, so you may need to toy around with it.

Basil_Brush

Thanks Corey, it works but it is not quite what I am trying to do. The modified piece of your code below is what I am trying to do but once again it only prints out one photo (category).

Basically I have a category which is a particular town, I also have other categories (Castles, Cathedrals, Abbeys etc) which I am trying to put on the bottom off the particular town if they are in the town, so I put the name of the town in the description (WHERE description='$town') from this I select a field called thumbnail which is a id of a photo that I would like to display. So for example London would select Westminster Abbey, The Tower of London etc and their corresponding thumbnails i.e 100, 200 and foreach of these thumbnails I would like to get the photo details from pp_photos i.e cat, bigimage etc.

$result = mysql_query("SELECT thumbnail FROM pp_categories 
  WHERE description='$town' AND parent = '533'") or die(mysql_error()); 

while($row = mysql_fetch_array( $result )) { 
  $thumbnail=$row['thumbnail']; 

  $query = "SELECT id, cat, bigimage FROM pp_photos WHERE id=$thumbnail"; 
  $resource = mysql_query($query) or die(mysql_error()); 
  while ($result = mysql_fetch_array($resource)) { 
    print "<a href=/photos/showgallery/cat/" . 
      $result['id'] . ".php><img src=/photos/data/" . $result['cat'] . "/thumbs/" . 				
      $result['bigimage'] . " border=0 class=relatedthumb></a>"; 
  } 
}

coreyp_1

sorry for the delay in getting back to you... It was a long day at work! I'm not seeing where the problem is, to tell you the truth. Have you checked the HTML output to see if anything else is being printed? Do you have a working example of the script online somewhere? I'd really like to help you get this going... There's no reason it shouldn't!