Search code examples
phpvariables

PHP MySQL how to set column/field names into variables after fetch_assoc

I am trying to get column names and the values, and then set the column names into variables... Does it make sense?

$con = mysqli_connect('localhost', 'mysql_user', 'mysql_password', 'db_name');
$sql = "SELECT * FROM `table`";
$res = mysqli_query($con,$sql);
while($row = $res->fetch_assoc()) {
    $column1 = $row['column1']; // <- can these be
    $column2 = $row['column2']; // as simple as one
    ...
    $column100 = $row['column100']; // line or two?
}
mysqli_close($con);

Can anyone guide me how to do that? Thanks.

Solution

  • You can use a string to declare a variable, called variable variables

    while($row = $res->fetch_assoc()) {
    foreach ($row as $key => $value)
        $$key = $value;
//      ^----------------------notice the double dollar
}

    Or, you can use extract

    while($row = $res->fetch_assoc()) {
    extract($row);
}

    To make sure you don't want to overwrite some variables already existing, you can prefix them.

    while($row = $res->fetch_assoc()) {
    foreach ($row as $key => $value)
        $someNicePrefix_{$key} = $value;
//                      ^----^-------------notice the curly brackets
}

    Using extract, you can add more parameters (described in the documentation linked above)

    while($row = $res->fetch_assoc()) {
    extract($row, EXTR_PREFIX_ALL, "someNicePrefix");
}

    Both methods will have the same effect. Notice that for extract, a character _ will be inserted between prefix and variable name