2 queries, one result ordered by DATE

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
dreeves
Forum Commoner
Posts: 39
Joined: Thu Oct 22, 2009 8:53 am

2 queries, one result ordered by DATE

Post by dreeves »

First of all, this might be accomplished in one query. I'm not sure. I would like to query two different tables, combine the result and order the results by date into a table. Both tables contain a date column. Here is an example of how I have been populating a table with the results from a single query

Code: Select all

$query1="SELECT * FROM table1";
$query2="SELECT * FROM table2";
$result= // ???
$num=mysql_numrows($result);
mysql_close($link);
// this is how I have been populating my table rows with the query results
// from only a single table. I'm sure it would be different with the more complex query results.
<?php
$i=0;
while ($i < $num) {
$date=mysql_result($result,$i,"date");
$field2=mysql_result($result,$i,"field2");
$field3=mysql_result($result,$i,"field3");
$field4=mysql_result($result,$i,"field4"); // ...and so on
?>
<TABLE><TR>
     <td><?php echo $date; ?></td>
     <td><?php echo $field2; ?></td>
     <td><?php echo $field3; ?></td>
     <td><?php echo $field4; ?></td>
</TR>
<?php
$i++;
}
?>
</TABLE>
dreeves
Forum Commoner
Posts: 39
Joined: Thu Oct 22, 2009 8:53 am

Re: 2 queries, one result ordered by DATE

Post by dreeves »

I have tried an INNER JOIN but when I ask it to "ORDER BY date" it wants to know which date column to use(from table1 or table 2) and I would like combine the results and then order by date.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: 2 queries, one result ordered by DATE

Post by califdon »

You'll have to be more specific about the 2 tables. Show us something like this:

Code: Select all

Table1:
    id          INT (auto-increment) Primary Key
    date1       DATE
    name        VARCHAR
    ....       etc.
 
Table2:
    id          INT (auto-increment) Primary Key
    date2       DATE
    place       VARCHAR
    ....
A Join is appropriate only if the the tables are related by common field values. You may need a Union rather than a Join, but it's impossible to say without knowing what kind of data are in your 2 tables.
dreeves
Forum Commoner
Posts: 39
Joined: Thu Oct 22, 2009 8:53 am

Re: 2 queries, one result ordered by DATE

Post by dreeves »

No problem. There are many columns in the table, but I only need two or three.

Code: Select all

 
Table1
      Date           Date
      Requirement    DECIMAL
      Reimbursement  DECIMAL
 
TABLE2
      Date            Date
      Contribution    DECIMAL
I've been trying UNION, but it seems to want the same number of columns from each table, is there a way around that?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: 2 queries, one result ordered by DATE

Post by califdon »

dreeves wrote:No problem. There are many columns in the table, but I only need two or three.

Code: Select all

 
Table1
      Date           Date
      Requirement    DECIMAL
      Reimbursement  DECIMAL
 
TABLE2
      Date            Date
      Contribution    DECIMAL
I've been trying UNION, but it seems to want the same number of columns from each table, is there a way around that?
That doesn't look like related data to me. Usually each transaction would be identified to an account number or something. Possibly all transactions in both tables relate to the same account?? If that's the case, they should probably all be in one table to begin with. The problem you are experiencing is due to the fact that the data is not organized in a logical way, or so it appears to me from the information you have provided. Relational databases must be organized in accordance with very strict rules of data normalization, or they won't work. Each table in a relational database relates to an entity, which can be clearly defined as a collection of people, places, things, events, transactions, etc. So the first step in designing (or in troubleshooting) a database is to define what entities are represented. It appears that both your tables represent some kind of transactions. Then, if your purpose is to show ALL transactions in Date order, you must either combine them into one table or use a Union query. There are no other options.

Yes, Union queries must draw data from symmetrical sources; same number of columns, same data types and sizes. You could do something like this:

Code: Select all

SELECT A.Date AS Udate, A.Requirement AS Ureq, A.Reimbursement AS Ureimb, 0.00 AS Ucontr FROM Table1 A 
UNION SELECT B.Date AS Udate, B.Contribution AS Ucontr, 0.00 AS Ureq, 0.00 AS Ureimb FROM TABLE2 B 
ORDER BY Udate
http://dev.mysql.com/doc/refman/5.0/en/union.html

You should avoid using a Reserved Word like "Date" as a field name. It will cause you no end of trouble.
dreeves
Forum Commoner
Posts: 39
Joined: Thu Oct 22, 2009 8:53 am

Re: 2 queries, one result ordered by DATE

Post by dreeves »

califdon wrote: That doesn't look like related data to me. Usually each transaction would be identified to an account number or something. Possibly all transactions in both tables relate to the same account??

You should avoid using a Reserved Word like "Date" as a field name. It will cause you no end of trouble.
Each client has two separate accounts. Each table keeps track of the transactions for the separate accounts. I would like a list of all transactions from both accounts for a single client.
I have simplified the above table massively (including the "date" field title) to hopefully make it easier to explain. I took your advice two weeks ago and normalized my database to a level that I was satisfied with.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: 2 queries, one result ordered by DATE

Post by califdon »

Do I understand, then, that each of these tables contains transactions for many accounts??? Then you must surely have an account ID field in both tables. Of course you will have to have a WHERE clause in your Union query, so that you only select records for the account you're interested in.

This is not a task for a table Join. It's simply a Union.

It's good that you have given thought to normalization. I think I would not have created separate tables, but a Union query should work. Although I still haven't seen your complete schema, the usual practice in financial databases of this sort is to consider the entity to be "transactions" and have a "transaction type" field to distinguish what kind of a transaction each one is. This would avoid the awkward Union query situation you now have and probably make all your calculations a whole lot simpler.
dreeves
Forum Commoner
Posts: 39
Joined: Thu Oct 22, 2009 8:53 am

Re: 2 queries, one result ordered by DATE

Post by dreeves »

califdon wrote: the usual practice in financial databases of this sort is to consider the entity to be "transactions" and have a "transaction type" field to distinguish what kind of a transaction each one is.
Thanks a lot, that's a great idea.
Post Reply