Passing an array to a query using a WHERE clause - works in phpmyadmin, not in my script
the Varable :qty is handled as Strinf So you have '1,1'
So you must use FIND_IN_SET
SELECT * FROM `dice_t` WHERE FIND_IN_SET(`qty`,:qty) AND FIND_ON_SET(`opacity`,:opacity) AND FIND_IN_SET(`color`,:color)
other ways are in this thread Can I bind an array to an IN() condition?
How to pass an array to a MySQL query using multiple WHERE clauses
Here's a solution that uses only positional parameters:
$active = 1;
$in = join(',', array_fill(0, count($arrList), '?'));
$sql = "SELECT * FROM `table` WHERE active=?
AND postID IN ($in)
ORDER BY postID DESC";
$stmt = $pdo->prepare($sql);
// make an ordinal array for all the positional parameters
// with $active as the first element.
$params = array_values($arrList);
array_unshift($params, $active);
$stmt->execute($params);
$output = $stmt->fetchAll(PDO::FETCH_OBJ);
passing array in where clause in join
$courses = implode(',', $array['courses']);
$teacher = implode(',', $array['teacher']);
return $this->db->query('Select
t1.id,t1.course_name,t1.course_duration,t1.course_price,t1.course_category,
t2.first_name , t2.last_name , t2.email
from teacher_courses as t1
LEFT JOIN teacher as t2 on t1.teacher_id=t2.id WHERE course_id IN ($courses) AND teacher_id IN ($teacher)')->result_array();
Passing value as array in Where clause in Laravel
use this instead
$qids = Examquestion::where('examid', $examid)->lists('qid');
then use this array in
$questions = Question::whereIn('qid', $qids)->get();
you'll get your result
How to create a dynamic WHERE clause for a query using an array list
Either check if JDBC Template from Spring handle that for you using a syntax which could be something like (from the doc, I don't think it does)
SELECT question_id FROM question WHERE category in (?...)
Or write your own query with the problems that may arise:
List<Object> parameters = new ArrayList<>(categories.size());
StringBuilder sb = new StringBuilde("SELECT question_id FROM question WHERE 1=1");
if (!categories.isEmpty()) {
if (categories.size() == 1) {
sb.append(" and category = ?");
} else {
sb.append(" and category in ");
sb.append(categories.stream()
.map(ignored -> "?")
.collect(joining(", ", "(", ")")));
sb.append(")");
}
parameters.addAll(categories);
}
Object[] paramArray = parameters.toArray();
jdbcTemplate.query(sb.toString(), stringMapper, paramArray);
Notes:
- some security/quality tool may report SQL issues because you are writing a dynamic SQL.
- Oracle put a limit on 1000 elements per IN. You would have to partition categories per group of 1000 (or less).
- I used a
stream()
in a more or less strange fashion in order to generate the "?". If you use commons-lang3, you can replace it by "(" + StringUtils.repeat("?", ", ", categories.size()) + ")"
(the example in the javadoc was probably done with this kind of use). - if you only have category as single criteria, you may probably remove the
1=1
as well as the and
.
Passing an array of condition to query where method in Yii2
Your $cond
variable is creating an array within an array; you're using both array()
and shorthand syntax []
(added in PHP 5.4). If you only intend $cond
to be a single level array, remove one of the array calls. Either
$cond = ['last_hard_state' => 0, 'last_hard_state' => 2];
or
$cond = array('last_hard_state' => 0, 'last_hard_state' => 2);
Related Topics