I am trying to make a listings of products, based on category. I have been fighting with this script for the past 3 hours trying to get this to work and for some reason it is only pulling the products that do not have a category2

PHP Code:

else if (isset($_GET['category1'])) { 
 
 $resultwithcat2 = mysql_query("SELECT * FROM products WHERE category1='$_GET[category1]'", $dbhandle);
 $resultnocat2 = mysql_query("SELECT * FROM products WHERE category1='$_GET[category1]' AND category2=''", $dbhandle);
 
 if (!empty($row[category2])) { 
  while ($row = mysql_fetch_array($resultwithcat2)) {   
 echo "<a href=\"products.php?category1&category2=".$row['category2']."\"><strong>".$row['category2']."</strong></a> in ".$row[category1]." <font color=green>WITH</font> 2nd category of ".$row[category2]."<br>";
  } 
 } 
 
 echo "<hr>";
 
 if (empty($row[category2])) {
  while ($row = mysql_fetch_array($resultnocat2)) { 
 echo "<a href=\"products.php?productid=".$row['productid']."\"><strong>".$row['productname']."</strong></a> in ".$row[category1]." <font color=red>NO</font> 2nd category <br>";
 }
 } 
 
} 


Breaking this code down, I think the problem lies somewhere within here:

PHP Code:

 
 $resultwithcat2 = mysql_query("SELECT * FROM products WHERE category1='$_GET[category1]'", $dbhandle);
 
 
 if (!empty($row[category2])) { 
  while ($row = mysql_fetch_array($resultwithcat2)) {   
 echo "<a href=\"products.php?category1&category2=".$row['category2']."\"><strong>".$row['category2']."</strong></a> in ".$row[category1]." <font color=green>WITH</font> 2nd category of ".$row[category2]."<br>";
  } 
 } 


Either something is wrong with my query or something is wrong with my php. I can't, for the life of me, figure out what is wrong with it. Like I said, it is pulling the results without a category2 properly. I want it to take the products with a category2 and list them at the top.

Note: The page with the problem can be found here: http://hvacdirect.us/products.php
Note: I have been testing in Test Category 4

is it my empty() function that is not allowing it to pull?

Oh!

1. you've used first IF statement with undeclared $row. You have to remove this if statement into while statement.
2. $row[category2] is incorrect. $row['category2'] - this is correct
3. the second if statement is incorrect like first. Corect it too.

Thanks Map, I appreciate the help. I'm still learning PHP as I go so there are probably a lot of stupid mistakes in the whole script.

Jeremy, I wasn't trying to bump my thread or infer that Chris was suppose to be my employee, it was already on top of the listings when I replied. Chris and Lady are just the people who end up helping me the most because they tend to be the only people to ever reply. I do admit, it was a pointless post for me to make.

Map, should the while statement look like this rather than what I originally had? I still haven't grasped where to, and where to not use single quotations.

PHP Code:

 
$result = mysql_query("SELECT * FROM products WHERE category1='$_GET[category1]'", $dbhandle);
 
 
if (!empty($row['category2'])) {
while ($row = mysql_fetch_array($result)) {
 
// Show items with a category2.
 
}
}
 
echo "<hr>"; 
 
if (empty($row['category2'])) {
while ($row = mysql_fetch_array($result)) {
 
// Show items without a category2
 
}
} 


EDIT: Is there a way to do an SQL query to see if a row has a value?

Example: SELECT * FROM products WHERE category2=HAS ANYTHING BUT BLANK
or would this be considered as "NOT NULL"? WHERE category2=NOT NULL

It looks like the problem is with your if condition:

PHP Code:

 if (!empty($row[category2])) 


Since $row is not set at this point, empty() returns true therefore !empty() is false. The second if statement is not negated so the condition is true.

I don't actually see the purpose of either if statement; I think you can get rid of both and just leave the while loops.

Is there a way to do an SQL query to see if a row has a value?

Example: SELECT * FROM products WHERE category2=HAS ANYTHING BUT BLANK
or would this be considered as "NOT NULL"? WHERE category2=NOT NULL

Of course there is...

select *
from products
where category2 is not null
and category2 != ''

When you check for null, you have to use "is" and "is not", as the = signs won't do.
After that, you can give as many condition in the where clause as you want, including listing each case that should be accepted or rejected.
Here, as you asked, the category2 field should not be null and should not have a blank value.