Mysql PDO : get row with "dynamic" fields in query

Question

I have a query with a dynamic field, how do I access this field without knowing its name?

define('FIELD_NAME',"name");
$stmt = $connexion->query('SELECT '.FIELD_NAME.' from mytable);   

while ($rs=$stmt->fetch(PDO::FETCH_OBJ)){
    echo $rs->FIELD_NAME;   // DOESN'T WORK
    echo $rs->name;         // WORK
}

Answer 1

Wrap the constant in {} to create dynamic variables.

echo $rs->{FIELD_NAME};

You can see some example from the documentation.

Curly braces may also be used, to clearly delimit the property name.

Demo: http://3v4l.org/sgvV4

Answer 2

There are lot of approaches to this. If it's not important that the variable name match the column_name, you could assign an alias to the expression in the SELECT statement

For example:

 SELECT whateverexpression AS mycol FROM mytable LIMIT 1;

Then, you'd "know" the name of the variable in the object is $mycol

    echo $rs->mycol;

I'm thinking this approach might be good in the more general case, when you're dealing with tables that have column names that were assigned by syphilitic idiot developers who had a good reason to

CREATE TABLE t (`Hey!$I (can)^name.\my->column Wh@tever` INT);
INSERT INTO t VALUES (42);
SELECT `Hey!$I (can)^name.\my->column Wh@tever` FROM t;

---

Obviously, there are lots of other approaches, like avoiding PDO::FETCH_OBJ and using PDO::FETCH_NUM instead. If you want to stick with PDO::FETCH_OBJ, I'm thinking assigning an alias would be workable.

Retrieving the metadata from the resultset is an approach I would consider, if getColumnMeta wasn't still experimental.

Answer 3

define('FIELD_NAME',"name");
        $stmt = $connexion->query('SELECT '.FIELD_NAME.' from mytable');   

        while ($rs=$stmt->fetch(PDO::FETCH_NUM)){
            echo $rs[0];
        }

Using this approach, if FIELD_NAME is defined to something like *, you will still be able to get the first column.