[SOLVED] Select and put into a PHP array all items in a column.

[jlange]

For some reason I can't get this to work; it ends up making an array of the one item in the first row.

$query = "SELECT name FROM object";
$result = mysql_query($query) or die ( "Error in query: $query. " . mysql_error() );
$row = mysql_fetch_assoc($result);
foreach($row as $value) { echo($value); }

 

Thoughts?

[akitchin]

you need to loop through the resource if you're trying to grab more than one row.  mysql_fetch_assoc() will only grab the associative array of the row currently pointed at by the resource:

 

$rows = array();
while ($current_row = mysql_fetch_assoc($result))
{
  $rows[] = $current_row;
}

 

the while() loop will execute the statement inside (here, assigning the current row to an array item in $rows) until the resource has been entirely processed.

[jlange]

Aha. Thanks very much, that's a very good thing to know both now and for the future!

 

I'm sure I'll be back with more questions before the end of the day!

[jlange]

Hm, interesting. I had to nest a foreach in a foreach to get the contents of that array. It was like an array in an array or something. Strange.

 

Thanks! I'll mark it as solved now.

[akitchin]

yes, it assigns each array of info from a row, to an array item itself within $rows.  if you want to simply echo each array without having to nest foreach()s afterward, echo from within the while():

 

$rows = array();
echo '<pre>';
while ($current_row = mysql_fetch_assoc($result))
{
  echo print_r($current_row, TRUE).'<br />';
}

[jlange]

Thanks much. I actually ended up SELECTing another value from the table (id). The list is being parsed for a <select> box that will then be submitted to remove whatever objects were selected. So, I have the id go to <select value="id"> and then I put the name column information betwixt the <select value="id"> and the </select>.

 

I sort of feel like I'm actually coding useful things for the first time in my life.

[akitchin]

if that's the case (where you're using id and "name"), might as well set the ID's as array indexes and keep one array:

 

$items = array();
while ($current_row = mysql_fetch_assoc($result))
{
  $items["{$current_row['id']}"] = $current_row['name'];
}
print_r($items);

 

or, more properly (to avoid uninitialized key notices):

 

$items = array();
while ($current_row = mysql_fetch_assoc($result))
{
  $items = array_merge($items, array($current_row['id'] => $current_row['name']));
}
print_r($items);

[jlange]

Hah, well, you lost me.

 

But it's okay, I have something that works!

[jlange]

Slightly off-topic: I have the id as the primary key. Is there a way to make new id's fill in the spaces caused by deleting old entries? It's just a temporary solution for now while I test out implementations, but I'd like to know.

[akitchin]

not as far as i know - one solution is deleting the ID column and recreating it (this will reseat all the values), however this isn't recommended as if you have linked tables, it will mix up the relationships.

[jlange]

alright. Thanks!