Help need with search

mebleu

I am working on my first database driven site and am unsure how to get my search working properly. The page I've set up I want to have a keyword search . . . which works; I want the visitor to be able to view all products . . . which works. The third way I'd like a search is by category. I want to have a selection menu that the visitor would pulldown and select what category they want to visit. I have it so it works but it doesn't work properly and I'm unsure how to fix it?

For each category I would like all the items related to that category to display on one page. Right now for every item I've listed in the database on my select pulldown it lists the category equal amount of times. i.e. if I have 3 products in a pet supply category in my pulldown menu it is displaying the pet supply category 3 times. 1 for each product related to that category, when I go to the view page it only displays just that one item, not all three. I would like my search page pulldown to display the category only once and have all the related items on one page for viewing. Since the view page works I'm thinking I need to set some type of limit on the first page? Can anyone tell me what I need to add to the code?

Here is a snippet from my search page code:

. . .

$SQLstring = "SELECT id, category FROM $TableName ORDER by category asc";

. . .

<select name=\"sel_record\">
<option value=\"\"> -- Choose a Category -- </option>";

		while ($row = mysqli_fetch_array($QueryResult)) {
			   $id = $row["id"];
			   $category = $row["category"];
			   echo "<option value=\"$id\">$category</option>";
		}
		echo "
		</select>&nbsp;&nbsp;<INPUT type=\"submit\" value=\"Go\"></p>

and a snippet from my view page:

. . .

$category=$GET['category'];
$id=$GET['id'];

// formulate and execute the query
$getCats = "SELECT * FROM $TableName WHERE id = '$_POST[sel_record]'";
$getCats_res = mysqli_query($DBConnect, $getCats) Or die("<p>Unable to execute the query.</p>" . "<p>Error code " . mysqli_errno($DBConnect) . ": " . mysqli_error($DBConnect)) ."</p>";

if (mysqli_num_rows($getCats_res) < 1) {
//invalid item
$display_block = "<p>Sorry, no categories exist!</p>";
} else {
//categories exist, so get them and then get information
while ($cat_row = mysqli_fetch_array($getCats_res)) {
$category = stripslashes($cat_row['category']);

            //now start the display_block and then get items

            	$id = $cat_row['id'];
            	$ProductName = stripslashes($cat_row['product_name']);
            		/*$Description = stripslashes($cat_row['description']);*/
            	$Price = stripslashes($cat_row['price']);
            	$thumb = stripslashes($cat_row['thumbnail']);

			$display_block .= "<table width=\"585\">
<tr align=\"center\">
<td width=\"72\" valign=\"middle\"><a href=\"shop_xxx.php?id=$id\"><img src=\"$thumb\"></td>
<td width=\"350\" valign=\"middle\"><a href=\"shop_xxx.php?id=$id\"><p><strong>$ProductName</strong></a></p></td>
<td width=\"72\" valign=\"middle\"><p class=\"body\"><strong> $Price</strong></p></td>
</tr>
</table>";
}

}

kakki

hiya ...

iam a bit unsure of what you want and the table-structure you use ...
i'm just guessing now ...

$SQLstring = "SELECT id, category FROM $TableName ORDER by category asc";

results in product-ids and their corresponding category ??
if so, then you may change
THIS:

. . .

$SQLstring = "SELECT id, category FROM $TableName ORDER by category asc";

. . .

<FORM method=\"POST\" action=\"viewbycat.php\">

<select name=\"sel_record\">
<option value=\"\"> -- Choose a Category -- </option>";

while ($row = mysqli_fetch_array($QueryResult)) {
$id = $row["id"];
$category = $row["category"];
echo "<option value=\"$id\">$category</option>";
}
echo "
</select>&nbsp;&nbsp;<INPUT type=\"submit\" value=\"Go\"></p>

<br />
</FORM>

into THIS:

. . .

$SQLstring = "SELECT DISTINCT(category) AS _category FROM $TableName ORDER by _category asc";

. . .

<FORM method=\"POST\" action=\"viewbycat.php\">

<select name=\"sel_record\">
<option value=\"\"> -- Choose a Category -- </option>";

while ($row = mysqli_fetch_array($QueryResult)) {
$category = $row["category"];
echo "<option value=\"$category\">$category</option>";
}
echo "
</select>&nbsp;&nbsp;<INPUT type=\"submit\" value=\"Go\"></p>

<br />
</FORM>

and THIS:

and a snippet from my view page:

. . .



$category=$_GET['category'];
$id=$_GET['id'];

// formulate and execute the query
$getCats = "SELECT * FROM $TableName WHERE id = '$_POST[sel_record]'";
$getCats_res = mysqli_query($DBConnect, $getCats) Or die("<p>Unable to execute the query.</p>" . "<p>Error code " . mysqli_errno($DBConnect) . ": " . mysqli_error($DBConnect)) ."</p>";

if (mysqli_num_rows($getCats_res) < 1) {
//invalid item
$display_block = "<p>Sorry, no categories exist!</p>";
} else {
//categories exist, so get them and then get information
while ($cat_row = mysqli_fetch_array($getCats_res)) {
$category = stripslashes($cat_row['category']);

//now start the display_block and then get items

$id = $cat_row['id'];
$ProductName = stripslashes($cat_row['product_name']);
/*$Description = stripslashes($cat_row['description']);*/
$Price = stripslashes($cat_row['price']);
$thumb = stripslashes($cat_row['thumbnail']);

$display_block .= "<table width=\"585\">
<tr align=\"center\">
<td width=\"72\" valign=\"middle\"><a href=\"shop_xxx.php?id=$id\"><img src=\"$thumb\"></td>
<td width=\"350\" valign=\"middle\"><a href=\"shop_xxx.php?id=$id\"><p><strong>$ProductName</strong></a></p></td>
<td width=\"72\" valign=\"middle\"><p class=\"body\"><strong> $Price</strong></p></td>
</tr>
</table>";
}
}

?>

into THIS:

and a snippet from my view page:

. . .



$category=$_GET['category'];

// formulate and execute the query
$getCats = "SELECT * FROM $TableName WHERE category = '$category'";
$getCats_res = mysqli_query($DBConnect, $getCats) Or die("<p>Unable to execute the query.</p>" . "<p>Error code " . mysqli_errno($DBConnect) . ": " . mysqli_error($DBConnect)) ."</p>";

if (mysqli_num_rows($getCats_res) < 1) {
//invalid item
$display_block = "<p>Sorry, no categories exist!</p>";
} else {
//categories exist, so get them and then get information
while ($cat_row = mysqli_fetch_array($getCats_res)) {
$category = stripslashes($cat_row['category']);

//now start the display_block and then get items

$id = $cat_row['id'];
$ProductName = stripslashes($cat_row['product_name']);
/*$Description = stripslashes($cat_row['description']);*/
$Price = stripslashes($cat_row['price']);
$thumb = stripslashes($cat_row['thumbnail']);

$display_block .= "<table width=\"585\">
<tr align=\"center\">
<td width=\"72\" valign=\"middle\"><a href=\"shop_xxx.php?id=$id\"><img src=\"$thumb\"></td>
<td width=\"350\" valign=\"middle\"><a href=\"shop_xxx.php?id=$id\"><p><strong>$ProductName</strong></a></p></td>
<td width=\"72\" valign=\"middle\"><p class=\"body\"><strong> $Price</strong></p></td>
</tr>
</table>";
}
}

?>

this may help you on this problem, but i noticed some other stuff:
beware of code like this:

$id=$_GET['id'];

this is prone to a sql-injection attack !!!

btw: i also noticed this:

echo "<FORM method=\"POST\" action=\"viewbycat.php\">"

is not directly wrong ... but hard to read ( @ least for me )
i think its better to use

echo '<FORM method="POST" action="viewbycat.php">';

or ... when using variables

echo '<a href="shop_xxx.php?id='.$id.'"><p><strong>'.$ProductName.'</strong></a>';

mebleu

Thanks for your help!

That is exactly what I was looking for the code to do.

I just had to change the _category to category and it worked. Not sure why my database didn't like that?

Also I really appreciate your other pointers. This is my first database site so it's great to get any tips that will help!

masonite

So I am trying to do a sub query. This is the first time I have worked with databases. I got it to were it worked with the first set of ORs and variables but when I go to add in the second query it stops finding results when there should be results.

$results= mysql_query("SELECT * FROM Schools (SELECT *FROM Schools WHERE Western=
'$westernDisicipline' OR English= '$englishDisicipline' OR Fences='$fencesDisicipline'
(SELECT * FROM Schools WHERE NCAA= '$NCAAtype' OR IHSA='$IHSAtype' OR OTHER= '$OTHERtype'))");