Conditional query, error in parameter

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?

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


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.

Thank you to all the response. BTW I changed


( $labs !== null )

to


( $labs != null )

And it works! thank you thank you!