using a php array in mysql query

Anon

I am using a range of values repeated several times in different mysql queries:

SELECT count(row) as cnt FROM table WHERE row LIKE '%value1%' OR row LIKE '%value2%' OR row LIKE '%value3%'

And:

SELECT count(row) as cnt FROM table WHERE row NOT LIKE '%value1%' AND row NOT LIKE '%value2%' AND row NOT LIKE '%value3%'

Is it possible to put these values into an array and reference them as a $var_array?

I have tried creating an array ($array_values) and assigning it to the query:

SELECT count(row) as cnt FROM table WHERE row LIKE '%$array_values%'

But that doesn't return any values. Is this possible (it feels as though it should be) or am I going about it all wrong?

Thanks in advance

Anon

Create the array. Then build your query by looping through the array.

i.e.
$sql = "select * from mytable where";
while ($value = array_pop($myarray)) {
$sql .= " row LIKE $value AND";
}

Of course, you'll have to deal with getting rid of the "AND" when you get to the last element in the array. But hopefully you get the idea.

amcgrath

Assuming $array_values is a valid array, you could use implode() on it to get near what you want (not accounting for ' marks and the like)....

[deleted]

You can prevent the final and by putting a starting AND in it's place:

$sql = "select * from mytable where AND 1 ";
while ($value = array_pop($myarray))
{
$sql .= " AND column LIKE '%$value%' ";
}

Anon

$sql = "select * from mytable where";
while ($value = array_pop($myarray)) {
$sql .= " row LIKE $value AND";
}
$sql .= "1 = 1";

simple, basic, but effective.
Anywhere you go in the database, 1 will always equal 1, I've used this several times in similar situations.

[deleted]

...and I posted that exact solution myself an hour ago :-)

Anon

Clever. I'll have to use that. I didn't know you could do "where AND 1". Much simpler than always having to check if any elements exist or if you are on your last element.

[deleted]

Woops, I meant

"SELECT * FROM table WHERE 1 AND"

not

"SELECT * FROM table WHERE AND 1"

the trick is simply that '1' is always true, so it doesn't matter, but you can put 'AND' after '1', but you can't put it 'AND' after 'WHERE'

Anon

That makes more sense. But wouldn't it be:

$sql = "SELECT * FROM table where 1";
while($mydata = array_pop($myarray))
{
$sql .= " AND field='$mydata'";
}

That way, the SQL statement is still legal if $myarray is empty.

Yes . . . I will definitely use this.

[deleted]

You are correct :-) I need caffeine... and lots of it...

Anon

Thanks for all the help, that has really helped me understand the concept. I've tried all the suggestions and they work great when constructing a complete query, but if nested inside or at the end of a larger mysql query the while loop does work. Maybe if I show more of the script, you someone can suggest how to improve it?

$sql = "SELECT count(row) as cnt FROM table WHERE column LIKE '%a%' AND column NOT LIKE '%b%' AND column NOT LIKE '%c%' AND column NOT LIKE '%d%'";

//a,b,c,d are specific to this query
//$var_array contains global values to be used in several queries

while($data = array_pop($var_array)) {
$sql .= " AND column NOT LIKE '%$data%'";
}

//database connection here

$result = mysql_query ($sql);
while ($row = mysql_fetch_array($result)) {
$end_value = $row["cnt"];
}

I am trying to count occurances of certain strings in a db column. Could this be done in a more efficient way?
Thanks once again!