我遇到了一个有趣的问题,即使用一个带有几个连接的选择查询从 mysql 表中检索数据。
1)查询:
$task_details = "SELECT tasks.task, ";
$task_details = $task_details . "tasks.description,";
$task_details = $task_details . "tasks.finishby, ";
$task_details = $task_details . "responsibles.full_name, ";
$task_details = $task_details . "task_assignments.completed, ";
$task_details = $task_details . "tasks.id, ";
$task_details = $task_details . "responsibles.user_id ";
$task_details = $task_details . "FROM tasks,task_assignments,responsibles ";
$task_details = $task_details . "WHERE ";
$task_details = $task_details . "tasks.id = task_assignments.id_task AND ";
$task_details = $task_details . "responsibles.id = task_assignments.id_assignee AND ";
$task_details = $task_details . "tasks.id = $id_task;";
$task_details_q = mysql_query($task_details) or die(mysql_error());
1a) 结果示例查询:
SELECT tasks.task, tasks.description, tasks.finishby, responsibles.full_name, task_assignments.completed, tasks.id, responsibles.user_id
FROM tasks, task_assignments, responsibles
WHERE tasks.id = task_assignments.id_task
AND responsibles.id = task_assignments.id_assignee
AND tasks.id =19
2)HTML/PHP代码:
<table class="task_table">
<thead>
<th>Task</th>
<th>Description</th>
<th>Due date</th>
<th>Person</th>
<th>Completed</th>
</thead>
<?php
$even = false;
$trow = "";
while($row = mysql_fetch_array($task_details_q))
{
$trow = $trow . "<tr";
if($even) $trow = $trow . " style='"background-color: #f2f2ed; '"";
$trow = $trow. ">";
$trow = $trow . "<td >$row[0]</td>";
$trow = $trow . "<td>" . $row[1] . "</td>";
$trow = $trow . "<td>" . date('d-m-Y',$row[2]) . "</td>";
$trow = $trow . "<td>$row[3]</td>";
$trow = $trow . "<td style='"text-align: center;'" >";
if($row[4] > 0)
{
$trow = $trow . "<a href='"javascript:modifyCompleted('remove','$row[6]',$row[5])'" title='"Click to change completion of this task by this person'">yes</a>";
}
else
{
$trow = $trow . "<a href='"javascript:modifyCompleted('add','$row[6]',$row[5])'" title='"Click to change completion of this task by this person'">no</a>";
}
$trow = $trow . "</td>";
$trow = $trow . "</tr>";
$even =! $even;
$number = $number + 1;
}
$trow = $trow . "<tr style='"border-top: 1px solid #666666;'"><td></td><td></td><td></td><td></td>";
$trow = $trow . "<td>";
$trow = $trow . "<a href='"javascript:modifyCompleted('add_all','all',$task_details_array[5])'" title='"Click to complete all'">Complete all</a>";
echo $trow;
?>
</table><br />
<span style="text-align: center;display:block;font-size: 12px;"><a href="tasks.php">Go back to task overview</a></span>
3)问题/问题:由于某种原因,显示的表格总是省略一条记录。我在PHP脚本的数量中使用了相同(或非常相似)的概念,但从未遇到过相同的问题。我认为查询本身不是问题 - 当我直接针对数据库运行它时,它会返回正确数量的值......(我认为)。
:
1)您不应该在添加信息时提交答案。 您应该单击原始问题下的"编辑",然后将新信息添加到问题中。
2)我认为如果我澄清mysql_query
和mysql_fetch_array
是如何工作的,你会看到发生了什么。
当您使用"SELECT"调用mysql_query
时,查询它会返回一个resource
。 此resource
只是对记录集的引用。 然后,当您在该resource
上调用 mysql_fetch_array
时,它将从集合中返回当前记录,并前进记录指针。
因此,当您第一次调用mysql_query
时,记录指针指向第一个结果。 然后调用mysql_fetch_array
第一条记录作为数组返回,指针前进到第二条记录。 下次调用mysql_fetch_array
时,将返回此第二条记录,然后指针将指向第三条记录。
如果没有第 3 条记录,下次调用 mysql_fetch_array
时将无法找到相应的记录,并且会返回 false。
这就是您使用while($row = mysql_fetch_array($task_details_q,MYSQL_NUM))
的原因。 您将结果放入变量$row
并推进结果指针,然后使用 $row
执行一些操作。 最终,您将把指针推进到最后一个结果之后,$row
将为假,这将阻止您的 while 循环前进。
现在我已经完成了理论,以下是您的代码发生了什么(我将删除不相关的代码//...
并在此过程中添加我自己的注释):
$task_details_q = mysql_query($task_details) or die(mysql_error());
//now you have a resource $task_details_q, it points to the first result
$task_details_array = mysql_fetch_array($task_details_q,MYSQL_NUM);
//you retreive the first result, and advance the pointer to the second result
for($x=0;$x < sizeof($task_details_array);$x++)
{
//you perform operations (echo'ing in this case) on your first result
echo $x . ". : " . $task_details_array[$x] . "<br />";
}
//... HTML CODE SKIPPED
$even = false;
$trow = "";
//the first time this while statement is called you place the data from the second result in $row,
//and advance the pointer to the third result
//the next time you go through the loop you try to place the data from the third result in $row,
//since you say there are only 2 results to your query $row is simply false.
//This causes the while to stop executing and the code to continue on
while($row = mysql_fetch_array($task_details_q,MYSQL_NUM))
{
//... PRINT TABLE CELLS FROM $row SKIPPED
}
//... REMAINING HTML SKIPPED
我不确定你是否真的需要代码块:
$task_details_array = mysql_fetch_array($task_details_q,MYSQL_NUM);
for($x=0;$x < sizeof($task_details_array);$x++)
{
echo $x . ". : " . $task_details_array[$x] . "<br />";
}
或者,如果您只是添加它进行调试。 如果它只是用于调试,请将其删除,您的第一个结果将显示在 while 循环中。 如果您需要执行该for
循环,请对此答案进行注释,我将编辑我的答案以在for
和while
循环中使用第一个结果。
由于 php 似乎是正确的,因此问题的原因可能在 html 中。您的标题行没有<tr>
标签,因此浏览器可能因为您缺少第一条记录而窒息。
修复此问题后,我建议您在 html 验证器中检查您的 html,以确保那里不再有错误。
@Ben:这是 mysql_query($task_details)
和 while($row = mysql_fetch_array($task_details_q))
之间的完整代码:
$task_details_q = mysql_query($task_details) or die(mysql_error());
$task_details_array = mysql_fetch_array($task_details_q,MYSQL_NUM);
for($x=0;$x < sizeof($task_details_array);$x++)
{
echo $x . ". : " . $task_details_array[$x] . "<br />";
}
?>
<h3>Task details - "<?php echo strtoupper($task_name); ?>"</h3>
<span class="notifierOK">Table below lists all people assigned to the task - including the status (complete / incomplete). To change person's status click on the 'yes' or 'no' link. If you then go back
to (<a href="tasks.php">previous page</a>) the completion percentage value will be re-calculated.</span><br />
<form id="task_details" method="post" name="task_details" style="margin-left: auto; margin-right: auto;width: 800px;box-shadow:10px 10px 5px #888888;">
<table class="task_table">
<thead>
<tr>
<th>Task</th>
<th>Description</th>
<th>Due date</th>
<th>Person</th>
<th>Completed</th>
<tr />
</thead>
<?php
$even = false;
$trow = "";
while($row = mysql_fetch_array($task_details_q,MYSQL_NUM))
请注意,我根据您的建议添加了"MYSQL_NUM"作为数组类型,因此它不一定属于那里。
我解决了它,我遇到了同样的问题。
$run_query = mysqli_query($conn, $stores);
if ($run_query === false){
//error
}else if (mysqli_num_rows($run_query)){
$row = mysqli_fetch_array($run_query);
echo 'bla bla bla' //on this echo I needed to show the data once.
$run_query = null;
$run_query = mysqli_query($conn, $stores);
while ($row = mysqli_fetch_array($run_query)) { //and here I needed to show up the loops of the results of my query.}
所以。。。我只是重置了运行查询并重新运行搜索的变量。它对我有用!;)