Php array to MySQL query condition structure

Question

I have following array in PHP:

$arr = [1, 3, '4A', '5A', '5C', '2B', '2C', '2E'];

Somehow I need to convert this array to a string which look like this:

 $filter =     "(id like '1%') OR 
                (id like '3%') OR 
                (id like '4A%') OR 
                (id like '5A%' OR id like '5C%') OR 
                (id like '2B%' OR id like '2C%' OR id like '2E%')";

The first character of the array value indicates a unique categorie and is wrapped in parentheses. In the end I will use this to query the database;

$sql = "SELECT * FROM list WHERE {$filter}";

Can someone help me converting this array to the correct string ? What is the best method to achieve this ?

To give you an idea of what I'm trying: The 'id' column in my database row looks like; 1B2875. Where the first char (1) indicates a categorie and the second char (B) the subcategorie. The given array is a result of a client-side filter request.

If its not too late I would really suggest normalizing this and using relationships to a category and just make the ID an auto_increment integer. This setup youre working with now is probably going to cause a lot of hurt later. — prodigitalson
– prodigitalson, Commented Dec 7, 2014 at 1:32

Œlrim · Accepted Answer · 2014-12-07 01:18:41Z

2

You can use regular expressions in SQL:

SELECT * FROM list WHERE id REGEXP '^(1|3|4A|…)[0-9]+$'

The value inside the parentheses can be generated with the PHP function implode('|', $filter_strings)

Note that you should validate and escape the filter strings first to prevent the user input from manipulating the query. The function preg_quote is useful here.

edited Dec 7, 2014 at 1:18

answered Dec 7, 2014 at 1:12

Œlrim

5512 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Niels Keurentjes Over a year ago

Kudos for thinking outside the box with the MySQL-side regexp.

mickmackusa · Accepted Answer · 2023-12-19 22:18:07Z

Populate an array of conditional segments and an array of %-wrapped values, then execute your query using a prepared statement (if there are any placeholders in the query) or a standard query (if there are no placeholders).

Code: (PHPize Demo)

$array = [1, 3, '4A', '5A', '5C', '2B', '2C', '2E'];

$conditions = [];
$params = [];
foreach ($array as $v) {
    $conditions[] = 'id LIKE ?';
    $params[] = "%$v%";
}

$sql = 'SELECT * FROM list';
if ($conditions) {
    $sql .= ' WHERE ' . implode(' OR ', $conditions);
    $result = $mysqli->execute_query($sql, $params);
} else {
    $result = $mysqli->query($sql);
}

foreach ($result as $row) {
    echo "<div>{$row['id']}</div>\n";
}

While pedestrian, the generated SQL will be:

SELECT * FROM list WHERE id LIKE ? OR id LIKE ? OR id LIKE ? OR id LIKE ? OR id LIKE ? OR id LIKE ? OR id LIKE ? OR id LIKE ?

If you are not yet enjoying PHP8.2, there are other ways to bind the parameters/values and execute the query.

Collectives™ on Stack Overflow

Php array to MySQL query condition structure

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related