Conditional query, error in parameter

[claro]

Greetings! I'm in my advance view, making a conditional query.
This is what I tried so far.

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

rror!:SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

any idea?

[vectorialpx]

 <?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 );
    
    .....
    
}

[K. Wolfe]

Your original problem was here:

              $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

[cpradio]

Your original problem was here:

              $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.

[claro]

Thank you to all the response. BTW I changed

( $labs !== null )

to

( $labs != null )

And it works! thank you thank you!