#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    50
    Rep Power
    1

    Trouble with query for birthday


    I have a table field (ad_dob) in the yyyy-mm-dd format and am trying to query to match if 'today' is their birthday.
    PHP Code:
    $month date("m");
    $day date("d");

    $stmt=$conn->prepare("select ad_dob, ad_surname from ads where MONTH(ad_dob) = :month AND DAY(ad_dob)) = :day order by ad_surname asc");
    $stmt->execute(array(':month'=>$month,
                      
    ':day'=>$day)); 
    Produces this error
    Code:
    Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 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 ') = '09' order by ad_surname asc' at line 1'
    But if I try to comment it out with quotes, it produces no results :/

    Comments on this post

    • Jacques1 agrees : Counter-rep ;-)
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,477
    Rep Power
    1752
    Dunno if it's your issue or not, but check the syntax highlighting - specifically brackets - you seem to have an extra )
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    50
    Rep Power
    1
    Sigh, I've been modifying this query over an hour trying to get the result I want... I missed that, ty :/
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,035
    Rep Power
    535
    Originally Posted by RodneyB
    Sigh, I've been modifying this query over an hour trying to get the result I want... I missed that, ty :/
    I have several suggestions:
    1. Don't put colons in front of your array element keys, only the placeholders in the query!
    2. Put a try/catch around your queries.
    3. Before preparing your query, set a variable as it so that you can echo() it first and use it in the try/catch.
    4. Use function showQuery() to troubleshoot.
    5. Come up with a consistent way to write your queries. I do a separate line for the SELECT, FROM, WHERE, GROUP BY, and ORDER BY clauses, but others I know have different standards.
    6. Make all SQL commands capital and all column names and placeholders under case.


    PHP Code:
    try {
        
    $sql="SELECT ad_dob, ad_surname
        FROM ads
        WHERE MONTH(ad_dob) = :month AND DAY(ad_dob) = :day
        ORDER BY ad_surname ASC"
    ;
        echo(
    showQuery($sql,array('month'=>$month,'day'=>$day)));
        
    $stmt=$conn->prepare($sql);
        
    $stmt->execute(array('month'=>$month,'day'=>$day));
    }
    catch(
    PDOException $e){die($sql.'<pre>'.print_r($e,1).'</pre>');}

    function 
    showQuery($sql$data)
    {
        
    $keys = array();
        
    $values = array();

        foreach (
    $data as $key=>$value)
        {
            if (
    is_string($key)) {$keys[] = '/:'.$key.'/';}
            else {
    $keys[] = '/[?]/';}

            if(
    is_numeric($value)) {$values[] = $value;}
            else{
    $values[] = '"'.$value .'"';}
        }
        
    $sql preg_replace($keys$values$sql1$count);
        return 
    $sql;

    Last edited by NotionCommotion; February 9th, 2014 at 11:03 AM.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,477
    Rep Power
    1752
    And be prepared to 'repel boarders' where those boarders have birthdays on 29th Feb ...
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc

IMN logo majestic logo threadwatch logo seochat tools logo