[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

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
0
BrighteyesDesign
Asked:
BrighteyesDesign
  • 4
  • 4
2 Solutions
 
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
 
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now