SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2008
    Location
    Hessle, East Yorkshire UK
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem with dates query

    Hi all

    I have the following query:

    Code:
    $querystring = '
    select offers.id AS offersID, offers_to_category.id, offers.title, DATE_FORMAT(offers.date_expire, "%d-%m-%Y") as date_expire, DATE_FORMAT(offers.date_activated, "%d-%m-%Y") as date_activated, offers.description, offers.offerLink, merchant.name, merchant.link, category.catTitle, offers_to_category.category_fk
    	from offers 
    		inner join merchant on offers.merchant=merchant.id 
    		inner join offers_to_category on offers.id=offers_to_category.offer_fk
    		inner join category on offers_to_category.category_fk=category.id
    WHERE date_expire>=now() AND date_activated<=now() AND 1=0';
    
    foreach($_GET['checkboxes'] as $key => $value)
    {
    	$key = mysql_real_escape_string($key);
    	$value = mysql_real_escape_string($value);
    	
    	$querystring .= " OR category_fk = '{$value}'";
    }
    
    $res = mysql_query($querystring);
    this dynamically builds based on the number of check boxes posted from a form.

    This all works ok, however the WHERE date_expire>=now() AND date_activated<=now() bit does not limit between the dates

    If i exclude all the category_fk's out of the where clause it works and does limit between the dates

    can anyone help?

    Thanks

    Digitalquill

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    put a ( before the 1 and put a ) on the end after your foreach loop.

    then look up "operator precedence" so you can learn why you need the ().
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  3. #3
    SitePoint Enthusiast
    Join Date
    Jan 2008
    Location
    Hessle, East Yorkshire UK
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have tried that:

    Code:
    WHERE date_expire>=now() AND date_activated<=now() AND (1=0';
    
    foreach($_GET['checkboxes'] as $key => $value)
    {
    	$key = mysql_real_escape_string($key);
    	$value = mysql_real_escape_string($value);
    	
    	$querystring .= " OR category_fk = '{$value}')";
    but that now brings back nothing... am I missing something




    Quote Originally Posted by longneck View Post
    put a ( before the 1 and put a ) on the end after your foreach loop.

    then look up "operator precedence" so you can learn why you need the ().

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you have to put the ) AFTER the foreach loop, not inside the foreach loop.

    and you also don't know that you have a syntax error in your SQL query because you're not doing any error checking on the mysql_query() line.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast


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
  •