SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist
    Join Date
    Dec 2008
    Location
    Plymouth, United Kingdon
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem with PHP PDO fetchOject

    It seems to be a bug or problem when I use PHP `PDO fetchOject` with the query below,

    Query:

    PHP Code:
    SELECT 
            p
    .*,
            
    t.*
        
        
    FROM root_pages AS p
            
        LEFT JOIN root_templates 
    AS t
        ON p
    .tmp_id t.tmp_id
        
        WHERE p
    .pg_url = ?
        AND ? 
    IS NOT NULL
        
        
    OR p.pg_url = ? 
        AND 
    p.pg_hide != ? 
    called from a PHP PDO db class,

    PHP Code:
     $page $this->database->fetch_object($sql,array(
                    
    $pg_url,
                    
    NULL,
                    
    $pg_url,
                    
    1
                
    )); 
    result:

    SQLSTATE[HY093]: Invalid parameter number: number of bound variables
    does not match number of tokens

    PHP PDO FetchOject method from the PDO db class,

    PHP Code:
    # return the current row of a result set as an object
            
    public function fetch_object($query$params = array())
            {
                try
                {
                    
    # prepare the query
                    
    $stmt $this->connection->prepare($query);
                    
                    
    # if $params is not an array, let's make it array with one value of former $params
                    
    if (!is_array($params)) $params = array($params);
                    
                    
    # execute the query
                    
    $stmt->execute($params);
                    
                    
    # return the result
                    
    return $stmt->fetchObject();
                    
    //return $stmt->fetch(PDO::FETCH_OBJ);
                
    }
                catch (
    PDOException $e
                {
                    
    # call the get_error function
                    
    $this->get_error($e);
                }
            } 
    It only will be fine if I call the method like this,

    PHP Code:
    $page $this->database->fetch_object($sql,array(
                    
    $pg_url,
                    
    1,
                    
    $pg_url,
                    
    1
                
    )); 
    But I can get the result without any error when I test one of the query below with `phpMyAdmin`,

    PHP Code:
     SELECT 
            p
    .*,
            
    t.*
        
        
    FROM root_pages AS p
            
        LEFT JOIN root_templates 
    AS t
        ON p
    .tmp_id t.tmp_id
            
        WHERE p
    .pg_url 'exhibition sample 6' 
        
    AND '1' IS NOT NULL
        
        
    OR p.pg_url 'exhibition sample 6' 
        
    AND p.pg_hide != '1' 
    or

    PHP Code:
    SELECT 
            p
    .*,
            
    t.*
        
        
    FROM root_pages AS p
            
        LEFT JOIN root_templates 
    AS t
        ON p
    .tmp_id t.tmp_id
            
        WHERE p
    .pg_url 'exhibition sample 6' 
        
    AND NULL IS NOT NULL
        
        
    OR p.pg_url 'exhibition sample 6' 
        
    AND p.pg_hide != '1' 
    Any ideas what I have missed when using `fetchOject`?

    The query does not cause any error when I use fetch-assoc method,

    PHP Code:
    $sql ="
        SELECT 
            p.*,
            t.*
        
        FROM root_pages AS p
            
        LEFT JOIN root_templates AS t
        ON p.tmp_id = t.tmp_id
        
            
        WHERE p.pg_url = 'exhibition sample 6' 
        AND ? IS NOT NULL
        
        OR p.pg_url = 'exhibition sample 6' 
        AND p.pg_hide != '1'
        "

    No error with

    PHP Code:
     $item $connection->fetch_assoc($sql,1); 
    or

    PHP Code:
     $item $connection->fetch_assoc($sql,NULL); 
    the `fetch_assoc` method,

    PHP Code:
     # fetch a single row of result as an array ( =  one dimensional array)
        
    public function fetch_assoc($query$params = array())
        {
            try
            {
                
    # prepare the query
                
    $stmt $this->connection->prepare($query);
                
                
    # if $params is not an array, let's make it array with one value of former $params
                
    if (!is_array($params)) $params = array($params);
                
                
    # execute the query
                
    $stmt->execute($params);
                
                
    # return the result
                
    return $stmt->fetch();
            }
            catch (
    PDOException $e
            {
                
    # call the get_error function
                
    $this->get_error($e);
            }
            
            
        } 
    Thanks,
    Lau

  2. #2
    SitePoint Enthusiast Adam Chrapkowski's Avatar
    Join Date
    Sep 2011
    Location
    Poland
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try to use "NULL" instead of NULL.

  3. #3
    SitePoint Evangelist
    Join Date
    Dec 2008
    Location
    Plymouth, United Kingdon
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if i use "NULL"

    the query will be like this below and it does not return the result i expect...

    PHP Code:
    SELECT 
            p
    .*,
            
    t.*
        
        
    FROM root_pages AS p
            
        LEFT JOIN root_templates 
    AS t
        ON p
    .tmp_id t.tmp_id
        
            
        WHERE p
    .pg_url 'exhibition sample 6' 
        
    AND 'NULL' IS NOT NULL
        
        
    OR p.pg_url 'exhibition sample 6' 
        
    AND p.pg_hide != '1' 

  4. #4
    SitePoint Enthusiast Adam Chrapkowski's Avatar
    Join Date
    Sep 2011
    Location
    Poland
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What are you expect by NULL IS NOT NULL it is never true.

  5. #5
    SitePoint Evangelist
    Join Date
    Dec 2008
    Location
    Plymouth, United Kingdon
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have found the mistake in the class I made, nothing wrong with passing NULL into fetchOject.

    thanks for the help!


Tags for this Thread

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
  •