phpmysqlmysqli

Get values from a column using its name


Is it possible to get all the values from a database knowing the name of the column? I have a database like this: The day of the week using a code number and some rows with information for each day. When the user asks it, I want to send all the info from that certain day.

id      1         2         3

1       Info    Info     Info
2       Info    Info     Info

Until now I have this:

 // I know the know of the column and I want to compare it with the week code 
 //   of today so they can get the info for today
        $day=date(w);
        $conn = mysqlConnector();
        $cols = $conn->prepare('SELECT * FROM cond WHERE COLUMN_NAME = :col LIMIT 1000 OFFSET 1');
        $cols->execute(array('col'=> $day));

I'm not sure if I should use COLUMN_NAME or how to use it, but this code doesn't work. Any idea?


Solution

  • You can't use column names as SQL bind variables.

    You could use a SQL statement something like this:

    SELECT `1` FROM cond LIMIT 1000 OFFSET 1
    

    To do this in php, you need to do something like this to create your query with

    `1`
    

    inserted into it as a literal.

    $day = 1;
    ...
    $cols = $conn->prepare("SELECT `$day` 
                              FROM cond 
                             WHERE COLUMN_NAME = :col
                             LIMIT 1000 OFFSET 1");
    $cols->execute();
    

    You should be careful with this; make sure the value of $day can't get corrupted by a malicious user.

    You should also be careful to make sure your column names, which happen to be integers, are surrounded by backticks:

    SELECT 1 FROM cond
    

    will give back a sequence of 1s, but

    SELECT `1` FROM cond
    

    will give back your data.