How to put MySQL cell values into variables named after column names in PHP

Question

This code selects cell values in MySQL and manually adds them to PHP variables:

$sql = "SELECT * FROM table LIMIT 0,1";
$result = mysql_query($sql);
while($rows = mysql_fetch_array($result)) {
    $col1 = $rows['col1'];
    $col2 = $rows['col2'];
    $col3 = $rows['col3'];
    .....
}

This is obviously unmanageable for multiple and large tables.

What's a better way to automatically generate the variable names and values without manually entering all the column names on all tables?

Answer 1

Wouldn't it be more convenient having associative arrays? That way you can call your variables with their column name as you describe plus you have the benefit of having them bundled in one unit which is much better if you need to pass more than one of them to any function or view or whatever.

so I would use mysql_fetch_assoc and that's it.

Answer 2

You could use extract() for that. But I'd keep the values in the array.

..SELECT x,y,z FROM ..
while( false!==($rows=mysql_fetch_array($result, MYSQL_ASSOC)) ) {
    extract($rows);
    echo $x;
    ...
}

Answer 3

Use variable variables: Code example:

$a = 'col1';
$$a = 'somevalue';
echo $col1;

will output 'somevalue'.

http://www.php.net/manual/en/language.variables.variable.php

Answer 4

I don't recommend having a variable for each row, I used to do the same to simplify writing HTML later:

echo "<tr><td>$name</td><td>$count</td></tr>";

Instead of:

echo "<tr><td>{$row['name']}</td><td>{$row['count']}</td></tr>";

Until I found a better and more readable way do it using mysql_fetch_object

while ($row = mysql_fetch_object($result)) {
  :
  echo "<tr><td>{$row->name}</td><td>{$row->count}</td></tr>";
  :
}

Answer 5

I think this is what you're looking for

$sql = "SELECT * FROM table LIMIT 0,1";
$result = mysql_query($sql);
while ($rows = mysql_fetch_array($result, MYSQL_ASSOC)) {
    foreach ($rows as $key => $value) {
     $$key = $value;
    }
}