PHP MySQL从2个不同的表中选择*，并在两个表中显示由datetime列排序的数据 [英] PHP MySQL Select * from 2 different tables and display the data mixed together ordered by datetime column in both tables

问题描述

我有以下代码...

 <？php 
 $ sql = 
 SELECT 
 tickets.company，tickets.datetime，tickets.ticketnumber，
 customer_notes.customer，customer_notes.timestamp，customer_notes.notes 
 FROM 
 ticket，customer_notes 
 WHERE ticket.company ='。$ _ GET [seq]。'和
 customer_notes.customer ='。$ _ GET [seq]。'
 GROUP BY customer_notes.customer，tickets .company; 
 
 $ rs = mysql_query（$ sql，$ conn）或die（mysql_error（））; 
 echo'< table width =100％border =0cellspacing =5cellpadding =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>'; 
？> 
  

票证表和customer_notes表根本没有参考 -

票证表是用于客户日志问题时的支持票据，然后customer_notes表用于客户打电话时，通过电话讲的所有内容都记录在此表中。

我试图让它以日期时间顺序显示票据表和客户注释表中的数据。

所以如果票被创建，然后添加一个注释，然后添加另一个注释，然后一个票，他们将使用上述代码显示日期时间顺序，但它不正确显示

  $ sql =
 
 SELECT datetime as datetime2，CONCAT（CONCAT（'< strong> Ticket< / strong>  - '，ticketnumber，'< br>'），summary）as displaydata from ticket where company ='。$ _ GET [seq]。'
 UNION 
将时间戳记作为datetime2，CONCAT（'< strong>注意< / strong>< br>'，note）as displaydata from customer_notes where customer = 。$ _ GET [seq]。'
 order by datetime2 DESC; 
 $ rs = mysql_query（$ sql，$ conn）或die（mysql_error（））; 
 echo'< table width =100％border =0cellspacing =5cellpadding =5>' 
 while（$ result = mysql_fetch_array（$ rs））
 {
 echo'< tr> 
< td width =150pxvalign =top>'。$ result [datetime2]。< / td> 
< td valign =top>'。nl2br（$ result [displaydata]）。< / td> 
< / tr>'; 
} 
 echo'< / table>'; 
  

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.

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.

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

解决方案

I managed to work this out using 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>';

这篇关于PHP MySQL从2个不同的表中选择*，并在两个表中显示由datetime列排序的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！