Geek asks and answers

How to reuse prepared statement parameter in mysql

I have a (semi) simple MySQL query that I am trying to use (via the mysqli php extension) and I cannot figure out how to do this.

My request looks like

SELECT DISTINCT Col1 from `table1` where `col2`= ? and `col3`=? 
UNION SELECT DISTINCT Col1 from `table2` where `col2`=(?) and `col3`=(?)

I have two tables that I don’t want to consider merging, and I just want to reuse the original two prepared "?" s. I know that I can do this when we paste the values ​​into the table, but my efforts to find the documents have so far proved futile.

Can I do it and how?

Update

Here is my code

$query='SELECT DISTINCT enginesizecc FROM `table1`  where year=? and vehicle_make= ? as UNION SELECT DISTINCT enginesizecc from `table2` WHERE year=(?) AND vehicle_make =(?)';     
$stmt=$sql->prepare($query);
echo $sql->error; //I'm in debug mode
$blank='';
if(array_key_exists('year', $_POST)){
    if(array_key_exists('make', $_POST)){
        $stmt->bind_param('ss', $_POST['year'], $_POST['make']);
    }
    else $stmt->bind_param('ss', $_POST['year'], $blank);
}
elseif(array_key_exists('make', $_POST)){
    $stmt->bind_param('ss', $blank, $_POST['make']);
}
else{
    //if(array_key_exists('model', $_POST)) $stmt->bind_param('sss', $blank, $blank);
    $stmt->bind_param('ss', $blank, $blank);
}
$stmt->execute();
$modelItem='';
$stmt->bind_result($modelItem);
$models=array();
while($stmt->fetch()){      
    $models[]=$modelItem;
}
sort($models);
return $models;

I know that I could just bind the same variables twice, but that seems pretty inefficient.

+5
source share
2 answers

PDO , MySQLi :

"SELECT x FROM y WHERE name = :name and key = :key"

PDO :name :key . , , MySQLi.

, MySQLi " (DRY)". ( , DRY).

, PDO MySQLi, (, call_user_func_array ..).

"", , . API MySQL , . PDO MySQL ( MySQL), MySQLi API- MySQL.

+5

, .

global  $dbconnection;

    $sql = "INSERT INTO Sales(order_id,client_id,sale_date,status) VALUES (?,?,?,?)";

    if ($stmt = $dbconnection->prepare($sql)) 
    {   
        /* Bind our params */ 
            $stmt->bind_param('iisi',$order_id,$client_id,$today,$status);  
        $stmt->execute();  
        $stmt->close();
    }
    else
    {
        print "ERROR";
    }
+1

More articles:


All Articles