Combining 2 tables for viewing

shaneH

I can see that this problem has been addressed several times. But I can not quite figure out how to apply the various answers to my particulars.

I have created two tables for proccessing two different kinds of customer orders. Both have several unique columns. But there is some information in both that is similar. I would like to have the contents of both tables displayed together on a page and sorted by each orders date no mater which table the record is in and at the moment both date fields ar labeled differently.

Here's my code for getting the information from one table:

$orders_qry = "
	SELECT *
	FROM Orders_Special
	ORDER BY Order_Date DESC, record_number DESC
	LIMIT $startnum , 20
	";

$orders_res = mysql_query($orders_qry);

while ($orders_row = mysql_fetch_array($orders_res))
{
list( $record_number, $Customer_Info, $Order_Detail, $Order_Date, $Order_Number, $Email, $Order_Status, $Tracking_Number, $Paid_By, $Notes, $Payment_Amount,  $Shipping_Cost, $Product_Cost, $Date_Shipped, $Handeled_By ) = $orders_row;

As you can guess I go on to have the information dispalyed out in a table. You can also probably see that I am only displaying a limited number of records and using next and previous links for veiwing a large number of records.

From what I can gather from other posts using a Union would do the job. I guess what is a little confusing is how the MySQL and PHP syntax should come together.

I probably didn't provide enough info to get a good inital answer. If you need more info just let me know.

planetsim

Shouldnt be any differences in PHP syntax the SQL will be different here is a basic JOIN

SELECT mytbl_1.*, mytbl_2.*
FROM mytbl_1
LEFT JOIN mytbl_2 ON (mytbl_2.myidfield = mytbl_1.myidfield)

Its relatively basic we only gather results from mytbl_2 where a match of the fields myidfield in both tables are equal to. With that said if there isnt an ID matching from the mytbl_2 you will get NULL results for that record.

shaneH

I don't think I clarified my question vary well. Your code, I think, is for combining information from Table2 with Table1, if a value in Table1 is equal to a value in Table2, to make it look like it's one record when viewed.

What I need is to view the records from both tables in one list sorted by dates. Both tables have there own unqiue order information but have been kept separate for a variety of reasons but I am finding that some times it would be nice to beable to look through and edit, if neccessary, both tables at the same time and all sorted by the date on the order. Now both tables have some similar fields but there are several that are different. I don't need to view all the field in given record but I do need to view field from both records that may be named differnt even though the two given fields may contain a similar type of data.

Basically, I need to retrieve data form table1 and from table2, sorting the data from both tables by date, and then if the data is from table1 view fields A, C, E, ect. and if the data is from table2 veiw fields B, C, G, ect.

I hope that helps clarify, but I don't know how clear you can actully make a complex question.

LoganK

Join

shaneH

That only confirms that I can't use a Join.

Table2
Bob | (order info) | 2005-07-05
Sue | (order info) | 2005-07-15
Dan | (order info) | 2005-07-17

But in Table1 the culomns are named:
Customer_Name, Customer_Order, Order_Date.

And in Table2 the culomns are named:
Customer, Order_Info, Order_Date.

A Join just takes a common value found in the Table1 and finds it in Table2 and "Joins" the data in Table2 to Table1 making it all one record when viewed.

In my case there is no common value and I do not want the value from Table2 to be added on to a record in Table1 when viewed. I want to veiw the records from Table1 independantly and the records from Table2 independantly but both sorted in one page by date with no distiction as to which table they belong to when viewed.

leatherback

Hi,

TRhe union statement seems like the way to go. I a mnot sure about your sorting.. Wether it works across the united selectes.

Let's try this:

$query = "
(select Customer_Name, Customer_Order, Order_Date from table1
ORDER BY Order_Date DESC
    LIMIT $startnum , 20 )

union

(select Customer, Order_Info, Order_Date from table2
ORDER BY Order_Date DESC
    LIMIT $startnum , 20 )


";

If this doesn't work, you could consider doing a select from first table1, then table2, with the apropriate limits, and sorts set. Then echo all into a PhP array, and do an arraysort, and create results from the array. You would only need half of the array, abviously, becaus you would have the double number of reocrds. But that is a matter of a while($i < $maxnum) {} style creation of the results.

Hope it helps,

LoganK

Allright...leatherback's union might work, I haven't used it myself before but still give it a try!

shaneH

I think that example of the Union is what I was looking for but unfortunatly I don't think it's going to do the job I need it to. Because, I have more than three field I am pulling information out off in each table. In my main Orders table I have 26 columns in and the second orders table I have 18 columns each with obviously more than 20 records and several columns without the same names.

The Union example does get the information but it only makes a nightmare out of trying to display it. I can not see how to write the code that will sort both tables by date and then display the first 20 records from the combine tables while being able to deturmine which table a record came from and implement the correct php for displaying that record from a given table.

leatherback

Hi,

Nope.. the union requiers the same colums to be used in the two queries.

You should then just do two queries. One on each table, for 20 records at each tabnle, and place all the results in an array, sort the array by date, and echo the first 20 out.

e.g.:
$i=0;
while($row = mysql_fetch_array($result_table1))
{
$all_records[$i] = array( 'date' => $row[date], 'somefield' =>$row[somefield] );
}

Lars_Berg

You can add constants to one of the select queries so they have the same number of columns

e.g.

select 'T1' as origin, Customer_Name, Customer_Order, Order_Date 
from t1
union all
select 'T2', Customer, 0 as CustomerOrder, Order_Date 
from t2
ORDER BY Order_Date DESC
    LIMIT $startnum , 20

The origin column shows which table the record comes from.