Page 1 of 1

[SOLVED] MySQL query help

Posted: Sun Jul 25, 2004 7:48 am
by mjseaden
Dear All,

I have three tables, one containing a list of incoming payments, along with a date the payment was made, and two tables containing a list of outgoing payments, also with a date each payment was made.

I want to 'amalgamate' these payments into one HTML table, so that all payments are 'interspliced' from all tables in date order, for example:

[incomepayment 1stJan]
[outgoingpayment1 5thJan]
[incomepayment 6thJan]
[outgoingpayment2 8thJan]

etc...

My initial thought was to do something like this:

Code: Select all

$query = 'SELECT FROM Income...'
$query2 = 'SELECT FROM Outgoing1...'
$query3 = 'SELECT FROM Outgoing2...'

$result = mysql_query($query)
$result2 = msql_query($query2)
$result3 = mysql_query($query3)

while( $row = mysql_fetch_array( $result ) )
{
    while( $row2 = mysql_fetch_array( $result2 ) )
    {
        while( $row3 = mysql_fetch_array( $result3 ) )
        {
              // echo in HTML table code
        }
    }
}
However, this obviously doesn't work, because I have to read all the values from the third MySQL table first ($row3), without knowing what the values from the second ($row2), followed by the first ($row) table values are.

I can't think of any way of doing this, without perhaps some complicated MySQL query that I don't know how to do - or otherwise some PHP code combining the single queries in a way I haven't yet thought of.

Any help would be greatly appreciated.

Yours sincerely

Mark

Posted: Sun Jul 25, 2004 9:15 am
by mjseaden
Sorted it out - I read in the elements of each table, then simply add them to a new table I create before stating an ORDER BY on that table. Therefore I still use the MySQL engine to organise the data, which makes things a lot easier.