SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict
    Join Date
    Dec 2011
    Posts
    221
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Conditional query, error in parameter

    Greetings! I'm in my advance view, making a conditional query.
    This is what I tried so far.
    Code:
    public static function advanceView($labs,$pcs=null)
        {
           
            if ($pcs == !null)
            {
                $pc  =  "AND lab.pclab.pclab_name = :pc";
                $param = 1;
            }
            else
            {
                $pc= "";
                $param = 0;
            }
           
            try{
                  $sql = "  SELECT 
                                device.sn.sn_number AS sn,
                                device.sn.sn_id AS sn_id
                            FROM lab.labname
                            INNER JOIN lab.pclab ON lab.labname.lab_id = lab.pclab.lab_id
                            AND device.names.dlist_id = device.list.dlist_id
                            WHERE device.sn.sn_delstat IS NULL
                            AND lab.labname.lab_id = :lab
                            $pc                 
                                                  
                            ";
                    $q = connection::$db->prepare($sql);
                   
                  
                    if ($param = 1)
                    {
                            $q -> bindParam(':pc',$pcs,PDO::PARAM_STR);
                            $q -> bindParam(':lab',$labs,PDO::PARAM_INT);
                    }
                    else 
                    {
                            $q -> bindParam(':lab',$labs,PDO::PARAM_INT);
                    }
                    
                    $q -> execute();
    It works well if I will not leave the variable $labs and $pcs empty. But if I want to select only the $labs, there is an error like
    Code:
    rror!:SQLSTATE[HY093]: Invalid parameter number: parameter was not defined
    any idea?

  2. #2
    SitePoint Addict bronze trophy vectorialpx's Avatar
    Join Date
    Dec 2012
    Location
    Bucharest
    Posts
    247
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
     <?php

    public static function advanceView($labs=null$pcs=null)
    {
        
    $params = array();
        
    $EXTRA_sql = array(
            
    'pcs' => '',
            
    'labs' => '',
        );

        if ( 
    $pcs !== null ) {
            
    $EXTRA_sql['pcs'] = " AND lab.pclab.pclab_name = :pcs ";
            
    $params[':pcs'] = $pcs;
        }

        
    // even better
        
    if ( $labs !== null ) {
            
    $EXTRA_sql['labs'] = " AND lab.labname.lab_id = :lab ";
            
    $params[':labs'] = $labs;
        }

        try {

            
    $sql "
                SELECT 
                    device.sn.sn_number AS sn,
                    device.sn.sn_id AS sn_id
                FROM
                    lab.labname
                INNER JOIN lab.pclab ON
                    lab.labname.lab_id = lab.pclab.lab_id
                    AND device.names.dlist_id = device.list.dlist_id
                WHERE
                    device.sn.sn_delstat IS NULL
                    
    {$EXTRA_sql['labs']}
                    
    {$EXTRA_sql['pcs']}
            "
    ;
            
    $q connection::$db->prepare($sql);
            
    $q->execute$params );
        
        .....
        
    }

  3. #3
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    65 Post(s)
    Tagged
    2 Thread(s)
    Your original problem was here:

    Code:
                  $sql = "  SELECT 
                                device.sn.sn_number AS sn,
                                device.sn.sn_id AS sn_id
                            FROM lab.labname
                            INNER JOIN lab.pclab ON lab.labname.lab_id = lab.pclab.lab_id
                            AND device.names.dlist_id = device.list.dlist_id
                            WHERE device.sn.sn_delstat IS NULL
                            AND lab.labname.lab_id = :lab
                            $pc                 
                                                  
                            ";
    Your using :lab, which is parameter binding, and then using a variable immediately after it? It's one or the other, and preferable parameter binding to avoid sql injections.

    http://php.net/manual/en/pdostatement.bindparam.php

  4. #4
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,127
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    Your original problem was here:

    Code:
                  $sql = "  SELECT 
                                device.sn.sn_number AS sn,
                                device.sn.sn_id AS sn_id
                            FROM lab.labname
                            INNER JOIN lab.pclab ON lab.labname.lab_id = lab.pclab.lab_id
                            AND device.names.dlist_id = device.list.dlist_id
                            WHERE device.sn.sn_delstat IS NULL
                            AND lab.labname.lab_id = :lab
                            $pc                 
                                                  
                            ";
    Your using :lab, which is parameter binding, and then using a variable immediately after it? It's one or the other, and preferable parameter binding to avoid sql injections.

    http://php.net/manual/en/pdostatement.bindparam.php
    Actually that part was okay, as $pc is a hard-coded additional SQL clause or an empty string. When it is an additional SQL clause, he is also binding a second variable. His biggest issue looks to be $pcs == !null which doesn't read correctly, and should have been $pcs !== null and the fact that if $labs is empty it will not parse to an INT in the bindParam call very well.

    This is where @vectorialpx ; seems to make a lot of sense (in my opinion) as it tackles both of those issues.

  5. #5
    SitePoint Addict
    Join Date
    Dec 2011
    Posts
    221
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Thank you to all the response. BTW I changed
    Code:
    ( $labs !== null )
    to
    Code:
    ( $labs != null )
    And it works! thank you thank you!
    Last edited by cpradio; Feb 5, 2013 at 20:40.


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
  •