Help with a basic search code

[sailorsmokey]

Hi all,

I have racked my brain over this for a week.  Let me start by showing my code:

______________________________________________________________
$count=1;

//search for cat results only
$querycategory="SELECT * FROM prods_to_cats WHERE category_id='$cat'";
$categoryresult=mysql_query($querycategory, $dbh);
while ($categoryrow= mysql_fetch_array($categoryresult))
{
$itemid=$categoryrow['id'];
$querycat="SELECT * FROM product WHERE id='$itemid' AND close_out='1' ORDER BY product_id ASC";
$catresult = mysql_query($querycat, $dbh);
// now you can display the results returned
  while ($row= mysql_fetch_array($catresult)) {
  $title = $row["product_id"];
  $item = $row["id"];
  $image = $row["image"];
  $descrip = $row["title"];
  $stockstatus = $row["stat"];
  $msrp = $row["regular_price"];
  $close = $row["close_out"];

  if($stockstatus=='IN STOCK')
{
$stockstatus="<font color=#009933>IN STOCK</font>";
}
else
{
$stockstatus="<font color=#FF0000>OUT OF STOCK</font>";
}
if($close=='1')
{
$gooddeal="<font color=#FF0000><b>CLOSE OUT!!</b></font>";
}
else
{
$gooddeal="";
}

  echo "$count.)&nbsp;<table border=0><tr><td><a href='http://www.watchesandthings.com/files/$image'><img src='http://www.watchesandthings.com/files/thumbnails/$image' width=70 height=100 border=0></a></td><td><b>$title</b>  $descrip<br>MSRP $$msrp<br>$stockstatus    $gooddeal</td></tr></table>" ;
?>
<br>
<?
  $count++ ;
  echo $count;
  }  //end of embedded while

  if($count<2){
  echo $count;
  exit("<p>I'm sorry, your search returned zero (0) results.");
  }
}    //end of outer while
_________________________________________________________________

Here's what's happening.  The search has been working great, but for the past week I've been trying to display a message when there are no results (or no items in that category that are also close-outs).  So I was staring at the code today, and I thought, why not use the $count variable?  This is working great except for when there is only one result to the search. 

If I comment out the if statement at the bottom with the exit() function, and echo $count, and run the search on a query I know results in only one item, I will see that $count=2.  So my if statement SHOULD  work because $count is NOT less than two, so the script will not exit.

So then I will un-comment that bottom if statement, still echoing count.  And when I run the query again, I will see that $count is not equal to 1, and then it displays the exit message. 

Why would that if statement affect what $count equals.  $count has a value that is set in it's own while loop - I don't see any way it could affect it.

Or maybe there is a better way for me to code this? 

Please help me fellas (and gals)!  I've spent many hours torturing myself over this..... :(

[ToonMariner]

try this:
[code]
<?php

//search for cat results only
$querycategory="SELECT * FROM prods_to_cats WHERE category_id='$cat'";
$categoryresult=mysql_query($querycategory, $dbh);

if (mysql_num_rows($categoryresult) == 0)
{
exit("<p>I'm sorry, your search returned zero (0) results.");
}
else
{

while ($categoryrow= mysql_fetch_array($categoryresult))
{
  $itemid=$categoryrow['id'];
  $querycat="SELECT * FROM product WHERE id='$itemid' AND close_out='1' ORDER BY product_id ASC";
  $catresult = mysql_query($querycat, $dbh);
  // now you can display the results returned
  while ($row= mysql_fetch_array($catresult)) {
  $title = $row["product_id"];
  $item = $row["id"];
  $image = $row["image"];
  $descrip = $row["title"];
  $stockstatus = $row["stat"];
  $msrp = $row["regular_price"];
  $close = $row["close_out"];

  if($stockstatus=='IN STOCK')
  {
    $stockstatus="<font color=#009933>IN STOCK</font>";
  }
  else
  {
    $stockstatus="<font color=#FF0000>OUT OF STOCK</font>";
  }
  if($close=='1')
  {
    $gooddeal="<font color=#FF0000>CLOSE OUT!!</font>";
  }
  else
  {
    $gooddeal="";
  }
  echo "$count.)&nbsp;<table border=0><tr><td><a href='http://www.watchesandthings.com/files/$image'><img src='http://www.watchesandthings.com/files/thumbnails/$image' width=70 height=100 border=0>[/url]</td><td>$title  $descrip
MSRP $$msrp
$stockstatus    $gooddeal</td></tr></table>" ;
  }
}    //end of outer while
}
?>
[/code]

[sailorsmokey]

Thanks for the idea, but this if the very first route I took one week ago.  It doesn't work because the query in the outer while loop simply checks to see if there are items in that category.  Then it moves to the inner while loop and checks to see if the item is a close out or not.  So I need to inner query in order to know if there will be results.

I've tried using mysql_num_rows() inside the inner while loop, but that won't work because as it loops through each item, maybe the first two results are close outs, so they will display, but then the third item isn't, so it will exit the program and I won't get the other 30 items.

So using the $count variable was my next idea which is acting really weird.  Again, for some reason, my final if clause is affecting what $count is equal to, but I can't see why.

Any other ideas??

[Barand]

I'm a bit confused about your id keys (product table has id and product_id according to you inner query) but

[code]<?php
$querycat = "SELECT p.*
FROM prods_to_cats c INNER JOIN product p
ON c.id = p.id
WHERE c.category_id='$cat' AND p.close_out='1' ";
$res = mysql_query($querycat) or die(mysql_error());

if (mysql_num_rows($res)==0) {
echo "No records for category '$cat'<br>";
}
else {
// display results here
}
?>[/code]

[sailorsmokey]

Barand,

Thank you so much.  Changing the query really worked.  I believe you have helped me before on joins almost two years ago.  You've made my life a lot easier.  =)

Does anyone have any links they might suggest regarding join statements?  I have looked at them on the Web quite a few times, but I have not found a "join statements for dummies" kind of explanation that I need.  I always seem to get confused by the descriptions I find. 

So if anyone has any good suggestions, please let me know.  I would love to find something that has a good reference page that I could print out and study so I can understand this better.

Thanks,
Sarah