php对sql记录进行排序

发布于 2024-12-03 16:20:13 字数 455 浏览 0 评论 0原文

我正在循环访问表中的记录，并且在每个循环中它都会打开另一个表以从中提取数据。我需要通过第二个表中的字段对主循环（$rs）进行排序。

我是 php 和 sql 的初学者，所以对我来说要轻松一些：P

代码示例：

$sql_result = mysql_query("SELECT * FROM table1", $db); // i want this to ORDER BY data from table2
while($rs = mysql_fetch_array($sql_result)) { 
    $sql_result2 = mysql_query("SELECT * FROM table2 WHERE id='$rs[data]'", $db); 
    $rs2 = mysql_fetch_array($sql_result2);

    //get data from table 2

原文

I'm looping through records in a table, and in each loop it opens another table to pull data from it. I need to ORDER the main loop ($rs) by a field from the 2nd table.

I'm somewhat a beginner to php and sql so go easy on me :P

code example:

$sql_result = mysql_query("SELECT * FROM table1", $db); // i want this to ORDER BY data from table2
while($rs = mysql_fetch_array($sql_result)) { 
    $sql_result2 = mysql_query("SELECT * FROM table2 WHERE id='$rs[data]'", $db); 
    $rs2 = mysql_fetch_array($sql_result2);

    //get data from table 2

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

陌生 2024-12-10 16:20:13

循环遍历结果集并为每一行执行第二个查询是不好的做法，会导致数据库不必要的负载。
看起来您需要一个INNER JOIN：

    SELECT t1.*
      FROM table1 t1
INNER JOIN table2 t2 ON t2.id = t1.data
  ORDER BY t2.field

这样，您就可以通过一个单个查询获取所有数据。

编辑：
一般来说，您应该尽量避免返回所有列（SELECT *），而只选择您真正需要的列：

SELECT t1.id, t2.field1, t1.field2, ...

这不仅会减少数据库的负载（选择和传输的数据更少）网络），但它也将有助于消除双列名称，因为当您想要显示双列名称时，双列名称会导致问题（正如您自己发现的那样）。

你可以做两件事来避免这个问题：

如果两个表都有相同名称和相同内容的列（例如，如果这些列用于连接表），则只需选择其中之一（无论是哪一个，因为它们相同）。
如果两个表都有名称相同但内容不同的列，并且您需要这两个表，最简单的方法是为其中一个表指定一个别名：
```
SELECT t1.id, t2.id AS AnotherId, ...
```
这将导致第二列被命名为“AnotherId”，因此您可以使用 $rs[AnotherId] 获取它。
老实说，我不是 PHP 专家，所以我不知道 PHP 是否也理解 $rs[table.field]，但是有足够多的数据访问技术在查询返回两列时出现问题具有相同的名称...因此为重复的列添加别名永远不会错。

Looping through a resultset and executing a second query for each row is bad practice and causes unnecessary load on the database.
It looks like you need an INNER JOIN:

    SELECT t1.*
      FROM table1 t1
INNER JOIN table2 t2 ON t2.id = t1.data
  ORDER BY t2.field

This way, you can get all the data with one single query.

EDIT:
Generally you should try to avoid returning all columns (SELECT *) and select only the columns that you really need:

SELECT t1.id, t2.field1, t1.field2, ...

This will not only reduce the load on the database (less data to select and to transfer over the network), but it will also help to eliminate double column names because double column names cause problems when you want to display them (as you found out yourself).

You can do two things to avoid this problem:

if both tables have columns with identical names and identical content (e.g. if these columns are used to join the tables), just select one of them (no matter which one, because they are identical).
if both tables have columns with identical names and different content and you need both of them, the easiest way would be to give one of them an alias:
```
SELECT t1.id, t2.id AS AnotherId, ...
```
This will cause the second column to be named "AnotherId", so you can get it with $rs[AnotherId].
Honestly, I'm no PHP guru so I don't know if PHP understands $rs[table.field] as well, but there are enough data access technologies which have problems when a query returns two columns with identical names...so aliasing duplicate columns can never be wrong.

回复收藏 0 原文

~没有更多了~