How to reuse a prepared statement parameter in mysql
mysql, mysqli, php
Solution
PDO allows you to name parameters specifically, like so, but MySQLi doesn't support named variables:
"SELECT x FROM y WHERE name = :name and key = :key"
In PDO this would let you re-use `:name` and `:key` after specifying their types. I'm not arguing over which is better, since you can achieve the same thing in MySQLi.
The thing is that MySQLi makes it rather hard to stick to the "Don't Repeat Yourself (DRY)" methodology. (Consider custom functions if you like DRY).
It's the reason some prefer PDO over MySQLi, but there are some funky workarounds (such as `call_user_func_array` in custom functions, etc).
And as to your "efficiency" comment, it really makes no difference to repeat the variables. It will be parameterized in the MySQL API call twice, but it hardly affects performance significantly. PDO parameterizes internally without using MySQL (unless you explicitly make it use MySQL), and MySQLi makes the MySQL API parameterize for it.
Problem
I have a (semi) simple MySQL query that I'm trying to use (via php mysqli extension), and I can't quite figure out how to do this. My query 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 deal with merging and I just want to reuse the original two prepared "?"s. I know there is something I can do for this when inserting values into a table, but my efforts in searching the docs have thus far proved useless. Can I do this, and how? update Here's 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 rather inefficient.