[SOLVED] Querying MySQL with an Array

[Gorkfu]

I'm sure this is possible, but I am struggling to figure it out. I want to use an array's values to search MySQL records. I figured maybe using regexp in MySQL would do the trick but I'm not familiar with regexp usage in MySQL.

 

I want to accomplish this with only one query if possible.

 

Here's a rough idea of what I'm trying to accomplish.

 

Array ( [0] => Item0 [1] => Item1 [2] => Item2 [3] => Item3 [4] => Item4. )

 

$result = $mysqli->query("SELECT `name` FROM `table1` WHERE `name` = '$array1' ORDER BY CHAR_LENGTH(name) DESC");

 

Any ideas?

 

Update:

I just realized sending an array wouldn't work and that I'll need to recombine it into a regular string. Thing is I would need MySQL to search each word or phrase independently.

[Gorkfu]

Well I figured it out with some php, although I'll have to tweak it a bit further with other things, but this is basically what I was going for...

 

$query = "SELECT `name` FROM `table1` WHERE ";
for($i = 0; $i < count($array1); $i++) {
	$query .= "`name` = '".trim($array1[$i])."'";
		if ($i < count($array1)-1) {
			$query .= " OR ";
		}
}
}
$query .= " ORDER BY CHAR_LENGTH(name) DESC";

[wildteen88]

You could of just used implode

 

$query = "SELECT `name` FROM `table1` WHERE `name` IN('". implode("','", $array1) . "')  ORDER BY CHAR_LENGTH(name) DESC";

 

Which will produce this query:

SELECT `name` FROM `table1` WHERE `name` IN('Item0','Item1', etc)  ORDER BY CHAR_LENGTH(name) DESC

 

Using IN() instead name='name1' ORr name='name2' etc is more efficient.

[Gorkfu]

That does make it much more efficient. However, I need a way to trim a space at the beginning of each value. I was just reading up on trim for MySQL but I don't see how to use it in conjuction with IN().

[Barand]

to trim each value in the array

 

function mytrim (&$a, $k)
{
    $a = trim($a);
}

$array1 = array(' aaa ', ' bbb ');

array_walk($array1, 'mytrim');

echo '<pre>', var_dump($array1, true), '</pre>';

[Gorkfu]

Thanks for that method Barand, but I tried a similar approach before and the the problem with that method is it uses a lot more memory than my original method. I'm still wondering if it is even possible to do the trim alongside with an IN in MySQL? It looks like you can only use one or the other in a query.

[Gorkfu]

Well I shrunk down my original code for now till I figure out something better.

 

$query = "SELECT `name` FROM `table1` WHERE `name` IN(";
for($i = 0; $i < count($array1); $i++) {
	$query .= "'".trim($array1[$i])."', ";
}
$query .= " 'other term') ORDER BY CHAR_LENGTH(name) DESC";

[wildteen88]

Your could use array_map instead

 

$query = "SELECT `name` FROM `table1` WHERE `name` IN('". implode("','", array_map('trim', $array1)) . "')  ORDER BY CHAR_LENGTH(name) DESC";

 

Alternatively it could be done when you generate your $array1 array

[Gorkfu]

Your could use array_map instead

 

Oh pure genius, thank you wildteen88.  It works so much faster now and less memory usage it seems. I learned a lot here too. 

 

Although one thing confuses me dealing with the php alternative I was using. The php was leaving out the last array value from the MySQL Query some of the time. Your method doesn't have this issue at all.

[wildteen88]

This is due to the comparison operator you are using here

for($i = 0; $i < count($array1); $i++) {

 

the < means less than.  Say your array contained 3 items.

 

The for loop will only iterate 2 times not 3. In order for it to iterate 3 times you'll need to use the less than or equal to operator (<=).

 

EDIT: Ignore me.

[Barand]

??? ???

[wildteen88]

haha. I'm a complete tit!

[Gorkfu]

Lol, you must of been thinking $i = 1 at first. Don't feel embarrassed, I actually confused myself at one point and thought the same thing. Lack of sleep does evil things to the brain. 

 

That's why it confused me, when I printed the array results it showed all the items correctly in the array but then when I would go and print the MySQL query it showed them all but the last array value and this behavior didn't even happen on every page. (Dynamic results.) Most likely the problem was right under my nose in some other code.

 

So I dunno, I'm not gonna worry about it, your solution works much better anyways. So one of you guys can mark this solved if you want.