[RESOLVED] quoting elements in an array

[jjstuart]

Hi,
I have a script that grabs all cities within a certain square mile. Then puts all the cities it finds into a variable called $cityArray. The code I am using is.

$allCities = implode(',',$cityArray);
$sql = "SELECT * FROM cal WHERE city IN ($allCities)";

The problem I am having is that the city column in my database is type varchar, so it requires all the cities in the $allCities variable to be in quotes. I need the cities in the variable $allCities to not only be comma seperated, but also be enclosed in quotes.

Any help is appreciated.

Thanks

[bradgrafelman]

So just use a foreach() loop to loop through the array and add each element into a string:

$allCities = '';
foreach($cityArray as $theCity)
     $allCities .= "'$theCity', ";

$sql = 'SELECT * FROM cal WHERE city IN (' . rtrim($allCities, ', ') . ')';

[Installer]

Or, almost the same thing:

foreach ($allCities as $key => $city) {
    $allCities[$key] = "'" . $city . "'";
}
$allCities = implode(',', $cityArray);
$sql = "SELECT * FROM cal WHERE city IN ($allCities)";

[bradgrafelman]

In case you're interested, my code snippet took an average 0.0000792 seconds to execute, while Installer's took an average 0.0001356 seconds to execute.

[Installer]

Is that including the query? 🙂

[jjstuart]

Thank you. This board is very helpful.

[bradgrafelman]

Installer wrote:

Is that including the query? 🙂

$cityArray = array(
	'Chicago', 'Springfield', 'Peoria', 'Decatur', 'Danville',
	'New York City', 'Boston', 'Indianapolis', 'Des Moines',
	'Atlanta', 'Pearl Harbor', 'Honolulu', 'Death Valley',
	'Miami', 'South Hampton', 'Cuba', 'Moline', 'Rock Island',
	'This is', 'getting stupid', 'so I will', 'quit now',
	'ok?', 'ok!', 'really, ', 'you can', 'stop now', '!!!');

$arr = explode(' ', microtime());
$starttime = $arr[1] + $arr[0];

foreach ($cityArray as $key => $city) { 
    $allCities[$key] = '\'' . $city . '\''; 
} 
$allCities = implode(',', $cityArray); 
$sql = "SELECT * FROM cal WHERE city IN ($allCities)";
echo $sql . '<hr>';

$arr = explode(' ', microtime());
$endtime = $arr[1] + $arr[0];
echo ( 'Installer\'s method loaded in <b>'.number_format(($endtime - $starttime), 7).'</b> seconds.<br />' );

EDIT: BTW, glad we could help. If we've solved your problem, don't forget to mark the thread resolved.

[Installer]

Sorry, I didn't mean to offend.

Just for interest, here's my test script, which doesn't include the query, but does have the rtrim():

$cityArray = array('Gotham City', 'Metropolis', 'Oz', 'Troy', 
                   'El Dorado', 'Macondo', 'Cibola', 'Hoboken');

$start = microtime(true);
for ($n = 0; $n < 1000; $n++){
    $allCities = ''; 
    foreach($cityArray as $theCity) { 
         $allCities .= "'$theCity', "; 
    }
    $allCities = rtrim($allCities, ', '); 
}
echo microtime(true) - $start . '<br />';
// 0.039983034133911

$start = microtime(true);
for ($n = 0; $n < 1000; $n++){
    foreach ($cityArray as $key => $city) { 
        $allCities[$key] = "'" . $city . "'"; 
    } 
    $allCities = implode(',', $cityArray); 
}
echo microtime(true) - $start . '<br />';
// 0.015254974365234

[bradgrafelman]

Odd. I would have thought that yours was slower because you're going through arrays twice - once through the foreach() loop to create a new array, and then using implode() to step through the newly created array to create a new string.

shrug

BTW, I wasn't offended whatsoever. I like to see different solutions and run speed tests to compare. In fact, I believe its your solutions that I've constantly done this with :p

[Installer]

It probably has to do with implode() being optimized, thus faster than straight concatenation. As a guess.

Edit: Ignore this.

[Installer]

Not to beat a dead horse, but this seems interesting. After changing this line in your routine:

$allCities .= "'$theCity', ";

to:

$allCities .= "'" . $theCity . "', ";

I get times like this:
0.019266988754272

[bradgrafelman]

Out of curiosity, what if we don't use double quotes?

$allCities .= '\'' . $theCity . '\', '; 

[Installer]

I get no real difference in the time, and with both kinds of quotes, over several runs, yours and mine are pretty close to even.

[Installer]

I wasted your time. After correcting a typo in my routine (I'll let you find it 🙂 ), yours (with the concatenation change) runs way faster than mine.

[bradgrafelman]

Using the wrong array as the parameter for your implode() call? You could always try removing all of the $key stuff from your snippet, since its all extraneous.

[Installer]

Why extraneous? If it operates only on "$city" the array elements won't be changed. (Although with PHP 5 "$city" could be passed by reference.)

Edit: I think this gets rid of my stupid mistakes:

$start = microtime(true); 
for ($n = 0; $n < 1000; $n++) { 
    $allCities = ''; 
    $cityArray = array('Gotham City', 'Metropolis', 'Oz', 'Troy', 'El Dorado', 'Macondo', 'Cibola', 'Hoboken');
    foreach($cityArray as $theCity) { 
         $allCities .= "'" . $theCity . "', "; 
    } 
    $allCities = rtrim($allCities, ', '); 
} 
echo microtime(true) - $start . '<br />'; 
// 0.026258945465088

$start = microtime(true); 
for ($n = 0; $n < 1000; $n++){
    $allCities = '';
    $cityArray = array('Gotham City', 'Metropolis', 'Oz', 'Troy', 'El Dorado', 'Macondo', 'Cibola', 'Hoboken');
    foreach ($cityArray as $key => $city) { 
        $cityArray[$key] = "'" . $city . "'";
    } 
    $allCities = implode(', ', $cityArray); 
} 
echo microtime(true) - $start . '<br />';
// 0.026687145233154

I wasn't allowing for the test looping.

[gammaster]

$allCities = "'".implode("','", $cityArray)."'";

[bradgrafelman]

Lol... gammaster's code just produced results around 0.0000508 seconds to execute once :/