列名和值获取PDO

Question

I try to get the values for columns in a table. First i want to fetch the tables. then i did like:

$sql = $dbh->query("DESCRIBE `players` ;");
foreach ($sql->fetchAll(PDO::FETCH_NUM) as $k) {
    echo 'Table:'  . $k[0]. '<input type="text" values=""><br>  ' ;
}

So what is best way now to get value for each table in same foreach?

SOLUTION HOW I DID:

function getColumnValue($column,$name) {
        global $dbh;
        $sqll = ("SELECT `$column` FROM `players` WHERE `name`='$name' ");
        $sthh = $dbh->prepare($sqll);
        $sthh->execute();
        return $sthh->fetch(PDO::FETCH_ASSOC);
}


foreach ($sql->fetchAll(PDO::FETCH_NUM) as $k) {
    $test = getColumnValue($k[0],'Test');
    echo 'Table: '.$k[0] .' <input type="text" value="'.$test[$k[0]].'" style="color:#000;"> <br> ';
}

Answer 1

You have two options to do this:

1. you use two separate queries as I explained in my comment to your question: one to fetch the table description as you already do and a second one to fetch the tables content (the rows). You need two (actually three) loops to iterate over the results, roughly like this:

   • fetch table description ("DESCRIBE TABLE tablename")
     • iterate over columns in result
     • output single column name
   • fetch contents of the table ("SELECT * FROM tablename ...")
     • iterate over rows inside the result
     • iterate over columns inside each row
       • output cell content of current row and column

2. you can use a union query for this, though it's kind of ugly and does not really simplify the structure of your code:

   SELECT 'colname1' AS colname1, 'colname2' AS colname2, 'colname3' AS colname3 FROM table name

   UNION

   SELECT colname1, colname2, colname3 FROM table name

   This single query concatenates the results of the two contained separate subqueries, so it gives what you originally asked for. You just have to make sure you query the same number and type of columns in both subqueries. But be aware that this is not really good style, since it is hard to debug and hard to maintain.

In general it is questionable if fetch the column names to output them is really such a good idea. I would think twice about this: you combine storage architecture and visualization into a single layer which is a bad idea: you will run into big problems when you have to make changes later on. Typically those different aspects should be kept separate for a good reason.