我正在处理三个在PHP应用程序中相关的MySQL表。有些测试有很多问题,问题也有很多答案。我想做的是如下循环数据:
foreach($tests as $test)
{
echo $test->testName;
foreach($test->questions as $question)
{
echo $question->questionText;
foreach($question->answers as $answer)
{
echo $answer->answerText;
}
}
}
我想知道的是,MySQL查询和PHP代码以这种方式循环通过它会是什么?
editMySQL不能返回这样的数组,我应该说的是MySQL+PHP代码会是什么样子。
为了清楚起见,这些表格是测试、问题和答案。问题表包含test_id列,答案包含question_id
谢谢!
我希望恢复的结构大致如下:
array(
'testName' = 'Test name string',
'questions' = array(
array(
'questionId' = 1,
'questionText' = 'Question string',
'answers' = array(
array(
'answerId' = 1,
'answerText' = 'Answer string'
),
array(
'answerId' = 2,
'answerText' = 'Answer string'
)
)
)
)
);
编辑
我目前的实现如下,我想做的是急于加载数据,而不是执行这么多查询
$tests = getTests();
foreach($tests as $test){
$questions = getQuestions($test->id);
foreach($questions as $question){
$answers = getAnswers($question->id);
foreach($answers as $answer){
// do answer things
}
}
}
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
$query = "SELECT testname, question, answer
FROM tests
JOIN questions ON (tests.id = question.test_id)
JOIN answers ON (questions.id = answers.id)
WHERE 'your condition'
ORDER BY tests.id, question.id"
$result = $mysqli->query($query);
您可以使用in_array函数来获得您想要的结果(您可以制作数组或打印结果)。以下是打印值的示例。(您可以使用id而不是名称进行改进)
$tests_arr = array();
$questions_arr = array();
while( $row = $result->fetch_array(MYSQLI_ASSOC) )
{
if(!in_array($row['testname'], $test_arr)
{
$test_arr[] = $row['testname'];
echo $row['testname'];
}
if(!in_array($result['question'], $question_arr))
{
$questions_arr[] = $row['question'];
echo $row['question'];
}
echo $row['answer'];
}
MySQL不能以您想要的格式返回数组结构-它能做的最好的事情就是用2D数组来表示列/行。
两种备选方案都将在PHP中实现。
- 为每个测试运行一个查询;遍历每个测试并运行查询以获取该测试的所有问题;遍历每个问题,并运行一个查询以获取该问题的答案
示例:
// get each test
$tests = array();
$results = $mysqli->query('SELECT id, name FROM tests;');
while ($test = $results->fetch_object()) $tests[] = $test;
foreach ($tests as $t => $test) {
// get all questions for this test
$tests[$t]['questions'] = array();
$results = $mysqli->query('SELECT id, name FROM questions WHERE test_id = ' . $test['id']);
while ($question = $results->fetch_object()) $tests[$t]['questions'][] = $question;
foreach ($tests[$t]['questions'] as $q => $question) {
// get all answers for this question
$tests[$t]['questions'][$q]['answers'] = array();
$results = $mysqli->query('SELECT id, name FROM answers WHERE question_id = ' . $question['id']);
while ($answer = $results->fetch_object()) $tests[$t]['questions'][$q][] = $answer;
}
}
2.使用MySQL联接运行单个查询,并对结果列表进行预迭代,以构建所需格式的数组。
在普通SQL中,您可以进行连接,但这会使行变平,行数将是测试*问题*答案。然后,您必须解析属性并将其映射到适当的对象。类似这样的东西:
$sql = "SELECT * FROM tests t, questions q, answers a WHERE q.t_id = t.id AND a.q_id = q.id";
$rows = // do your fetching, pdo, mysqli, whatever, just get the rows
$tests = array();
foreach($rows as $row) {
// map test
if(!isset($tests[$row['t_id']])) $tests[$row['t_id']] = array(
// set test attributes here (without the questions)
);
// map question
if(!isset($tests[$row['t_id']]['questions'][$row['q_id']])) = array(
// set question attributes here (without the answers)
);
// map answer
if(!isset($tests[$row['t_id']]['questions'][$row['q_id']]['answers'][$row['a_id']])) = array(
// set answer attributes here
);
}
$tests数组现在应该具有所需的层次结构,但一定要测试一下,但即使它不起作用,你也会明白的。
话虽如此,我们来看看ORM-s。对于最简单的(以一种好的方式)之一,请看一下:http://phpmaster.com/database-interaction-made-easy-with-notorm/
本教程很容易完成,最终您会得到您所要求的,一组具有属性的对象和基于表关系的良好层次结构。
您需要LEFT JOIN
,以便结果类似于:
test1|question1|answer1
test1|question1|answer2
test1|question2|answer1
test1|question2|answer2
test2|question1|answer1
然后以这样一种方式循环,它们总是按这个顺序出现,所以写一个循环来获取一行,如果测试自上一个测试以来发生了变化,则转到数组中的下一个测试。对问题重复上述步骤,最后添加答案。
一些伪码
fetcharray()
if test > lasttest
arraytest++
arrayquestion = 0
elif question > lastquestion
arrayquestion++
array[arraytest][arrayquestion][] = fetched array