php - 从返回的 sql 查询中提取值

Question

我正在使用 PHP 工作。我有一个可以从中提取数据的数据库。我现在已将数据输出到表中。

我的愿望是获取这个表并分离数据以传递给单独的变量，我如何传递给数组？

例如原文

Name          hours
Bob             4
Harry           6
Bob             2
Harry           5
Dave            1
Bob             2

返回并在表中

Bob             3
Harry           2
Dave            1

我想按照我的意愿单独获取每个值并且也是按行顺序，所以我有变量


鲍勃 = 3,
哈利=2，
戴夫=1

并且还取值 3,2,1

这将是输出和用于创建饼图的变量

这是我的sql查询

    $dbHandle = new PDO("mysql:host=$host;dbname=$dbName",$user,$pass);
    $query ="SELECT platform, count(*) FROM review GROUP BY platform";
    $sqlQuery = $query; // put your students table name
    echo $sqlQuery;  //helpful for debugging to see what SQL query has been created
    $statement = $dbHandle->prepare($sqlQuery); // prepare PDO statement
    $statement->execute();   // execute the PDO statement
    echo "<table border='1'>";
    while ($row = $statement->fetch()) {
    echo "<tr><td>" . $row[0] ."</td><td>". $row[1];}
    echo "</table>";
    $dbHandle = null;

Answer 1

您可以使用 AS 命名查询中的 SQL 列，并使用 ORDER BY 对其进行排序:SELECT platform AS plat, count(platform) AS count FROM review GROUP BY 平台 ORDER BY 计数 DESC

在获取结果的代码中，您可以选择将每一行作为关联数组获取:

while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {//this will fetch results and index them into the row array with column names:
    echo "<tr><td>" . $row['plat'] ."</td><td>". $row['count']."</td></tr>";
}

这可能是最简单的方法，但请记住，您稍后可能想要以不同的方式订购它们。在获得结果时回显结果可能是一个糟糕的设计。更好的方法是将它们存储在一个数组中，然后循环并回显它。所以这里是:

$results = $statement->fetchAll(PDO::FETCH_ASSOC);

//order the results the way you want here

echo "<table border='1'>";
//then echo them out:
foreach($results as $key => $result){
    echo "<tr><td>" . $result['plat'] ."</td><td>". $result['count']."</td></tr>";
}
echo "</table>";

总而言之，这是您的代码示例:

$dbHandle = new PDO("mysql:host=$host;dbname=$dbName",$user,$pass);
$query ="SELECT platform AS plat, count(platform) AS count FROM review GROUP BY platform ORDER BY count DESC";
$sqlQuery = $query; // put your students table name
//echo $sqlQuery;  //helpful for debugging to see what SQL query has been created
$statement = $dbHandle->prepare($sqlQuery); // prepare PDO statement
$statement->execute();   // execute the PDO statement
$results = $statement->fetchAll(PDO::FETCH_ASSOC);//data is here, just draw the chart

//you can access the data in the array like this: 

foreach($results as $key => $value){//$value is one row of results
    echo '<some html you="need" for the piechart>'.$value['plat']/*name of the platform*/.'</some closing tag perhaps><another tag>'.$value['count'].'</closed>';
}
$dbHandle = null;