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_Leyla, $Leyla);
$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 &amp;  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&amp;type=1" type="text/javascript"></script>
                    Total:
                    <script language="JavaScript" src="http://www.romancart.com/cartinfo.asp?storeid=50225&amp;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>&nbsp;</td>");
    $rs_items_endRow++;
}
echo("</tr>");
}?>
          </table></td>
        </tr>
        <tr>
          <td>&nbsp;</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 &amp; 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);
?>

Open in new window

Picture-5.png
Picture-4.png
Picture-6.png
BrighteyesDesignAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jmyeomCommented:
$size = $_GET["size"];
if($size == 'ANY'){   //Where Any just takes on a value of ANY.
  $size_query = "size LIKE '%'" ;
} else {
  $size_query = "size = '$size'" ;
}


should ANY not be ALL ?

in the options you have the option all, not any


also, FROM rs_size WHERE style = '' = '%%'ORDER BY price ASC

in your html, you "ALL" option is = in value to "" meaning, the all section does not have a value, maybe thats why its messing up?


just a few points
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BrighteyesDesignAuthor Commented:
Hi jmyeom, thanks for responding.

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_Leyla, $Leyla);
$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_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);

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
0
BrighteyesDesignAuthor Commented:
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)?
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

birwinCommented:
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";
0
BrighteyesDesignAuthor Commented:
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_Leyla, $Leyla);
$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_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);

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?
0
birwinCommented:
If the code isn't live put
 echo $query_rs_items;
under the query. Run the page, copy the result, and post it.
If the site is live add
echo "
 
<!-- $query_rs_items --->
 
";
(the line feeds are deliberate)
Then view the page source and copy the query and post it.
 
0
birwinCommented:
Actually that should read:

echo "<!--
$query_rs_items
--->";  
(the line feeds are deliberate)
0
BrighteyesDesignAuthor Commented:
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_Leyla, $Leyla);
$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!
0
birwinCommented:
I see the error.
Remove the = from the query
$size_query '%" . $size . "%'ORDER BY price ASC";
the = or LIKE is in the variable.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.