Packaging mysql_query results

does mysql_fetch_assoc() = key/value pairs?

[createErrorMsg]

I have a database table, Widgets, that stores the following information:

Widget_name, widget_region, widget_color, widget_size

I want to SELECT a row from that table, then store the results in an array with key/value pairs that can be sent into another function and used to populate a form.

Thus far, I've been doing the following....

$sql = "SELECT * FROM Widgets WHERE id=$myid";
$results = mysql_query($sql);
$fetch = mysql_fetch_assoc($results);

$name = $fetch['widget_name'];
$region = $fetch['widget_region'];
$color = $fetch['widget_color'];
$size = $fetch['widget_size'];

$all_values = array('name'=>$name, 'region'=>$region, 'color'=>$color, 'size'=>$size);

$All_values is then sent to the necessary functions which pluck values from the array and use as needed.

While this works well, it's a real pain to make changes to what information is stored in the database. Adding a field to the DB means also going into the script and adding a new line to pull that field's result and add it to a useable variable, then another manual addition to the $all_values array to add the new variable in.

I have a sneaking suspicion that I'm adding unneccesary steps here. I would much prefer to have the script automatically pull the values from the DB and create the $all_values array with key/value pairs taken directly from the DB.

Is there a way to do this? I know mysql_fetch_assoc() pulls the result row as an associative array, but is this the same thing as the $all_values array I've manually built above? Can I just dump the mysql_fetch_assoc() results into $all_values and send it into another function for use?

If not, can anyone suggest another method to automatically populate the $all_values array with the information from the database?

Thanks in advance for any responses. I feel like something really obvious is staring me in the face...

cEM

[jollymcfats]

Yes, mysql_fetch_assoc() is already doing what you want with $all_values. The keys are the column names, and the values are the column values. It's a regular array, and can be sent off to any function you like.

You're making local variables and dropping a prefix from the column names in the code you're got there. You can continue to do that too, in fewer steps:

$sql = "SELECT widget_name AS name, widget_region AS region, widget_color AS color, " .
"widget_size AS size FROM Widgets WHERE id=$myid";
$results = mysql_query($sql);

$all_values = mysql_fetch_assoc($results);
list($name, $region, $color, $size) = array_values($all_values);

Much easier to update when you've made a database change. IMHO it should never be easy to make a database change- if it is, the code probably has either too many assumptions or too much magic, and either will come back to bite you eventually.

[coopster]

If you weren't renaming your variables, you could simply

extract(mysql_fetch_assoc($results));

[jollymcfats]

Nice.

extract()

would actually work fine in place of the

list(...) ...

above:

extract($all_values);

or combine the last two lines:

extract($all_values = mysql_fetch_assoc($result));

[createErrorMsg]

Sorry to have taken so long to reply.

Thanks to both of you for the great responses. Everything is currently copacetic. :)

cEM