Member Avatar for garyjohnson

I have a mysql query that seperates an array of variables and uses each variables to compare inside the database. The query uses a OR clause and I want to make it so it can also use an AND clause. The trick is I want the left side of the query to use the OR clause and the right side use the OR and AND clause. This probably doesnt make sense so let me try to make an example code.

$query = "SELECT * FROM table WHERE name ='$name' OR (number ='$number' AND number2 = '$number2')";

I have not tried making this code at all because I believe it is beyond my knowledge to even attempt. The sample code may be how you actually do it but I do not know.

Here is my actually query

$query = "SELECT * FROM comments WHERE commenter_username IN (" . join(',', $list) . ") Or Number IN (" . join(',', $list) . ") ORDER BY comment_id DESC";

What I want my code to look like and work would be something like this,

$query = "SELECT * FROM comments WHERE commenter_username IN (" . join(',', $list) . ") Or (Number IN (" . join(',', $list) . ") AND Number2 IN (" . join(',', $list2) . ")) ORDER BY comment_id DESC";
  • 2 Contributors
  • 2 Replies
  • 290 Views
  • 1 Hour Discussion Span
  • Latest Post Latest Post by garyjohnson

Dani AI

Generated

Quick add-on to what solved the original problem: 's idea to prepare the lists first is the right direction, and the query grouping you sketched is valid — but a few practical points will make it safe and reliable for production.

Parentheses + precedence: SQL evaluates AND before OR, so your intended grouping is enforced by using parentheses, but keeping them explicit avoids mistakes when you change the query later. See MySQL operator precedence for details: MySQL operator precedence. (dev.mysql.com)

Avoid concatenating raw values into SQL. Use parameterized queries (prepared statements) so user data never becomes SQL. With PDO the common pattern for dynamic IN() lists is to generate one placeholder per value and pass the array to execute. Example pattern (positional placeholders shown):

$ph1 = implode(',', array_fill(0, count($list1), '?'));
$ph2 = implode(',', array_fill(0, count($list2), '?'));
$ph3 = implode(',', array_fill(0, count($list3), '?'));

$sql = "SELECT * FROM comments WHERE commenter_username IN ($ph1) OR (number IN ($ph2) AND number2 IN ($ph3)) ORDER BY comment_id DESC";
$stmt = $pdo->prepare($sql);
$stmt->execute(array_merge($list1, $list2, $list3));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

The PDO docs cover prepared statements; the dynamic-IN approach is a common, safe pattern. (php.net)

Handle edge-cases: do not emit IN () (empty list) — that produces a syntax error in MySQL. If a source list can be empty either skip that clause entirely or supply an impossible sentinel (e.g., -1 for numeric ids) or transform the logic so the clause is omitted. See discussion on empty IN lists. (stackoverflow.com)

Performance note: index the columns used in WHERE (commenter_username, number, number2) and check the plan with EXPLAIN. Very large IN lists can be slow; alternatives include loading the values into a temporary table and JOINing, or using EXISTS/subqueries depending on data shape. See MySQL optimization and indexes guidance. (dev.mysql.com)

Checklist: keep parentheses explicit, use prepared statements, guard against empty arrays, test with EXPLAIN, and index the filtered columns. This will make 's final query both safe and efficient.

Recommended Answers

All 2 Replies

Member Avatar for Member #120589

I can't see anything wrong with your query, other than the fact that you seem to be looking for data in the same list for the 'left hand' and the first 'right hand' ($list).

You could create the arrays before writing the SQL:

$sqlList1 = implode(',',$list);
$sqlList2 = implode(',',$list2);
$sqlList3 = implode(',',$list3);

$query = "SELECT * FROM comments WHERE commenter_username IN ($sqlList1) Or (Number IN ($sqlList2) AND Number2 IN ($sqlList3)) ORDER BY comment_id DESC";

This is assuming that you have fieldnames Number and Number2.

Member Avatar for garyjohnson

Thank you so much! It works perfectly!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.