Using table lists in drop down query
I have a search function setup for a jewellry website which allows the user to search by colour, type and size. The colour and type is fine.
With the size query i have a database setup with just the ring sizes. The main product database has a 'sizes' coloumn where sizes have been added in the format L, M, N, O (for example).
I need the search function the query what is selected against the 'sizes' column of the main product table then it turn showing the matching results.
The form side i'm sure is ok as the size parameter is included in the url http://www.leylasbijoux.com/jewelleryrings.php?style=ANY&colour=ANY&size=M&submit.x=35&submit.y=12
On the results page I have tried the following:
$size = $_GET["size"];
if($size == 'ANY'){ //Where Any just takes on a value of ANY.
$size_query = "size LIKE '%'" ;
} else {
$size_query = "size = '$size'" ;
}
mysql_select_db($database_
$query_rs_items = "SELECT name, image, id, name, price FROM products WHERE $colour_query AND FROM rs_size WHERE $size_query = '%" . $size . "%'ORDER BY price ASC";
And the following error displays:
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 'FROM rs_size WHERE style = '' = '%%'ORDER BY price ASC' at line 1
Here's the work in progress url http://www.leylasbijoux.com/jewellery.php
Picture-4.png
Picture-6.png
With the size query i have a database setup with just the ring sizes. The main product database has a 'sizes' coloumn where sizes have been added in the format L, M, N, O (for example).
I need the search function the query what is selected against the 'sizes' column of the main product table then it turn showing the matching results.
The form side i'm sure is ok as the size parameter is included in the url http://www.leylasbijoux.com/jewelleryrings.php?style=ANY&colour=ANY&size=M&submit.x=35&submit.y=12
On the results page I have tried the following:
$size = $_GET["size"];
if($size == 'ANY'){ //Where Any just takes on a value of ANY.
$size_query = "size LIKE '%'" ;
} else {
$size_query = "size = '$size'" ;
}
mysql_select_db($database_
$query_rs_items = "SELECT name, image, id, name, price FROM products WHERE $colour_query AND FROM rs_size WHERE $size_query = '%" . $size . "%'ORDER BY price ASC";
And the following error displays:
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 'FROM rs_size WHERE style = '' = '%%'ORDER BY price ASC' at line 1
Here's the work in progress url http://www.leylasbijoux.com/jewellery.php
<?php require_once('Connections/Leyla.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
}
$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}
$colour = $_GET["colour"];
if($colour == 'ANY'){ //Where Any just takes on a value of ANY.
$colour_query = "colour LIKE '%'" ;
} else {
$colour_query = "colour = '$colour'" ;
}
$style = $_GET["style"];
if($style == 'ANY'){ //Where Any just takes on a value of ANY.
$style_query = "style LIKE '%'" ;
} else {
$style_query = "style = '$style'" ;
}
$size = $_GET["size"];
if($size == 'ANY'){ //Where Any just takes on a value of ANY.
$size_query = "size LIKE '%'" ;
} else {
$size_query = "size = '$size'" ;
}
mysql_select_db($database_Leyla, $Leyla);
$query_rs_items = "SELECT name, image, id, name, price FROM products WHERE $colour_query AND FROM rs_size WHERE $style_query = '%" . $size . "%'ORDER BY price ASC";
$rs_items = mysql_query($query_rs_items, $Leyla) or die(mysql_error());
$row_rs_items = mysql_fetch_assoc($rs_items);
$totalRows_rs_items = mysql_num_rows($rs_items);
mysql_select_db($database_Leyla, $Leyla);
$query_rs_style = "SELECT * FROM ringstyle";
$rs_style = mysql_query($query_rs_style, $Leyla) or die(mysql_error());
$row_rs_style = mysql_fetch_assoc($rs_style);
$totalRows_rs_style = mysql_num_rows($rs_style);
mysql_select_db($database_Leyla, $Leyla);
$query_rs_colour = "SELECT * FROM colour";
$rs_colour = mysql_query($query_rs_colour, $Leyla) or die(mysql_error());
$row_rs_colour = mysql_fetch_assoc($rs_colour);
$totalRows_rs_colour = mysql_num_rows($rs_colour);
mysql_select_db($database_Leyla, $Leyla);
$query_rs_size = "SELECT * FROM ringsize";
$rs_size = mysql_query($query_rs_size, $Leyla) or die(mysql_error());
$row_rs_size = mysql_fetch_assoc($rs_size);
$totalRows_rs_size = mysql_num_rows($rs_size);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Cocktail Rings, Earings, Bracelets & Jewellry</title>
<script src="Scripts/swfobject_modified.js" type="text/javascript"></script>
<link href="leyla.css" rel="stylesheet" type="text/css" />
</head>
<body>
<table width="900" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td align="center"><img src="images/leya-logo.jpg" width="242" height="100" alt="Leya Bijoux Logo" /></td>
</tr>
<tr>
<td align="center"><table width="209" height="10" border="0" cellpadding="0" cellspacing="0">
<tr>
<td><!-- saved from url=(0014)about:internet -->
<div id='menu' style='display:none;'>
<div><a href='index.html'>HOME</a></div>
<div><a href='jewellery.php'>RINGS</a></div>
<div><a href='bracelets.php'>BRACELETS</a></div>
<div><a href='earrings.php'>EAR RINGS</a></div>
<div><a href='necklaces.php'>NECKLACES</a></div>
<div><a href='bangles.php'>BANGLES</a></div>
<div><a href='about.php'>ABOUT LEYLA</a></div>
<div><a href='faqs.php'>FAQ%5C%27S</a></div>
<div><a href='contact.php'>CONTACT</a></div>
</div>
<div id="f_source_navigation" style="position:relative; top:0px; left:0px; width:928px;">
<!-- Don't change any part of the DIV code (Press Ctrl+Shift+F to edit) -->
<script src='Menu/MenuScript.js' type='text/javascript'></script>
<!-- saved from url=(0014)about:internet -->
<!-- Flash Object code (EMBED tag will be generated automatically) -->
<div id="f-source-menu" style="position:relative;" onmouseover="if(!ied){i= fdiv.style.height.indexOf('px');h=Number(fdiv.style.height.substring(0,i));fdiv.style.height=h+1+'px';}">
<object classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=9,0,28,0" id="Menu" width="928" height="33" >
<param name="movie" value="Menu/menu.swf" />
<param name="quality" value="high" />
<param name="wmode" value="transparent" />
<param name="FlashVars" value="flashlet={bg_Pic_URL:'None',xml_Path:'',stretch_width_to:'928px',_TransparencyShadow:30,_removeSubDelay:500,_minSubWidth:60,_max_height:550,_removeDividers:false,showSearchField:false,searchFieldWidth:80,searchTextColor:#000000,searchFieldColor:#FFFFFF,mainSoundURL:'',clickSoundURL:'',subSoundURL:'',_mainButtonHeight:28,_menuColor:#DDDDDD,_mainFont:'Times Roman',_mainFontSize:10,_mainButtonTextColor:#5e5e5e,_mainButWidthExt:6,_mainHighlightColor:#CCCCCC,_TransparencyMain:100,_subButtonHeight:20,_subMenuColor:#FEFEFE,_subFont:'Times New Roman',_subFontSize:10,_subButtonTextColor:#333333,_subHighlightColor:#999999,_TransparencySub:100,_pos_type:'relative',_pos_centered:false,_pos_top:0,_pos_left:0,_pos_insert:'the insertion point',_file_folder:'Menu'}" />
<noscript>
The <a href="http://f-source.com/">flash menu</a> provided by f-source.com. All Rights Reserved
</noscript>
</object>
<script type="text/javascript">var connected; var message="Hello!"; if (connected){ Run_f_source_menu() }else{ alert("The file MenuScript.js could not be loaded!") }</script>
</div>
</div></td>
</tr>
</table></td>
</tr>
<tr>
<td><div>
<table width="930" border="0" cellspacing="0" cellpadding="0">
<tr>
<td align="center"><table width="929" border="0" cellpadding="0" cellspacing="0" class="querybar">
<tr>
<td width="122" bgcolor="#BBCACC" class="type"><table width="109" height="18" border="0" cellpadding="0" cellspacing="0" class="type">
<tr>
<td><?php echo $row_rs_items['type']; ?></td>
</tr>
</table></td>
<td width="598" align="center" bgcolor="#BACACC"><form id="form1" name="form1" action="jewelleryrings.php" method="get">
<table width="560" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="518" align="center" valign="middle"><p class="black">Ring type
<select name="style" class="black" id="style">
<option value="" <?php if (!(strcmp("", $row_rs_style['style']))) {echo "selected=\"selected\"";} ?>>All</option>
<?php
do {
?>
<option value="<?php echo $row_rs_style['style']?>"<?php if (!(strcmp($row_rs_style['style'], $row_rs_style['style']))) ?>><?php echo $row_rs_style['style']?></option>
<?php
} while ($row_rs_style = mysql_fetch_assoc($rs_style));
$rows = mysql_num_rows($rs_style);
if($rows > 0) {
mysql_data_seek($rs_style, 0);
$row_rs_style = mysql_fetch_assoc($rs_style);
}
?>
</select>
Colour
<select name="colour" class="black" id="colour">
<option value="" <?php if (!(strcmp("", $row_rs_colour['colour']))) {echo "selected=\"selected\"";} ?>>All</option>
<?php
do {
?>
<option value="<?php echo $row_rs_colour['colour']?>"<?php if (!(strcmp($row_rs_colour['colour'], $row_rs_colour['colour']))) ?>><?php echo $row_rs_colour['colour']?></option>
<?php
} while ($row_rs_colour = mysql_fetch_assoc($rs_colour));
$rows = mysql_num_rows($rs_colour);
if($rows > 0) {
mysql_data_seek($rs_colour, 0);
$row_rs_colour = mysql_fetch_assoc($rs_colour);
}
?>
</select>
Size
<select name="size" class="black" id="size">
<option value="" <?php if (!(strcmp("", $row_rs_size['size']))) {echo "selected=\"selected\"";} ?>>All</option>
<?php
do {
?>
<option value="<?php echo $row_rs_size['size']?>"<?php if (!(strcmp($row_rs_size['size'], $row_rs_size['size']))) ?>><?php echo $row_rs_size['size']?></option>
<?php
} while ($row_rs_size = mysql_fetch_assoc($rs_size));
$rows = mysql_num_rows($rs_size);
if($rows > 0) {
mysql_data_seek($rs_size, 0);
$row_rs_size = mysql_fetch_assoc($rs_size);
}
?>
</select>
Price Range
<select name="range" class="black" id="range">
<option value="0-50">Less than £50</option>
<option value="51-100">£51 - £100</option>
<option value="101-200">£101-£200</option>
<option value="201-300">£201 - £300</option>
<option value="300-99999999">£300 +</option>
<option selected="selected">All</option>
</select>
</p></td>
<td width="62" align="left" valign="middle"><span class="black">
<input name="submit" type="image" id="submit" src="images/search.png" alt="Add to Basket" />
</span></td>
</tr>
</table>
</form></td>
<td width="209" bgcolor="#BACACC"><table width="237" border="0" cellpadding="0" cellspacing="0" class="checkoutbar">
<tr>
<td align="right" class="black">No of items:
<script language="JavaScript" src="http://www.romancart.com/cartinfo.asp?storeid=50225&type=1" type="text/javascript"></script>
Total:
<script language="JavaScript" src="http://www.romancart.com/cartinfo.asp?storeid=50225&type=2" type="text/javascript"></script>
<a href="http://www.romancart.com/cart.asp?storeid=50225" class="price">Checkout</a></td>
<td align="right"><img src="images/bag.png" alt="checkout" width="35" height="33" border="0" usemap="#Map4" /></td>
</tr>
</table>
<map name="Map4" id="Map42">
<area shape="rect" coords="9,4,28,29" href="#" />
</map>
<map name="Map4" id="Map4">
<area shape="rect" coords="9,4,28,29" href="#" />
</map></td>
</tr>
</table></td>
</tr>
<tr>
<td><table cellpadding="0" cellspacing="1" >
<tr>
<?php
$rs_items_endRow = 0;
$rs_items_columns = 4; // number of columns
$rs_items_hloopRow1 = 0; // first row flag
do {
if($rs_items_endRow == 0 && $rs_items_hloopRow1++ != 0) echo "<tr>";
?>
<td><table cellpadding="0" cellspacing="0" >
<tr>
<td><table border="0" cellpadding="0" cellspacing="0" class="frame">
<tr>
<td height="166" align="center" valign="middle"><table width="230" border="0" cellpadding="0" cellspacing="0" class="prodholder">
<tr>
<td align="center" valign="middle"><a href="product.php?id=<?php echo $row_rs_items['id']; ?>""><img src="uploadsmall/<?php echo $row_rs_items['image']; ?>" alt="" border="0" /></a></td>
</tr>
</table>
<a href="product.php"></a></td>
</tr>
<tr>
<td><table width="228" border="0" cellpadding="5" cellspacing="0" class="h40">
<tr>
<td valign="top" class="prodtitle"><table width="220" height="16" border="0" cellpadding="0" cellspacing="0">
<tr>
<td><span class="name"><?php echo $row_rs_items['name']; ?></span>
<input name="id" type="hidden" id="id" value="<?php echo $row_rs_items['id']; ?>" />
<br />
<span class="price">£<?php echo $row_rs_items['price']; ?></span></td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table></td>
<?php $rs_items_endRow++;
if($rs_items_endRow >= $rs_items_columns) {
?>
</tr>
<?php
$rs_items_endRow = 0;
}
} while ($row_rs_items = mysql_fetch_assoc($rs_items));
if($rs_items_endRow != 0) {
while ($rs_items_endRow < $rs_items_columns) {
echo("<td> </td>");
$rs_items_endRow++;
}
echo("</tr>");
}?>
</table></td>
</tr>
<tr>
<td> </td>
</tr>
</table>
</div></td>
</tr>
<tr>
<td><table width="410" border="0" cellpadding="10" cellspacing="0">
<tr>
<td class="copyright">© 2010 Leylas Design : Terms & conditions : Delivery</td>
</tr>
</table></td>
</tr>
</table>
</body>
</html>
<?php
mysql_free_result($rs_items);
mysql_free_result($rs_style);
mysql_free_result($rs_colour);
mysql_free_result($rs_size);
?>
Picture-4.png
Picture-6.png
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Also, in the query:
$query_rs_items = "SELECT name, image, id, name, price FROM products WHERE $colour_query AND $style_query FROM rs_size WHERE $size_query = '%" . $size . "%'ORDER BY price ASC";
I have put FROM rs_size which is the recordset name. should this be the Recordset name or table name (ringsize)?
$query_rs_items = "SELECT name, image, id, name, price FROM products WHERE $colour_query AND $style_query FROM rs_size WHERE $size_query = '%" . $size . "%'ORDER BY price ASC";
I have put FROM rs_size which is the recordset name. should this be the Recordset name or table name (ringsize)?
I didn't read your full snippet, but from your description, shouldn't
$size = $_GET["size"];
if($size == 'ANY'){ //Where Any just takes on a value of ANY.
$size_query = "size LIKE '%'" ;
} else {
$size_query = "size = '$size'" ;
}
be
$size = $_GET["size"];
if($size == 'ANY'){ //Where Any just takes on a value of ANY.
$size_query = "size LIKE" ;
} else {
$size_query = "size =";
}
Since your query is
$size_query = '%" . $size . "%'ORDER BY price ASC";
Which with your current code would translate to:
size LIKE '%'= '%" . $size . "%'ORDER BY price ASC";
$size = $_GET["size"];
if($size == 'ANY'){ //Where Any just takes on a value of ANY.
$size_query = "size LIKE '%'" ;
} else {
$size_query = "size = '$size'" ;
}
be
$size = $_GET["size"];
if($size == 'ANY'){ //Where Any just takes on a value of ANY.
$size_query = "size LIKE" ;
} else {
$size_query = "size =";
}
Since your query is
$size_query = '%" . $size . "%'ORDER BY price ASC";
Which with your current code would translate to:
size LIKE '%'= '%" . $size . "%'ORDER BY price ASC";
ASKER
Hi birwin,
I changed the code to:
$size = $_GET["size"];
if($size == 'ANY'){ //Where Any just takes on a value of ANY.
$size_query = "size LIKE" ;
} else {
$size_query = "size =";
}
mysql_select_db($database_
$query_rs_items = "SELECT name, image, id, name, price FROM products WHERE $colour_query FROM ringsize WHERE $size_query = '%" . $size . "%'ORDER BY price ASC";
mysql_select_db($database_
$query_rs_size = "SELECT * FROM ringsize";
$rs_size = mysql_query($query_rs_size
$row_rs_size = mysql_fetch_assoc($rs_size
$totalRows_rs_size = mysql_num_rows($rs_size);
But the error:
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 'FROM ringsize WHERE size = = '%M%'ORDER BY price ASC' at line 1
displayed, any ides?
I changed the code to:
$size = $_GET["size"];
if($size == 'ANY'){ //Where Any just takes on a value of ANY.
$size_query = "size LIKE" ;
} else {
$size_query = "size =";
}
mysql_select_db($database_
$query_rs_items = "SELECT name, image, id, name, price FROM products WHERE $colour_query FROM ringsize WHERE $size_query = '%" . $size . "%'ORDER BY price ASC";
mysql_select_db($database_
$query_rs_size = "SELECT * FROM ringsize";
$rs_size = mysql_query($query_rs_size
$row_rs_size = mysql_fetch_assoc($rs_size
$totalRows_rs_size = mysql_num_rows($rs_size);
But the error:
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 'FROM ringsize WHERE size = = '%M%'ORDER BY price ASC' at line 1
displayed, any ides?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Actually that should read:
echo "<!--
$query_rs_items
--->";
(the line feeds are deliberate)
echo "<!--
$query_rs_items
--->";
(the line feeds are deliberate)
ASKER
Sussed it!
it should be:
$size = $_GET["size"];
if($size == 'ANY'){ //Where Any just takes on a value of ANY.
$size_query = "size LIKE '%'" ;
} else {
$size_query = "size LIKE '%$size%'" ;
}
mysql_select_db($database_
$query_rs_items = "SELECT name, image, id, name, price FROM products WHERE $colour_query AND $style_query AND $size_query ORDER BY price ASC";
All comments helped to get there though hence the points!
it should be:
$size = $_GET["size"];
if($size == 'ANY'){ //Where Any just takes on a value of ANY.
$size_query = "size LIKE '%'" ;
} else {
$size_query = "size LIKE '%$size%'" ;
}
mysql_select_db($database_
$query_rs_items = "SELECT name, image, id, name, price FROM products WHERE $colour_query AND $style_query AND $size_query ORDER BY price ASC";
All comments helped to get there though hence the points!
I see the error.
Remove the = from the query
$size_query '%" . $size . "%'ORDER BY price ASC";
the = or LIKE is in the variable.
Remove the = from the query
$size_query '%" . $size . "%'ORDER BY price ASC";
the = or LIKE is in the variable.
ASKER
ANY is the value when 'All' is selected. That parts works ok with colour and type so i think that's probably ok.
It's somewhere with this part:
$size = $_GET["size"];
if($size == 'ANY'){ //Where Any just takes on a value of ANY.
$size_query = "size LIKE '%'" ;
} else {
$size_query = "size = '$size'" ;
}
mysql_select_db($database_
$query_rs_items = "SELECT name, image, id, name, price FROM products WHERE $colour_query AND $style_query FROM rs_size WHERE $size_query = '%" . $size . "%'ORDER BY price ASC";
mysql_select_db($database_
$query_rs_size = "SELECT * FROM ringsize";
$rs_size = mysql_query($query_rs_size
$row_rs_size = mysql_fetch_assoc($rs_size
$totalRows_rs_size = mysql_num_rows($rs_size);
In my initial code there was a slight error i used ry = '%" . $style . "%'OR which has now been changed to ry = '%" . $size . "%'OR
The error message is now:
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 'FROM rs_size WHERE size = 'M' = '%M%'ORDER BY price ASC' at line 1
Thanks again