Procedural MySQLi, pushing results to associative array

[csdude55]

I'm selecting data from a table that has 1 column, but can have up to 100 rows. Then I take the data and set an associative array with a key of the value of each row, and a value of 1. This lets me just see if it exists later.

The code looks like like this (I tried to simplify it for this post, please forgive any typos):

$query = sprintf("SELECT col FROM table WHERE id=%s",
 mysqli_real_escape_string($dbh, $_GET['id']));

$results = mysqli_query($dbh, $query);

if ($results && mysqli_num_rows($results) > 0) {
 $arr = [];

 while (list($str) = mysqli_fetch_row($results))
  $arr[$str] = 1;
}

Is there a way to create this array without going through a while loop?

I thought that this would work:

$arr = mysqli_fetch_assoc($results);

but it creates $arr['col'] = "value", and I think it overwrites each one until $arr['col'] only equals the last value. So it would work if I only had 1 row result, but that's all.

[robzilla]

$results = mysqli_query($dbh, "SELECT GROUP_CONCAT(col) FROM table WHERE id='" . mysqli_real_escape_string($dbh, $_GET['id'])) . "'");

if ($results && mysqli_num_rows($results) > 0) {
 $arr = explode(',', mysqli_fetch_row($results));
}

Then use in_array() on $arr to see if any particular value exists.

[robzilla]

Sorry, for some reason I assumed the data in each row would be a simple ID. The above may not be appropriate for your situation, you be the judge :-)

Having said that, I can't really think of any other way to avoid looping over the result set.

[csdude55]

I did a speed test on in_array() on my old server and it was considerably slower, which is why I moved it to an associative array in the first place. Realistically, I know that the speed of looping over 100 results is microscopic, but you know how I am... if there's a built in function that would have done it and saved me 100ms then it couldn't hurt :-)

[robzilla]

Maybe isset() would be slightly faster.

[csdude55]

I'm using isset() with the associative array (although I'm not sure if it's really necessary, I'm just using it to clean up PHP notices), but how would one use it with an indexed array?

[robzilla]

You wouldn't :-)