通过循环查询创建多个表 [英] Create multiple tables by cycling through a query

问题描述

这是我当前的代码: $ varVeh = $ _ POST ['Veh_num'];

Here is my current code: $varVeh=$_POST['Veh_num'];

$sql_HiScores = "SELECT 
     c.course_name as course,
     e.distance as distance, e.score as score,
     e.time as time, e.user as User
   FROM hc_entries e
   LEFT JOIN hc_course c on e.course=c.course_num
   WHERE e.vehicle=$varVeh
   ORDER BY course, score DESC";

$result_HiScores = mysql_query($sql_HiScores);

$sql_vehName="SELECT Veh_name FROM hc_vehicle_type WHERE Veh_num=$varVeh ";
$result_vehName = mysql_query($sql_vehName);
$vehName=mysql_fetch_assoc($result_vehName);

echo "<table><tr><th>Best Scores for ".$vehName['Veh_name']."</th></tr></table>";
echo "<table border='1'>";
echo "<tr><th>Course</th><th>Score</th><th>Distance</th><th>Player</th><th>Time</th></tr>";

while($row = mysql_fetch_array($result_HiScores))
{
    echo "<tr>";  
    echo "<td>" .$row['course'] . "</td>";
    echo "<td>" .$row['score'] . "</td>";
    echo "<td>" .$row['distance'] . "</td>";
    echo "<td>" .$row['User'] . "</td>";
}

echo "</table>";

我想我要做的是创建一个查询，该查询从构建数组的e.course中选择*.然后循环遍历具有数组结果的现有查询.最后，我想为每个课程显示单独的表格，并将其限制为每个课程的前5个结果.

What I think I have to do is create a query that selects * from e.course that builds an array. Then cycle through the existing query with the array results. Finally, I would like to display individual tables for each course and limit it to the top 5 results for each course.

任何人都可以确认或否认我的逻辑，并指出我的方向吗?

Can anyone confirm or deny my logic, and point me in a direction?

推荐答案

首先，您不应该使用mysql_函数，因为它们已被弃用.至少，您应该切换到 mysqli_ (一个非常简单的切换)或更好的方法，了解如何使用 PDO .切换有点不同，涉及更多，但是您的代码会更好，更安全.

First of all, you shouldn't be using the mysql_ functions, they're deprecated. At the least, you should switch to mysqli_ (a pretty easy switch), or better, learn how to use PDO. It's a bit different and more involved to switch, but your code will be better and safer for it.

顺带一提:您的逻辑非常准确.就我所知，用SQL将您的结果限制为每门课程的前5个结果并不是一件容易的事，因此您的计划是好的:查询课程列表，然后循环浏览使用您现有的查询，为每门课程运行一次，并以LIMIT 5排名前5.

With that out of the way: your logic is pretty accurate. Limiting your results to the top 5 results for each course in one query isn't something that's easily done with SQL to my knowledge, so your plan is good: query a list of courses, then cycle through them with your existing query, running it once for each course, with a LIMIT 5 to get the top 5.

您最好将表生成也保留在此循环中，因为它是每课程表.您希望将VehName查询移出循环，因为您只需要运行一次即可.

You might as well keep the table generation within this loop as well, since it's a table-per-course. You'd want to move the VehName query out of the loop, since you only need to run that once.

此外，一些未经请求的PHP建议:标记外的任何文本都将直接输出，因此请利用其内置模板和

Also, some unsolicited PHP advice: any text outside of the tags will just be output directly, so take advantage of its built-in-templating and alternative syntax to make your table generation code nicer:

<?php
   /* Gather your data here... */
?>
<table>
  <tr><th>Best Scores for <?php echo $vehName['Veh_name'] ?></th></tr>
</table>
<table border='1'>
  <tr>
    <th>Course</th>
    <th>Score</th>
    <th>Distance</th>
    <th>Player</th>
    <th>Time</th>
  </tr>
  <?php while($row = mysql_fetch_array($result_HiScores)): ?>
  <tr>  
    <td><?php echo $row['course'] ?></td>
    <td><?php echo $row['score'] ?></td>";
    <td><?php echo $row['distance'] ?></td>";
    <td><?php echo $row['User'] ?></td>";
  </tr>
  <?php endwhile; ?>
</table>

这篇关于通过循环查询创建多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！