SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 28
  1. #1
    SitePoint Zealot
    Join Date
    Sep 2010
    Posts
    106
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PHP/MySQL search - query builder not working

    Hi Folks,

    Can anyone help me figure out what I have done wrong here.
    I am trying to make a search system that builds a query according to the options selected by the user.

    As well as a text box ('search') the user can choose from 3 drop down menus:
    category
    location
    dealtype

    The default for each of the drop downs is 'Any' which I have given a null value "". So if the string length of any of category, location or dealtype is greater than 1 they should be added to the query.
    I'm in over my head here so any pointers would come in very handy.

    Right now I have this 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 '' at line 1"

    Any way, here's the code:

    --------------------------
    // search query builder starts
    if (isset($_GET['search'])){
    //set search variable
    $search=$_GET['search'];
    // reset search query clause variables
    $where_clauses= "";
    $location="";
    $category="";
    $dealtype="";

    // set search query variables with GET data
    if (isset($_GET['location'])){$location=$_GET['location'];}
    if (isset($_GET['category'])){$category=$_GET['category'];}
    if (isset($_GET['dealtype'])){$dealtype=$_GET['dealtype'];}

    //test for "any" (any is set to "" in every case)

    if (strlen($location >1)){
    $where_clauses .= "AND location ='.$location'. ','";
    }
    if (strlen($category >1)){
    $where_clauses .= "AND category ='.$category'. ','";
    }
    if (strlen($dealtype >1)){
    $where_clauses .= "AND dealtype ='.$dealtype'";
    }

    //construct query

    mysql_select_db($database_deals, $deals);
    $query_rs_deals = "SELECT * FROM deals WHERE description LIKE '$search'"."'$where_clauses'";
    $rs_deals = mysql_query($query_rs_deals, $deals) or die(mysql_error());
    $row_rs_deals = mysql_fetch_assoc($rs_deals);
    $totalRows_rs_deals = mysql_num_rows($rs_deals);
    }
    // search query builder ends

    --------------------------------

  2. #2
    SitePoint Enthusiast
    Join Date
    Jul 2006
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You have a few issues that I can see right off the bat.

    First, the first two $where_clauses end with a comma but they are not needed.
    Second, there are no spaces before each 'AND' in $where_clauses.
    Third, there are single quotes around $where_clauses in $query_rs_deals. Remove them.
    Fourth, strlen($location >1) is not valid. It should be strlen($location) >1

    EDIT: Sorry, also remove the '.' before $location, $category, $dealtype in each $where_clauses statement.

    Your code should look something like this:

    Code:
    if (strlen($location)>1){
    $where_clauses .= " AND location ='$location'";
    }
    if (strlen($category)>1){
    $where_clauses .= " AND category ='$category'";
    }
    if (strlen($dealtype)>1){
    $where_clauses .= " AND dealtype ='$dealtype'";
    }
    
    $query_rs_deals = "SELECT * FROM deals WHERE description LIKE '$search'".$where_clauses;
    Let us know if that still produces errors.

  3. #3
    Non-Member Max Height's Avatar
    Join Date
    Dec 2011
    Posts
    303
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)
    The error message is telling you that there are syntax errors in your sql query.

    Echo out the actual query being run and the syntax errors should be easily seen. Then just fix your php code to make sure it creates a properly syntaxed sql query.

    Code:
    $query_rs_deals = "SELECT * FROM deals WHERE description LIKE '$search'"."'$where_clauses'";
    
    echo $query_rs_deals;
    
    die();

  4. #4
    SitePoint Zealot
    Join Date
    Sep 2010
    Posts
    106
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys,

    I've made programthis changes (thanks programthis).
    I still have 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 '' at line 1

    WHen I try Max Height's suggestion I get this.

    SELECT * FROM deals WHERE description LIKE ''' AND location ='London' AND category ='Clothing' AND dealtype ='30% Discount''

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2006
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, the error now is that the search field is blank and you still have it in the SQL query. Try modifying your code as follows:

    // define $search to be empty by default
    $search = '';
    // only set $search to a string if $_GET['search'] is set
    if(isset($_GET['search'])){
    $search= " description LIKE '". $_GET['search'] . "'";
    }

    if (strlen($location)>1){
    // add 'AND' only if the search field was used
    if(strlen($search) >0)){
    $where_clauses .= ' AND ';
    }
    $where_clauses .= "location ='$location'";
    }
    if (strlen($category)>1){
    // add 'AND' only if the search field or location was selected
    if(strlen($search) >0 || strlen($location) >0){
    $where_clauses .= ' AND ';
    }
    $where_clauses .= "category ='$category'";
    }
    if (strlen($dealtype)>1){
    // add AND only if the search field, location or category were selected
    if(strlen($search) >0 || strlen($location) >0 || strlen($category) >0){
    $where_clauses .= ' AND ';
    }
    $where_clauses .= "dealtype ='$dealtype'";
    }

    $query_rs_deals = "SELECT * FROM deals WHERE ".$search.$where_clauses;

  6. #6
    Non-Member Max Height's Avatar
    Join Date
    Dec 2011
    Posts
    303
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)
    so now you can see the actual query your code generates.

    You need to fix the code that generates the LIKE value for the query and you have a mismatched quote at the end of the query.

    This looks like some sort of homework exercise?

  7. #7
    SitePoint Zealot
    Join Date
    Sep 2010
    Posts
    106
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay guys,

    back at it after a brief night's sleep . Feels like we are making progress.
    I have two options now, the original code with your embellishments and Max heights re-write. Here's where I am up to:

    --------------------------
    With the original code in place I now get this output when I echo out the SQL:

    SELECT * FROM deals WHERE description LIKE 'cut' AND location ='Brighton' AND category ='Hair and Beauty' AND dealtype ='BOGOFF'

    That all looks good to me. But when I comment out the echo sql line and run the page as normal I get this 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 '' at line 1

    --------------------------
    When run the page with Max Height's rewritten code I get the following errors:

    Notice: Undefined variable - on these lines
    47. if (strlen($location)>1){
    54. if (strlen($category)>1){
    61. if (strlen($dealtype)>1){

    The page stops loading at that point.
    Probably easy to fix I think but I will have to study it a bit cos I'm not sure what's going on in there yet.

    --------------------------

    Happy to pursue either option if anyone can see a way to make it work.
    Any ideas?

  8. #8
    Non-Member Max Height's Avatar
    Join Date
    Dec 2011
    Posts
    303
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by davecheet View Post
    back at it after a brief night's sleep .
    ...
    ...
    When run the page with Max Height's rewritten code I get the following errors:
    What rewritten code? All I have shown you is how to see what the actual query being run is.

    Maybe you need a little more sleep

  9. #9
    SitePoint Zealot
    Join Date
    Sep 2010
    Posts
    106
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OH yeah, it was Programthis who posted the new code. (You could be right about the sleep).
    Anyway, the original version is very close to working. In fact when I copy the SQl output form the page and paste it into phpmyadmin I get no error. Yet on the page I still get 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 '' at line 1
    Does that explain anything?

  10. #10
    SitePoint Zealot
    Join Date
    Sep 2010
    Posts
    106
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am truly out of ideas now.

    The SQL code generated by the query builder looks okay. Here's an example.

    SELECT * FROM deals WHERE description LIKE 'pizza' AND location ='Manchester' AND category ='Restaurants' AND dealtype ='free'

    But I still get this error on the page:
    I still get 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 '' at line 1

    Anyone any idea how that can happen?

  11. #11
    SitePoint Member
    Join Date
    Sep 2007
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As the query syntax looks correct then I guess you may need to check your table's name or field's name.

  12. #12
    SitePoint Zealot
    Join Date
    Sep 2010
    Posts
    106
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    They seem to be correct too? Anything else I should check for?

  13. #13
    SitePoint Zealot
    Join Date
    Sep 2010
    Posts
    106
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Also, if the error is near " then I guess it must be at the beginning or the end of the query. Any idea which of the two I should be focussing on?

  14. #14
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Just before you execute the query, var_dump the $variable holding the final composed query you're about to execute. Chances are, it's not what you expect it is.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  15. #15
    SitePoint Zealot
    Join Date
    Sep 2010
    Posts
    106
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Looks okay I think.

    string(161) "SELECT * FROM deals WHERE headline LIKE 'pizza' AND location = 'Manchester' AND category = 'restaurants' AND dealtype = 'halfprice'"

  16. #16
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Nope, that query looks fine. I think you're missing something somewhere, which I guess is why you're here.

    Are you sure that it's that query you're executing? Let's try something...

    Can you execute the query with this function please?

    Code PHP:
    <?php
    function execute_query($query){
    	if( ! ($result = mysql_query($query))){
    		throw new Exception(
    			sprintf('Error: %s SQL: %s', mysql_error(), $query),
    			mysql_errno()
    		);
    	}
    	return $result;
    }
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  17. #17
    SitePoint Zealot
    Join Date
    Sep 2010
    Posts
    106
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No change. Same error.
    By execute "execute the query with this function", I assume you meant paste that code into the page and load it up.
    (clearly out of my depth here!?)

  18. #18
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Nope.

    Place that piece of code at the top of the PHP script and instead of passing the query to mysql_query, pass it to execute_query.

    Don't worry, you'll get there.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  19. #19
    SitePoint Zealot
    Join Date
    Sep 2010
    Posts
    106
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think I did that right but no change. Did I do it right? here's the code I have now can you just let me know if I did the execute_query thing correctly?

    ------------------------------
    // search query builder starts

    // execute query starts
    function execute_query($query){
    if( ! ($result = mysql_query($query))){
    throw new Exception(
    sprintf('Error: %s SQL: %s', mysql_error(), $query),
    mysql_errno()
    );
    }
    return $result;
    }
    // execute query ends

    if (isset($_GET['search'])){

    //set search variable

    $search=$_GET['search'];

    // reset search query variables
    $where_clauses= "";
    $location="";
    $category="";
    $dealtype="";

    // set search query variables with GET data

    if (isset($_GET['location'])){$location=$_GET['location'];}
    if (isset($_GET['category'])){$category=$_GET['category'];}
    if (isset($_GET['dealtype'])){$dealtype=$_GET['dealtype'];}

    if (strlen($location)>1){
    $where_clauses .= " AND location = '$location'";
    }
    if (strlen($category)>1){
    $where_clauses .= " AND category = '$category'";
    }
    if (strlen($dealtype)>1){
    $where_clauses .= " AND dealtype = '$dealtype'";
    }
    $where_clauses = rtrim($where_clauses);

    //construct query
    mysql_select_db($database_deals, $deals);
    $query_rs_deals = "SELECT * FROM deals WHERE headline LIKE '$search' OR description LIKE '$search'".$where_clauses."";
    $rs_deals = execute_query($query_rs_deals, $deals) or die(mysql_error());
    $row_rs_deals = mysql_fetch_assoc($rs_deals);
    $totalRows_rs_deals = mysql_num_rows($rs_deals);
    }

    // search query builder ends
    ------------------------------

    PS - Happy New Year.

  20. #20
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Happy new year to you too.

    Barring passing the $deals connection along, yes, you're doing it correctly. You say "no change", does this mean you're still getting the error you previously mentioned?
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  21. #21
    SitePoint Zealot
    Join Date
    Sep 2010
    Posts
    106
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes exactly the same error.

  22. #22
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    I cant see how that's possible, unless the error is coming from another query? Can you post the entire, unaltered, script as it stands now in BBCODE tags please?
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  23. #23
    SitePoint Zealot
    Join Date
    Sep 2010
    Posts
    106
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Here it is

    Code:
    <?php require_once('Connections/conwydeals.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;
    }
    }
    if (!isset($_GET['search'])){
    mysql_select_db($database_conwydeals, $conwydeals);
    $query_rs_deals = "SELECT * FROM deals";
    $rs_deals = mysql_query($query_rs_deals, $conwydeals) or die(mysql_error());
    $row_rs_deals = mysql_fetch_assoc($rs_deals);
    $totalRows_rs_deals = mysql_num_rows($rs_deals);
    }
    // search query builder starts
    function execute_query($query){
    	if( ! ($result = mysql_query($query))){
    		throw new Exception(
    			sprintf('Error: %s SQL: %s', mysql_error(), $query),
    			mysql_errno()
    		);
    	}
    	return $result;
    }
    if (isset($_GET['search'])){
    //set search variable	
    $search=$_GET['search'];
    // reset search query variables
    $where_clauses= "";
    $location="";
    $category="";
    $dealtype="";
    // set search query variables with GET data
    if (isset($_GET['location'])){$location=$_GET['location'];}
    if (isset($_GET['category'])){$category=$_GET['category'];}
    if (isset($_GET['dealtype'])){$dealtype=$_GET['dealtype'];}
    
    if (strlen($location)>1){
    $where_clauses .= " AND location = '$location'";
    }
    if (strlen($category)>1){
    $where_clauses .= " AND category = '$category'";
    }
    if (strlen($dealtype)>1){
    $where_clauses .= " AND dealtype = '$dealtype'";
    }
    $where_clauses = rtrim($where_clauses);
    //construct query
    mysql_select_db($database_conwydeals, $conwydeals);
    $query_rs_deals = "SELECT * FROM deals WHERE headline LIKE '$search' OR description LIKE '$search'".$where_clauses."";
    $rs_deals = execute_query($query_rs_deals, $conwydeals) or die(mysql_error());
    $row_rs_deals = mysql_fetch_assoc($rs_deals);
    $totalRows_rs_deals = mysql_num_rows($rs_deals);
    }
    // search query builder ends
    
    mysql_select_db($database_conwydeals, $conwydeals);
    $query_rs_categories = "SELECT categoryname FROM categories ORDER BY categoryname ASC";
    $rs_categories = mysql_query($query_rs_categories, $conwydeals) or die(mysql_error());
    $row_rs_categories = mysql_fetch_assoc($rs_categories);
    $totalRows_rs_categories = mysql_num_rows($rs_categories);
    
    mysql_select_db($database_conwydeals, $conwydeals);
    $query_rs_locations = "SELECT * FROM location ORDER BY town ASC";
    $rs_locations = mysql_query($query_rs_locations, $conwydeals) or die(mysql_error());
    $row_rs_locations = mysql_fetch_assoc($rs_locations);
    $totalRows_rs_locations = mysql_num_rows($rs_locations);
    
    mysql_select_db($database_conwydeals, $conwydeals);
    $query_rs_dealtype = "SELECT * FROM dealtype ORDER BY type ASC";
    $rs_dealtype = mysql_query($query_rs_dealtype, $conwydeals) or die(mysql_error());
    $row_rs_dealtype = mysql_fetch_assoc($rs_dealtype);
    $totalRows_rs_dealtype = mysql_num_rows($rs_dealtype);
    ?>
    
    <!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>Latest Deals, Offers and Discounts in Conwy</title>
    <?php 
    //echo $query_rs_deals;
    //
    //die();
    ?>
    <link href="css/screen.css" rel="stylesheet" type="text/css" />
    <!-- DMXXzone scripts -->
    <script type="text/javascript" src="ScriptLibrary/jquery-latest.pack.js"></script>
    <!-- DMXzone scripts Ends -->
    <!-- Typekit Fonts Set -->
    <script type="text/javascript" src="http://use.typekit.com/ecq3igb.js"></script>
    <script type="text/javascript">try{Typekit.load();}catch(e){}</script>
    <!-- Typekit Fonts Ends -->
    <!-- menu scripts -->
    <link type="text/css" href="menu.css" rel="stylesheet" />
    <script type="text/javascript" src="jquery.js"></script>
    <script type="text/javascript" src="menu.js"></script>
    <script type="text/javascript" src="js/uniform.js"></script>
    <link rel="stylesheet" href="css/uniform.default.css" type="text/css" media="screen" charset="utf-8" />
    <!-- menu ends -->
    <!--[if IE 5]>
    <style type="text/css"> 
    /* place css box model fixes for IE 5* in this conditional comment */
    .twoColFixRt #sidebar1 { width: 220px; }
    </style>
    <![endif]--><!--[if IE]>
    <style type="text/css"> 
    /* place css fixes for all versions of IE in this conditional comment */
    .twoColFixRt #sidebar1 { padding-top: 30px; }
    .twoColFixRt #mainContent { zoom: 1; }
    /* the above proprietary zoom property gives IE the hasLayout it needs to avoid several bugs */
    </style>
    <![endif]-->
    
    <!-- Typekit Fonts Set -->
    <script type="text/javascript" src="http://use.typekit.com/ecq3igb.js"></script>
    <script type="text/javascript">try{Typekit.load();}catch(e){}</script>
    <!-- Typekit Fonts Ends -->
    
    </head>
    
    <body class="twoColFixRt">
    
    <p>&nbsp;</p>
    <div id="container">
    <div id="header">
    </div><!-- Header div ends -->
    <!-- Searchbox div ends -->
      <div id="sidebar1">
      <?php // include('includes/searchbox.php');?>
      <?php include('includes/sidebar.php');?>
        <!-- end #sidebar1 -->
    <div>
    <?php include('includes/topmenu.php'); ?>
    </div><!-- Topmenu div ends -->
    <div id="mainContent">
    <br />
    <h1>Latest Deals, Offers and Discounts in Conwy.</h1>
    <p>&nbsp;</p>
    <div id ="dealsearchwrapper">
    <p>
      <?php // include('includes/dealsearch.php');?>
      
      <script language="JavaScript">
    <!--
    $(function(){ $("select").uniform(); });;
    $("select, input:checkbox, input:file, input:radio, input:file input:select input:textarea").uniform();
    // -->
    </script>
    </p>
    <form action="deals7.php" method="get" enctype="multipart/form-data" name="frm_dealsearch" id="frm_dealsearch">
    <select name="location" id="location">
      <option value="">Anywhere in conwy</option>
      <?php
    do {  
    ?>
      <option value="<?php echo $row_rs_locations['town']?>"><?php echo $row_rs_locations['town']?></option>
      <?php
    } while ($row_rs_locations = mysql_fetch_assoc($rs_locations));
      $rows = mysql_num_rows($rs_locations);
      if($rows > 0) {
          mysql_data_seek($rs_locations, 0);
    	  $row_rs_locations = mysql_fetch_assoc($rs_locations);
      }
    ?>
      </select>
      <select name="dealtype" id="dealtype">
        <option value="">Any deals</option>
        <?php
    do {  
    ?>
        <option value="<?php echo $row_rs_dealtype['type']?>"><?php echo $row_rs_dealtype['type']?></option>
        <?php
    } while ($row_rs_dealtype = mysql_fetch_assoc($rs_dealtype));
      $rows = mysql_num_rows($rs_dealtype);
      if($rows > 0) {
          mysql_data_seek($rs_dealtype, 0);
    	  $row_rs_dealtype = mysql_fetch_assoc($rs_dealtype);
      }
    ?>
      </select>
      <select name="category" id="category">
        <option value="">All categories</option>
        <?php
    do {  
    ?>
        <option value="<?php echo $row_rs_categories['categoryname']?>"><?php echo $row_rs_categories['categoryname']?></option>
        <?php
    } while ($row_rs_categories = mysql_fetch_assoc($rs_categories));
      $rows = mysql_num_rows($rs_categories);
      if($rows > 0) {
          mysql_data_seek($rs_categories, 0);
    	  $row_rs_categories = mysql_fetch_assoc($rs_categories);
      }
    ?>
      </select>
    </p>
    <p>
      <input type="text" name="search" id="search" style="margin-left:5px; width:495px;" / >
      <input type="submit" name="submit" id="submit" value="Search" style="display:inline; margin-left:20px;"  />
    </form>
    <div class="clearleft">&nbsp;</div>
    </div><!-- dealsearchwrapper div ends -->
    <div id="alldealswrapper">
    <?php do { ?>
    <?php $business = $row_rs_deals['userref'];
    mysql_select_db($database_conwydeals, $conwydeals);
    $query_rs_business = "SELECT name FROM users WHERE id = $business";
    $rs_business = mysql_query($query_rs_business, $conwydeals) or die(mysql_error());
    $row_rs_business = mysql_fetch_assoc($rs_business);
    $totalRows_rs_business = mysql_num_rows($rs_business);
    ?>
    <!-- deal wrapper starts -->
    <div class="dealwrapper">
        <h2><?php echo $row_rs_deals['headline']; ?></h2>
        <h3><?php echo $row_rs_business['name']; ?></h3>
        <p><img src="images/deals/thumb_<?php echo $row_rs_deals['image']; ?>" alt="Great Deals and Discounts in Conwy" class="shadow" />
          <?php echo $row_rs_deals['description']; ?>
        </p>
        <div class="datestart"><span class="label">Deal Starts:</span><?php print strftime("%a %e %b",strtotime($row_rs_deals['datestart']));?></div><br />
        <div class="datefinish"><span class="label">Deal Ends:</span><?php print strftime("%a %e %b",strtotime($row_rs_deals['datefinish']));?></div>
        
        <div>
        <a href="deal.php?id=<?php echo $row_rs_deals['id']; ?>" class="more" >
        More Details Here
        </a>
        </div>
        <p>&nbsp;</p>
        <p>&nbsp;</p>
        <p class="clearer">&nbsp;</p>
    </div><!-- deal wrapper ends -->
      <?php } while ($row_rs_deals = mysql_fetch_assoc($rs_deals)); ?>
    </div><!-- all deals wrapper ends -->
      
    </div><!-- Main Content div ends -->
    <!-- This clearing element should immediately follow the #mainContent div in order to force the #container div to contain all child floats --><br class="clearfloat" />
    <!-- end #container --></div>
    </body>
    </html>
    <?php
    mysql_free_result($rs_deals);
    
    mysql_free_result($rs_categories);
    
    mysql_free_result($rs_locations);
    
    mysql_free_result($rs_dealtype);
    ?>

  24. #24
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Try running this...

    Code PHP:
    <?php require_once('Connections/conwydeals.php');
    function execute_query($query, $connection = null){
    	if( ! ($result = mysql_query($query, $connection))){
    		throw new Exception(
    			sprintf('Error: %s SQL: %s', mysql_error(), $query),
    			mysql_errno()
    		);
    	}
    	return $result;
    }
    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;
    }
    }
    if (!isset($_GET['search'])){
    mysql_select_db($database_conwydeals, $conwydeals);
    $query_rs_deals = "SELECT * FROM deals";
    $rs_deals = execute_query($query_rs_deals, $conwydeals) or die(mysql_error());
    $row_rs_deals = mysql_fetch_assoc($rs_deals);
    $totalRows_rs_deals = mysql_num_rows($rs_deals);
    }
    // search query builder starts
    if (isset($_GET['search'])){
    //set search variable	
    $search=$_GET['search'];
    // reset search query variables
    $where_clauses= "";
    $location="";
    $category="";
    $dealtype="";
    // set search query variables with GET data
    if (isset($_GET['location'])){$location=$_GET['location'];}
    if (isset($_GET['category'])){$category=$_GET['category'];}
    if (isset($_GET['dealtype'])){$dealtype=$_GET['dealtype'];}
     
     
    if (strlen($location)>1){
    $where_clauses .= " AND location = '$location'";
    }
    if (strlen($category)>1){
    $where_clauses .= " AND category = '$category'";
    }
    if (strlen($dealtype)>1){
    $where_clauses .= " AND dealtype = '$dealtype'";
    }
    $where_clauses = rtrim($where_clauses);
    //construct query
    mysql_select_db($database_conwydeals, $conwydeals);
    $query_rs_deals = "SELECT * FROM deals WHERE headline LIKE '$search' OR description LIKE '$search'".$where_clauses."";
    $rs_deals = execute_query($query_rs_deals, $conwydeals) or die(mysql_error());
    $row_rs_deals = mysql_fetch_assoc($rs_deals);
    $totalRows_rs_deals = mysql_num_rows($rs_deals);
    }
    // search query builder ends
     
     
    mysql_select_db($database_conwydeals, $conwydeals);
    $query_rs_categories = "SELECT categoryname FROM categories ORDER BY categoryname ASC";
    $rs_categories = execute_query($query_rs_categories, $conwydeals) or die(mysql_error());
    $row_rs_categories = mysql_fetch_assoc($rs_categories);
    $totalRows_rs_categories = mysql_num_rows($rs_categories);
     
     
    mysql_select_db($database_conwydeals, $conwydeals);
    $query_rs_locations = "SELECT * FROM location ORDER BY town ASC";
    $rs_locations = execute_query($query_rs_locations, $conwydeals) or die(mysql_error());
    $row_rs_locations = mysql_fetch_assoc($rs_locations);
    $totalRows_rs_locations = mysql_num_rows($rs_locations);
     
     
    mysql_select_db($database_conwydeals, $conwydeals);
    $query_rs_dealtype = "SELECT * FROM dealtype ORDER BY type ASC";
    $rs_dealtype = execute_query($query_rs_dealtype, $conwydeals) or die(mysql_error());
    $row_rs_dealtype = mysql_fetch_assoc($rs_dealtype);
    $totalRows_rs_dealtype = mysql_num_rows($rs_dealtype);
    ?>
     
     
    <!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>Latest Deals, Offers and Discounts in Conwy</title>
    <?php 
    //echo $query_rs_deals;
    //
    //die();
    ?>
    <link href="css/screen.css" rel="stylesheet" type="text/css" />
    <!-- DMXXzone scripts -->
    <script type="text/javascript" src="ScriptLibrary/jquery-latest.pack.js"></script>
    <!-- DMXzone scripts Ends -->
    <!-- Typekit Fonts Set -->
    <script type="text/javascript" src="http://use.typekit.com/ecq3igb.js"></script>
    <script type="text/javascript">try{Typekit.load();}catch(e){}</script>
    <!-- Typekit Fonts Ends -->
    <!-- menu scripts -->
    <link type="text/css" href="menu.css" rel="stylesheet" />
    <script type="text/javascript" src="jquery.js"></script>
    <script type="text/javascript" src="menu.js"></script>
    <script type="text/javascript" src="js/uniform.js"></script>
    <link rel="stylesheet" href="css/uniform.default.css" type="text/css" media="screen" charset="utf-8" />
    <!-- menu ends -->
    <!--[if IE 5]>
    <style type="text/css"> 
    /* place css box model fixes for IE 5* in this conditional comment */
    .twoColFixRt #sidebar1 { width: 220px; }
    </style>
    <![endif]--><!--[if IE]>
    <style type="text/css"> 
    /* place css fixes for all versions of IE in this conditional comment */
    .twoColFixRt #sidebar1 { padding-top: 30px; }
    .twoColFixRt #mainContent { zoom: 1; }
    /* the above proprietary zoom property gives IE the hasLayout it needs to avoid several bugs */
    </style>
    <![endif]-->
     
     
    <!-- Typekit Fonts Set -->
    <script type="text/javascript" src="http://use.typekit.com/ecq3igb.js"></script>
    <script type="text/javascript">try{Typekit.load();}catch(e){}</script>
    <!-- Typekit Fonts Ends -->
     
     
    </head>
     
     
    <body class="twoColFixRt">
     
     
    <p>&nbsp;</p>
    <div id="container">
    <div id="header">
    </div><!-- Header div ends -->
    <!-- Searchbox div ends -->
      <div id="sidebar1">
      <?php // include('includes/searchbox.php');?>
      <?php include('includes/sidebar.php');?>
        <!-- end #sidebar1 -->
    <div>
    <?php include('includes/topmenu.php'); ?>
    </div><!-- Topmenu div ends -->
    <div id="mainContent">
    <br />
    <h1>Latest Deals, Offers and Discounts in Conwy.</h1>
    <p>&nbsp;</p>
    <div id ="dealsearchwrapper">
    <p>
      <?php // include('includes/dealsearch.php');?>
     
      <script language="JavaScript">
    <!--
    $(function(){ $("select").uniform(); });;
    $("select, input:checkbox, input:file, input:radio, input:file input:select input:textarea").uniform();
    // -->
    </script>
    </p>
    <form action="deals7.php" method="get" enctype="multipart/form-data" name="frm_dealsearch" id="frm_dealsearch">
    <select name="location" id="location">
      <option value="">Anywhere in conwy</option>
      <?php
    do {  
    ?>
      <option value="<?php echo $row_rs_locations['town']?>"><?php echo $row_rs_locations['town']?></option>
      <?php
    } while ($row_rs_locations = mysql_fetch_assoc($rs_locations));
      $rows = mysql_num_rows($rs_locations);
      if($rows > 0) {
          mysql_data_seek($rs_locations, 0);
    	  $row_rs_locations = mysql_fetch_assoc($rs_locations);
      }
    ?>
      </select>
      <select name="dealtype" id="dealtype">
        <option value="">Any deals</option>
        <?php
    do {  
    ?>
        <option value="<?php echo $row_rs_dealtype['type']?>"><?php echo $row_rs_dealtype['type']?></option>
        <?php
    } while ($row_rs_dealtype = mysql_fetch_assoc($rs_dealtype));
      $rows = mysql_num_rows($rs_dealtype);
      if($rows > 0) {
          mysql_data_seek($rs_dealtype, 0);
    	  $row_rs_dealtype = mysql_fetch_assoc($rs_dealtype);
      }
    ?>
      </select>
      <select name="category" id="category">
        <option value="">All categories</option>
        <?php
    do {  
    ?>
        <option value="<?php echo $row_rs_categories['categoryname']?>"><?php echo $row_rs_categories['categoryname']?></option>
        <?php
    } while ($row_rs_categories = mysql_fetch_assoc($rs_categories));
      $rows = mysql_num_rows($rs_categories);
      if($rows > 0) {
          mysql_data_seek($rs_categories, 0);
    	  $row_rs_categories = mysql_fetch_assoc($rs_categories);
      }
    ?>
      </select>
    </p>
    <p>
      <input type="text" name="search" id="search" style="margin-left:5px; width:495px;" / >
      <input type="submit" name="submit" id="submit" value="Search" style="display:inline; margin-left:20px;"  />
    </form>
    <div class="clearleft">&nbsp;</div>
    </div><!-- dealsearchwrapper div ends -->
    <div id="alldealswrapper">
    <?php do { ?>
    <?php $business = $row_rs_deals['userref'];
    mysql_select_db($database_conwydeals, $conwydeals);
    $query_rs_business = "SELECT name FROM users WHERE id = $business";
    $rs_business = execute_query($query_rs_business, $conwydeals) or die(mysql_error());
    $row_rs_business = mysql_fetch_assoc($rs_business);
    $totalRows_rs_business = mysql_num_rows($rs_business);
    ?>
    <!-- deal wrapper starts -->
    <div class="dealwrapper">
        <h2><?php echo $row_rs_deals['headline']; ?></h2>
        <h3><?php echo $row_rs_business['name']; ?></h3>
        <p><img src="images/deals/thumb_<?php echo $row_rs_deals['image']; ?>" alt="Great Deals and Discounts in Conwy" class="shadow" />
          <?php echo $row_rs_deals['description']; ?>
        </p>
        <div class="datestart"><span class="label">Deal Starts:</span><?php print strftime("%a %e %b",strtotime($row_rs_deals['datestart']));?></div><br />
        <div class="datefinish"><span class="label">Deal Ends:</span><?php print strftime("%a %e %b",strtotime($row_rs_deals['datefinish']));?></div>
     
        <div>
        <a href="deal.php?id=<?php echo $row_rs_deals['id']; ?>" class="more" >
        More Details Here
        </a>
        </div>
        <p>&nbsp;</p>
        <p>&nbsp;</p>
        <p class="clearer">&nbsp;</p>
    </div><!-- deal wrapper ends -->
      <?php } while ($row_rs_deals = mysql_fetch_assoc($rs_deals)); ?>
    </div><!-- all deals wrapper ends -->
     
    </div><!-- Main Content div ends -->
    <!-- This clearing element should immediately follow the #mainContent div in order to force the #container div to contain all child floats --><br class="clearfloat" />
    <!-- end #container --></div>
    </body>
    </html>
    <?php
    mysql_free_result($rs_deals);
     
     
    mysql_free_result($rs_categories);
     
     
    mysql_free_result($rs_locations);
     
     
    mysql_free_result($rs_dealtype);
    ?>
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  25. #25
    SitePoint Zealot
    Join Date
    Sep 2010
    Posts
    106
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Something New !!!
    A new error

    Fatal error: Uncaught exception 'Exception' with message '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 '' at line 1 SQL: SELECT name FROM users WHERE id = ' in /Applications/MAMP/htdocs/Conwydeals/deals7B.php:4 Stack trace: #0 /Applications/MAMP/htdocs/Conwydeals/deals7.php(243): execute_query('SELECT name FRO...', Resource id #3) #1 {main} thrown in /Applications/MAMP/htdocs/Conwydeals/deals7B.php on line 4

    thanks, I reckon this will give me something to go on.

    Dave


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •