PHP MySQL 从 2 个不同的表中选择 * 并显示两个表中按日期时间列排序的混合在一起的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15229239/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PHP MySQL Select * from 2 different tables and display the data mixed together ordered by datetime column in both tables
提问by prodigitalson
I have the following code...
我有以下代码...
<?php
$sql =
"SELECT
tickets.company, tickets.datetime, tickets.ticketnumber,
customer_notes.customer, customer_notes.timestamp, customer_notes.notes
FROM
tickets, customer_notes
WHERE tickets.company = '".$_GET["seq"]."' AND
customer_notes.customer = '".$_GET["seq"]."'
GROUP BY customer_notes.customer, tickets.company ";
$rs=mysql_query($sql,$conn) or die(mysql_error());
echo '<table width="100%" border="0" cellspacing="5" cellpadding="5">';
while($result=mysql_fetch_array($rs))
{
echo '<tr>
<td>'.$result["timestamp"].'</td>
<td>'.$result["notes"].'</td>
</tr>
<tr>
<td>'.$result["datetime"].'</td>
<td>'.$result["ticketnumber"].'</td>
</tr>';
}
echo '</table>';
?>
The tickets table and customer_notes table have no reference at all - they are totally separate.
Tickets 表和 customer_notes 表根本没有参考——它们是完全分开的。
The tickets table is for support ticket when customer log issues they are having and then the customer_notes table is for when customers phone up, everything that is spoken about over the phone is logged in this table.
票证表用于客户记录他们遇到的问题时的支持票，然后 customer_notes 表用于客户打电话时，通过电话谈论的所有内容都记录在此表中。
I am trying to make it display data from the tickets table and the customer notes table in datetime order.
我试图让它按日期时间顺序显示来自票证表和客户备注表的数据。
so if a ticket was created, then a note added, then another note added, then a ticket they will display in datetime order using the above code however it is not displaying it correctly
因此，如果创建了一张票，然后添加了一个注释，然后添加了另一个注释，然后他们将使用上面的代码按日期时间顺序显示一张票，但是它没有正确显示
回答by Steve's a D
Your question is a little vague so I'll give you the two options.
你的问题有点含糊，所以我会给你两个选择。
1)You have two similar tables and want to select all the records from both: (If you want all the data from columns A, B, C which both tables have)
1）您有两个相似的表，并希望从两个表中选择所有记录：（如果您想要两个表都有的 A、B、C 列中的所有数据）
If the tables have a similar structure(same columns) you should use a union. A union takes the results from both tables, and combines them into one result set. However, both tables have to have the same structure (or at least, call the fields that are similar if the entire structure isn't identical).
如果表具有相似的结构（相同的列），则应使用union。联合从两个表中获取结果，并将它们组合成一个结果集。但是，两个表必须具有相同的结构（或者至少，如果整个结构不相同，则调用相似的字段）。
2)The tables have different columns and you want to merge them over some common attribute: (If you want column A,b and C from the first table, and then column D, E, and F from the second table.)
2）这些表有不同的列，你想通过一些共同的属性合并它们：（如果你想要第一个表中的 A、b 和 C 列，然后第二个表中的 D、E 和 F 列。）
You should joinusing a primary/foreign key. The most common join is an inner join which says if either the left or right table don't match up, omit that record. It all depends on what you're attempting to do (your question was a little vague)
您应该使用主键/外键加入。最常见的连接是内部连接，它表示如果左表或右表不匹配，则忽略该记录。这完全取决于您尝试做什么（您的问题有点含糊）
Also, your code is extremely vulnerable to SQL injection attacks
此外，您的代码极易受到 SQL 注入攻击
回答by prodigitalson
I think what you are trying to do here is get the list of tickets with customer notes in which case a simple left join would work, though you may need to loop through the entire list before doing your ouput:
我认为您在这里尝试做的是获取带有客户备注的票证列表，在这种情况下，简单的左连接可以工作，尽管您可能需要在执行输出之前遍历整个列表：
Im not sure how your customer notes are related to the tickect exactly but i see youre using company in your query so thats what ill use:
我不确定您的客户备注如何与ticect 完全相关，但我看到您在查询中使用了公司，所以这就是错误使用：
SELECT t.company, t.ticketnumber, t.datetime, t.ticketnumber, c.customer, c.timestamp, c.notes
FROM tickets t
LEFT JOIN customer_notes c ON (t.company = c.customer)
WHERE t.company = %s
ORDER BY t.company, t.datetime, t.ticketnumber, c.timestamp DESC
So then you can do:
那么你可以这样做：
mysql_query(sprintf($theQueryFromAbove, mysql_real_escape_string($_GET['seq'])));
This will give you a flat result set - meaning you will have the ticket data multiple times, but it should be ordered in clumps so youll have all the data for a company/ticketnumber in a contiguous set of rows so its easier to prep for output.
这将为您提供一个平坦的结果集 - 这意味着您将多次获得票证数据，但它应该成群订购，以便您将公司/票号的所有数据放在一组连续的行中，以便更容易准备输出.
回答by prodigitalson
I managed to work this out using UNION
我设法使用 UNION 解决了这个问题
$sql ="
SELECT datetime as datetime2, CONCAT(CONCAT('<strong>Ticket</strong> - ', ticketnumber, '<br>'),summary) as displaydata from tickets where company = '".$_GET["seq"]."'
UNION
SELECT timestamp as datetime2, CONCAT('<strong>Note</strong><br>',notes) as displaydata from customer_notes where customer='".$_GET["seq"]."'
order by datetime2 DESC ";
$rs=mysql_query($sql,$conn) or die(mysql_error());
echo '<table width="100%" border="0" cellspacing="5" cellpadding="5">';
while($result=mysql_fetch_array($rs))
{
echo '<tr>
<td width="150px" valign="top">'.$result["datetime2"].'</td>
<td valign="top">'.nl2br($result["displaydata"]).'</td>
</tr>';
}
echo '</table>';
回答by Hazzit
The first thing you want to do is change the SQL statement. The link between your tables seems to be tickets.companyand customer_notes.customer(this assumption turned out to be wrong, see Edit below). You can now change your SQL to link both tables with a JOIN. I used a LEFT join so that tickets withough a note will still be listed:
您要做的第一件事是更改 SQL 语句。您的表之间的链接似乎是tickets.company和customer_notes.customer（这个假设被证明是错误的，请参阅下面的编辑）。您现在可以更改 SQL 以使用 JOIN 链接两个表。我使用了 LEFT 连接，以便仍会列出带有注释的票：
(To help readability, the following code samples are just SQL, so you'll have to wrap it in quotes for PHP.)
（为了提高可读性，以下代码示例只是 SQL，因此对于 PHP，您必须将其括在引号中。）
SELECT tickets.company, tickets.datetime, tickets.ticketnumber,
customer_notes.timestamp, customer_notes.notes
FROM tickets
LEFT JOIN customer_notes ON tickets.company=customer_notes.customer
ORDER BY tickets.datetime DESC, customer_notes.timestamp DESC
It is ordered by descending ticket creation date. All notes are ordered by descending timestamp. The result of this select will look somewhat like this
它按票证创建日期降序排列。所有笔记都按时间戳降序排列。这个选择的结果看起来有点像这样
company datetime ticketnumber timestamp notes
ACME Corp 2013-01-12 2 20130113110034 firstnotetext
ACME Corp 2013-01-12 2 20130113140245 secondnotetext
Somecorp 2013-01-10 1
When you only want to list the tickets and notes from one company, you will have to insert a WHERE clause just before the ORDER BY clause like this:
当您只想列出一家公司的票证和备注时，您必须在 ORDER BY 子句之前插入 WHERE 子句，如下所示：
WHERE tickets.company = {put your escaped $_GET parameter here}
When you put parameters into your $_GET['seq'] parameter into your SQL statement, make sure you escape it properly to prevent SQL injections:
当您将参数放入 $_GET['seq'] 参数到 SQL 语句中时，请确保正确转义它以防止 SQL 注入：
$sql="WHERE tickets.company = '".mysql_real_escape_string($_GET['seq'])."'";
Edit:In the comments you stated that the two tables do not relate to one another. Normally you would just use two SELECTs, but you want a single table sorted by date/time. So you'll have to use a UNION:
编辑：在您的评论中，您指出这两个表彼此不相关。通常您只需要使用两个 SELECT，但您需要一个按日期/时间排序的表。所以你必须使用联合：
SELECT company as name, datetime as t, ticketnumber as more_info
UNION
SELECT customer as name, timestamp as t, notes as more_info
ORDER BY t DESC
P.S. I haven't actually tried it, but if my memory serves me right, you can interchange datetime fields and timestamps when it comes to ordering, if not, just cast them both to the same date/time format by using TIMESTAMP(datetime) as t
PS我还没有真正尝试过，但是如果我没记错的话，您可以在订购时交换日期时间字段和时间戳，如果没有，只需使用以下命令将它们都转换为相同的日期/时间格式 TIMESTAMP(datetime) as t
回答by Motes
The below small change might work better for you. I believe without a relation between the two tables you could get a Cartesian product of the tables.
下面的小改动可能更适合你。我相信如果两个表之间没有关系，您可以获得表的笛卡尔积。
<?php
$sql =
"SELECT
tickets.company, tickets.datetime, tickets.ticketnumber,
customer_notes.timestamp, customer_notes.notes
FROM
tickets, customer_notes
WHERE tickets.company = customer_notes.customer AND
customer_notes.customer = '".$_GET["seq"]."'
GROUP BY tickets.company ";
$rs=mysql_query($sql,$conn) or die(mysql_error());
echo '<table width="100%" border="0" cellspacing="5" cellpadding="5">';
while($result=mysql_fetch_array($rs))
{
echo '<tr>
<td>'.$result["timestamp"].'</td>
<td>'.$result["notes"].'</td>
</tr>
<tr>
<td>'.$result["datetime"].'</td>
<td>'.$result["ticketnumber"].'</td>
</tr>';
}
echo '</table>';
?>
