php - 将MySQL查询结果存储到二维数组中

Question

我还没有看到类似的问题，所以我要问一个新问题。我还在尝试使用循环和数组，所以如果这是一个非常愚蠢的问题或非常糟糕的代码，我深表歉意。我确实能够用大约 50 行代码实现我想要的结果，但它更加不优雅(我基本上将每个查询都放在硬编码行中，而不是使用变量)。因此，我正在尝试学习如何更简洁地编写代码以使其更加高效。但我可能走错了路......

我有一个具有以下基本结构的表格:

[id / username / email / content / side / category / rating]

侧面可以是"is"或“否”，类别可以是“红色”、“蓝色”或“绿色”。这些在提交表单中受到限制。

我试图在循环中执行以下查询来提取所有所需的数据并将它们存储到 php 中的二维变量中:

$dialecticSides = array("yes","no");
$numberSides = 2;
$dialecticRow = array("red","blue","green");
$numberRows = 3;

$dialectic_queryString = $dialectic_mysqlQuery = $dialectic_sqlResult = $dialectic_totalRows = array();

for($x=0;$x<$numberSides;$x++) {
    for ($y=0;$y<$numberRows;$y++) {        
        $dialectic_queryString[$x][$y] = "SELECT * FROM ". $dialectic_sqlTable ." WHERE side = '" . $dialecticSides[$x]. "' AND category = '". $dialecticRow[$y]."' ORDER BY rating DESC";
        $dialectic_mysqlQuery[$x][$y] = mysql_query($dialectic_queryString[$x][$y], $commenting_conn) or die(mysql_error());
        $dialectic_sqlResult[$x][$y] = mysql_fetch_assoc($dialectic_mysqlQuery[$x][$y]);
        $dialectic_totalRows[$x][$y] = mysql_num_rows($dialectic_sqlResult[$x][$y]);

        var_dump($dialectic_sqlResult);
    }
}

我想要的输出是这样的:

Array
(
    [0] => Array
        (
          [0] => Array
             (
               all the comments which are in row 0 (yes) and side 0 (red)
             )
          [1] => Array
             (
               all the comments which are in row 0 (yes) and side 1 (blue)
             )
          [2] => Array
             (
               all the comments which are in row 0 (yes) and side 2 (green)
             )
        )
    [1] => Array
        (
          [0] => Array
             (
                all the comments which are in row 1 (no) and side 0 (red)
             )
          [1] => Array
             (
               all comments which are in row 1 (no) and side 1 (blue)
             )
          [2] => Array
             (
               all the comments which are in row 1 (no) and side 2 (green)
             )
        )
)

本质上，如果我想知道我正在做的事情是否有意义，或者我是否应该放弃并返回将查询硬编码为不同的结果变量。

我的查询失败 - 查询的输出为空。我究竟做错了什么？提前致谢!

====解决了====

for($x=0;$x<$numberSides;$x++) {
for ($y=0;$y<$numberRows;$y++) {        
    $queryString[$x][$y] = "SELECT * FROM comments WHERE topic_id = '{$topicid}' AND side = '{$x}' AND row = '{$y}'";
    $result[$x][$y] = mysqli_query($db_connection,$queryString[$x][$y]) or die(mysqli_error($db_connection));
    while($eachcomment = mysqli_fetch_assoc($result[$x][$y])) {
        $array[$x][$y][] = $eachcomment;
    }
}

Answer 1

您不想将查询等放入数组中。
您只需要一个查询。
定义 2 个数组以获取输出行和列:

$rowarray = array("yes" => 0, "no" => 1); // 2 rows?
$colarray = array("red" => 0, "blue" => 1, "green" => 2); // 3 cols?

初始化输出数组:

$outputarray = array(array('', '', ''), array('', '', ''));

从您的查询中获取 $result。

SELECT * FROM table

(没有 WHERE 子句)
然后处理结果:

while (($qrow = mysql_fetch_assoc($result))<>NULL) {

$qrow 是一个关联数组:'side' => 'yes'、'category' => 'red' 等
对于每个 qrow，获取输出行和列:

$row = $rowarray[$qrow['side']];
$col = $colarray[$qrow['category']]

此时你可以将你想要的内容累积到$outputarray中:

$outputarray[$row][$col] .= $qrow['comments']; 

这是“while”循环的结束:

}