Complex MySQL Query For a Newbie.

[rts5678]

Hi. I have 2 mysQL tables in a customer service related application.

 

One table is called messages the other table is called responses.

 

My goal is to display messages from customers and responses from the customer service staff such that the latest message or response goes on top with the oldest message in the conversation being shown at the bottom:

 

 

Newest Message or Response

....................

....................

....................

Oldest Message

 

 

Since the above conversation (messages/responses) can be lengthy, due to web page limitations, I would like to be able to show only part of the conversation and the rest/full conversation I can show on another page via a clickable link etc.

 

For that purpose, within the mysql query or probably before the mysql query via some php code snippet I would like to be able to plug in a number (x) in the mysql query, or in the php code snippet, where x is the number of characters I want displayed (let's say 250 or so characters of the newest customer message or customer sevice response).

 

 

 

For the purpose of this query in table 'messages' the following 3 columns are of interest:

 

1-msg_id, (primary key, auto increment)

2-ticket_id, (carries same value in both tables)

3-message

 

 

 

In table 'responses' the following 4 tables can be of interest:

 

1-response_id, (primary key, auto increment),

2-msg_id,

3-ticket_id, (same value in both tables)

4-response

 

 

I am new to php/mysql and have tried to construct various queries using mySQL join between the two tables but without luck. I have provided my query at the end of this message.

 

This is the problem I am having:

 

when a customer sends in the intial message, that message is assigned a ticket_id in table 'messages', BUT until a customer service responds to it, the same ticket_id number is not created in the table 'responses' hence until the first customer service message is responded, nothing shows up in my results for that ticket. It's only after a response from customer service is entered (in the table responses) that the ticket_id becomes shared in both tables and then shows up in my results.

 

 

Please bear in mind that I am new to writing these kind of queries and so what I came up with below may look quite funny to the experts out there, so please feel free to help me with a whole new and possibly better query.

 

 

Please remember that after I am able to display the messages and responses in a sequence, I would like the latest messages on top and also be able to display only the first 250 or so characters of the conversation.

 

 

Your help will be greatly appreciated as I have already spend a lot of time on this problem.

 

Thanks a bunch in advance.

 

<?php

 

$whatever = $row['ticket_id'];

 

$query = "SELECT msg.msg_id, resp.msg_id, msg.message, resp.response FROM messages msg, responses resp where msg.ticket_id = resp.ticket_id AND resp.ticket_id ='$whatever' order by msg.created";

 

$result = mysql_query($query) or die(mysql_error());

 

 

// Print out the contents of each row into a table

while($row = mysql_fetch_array($result)){

echo $row['message']. " <br> ". $row['response'];

echo "<br />";

}

?>

[aschk]

Your query is old SQL syntax

$query = "SELECT msg.msg_id, resp.msg_id, msg.message, resp.response FROM messages msg, responses resp where msg.ticket_id = resp.ticket_id AND resp.ticket_id ='$whatever' order by msg.created";

 

Let's break this down and show you how to do JOINs properly.

 

What you want:

You want all the messages (and responses if there are any) for a particular ticket id (or for all the tickets).

 

What the relationships are: (anything followed by an ? is a question)

Each customer can have 0 or many tickets

Each ticket can have only 1 message??? (is the message the ticket? are they not the same thing?)

Each message can have 0 or many responses.

 

Customer 1 -> 0..* Messages(Tickets) 1 -> 0..* Responses

 

Required tables

Your require the customer table  (you're selecting which customer you're interested in?) -> initial table

Each customer might have 0 or more tickets (messages) ->  therefore LEFT JOIN

Each message might have 0 or more responses -> therefore LEFT JOIN

 

SQL

SELECT msg.msg_id
       ,resp.msg_id
       ,msg.message
       ,resp.response
FROM messages msg
LEFT JOIN responses resp
WHERE msg.ticket_id = resp.ticket_id 
AND resp.ticket_id ='$whatever' 
ORDER BY msg.created

 

Summary:

I have accounted for your current table layout, however I feel you need some definition on what your tables contain. You seem to be duplicating information across tables (ticket_id ???) which defeats the point of normalisation. I'm assuming you have some form of user table at present but here is how I see the layout as it should be... (minimal format)

 

[users]

user_id (primary key)

name

 

[tickets]

ticket_id (primary key)

user_id (foreign key)

ts_inserted

 

[ticket_dialogue]

ticket_id (foreign key) }_ composite primary key

user_id (foreign key)  }

message

 

Using the above, you can see that

1) users can have tickets

2) each ticket should have 1 or more ticket_dialogues (on creating a ticket the 1st message is added).