Jump to content


Photo

Help with a basic search code


  • Please log in to reply
4 replies to this topic

#1 sailorsmokey

sailorsmokey
  • Members
  • PipPip
  • Member
  • 27 posts
  • LocationDenver, Colorado

Posted 20 July 2006 - 11:35 PM

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.watchesan...umbnails/$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..... :(
Sarah aka Sailor Smokey
[br]http://www.slingindirtranch.com

#2 ToonMariner

ToonMariner
  • Members
  • PipPipPip
  • Advanced Member
  • 3,342 posts
  • LocationNewcastle upon Tyne, UK

Posted 21 July 2006 - 12:46 AM

try this:
<?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
}
?>

follow me on twitter @PHPsycho

#3 sailorsmokey

sailorsmokey
  • Members
  • PipPip
  • Member
  • 27 posts
  • LocationDenver, Colorado

Posted 21 July 2006 - 04:13 PM

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??
Sarah aka Sailor Smokey
[br]http://www.slingindirtranch.com

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,016 posts

Posted 21 July 2006 - 07:55 PM

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

<?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
}
?>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 sailorsmokey

sailorsmokey
  • Members
  • PipPip
  • Member
  • 27 posts
  • LocationDenver, Colorado

Posted 26 July 2006 - 10:59 PM

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
Sarah aka Sailor Smokey
[br]http://www.slingindirtranch.com




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users