Using PHP/MySQL for Products Search Page based on Attributes

Page 1 of 1

8 Replies - 4402 Views - Last Post: 08 February 2012 - 09:21 AM Rate Topic: -----

#1 BlueCollarWorkwear  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 07-February 12

Using PHP/MySQL for Products Search Page based on Attributes

Posted 07 February 2012 - 02:40 PM

Greatly struggling with building a search page for my store's products. Here is a link to the current page:
http://www.bcwstore....ng/footWear.php
I want this search page to return all products that meet selected attributes and manufacturers. It's very close to functioning, but I keep getting this error when trying to refine my search:
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/content/b/c/w/bcw1928/html/testing/footWear.php on line 183
After selecting a category, the logic is:
Attributes: If selected, AND (i.e. Steel Toe AND Waterproof AND Insulated)
Manufactureres: If selected, OR (i.e. Wolverine OR Timberland OR Carolina)
Here is the source from the form:
<form name='refiner' action='footWear.php?act=refine' method='POST' >
        <tr>
        		<td><a href="footWear.php?act=mas&toplevel=25" >Men's</a></td>
				 <td><a href="footWear.php?act=mas&toplevel=26" >Women's</a></td>
				 <td><a href="footWear.php?act=mas&toplevel=27" >6&quot; Boots</a></td>
				 <td><a href="footWear.php?act=mas&toplevel=30" >8-9&quot; Boots</a></td>
				 <td><a href="footWear.php?act=mas&toplevel=36" >Logger Boots</a></td>
				 <td><a href="footWear.php?act=mas&toplevel=31" >Hiker/Athletic</a></td>
				 <td><a href="footWear.php?act=mas&toplevel=32" >Shoe/Oxford</a></td>

         </tr>
        </table>


        </div>
        					<div id="sideBar">
                            	<h1>Select Attributes</h1>

                                <div class="checkBoxBar">
                                <div class="spacer" style="height:5px;"></div>
                                <?php $FootWearPage->FrontSearchRefine(); ?>
                                </div>
        					</div>

                            <div id="content" style="width:582px;">
                             <div class="footwearContentSpacer">
                            </div>

                            <?php
	Switch ($_GET['act'])
	{
		case "clear":
			unset($_SESSION['toplevel']);
			echo "Search Results Cleared<br>";
			unset($_SESSION['toplevel']);
			$SQL="SELECT DISTINCT(ProdID) FROM Products WHERE ProdID IN (SELECT Product FROM Products_Attributes WHERE Attribute='47')";
			$result = $FootWearPage->query($SQL);
			$num_rows = mysql_num_rows($result);
			If ($num_rows>0)
			{
				echo "Currently showing all footwear<br>";
				while($row = $FootWearPage->fetch($result))
				{
					$FootWearPage-> FrontGetSmallProds($row['ProdID']);
				}
			}
			break;
		case "refine":
			$SQL="SELECT DISTINCT(ProdID) FROM Products WHERE ";
			$attCount=0;
			$mfgCount=0;
			$Tree="<span class='current'><a href='Products.php'>Products</a> ";
			//Checking to see if a top level attribute is assigned
			if (isset($_SESSION['toplevel']))
			{
				//echo "TOPLEVEL SET<BR>";
				$ATTName=$FootWearPage->GetAttName($_SESSION['toplevel']);
				$Tree=$Tree.">> <a href=Products.php?act=search&term=att&value=".$_SESSION['toplevel'].">". $ATTName ."</a> ";
				$SQL=$SQL." ProdID IN (SELECT Product FROM Products_Attributes WHERE Attribute ='".$_SESSION['toplevel']."')";
				$attCount++;
			}
			Foreach ($_POST as $key=>$value)
			{
				if(left($key,4)=="MFG_")
				{
					If($mfgCount==0)
					{
						$MFG= " MFG='".$value."'";
					}
					else
					{
						$MFG=$MFG." OR MFG='".$value."'";
					}
					$mfgCount++;
				}
				else
				{
					if($attCount==0)
					{
						$ATTName=$FootWearPage->GetAttName($value);
						$Tree=$Tree.">> <a href=Products.php?act=search&term=att&value=".$value.">". $ATTName ."</a>";
						$ATT= $ATT." ProdID IN (SELECT Product FROM Products_Attributes WHERE Attribute ='".$value."')";
					}
					else
					{
						$ATTName=$FootWearPage->GetAttName($value);
						$Tree=$Tree.">> <a href=Products.php?act=search&term=att&value=".$value.">". $ATTName ."</a>";
						$ATT=$ATT." AND ProdID IN (SELECT Product FROM Products_Attributes WHERE Attribute ='".$value."')";
					}
					//echo $key."=>".$value."</br>";
					$attCount++;
				}
			}
			if(strlen($MFG)>1 AND strlen($ATT)>1)
				{
					$ATT= "AND ".$ATT;
				}

			$SQL=$SQL.$MFG.$ATT;
			$SQL=$SQL." AND Products.Active='1' ORDER BY ProdID ASC";

			//echo $SQL."<br>";

			//echo $Tree."&nbsp;&nbsp;<a href='footWear.php?act=clear'>Clear Search Results</a></span><br>";
			$result = $FootWearPage->query($SQL);
			$num_rows = mysql_num_rows($result);
			If ($num_rows>0)
			{
				echo "Search Returned ".$num_rows." products <br>";
				while($row = $FootWearPage->fetch($result))
				{
					$FootWearPage-> FrontGetSmallProds($row['ProdID']);
				}
			}
				else
				{
					echo "Search returned no results";
				}
			break; //Case:Refine
		case "mas":
				If (isset($_GET['toplevel']))
				{
					//echo $FootWearPage->GetAttName($_GET['toplevel'])."<br>";
					$_SESSION['toplevel']=$_GET['toplevel'];
					$SQL="SELECT ProdID FROM Products WHERE ProdID IN (SELECT Product FROM Products_Attributes WHERE Attribute ='".$_GET['toplevel']."')";
					$Tree="<span class='current'><a href='Products.php'>Products</a>: ";
					$ATTName=$FootWearPage->GetAttName($_SESSION['toplevel']);
					$Tree=$Tree.">> <a href=Products.php?act=search&term=att&value=".$_SESSION['toplevel'].">". $ATTName ."</a>:  ";
					echo $Tree."&nbsp;&nbsp; <a href='footWear.php?act=clear'>Clear Search Results</a></span><br>";
					$FootWearPage->FrontGetProducts("custom",$SQL);
				}
				else
				{
					echo "<div style='text-align:center;'>Please select an entry at the top of the page or to the left to begin an advanced footwear search.<br>";
					$SQL="SELECT DISTINCT(ProdID) FROM Products WHERE ProdID IN (SELECT Product FROM Products_Attributes WHERE Attribute='47')";
					$result = $FootWearPage->query($SQL);
					$num_rows = mysql_num_rows($result);
					If ($num_rows>0)
					{
						echo "Currently showing all footwear<br></div>";
						while($row = $FootWearPage->fetch($result))
						{
							$FootWearPage-> FrontGetSmallProds($row['ProdID']);
						}
					}

				}

				break;
		default:
				$SQL="SELECT DISTINCT(ProdID) FROM Products WHERE ProdID IN (SELECT Product FROM Products_Attributes WHERE Attribute='47')";
				$result = $FootWearPage->query($SQL);
				$num_rows = mysql_num_rows($result);
				If ($num_rows>0)
				{
					echo "Currently showing all footwear<br>";
					while($row = $FootWearPage->fetch($result))
					{
						$FootWearPage-> FrontGetSmallProds($row['ProdID']);
					}
				}
	}

?>

                            </div>


</div>
<div id="rightBarWrapper">
 <h5>Select Manufacturers</h5>

 <div class="checkBoxBar rightBar">


                                <div class="spacer" style="height:5px;"></div>
                                <table width="138" cellpadding="0">
                                	<tr height="24">
                                    <td width="24"><input type="checkbox" name="MFG_Wolverine" value="Wolverine"/></td><td width="106">Wolverine</td>
                                    </tr>
                                    <tr height="24">
                                    <td><input type="checkbox"/ name='MFG_Timberland' value='Timberland'></td><td>Timberland</td>
                                    </tr>
                                    <tr height="24">
                                    <td><input type="checkbox"/ name='MFG_Rocky' value ='Rocky'></td><td>Rocky</td>
                                    </tr>
                                    <tr height="24">
                                    <td><input type="checkbox"/ name='MFG_Nautilus' value='Nautilus'></td><td>Nautilus</td>
                                    </tr>
                                    <tr height="23">
                                    <td><input type="checkbox"/ name='MFG_Thorogood' value ='Thorogood'></td><td>Thorogood</td>
                                    </tr>
                                    <tr height="23">
                                    <td><input type="checkbox"/ name='MFG_LaCrosse' value='LaCrosse'></td><td>LaCrosse</td>
                                    </tr>
                                    <tr height="23">
                                    <td><input type="checkbox"/ name='MFG_Muck' value='Muck'></td><td>Muck</td>
                                    </tr>
                                    <tr height="23">
                                    <td><input type="checkbox"/ name='MFG_Converse-Shoes' value='Converse-Shoes'></td><td>Converse</td>
                                    </tr>
                                    <tr height="23">
                                    <td><input type="checkbox"/ name='MFG_Georgia' value='Georgia'></td><td>Georgia</td>
                                    </tr>
                                    <tr height="23">
                                    <td><input type="checkbox"/ name='MFG_Chippewa-Boots' value='Chippewa-Boots'></td><td>Chippewa</td>
                                    </tr>
                                    <tr height="23">
                                    <td><input type="checkbox"/ name='MFG_Bates-Boots' value='Bates-Boots'></td><td>Bates</td>
                                    </tr>
                                    <tr height="23">
                                    <td><input type="checkbox"/ name='MFG_Skechers' value='Skechers'></td><td>Skechers</td>
                                    </tr>
                                    <tr height="23">
                                    <td><input type="checkbox"/ name='MFG_Rockport' value='Rockport'></td><td>Rockport</td>
                                    </tr>
									 <tr height="23">
                                    <td><input type="checkbox"/ name='MFG_Cofra' value='Cofra'></td><td>Cofra</td>
                                    </tr>
									 <tr height="23">
                                    <td><input type="checkbox"/ name='MFG_Florshiem' value='Florshiem'></td><td>Florshiem</td>
                                    </tr>
									 <tr height="23">
                                    <td><input type="checkbox"/ name='MFG_Carolina-Boots' value='Carolina-Boots'></td><td>Carolina</td>
                                    </tr>
									 <tr height="23">
                                    <td><input type="checkbox"/ name='MFG_Danner' value='Danner'></td><td>Danner</td>
                                    </tr>
									 <tr height="23">
                                    <td><input type="checkbox"/ name='MFG_Double H' value='Double H'></td><td>Double H</td>
                                    </tr>
									 <tr height="23">
                                    <td><input type="checkbox"/ name='MFG_Dr. Martens' value='Dr. Martens'></td><td>Dr. Martens</td>
                                    </tr>
									 <tr height="23">
                                    <td><input type="checkbox"/ name='MFG_Golden Retriever' value='Golden Retriever'></td><td>Golden Retriever</td>
                                    </tr>
									<tr height="23">
									<td><input type="checkbox"/ name='MFG_Michelin' value='Michelin'></td><td>Michelin</td>
									                                    </tr>
									<tr height="23">
									<td colspan='2'><input type="submit" value ="Update Results"></td>
									</tr>
                                </table>
								</form>


And here is the code for FrontSearchRefine():
function FrontSearchRefine()
	{

		$SQL="SELECT * FROM Attributes WHERE id = '22' OR id = '18' OR id = '24' OR id = '33' OR id = '34' OR id = '35' OR id = '37' OR id = '38' OR id = '39' OR id = '40' OR id = '41' OR id = '42' OR id = '43' OR id ='45'";
		$this->connect();
		$result = $this->query($SQL);

		echo "<table width='182' cellpadding='0'>";
			while($row = $this->fetch($result))
			{
				$liclass = ($row_count % 2) ? $class2 : $class1;
				echo "<tr height='23'>\n";
				echo "<td><input type ='checkbox' name ='".$row['id']."' value='".$row['id']."'></td><td>".$row['Attribute'] ."</td>";
				echo"</tr>\n";
			}
		echo "</table>";
		echo "<input type ='submit' value='Update Results'></li>";
	}


I know that's a great deal of code to sift through, but can anyone see what I am missing?

Is This A Good Question/Topic? 0
  • +

Replies To: Using PHP/MySQL for Products Search Page based on Attributes

#2 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 5667
  • View blog
  • Posts: 22,509
  • Joined: 23-August 08

Re: Using PHP/MySQL for Products Search Page based on Attributes

Posted 07 February 2012 - 04:17 PM

Any time you see this, it's time to go back to PHP/MySQL 101:

Never assume a SQL query, particularly one built dynamically, will automagically work.

You must check the results of your query and if it fails print it (when in development) or log it (when exposed to the public).

$sql = "my_assembled_sql_statement";
$result = mysql_query($sql);
if (!$result)
{
    // Only die in development, not in production.
    die("Query {$sql} failed: " . mysql_error());
}

Was This Post Helpful? 3
  • +
  • -

#3 BlueCollarWorkwear  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 07-February 12

Re: Using PHP/MySQL for Products Search Page based on Attributes

Posted 07 February 2012 - 05:11 PM

Ok, I understand the need for that fix. But the underlying problem is what has me more perplexed. There should definitely be a result (based on the logic of the search and the products in the database), and I'm not sure why my code isn't reflecting that. It works flawlessly until adding the manufacturer portion into the equation. In other words, this was working great when just searching by category and attributes, but I'm trying to have it further refine by manufacturer.
Was This Post Helpful? 0
  • +
  • -

#4 codeprada  Icon User is offline

  • Changed Man With Different Priorities
  • member icon

Reputation: 934
  • View blog
  • Posts: 2,329
  • Joined: 15-February 11

Re: Using PHP/MySQL for Products Search Page based on Attributes

Posted 07 February 2012 - 08:15 PM

Which line is #183 in Footwear.php?

Also you can simply this query
SELECT * FROM Attributes WHERE id = '22' OR id = '18' OR id = '24' OR id = '33' OR id = '34' OR id = '35' OR id = '37' OR id = '38' OR id = '39' OR id = '40' OR id = '41' OR id = '42' OR id = '43' OR id ='45'
to
SELECT * FROM Attributes WHERE id IN('22', '18', '24', '33' .... '45')

Was This Post Helpful? 0
  • +
  • -

#5 BlueCollarWorkwear  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 07-February 12

Re: Using PHP/MySQL for Products Search Page based on Attributes

Posted 08 February 2012 - 07:38 AM

Oh I didn't realize that the line numbers got messed up when I copied and pasted it. Line 183 would be:
$num_rows = mysql_num_rows($result);


Here's the surrounding code:
$SQL=$SQL." AND Products.Active='1' ORDER BY ProdID ASC";

			//echo $SQL."<br>";

			//echo $Tree."&nbsp;&nbsp;<a href='footWear.php?act=clear'>Clear Search Results</a></span><br>";
			$result = $FootWearPage->query($SQL);
			$num_rows = mysql_num_rows($result);
			If ($num_rows>0)
			{
				echo "Search Returned ".$num_rows." products <br>";
				while($row = $FootWearPage->fetch($result))
				{
					$FootWearPage-> FrontGetSmallProds($row['ProdID']);
				}
			}
				else
				{
					echo "Search returned no results";
				}

Was This Post Helpful? 0
  • +
  • -

#6 codeprada  Icon User is offline

  • Changed Man With Different Priorities
  • member icon

Reputation: 934
  • View blog
  • Posts: 2,329
  • Joined: 15-February 11

Re: Using PHP/MySQL for Products Search Page based on Attributes

Posted 08 February 2012 - 07:54 AM

Basically like what JackOfAllTrades was saying is to dump the query just before executing it. Place these lines just before $result = $FootWearPage->query($SQL); 
var_dump($SQL);
exit;
and verify that the SQL query is valid.
Was This Post Helpful? 1
  • +
  • -

#7 BlueCollarWorkwear  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 07-February 12

Re: Using PHP/MySQL for Products Search Page based on Attributes

Posted 08 February 2012 - 08:12 AM

Ok, now I'm getting an idea of where I'm going. I have a syntax error, but I can't quite find it:

Query SELECT DISTINCT(ProdID) FROM Products WHERE ProdID IN (SELECT Product FROM Products_Attributes WHERE Attribute ='30') MFG='Wolverine' OR MFG='Timberland' OR MFG='Rocky' AND Products.Active='1' ORDER BY ProdID ASC failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MFG='Wolverine' OR MFG='Timberland' OR MFG='Rocky' AND Products.Active='1' ORDER' at line 1
Was This Post Helpful? 0
  • +
  • -

#8 codeprada  Icon User is offline

  • Changed Man With Different Priorities
  • member icon

Reputation: 934
  • View blog
  • Posts: 2,329
  • Joined: 15-February 11

Re: Using PHP/MySQL for Products Search Page based on Attributes

Posted 08 February 2012 - 08:24 AM

Just before MFG='Wolverine' you must have either AND or OR. You now have to go over your algorithm and make the changes.
Was This Post Helpful? 1
  • +
  • -

#9 BlueCollarWorkwear  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 07-February 12

Re: Using PHP/MySQL for Products Search Page based on Attributes

Posted 08 February 2012 - 09:21 AM

Ok, thanks, I should have spotted that. I need to work on the query some more. Thank you for the tips
Was This Post Helpful? 0
  • +
  • -
Page 1 of 1