CODEWITHSUNDEEP
phpmysql
user7461846
user7461846

Reputation:

select where like with an array

$tag = 'sky';

select rows where tags contains $tag:

$sql = "select * from images where tags like '%" . $tag . "%' order by date desc";

What if I have an array of tags:

$tags = array('sky', 'earth', 'sun'); // max 3

foreach($tags as $tag) {
    $sql = "select * from images where tags like '%" . $tag . "%' order by date desc";
}

Is this the right way, especially regarding performances.

The table images has about 20.000 rows.

Upvotes: 0

Views: 43

Answers (3)

Hassaan
Hassaan

Reputation: 7672

Add multiple tags to your query using OR in your query

$sql = "select * from images where tags like '%" . $tag[0] . "%' OR tags like '%" . $tag[1] . "%' OR tags like '%" . $tag[2] . "%' order by date desc";

You don't need to use foreach to run query

UPDATE 1

$comma_separated = "('" . implode("','", $tags) . "')";
$sql = "select * from images where tags IN ".$comma_separated;

Most Efficient Way

A REGEXP might be more efficient, you have to benchmark it by your self

$sql = "select * from images where tags REGEXP '" . implode('|', $tags) . "'  order by date desc";

Upvotes: 0

xanadev
xanadev

Reputation: 826

Here is a possible implementation, you don't have to know the array size.

$tags = array('one', 'two');
$sql = "select * from images where tags like '%" . implode("%' OR tags like '%",$tags) . "%' order by date desc";

Upvotes: 0

Ricardo Lohmann
Ricardo Lohmann

Reputation: 26320

You can use regexp.

$sql = "select * from images where tags REGEXP '" . implode('|', $tags) . "'  order by date desc";

Your final result will be:

select * from images where tags REGEXP 'sky|earth|sun' order by date desc

Upvotes: 1

Related Questions