CODEWITHSUNDEEP
phpmysqldatabase
user2135867
user2135867

Reputation:

PHP MySQL Select * from 2 different tables and display the data mixed together ordered by datetime column in both tables

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

Upvotes: -1

Views: 31215

Answers (5)

user2135867
user2135867

Reputation:

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>';

Upvotes: 1

Hazzit
Hazzit

Reputation: 6882

The first thing you want to do is change the SQL statement. The link between your tables seems to be tickets.company and 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:

(To help readability, the following code samples are just SQL, so you'll have to wrap it in quotes for 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:

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:

$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 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

Upvotes: 0

prodigitalson
prodigitalson

Reputation: 60413

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:

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.

Upvotes: 1

Motomotes
Motomotes

Reputation: 4237

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>';
    ?>

Upvotes: 0

Steve&#39;s a D
Steve&#39;s a D

Reputation: 3821

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)

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).

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.)

You should join using 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

Upvotes: 4

Related Questions