[RESOLVED] Using an array in SQL statement

boneill

I am developing a search engine that needs to capture user input as an array and use this in the WHERE part of my SQL statement.

I have captured the input from a form, exploded it into an array, and trimmed the individual elements. How do I now get these elements into my SQL statement:

"SELECT * FROM table WHERE colname IN ("array elements here").

Any help greatly appreciated.

Thanks,

Brendan

laserlight

You should [man]implode/man the array into a comma separated value list.

boneill

Wow! That was quick.

I assume I'll have to add quotes around each element before imploding, but your suggestion sounds like it'll do the trick.

Many thanks, laserlight.

Brendan

boneill

No Joy, I'm afraid.

I have an error in the code when adding the double quotes, and when I implode the array, it hasn't processed the 'foreach' statement.

This is what I have at the moment:

<?php
$LotNums = strtoupper($_POST["tfLotSearch"]);
$Lots = explode(",",$LotNums);
foreach($Lots as $Lot) {
$Lot=trim($Lot);
$Lot = \".$Lot.\";
}
$LotVals = implode(",",$Lots);
?>

Where am I going wrong?

Brendan

laserlight

Try something along these lines:

<?php
function formatArrayForIN($array) {
	foreach ($array as $key => $value) {
		$array[$key] = "'" . $value . "'";
	}
	return implode(",", $array);
}

$array = array();
foreach ($_POST["tfLotSearch"] as $key => $value) {
	$array[$key] = strtoupper($value);
}
$LotVals = formatArrayForIN($array);
?>

Installer

Or this:

$LotNums = strtoupper($_POST['tfLotSearch']);  

$Lots = explode(',', $LotNums);  

foreach($Lots as $key => $Lot) {  

    $Lots[$key] = "'" . trim($Lot) . "'"; 
}  

$LotVals = implode(',', $Lots); 
$sql = 'SELECT * FROM table WHERE colname IN (' . $LotVals . ')';  

echo $sql . '<br />';

laserlight

hmm... yeah, it didnt occur to me that $_POST['tfLotSearch'] is a string rather an array.

My example should then be revised to:

<?php
function formatArrayForIN($array) {
    foreach ($array as $key => $value) {
        $array[$key] = "'" . $value . "'";
    }
    return implode(",", $array);
}
$LotVals = formatArrayForIN(
	explode(",", strtoupper($_POST["tfLotSearch"]))
	);
?>

But then if the input is a string to begin with, then it might be better to use regex.

laserlight

Yep, this should be the solution using regex, but I havent done extensive testing and I'm not a regex expert so I cant say for sure:

$LotVals = preg_replace('/([^\,]+)/', '\\'\1\\'', strtoupper($_POST["tfLotSearch"]));

EDIT:
are we ever going to fix the php and code tag parsers for dealing with regex?
[code] does it better than [php], but it still isnt perfect enough.

Installer

Here's mine in a function 🙂 :

function my_query($str)
{
    $Lots = explode(',', strtoupper($str));  

    foreach($Lots as $key => $Lot) {  

        $Lots[$key] = "'" . trim($Lot) . "'"; 
    }  

    $LotVals = implode(',', $Lots); 
    $sql = 'SELECT * FROM table WHERE colname IN (' . $LotVals . ')';  

    return $sql;
}

$result = mysql_query($db_conn, my_query($_POST['tfLotSearch']));

Effectively the same as l.l.'s.

Edit: BTW, Boneill, a couple things you were doing wrong in this block:

foreach($Lots as $Lot) { 
$Lot=trim($Lot); 
$Lot = \".$Lot.\"; 
}

- Changing the each value in a foreach doesn't change the actual value in the array.
- " (double quote) is a string, so you need to quote it ("\"" or '"').

laserlight

Here's mine in a function

tsk tsk, hardcoded table and column names, and a redundant $sql variable (and $LotVals, for that matter). tsk :p

mrhappiness

i don't see no reason for regex 😕

$input =  'hallo,i,am,mrhappines,and,here,are,some,numbers,:,1,2,78,75.7,and,double,quotations,"';
//remove magic_quotes if neccessary
if (get_magic_quotes_gpc())
  $input = stripslashes($input);
echo 'Hi there, you entered: »'.htmlentities($input).'«';

//escape things for mysql
$input = mysql_real_escape_string($input);

//convert to array
$input_array = explode(',', $input);

//re-convert and add '
$input = "'".implode("', '", $input_array)."'";
echo '<hr />Looking for entries<br />
  WHERE columnname IN ('.$input.')';

output:

Hi there, you entered: »hallo,i,am,mrhappines,and,here,are,some,numbers,:,1,2,78,75.7,and,double,quotations,"«
---------------------
Looking for entries
WHERE columnname IN ('hallo', 'i', 'am', 'mrhappines', 'and', 'here', 'are', 'some', 'numbers', ':', '1', '2', '78', '75.7', 'and', 'double', 'quotations', '\"')

Installer

Well, let's do it like this, then:

function formatArrayForIN($array) { 
    foreach ($array as $key => $value) { 
        $array[$key] = "'" . trim($value) . "'"; 
    } 
    return implode(",", $array); 
}

$LotVals = formatArrayForIN( 
    explode(",", strtoupper($_POST["tfLotSearch"])) 
    ); 
$sql = 'SELECT * FROM table WHERE colname IN (' . $LotVals . ')';  

$result = mysql_query($db_conn, $sql);

mrhappiness

i like my single implode more than your foreach and your implode 😉

the difference in speed should not be noticed but it's there 🙂

laserlight

Yeah, I saw that before, but it didnt come to mind.
Since we eliminate the user defined loop for PHP's own loop, the time is about halved, at least on my comp.
Regex and the other implementation took about the same time.

$LotVals = "'" . implode("','", explode(",", strtoupper($_POST["tfLotSearch"]))) . "'";

Installer

Three function calls in one statement? tsk :p

laserlight

Three function calls in one argument?

It's quite clear what's happening, so we save on intermediate variables (and hence save on assignment operations where they dont necessarily increase readability).

boneill, are you reading all this?

Installer

OK. Then I resubmit my function, since it's quite clear that it's specific-purpose code.

Edit: 🙂

mrhappiness: I like yours, too. I hadn't seen it when I posted my following message.

mrhappiness

[off-topic]
you shall be forgiven 🙂
[/off-topic]

bot

boneill

Many thanks to all who contributed. Problem solved based on laserlight's 2nd post, and just came back to say thanks and saw the flood of contributions. What a great community!

The regex stuff kind of passed me in both lanes at the same time 😉 , but I have learned a lot in the past hour or so.

I have a bit more work to do, but the results should be up on www.mealys.com in a few days.

Regards,
Brendan.