Page 1 of 2
Creating a recordset from two tables
Posted: Tue Jul 11, 2006 10:28 am
by thdrought
Weirdan | Please use Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
I am trying to modify a PHP page that currently pulls data out of a mySQL DB table. I would like the recordset to actually pull data from two tables, but the two tables only have 1 field in common, the ticket_ID field.
Here are the statement pulling data from the one table...
Code: Select all
$sql = sprintf("SELECT th.thread_id, th.thread_date, th.thread_subject, th.thread_cc, th.is_agent_message, a.address_address, pgu.name_first, pgu.name_last FROM (ticket t, thread th, address a, public_gui_users pgu) WHERE th.ticket_id = t.ticket_id AND th.ticket_id = %d AND a.address_id = th.thread_address_id AND pgu.public_user_id = a.public_user_id AND th.thread_type = 'email' ORDER BY th.thread_date ASC ", $ticket_id);
$res = $cerberus_db->query($sql);
The data I would like to pull from the second table and append to the above recordset as would be...
Code: Select all
("SELECT tt.thread_time_id, tt.thread_time_date, tt.thread_time_hrs_spent, tt.thread_time_hrs_chargeable, tt.thread_time_hrs_billable, tt.thread_time_hrs_payable, tt.thread_time_working_agent_id, tt.thread_time_summary, tt.thread_time_date_billed, tt.thread_time_created_date FROM (thread_time_tracking tt, public_gui_users pgu) WHERE tt.ticket_id = t.ticket_id AND tt.ticket_id = %d ORDER BY tt.time_date ASC", $ticket_id);
I am unsure how to do this as the fields are completely different. I guess I could call each seperate and just diplay each recordset individually, but would rather mix the two and display both results together by date.
Suggestions would be appreciated. Thanks.
Weirdan | Please use Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Posted: Tue Jul 11, 2006 12:29 pm
by RobertGonzalez
It is one heck of a query, but try this...
Code: Select all
<?php
$sql = "SELECT th.thread_id, th.thread_date, th.thread_subject, th.thread_cc, th.is_agent_message,
a.address_address,
pgu.name_first, pgu.name_last,
tt.thread_time_id, tt.thread_time_date, tt.thread_time_hrs_spent, tt.thread_time_hrs_chargeable, tt.thread_time_hrs_billable, tt.thread_time_hrs_payable, tt.thread_time_working_agent_id, tt.thread_time_summary, tt.thread_time_date_billed, tt.thread_time_created_date
FROM ticket t
INNER JOIN thread th ON th.ticket_id = t.ticket_id
INNER JOIN address a ON a.address_id = th.thread_address_id
INNER JOIN public_gui_users pgu ON pgu.public_user_id = a.public_user_id
INNER JOIN thread_time_tracking tt ON tt.ticket_id = t.ticket_id
WHERE th.ticket_id = $ticket_id
AND tt.ticket_id = $ticket_id
AND th.thread_type = 'email'
ORDER BY th.thread_date ASC, tt.time_date ASC";
?>
Posted: Tue Jul 11, 2006 12:44 pm
by jamiel
You hacking the cerberus code?

-- I had to do that for my company a few months back.
Posted: Tue Jul 11, 2006 1:56 pm
by thdrought
jamiel wrote:You hacking the cerberus code?

-- I had to do that for my company a few months back.
YES! You have to hack the code. It has great potential, but, until that potential is finally realized...
I've made about 5 major changes to the 3.1 code, and have another few too do. Even found some errors that I've resolved on my site and I've posted on their forums. Waiting to see if they fix it too.
Posted: Tue Jul 11, 2006 2:27 pm
by thdrought
Everah wrote:It is one heck of a query, but try this...
First, thank you for helping. I tried the code above and entered 1 ticket number. This particular ticket has 8 records in the first "ticket reply" table and 3 records in the "ticket time tracking" table. When I ran the code, it combined both and gave me 24 records returned, instead of the 11 I was expecting.
What I was hoping to do was to have 11 records, and where the fields are not the same between the two tables, they would be blank. I haven't worked with SQL in quite a few years, but still couldn't think of how to accomplish this task.
The only thing I could come up with in theory was creating an array off the first tables recordset, then adding the new fields for the second table to the array, then running the second tables query to populate them. But, as confusing that is too me, I don't know whether it would work, or how to write something like that.
Any other suggestions or tricks ( or array code

)
Posted: Tue Jul 11, 2006 7:22 pm
by RobertGonzalez
You know, I think a Union query might work for you. Inner Join returns common results. Outer Join returns uncommon results. Union I believe returns all results from one and appends that return to the return of the other.
Posted: Wed Jul 12, 2006 5:34 am
by thdrought
A 'Union' query was my first instinct too, and I tried it. It failed saying that the fields were different. After more research, I found Unions would only work if all the fields were the same.
Posted: Wed Jul 12, 2006 7:13 am
by GM
You can "cheat" with a union query:
Code: Select all
mysql> SELECT * FROM table1;
+--------+--------+--------+--------+
| field1 | field2 | field3 | field4 |
+--------+--------+--------+--------+
| 1 | 2 | 4 | 4 |
| 2 | 7 | 2 | 4 |
| 3 | 7 | 3 | 5 |
| 4 | 4 | 3 | 5 |
+--------+--------+--------+--------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM table2;
+--------+---------+--------+
| fielda | fieldb | fieldc |
+--------+---------+--------+
| 1 | Hello | Hi |
| 2 | Goodbye | Bye |
| 3 | Cheers | Thanks |
+--------+---------+--------+
3 rows in set (0.00 sec)
mysql> SELECT field1, field2, field3, field4, '', '', '' FROM table1
-> UNION ALL
-> SELECT '', '', '', '', fielda, fieldb, fieldc FROM table2;
+--------+--------+--------+--------+---+---------+--------+
| field1 | field2 | field3 | field4 | | | |
+--------+--------+--------+--------+---+---------+--------+
| 1 | 2 | 4 | 4 | | | |
| 2 | 7 | 2 | 4 | | | |
| 3 | 7 | 3 | 5 | | | |
| 4 | 4 | 3 | 5 | | | |
| | | | | 1 | Hello | Hi |
| | | | | 2 | Goodbye | Bye |
| | | | | 3 | Cheers | Thanks |
+--------+--------+--------+--------+---+---------+--------+
7 rows in set (0.01 sec)
Notice that I've "padded" the selection fields in the queries so that the same number of fields appear in both queries. This allows you to make the union.
Don't know whether this is any use to you?
Posted: Wed Jul 12, 2006 8:45 am
by RobertGonzalez
That's a neat little trick GM. Thanks for that.
Posted: Wed Jul 12, 2006 12:15 pm
by thdrought
GM wrote:You can "cheat" with a union query:
Well, that is a lot of use to me. I had to make a few mods to get it to work. It's just a starting point right now, and Ill need to clean it up before making it live. But, here's what I learned from this. for each field thats blank, I had to define it with ' ' as name_first in the first select statement. Otherwise, it didn't work.
Now though, the field in the second select statement is only as large as what was passed to it from the first. Meaning, each '' field, if I add 1 space within the quote, I get 1 character returned in the second select statement. 2 space = 2 characters and so on.
Is it possible to predefine the fields with a varchar(50) within the first select statement? This is a weird situation, I know, but, Im getting closer thanks to all your help.
Code: Select all
(SELECT tt.thread_time_id as thread_id, tt.ticket_id as ticket_id, tt.thread_time_date as time_date, tt.thread_time_hrs_spent, tt.thread_time_hrs_chargeable, tt.thread_time_hrs_billable, tt.thread_time_hrs_payable, tt.thread_time_working_agent_id, tt.thread_time_summary as thread_subject, tt.thread_time_date_billed, tt.thread_time_created_by_id, tt.thread_time_created_date, ' ' as thread_cc, '1' as is_agent_message, ' ' as address_address, ' ' as name_first, ' ' as name_last
FROM (ticket t, thread_time_tracking tt)
WHERE tt.ticket_id = t.ticket_id AND tt.ticket_id = 19)
UNION ALL
(SELECT th.thread_id as thread_id, th.ticket_id as ticket_id, th.thread_date as time_date, '', '', '', '', '', th.thread_subject as thread_subject, '', '', '', th.thread_cc as thread_cc, th.is_agent_message as is_agent_message, a.address_address as address_address, pgu.name_first as name_first, pgu.name_last as name_last
FROM (ticket t, thread th, address a, public_gui_users pgu)
WHERE th.ticket_id = t.ticket_id AND th.ticket_id = 19 AND a.address_id = th.thread_address_id AND pgu.public_user_id = a.public_user_id AND th.thread_type = 'email')
Posted: Wed Jul 12, 2006 1:12 pm
by thdrought
Well, I got it to work just fine now. Some of the fields that I was having problems with, see above, have been either eliminated or combined with other fields. Here's the results. Thanks everyone.
Code: Select all
(SELECT
't' as thread_type,
tt.thread_time_id as thread_id,
tt.ticket_id as ticket_id,
tt.thread_time_created_date as time_date,
tt.thread_time_hrs_spent,
tt.thread_time_hrs_chargeable,
tt.thread_time_hrs_billable,
tt.thread_time_hrs_payable,
tt.thread_time_summary as thread_subject,
tt.thread_time_date_billed,
tt.thread_time_date,
' ' as thread_cc,
'1' as is_agent_message,
u.user_email as address_address,
u.user_display_name as name_first
FROM (
ticket t,
thread_time_tracking tt,
user u
)
WHERE
tt.ticket_id = t.ticket_id AND
tt.ticket_id = 22 AND
u.user_id = tt.thread_time_working_agent_id
)
UNION ALL
(SELECT
'e' as thread_type,
th.thread_id as thread_id,
th.ticket_id as ticket_id,
th.thread_date as time_date,
'',
'',
'',
'',
th.thread_subject as thread_subject,
'',
'',
th.thread_cc as thread_cc,
th.is_agent_message as is_agent_message,
a.address_address as address_address,
CONCAT(pgu.name_first,' ',pgu.name_last) as name_first
FROM (
ticket t,
thread th,
address a,
public_gui_users pgu
)
WHERE
th.ticket_id = t.ticket_id AND
th.ticket_id = 22 AND
a.address_id = th.thread_address_id AND
pgu.public_user_id = a.public_user_id AND
th.thread_type = 'email'
)
ORDER BY time_date
Posted: Wed Jul 12, 2006 1:17 pm
by RobertGonzalez
Now that is a query. I'd be interested to see the execution time on that thing.
Posted: Wed Jul 12, 2006 1:25 pm
by thdrought
Everah wrote:Now that is a query. I'd be interested to see the execution time on that thing.
How do I get that info? I only have access to phpMyAdmin screen. Is there something I can run with the query to display it? Then I'll do a before and after.
Posted: Wed Jul 12, 2006 11:23 pm
by RobertGonzalez
If you run the query in phpMyAdmin it should return the execution time above the result set.
Posted: Thu Jul 13, 2006 2:43 am
by GM
Also, if you execute "EXPLAIN SELECT ......" it will show you all the steps that the DBMS uses to resolve the query. It is very useful for highlighting areas where it would be useful to create new indexes.