Jump to content

Help with a basic search code


sailorsmokey

Recommended Posts

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..... :(
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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??
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.