[SOLVED] Join 2 select statements to create 1 recordset

[ikmyer]

I realize the title kinda suggests this is more of a mysql problem but what I'm looking for is the solution or suggestions using php.

 

I have 2 select statements which select date from 2 different tables.

SELECT income.date, income.amount, income.check_number, income.note, clients.first_name, clients.last_name 
FROM income INNER JOIN clients ON income.income_client_id=clients.client_id 
WHERE income.date BETWEEN '$date1' and  '$date2' AND income_user_id = $user_id

and

SELECT expenses.date, expenses.amount, expenses.note, expense_type.exp_name 
FROM expenses INNER JOIN expense_type ON expenses.exp_exp_type_id=expense_type.exp_type_id 
WHERE expenses.date BETWEEN '$date1' and  '$date2' AND exp_user_id = $user_id

 

I want to essentially combine those 2 to form 1 recordset ( using an array maybe? ) so that I can sort them all by date.  So put them together and sort by date.

 

I was working on some stuff and this is what I came up with but it doesn't seem to be working right.

$counter = 0;	
do{ 	
echo $row_income['date'] . "<br>";

$statement[$counter][date] = $row_income['date'];
$statement[$counter][title] = $row_income['first_name'];
$statement[$counter][note] = $row_income['note'];
$statement[$counter][check_num] = $row_income['check_number'];
$statement[$counter][expense] = 0;
$statement[$counter][income] = $row_income['amount'];

//print_r($statement);
//echo "<br><br>";

$counter++;	
} while ($row_income = mysql_fetch_assoc($income));	

do{ 	
echo $row_expense['date'] . "<br>";

$statement[$counter][date] = $row_expense['date'];
$statement[$counter][title] = $row_expense['exp_name'];
$statement[$counter][note] = $row_expense['note'];
$statement[$counter][check_num] = "";
$statement[$counter][expense]     = $row_expense['amount'];
$statement[$counter][income] = 0;

//print_r($statement);
//echo "<br><br>";

$counter++;	
} while ($row_expense = mysql_fetch_assoc($expense));

 

Which creates the following:

Array ( 
[0] => Array ( [date] => 2007-08-03 [title] => Nicole & Kevin [note] => [check_num] => [expense] => 0 [income] => 150.00 ) 
[1] => Array ( [date] => 2007-08-03 [title] => Tom [note] => [check_num] => [expense] => 0 [income] => 120.00 ) 
[2] => Array ( [date] => 2006-02-08 [title] => Toys [note] => Colors and Coloring books [check_num] => [expense] => 20.25 [income] => 0 ) 
[3] => Array ( [date] => 2004-07-07 [title] => Toys [note] => [check_num] => [expense] => 2.00 [income] => 0 ) 
[4] => Array ( [date] => 2007-07-04 [title] => Toys [note] => Garage Sales Toys [check_num] => [expense] => 50.25 [income] => 0 ) 
[5] => Array ( [date] => 2007-08-08 [title] => Office Supplies [note] => [check_num] => [expense] => 20.00 [income] => 0 ) ) 

 

How can i get that to sort by date?  Or would anyone recommend going about this another way.

[Psycho]

You should not use do...while, use while... instead. A do...while loop will always run at least once, which in this case would generate an error if the recordset was empty. Probably won't make a difference in your situation, but just good practice.

 

Also, no need to use $counter

 

<?php

while ($record = mysql_fetch_assoc($income)) {

  $statement[]['date'] = $record['date'];
  $statement[]['title'] = $record['first_name'];
  $statement[]['note'] = $record['note'];
  $statement[]['check_num'] = $record['check_number'];
  $statement[]['expense'] = 0;
  $statement[]['income'] = $record['amount'];

}

while ($record = mysql_fetch_assoc($expense)) {

  $statement[]['date'] = $record['date'];
  $statement[]['title'] = $record['exp_name'];
  $statement[]['note'] = $record['note'];
  $statement[]['check_num'] = "";
  $statement[]['expense']     = $record['amount'];
  $statement[]['income'] = 0;

}

// Obtain a list of columns
foreach ($statement as $key => $row) {
    $date[$key]  = $row['date'];
    $title[$key] = $row['title'];
}

array_multisort($date, SORT_ASC, $title, SORT_ASC, $statement);

//The array $statement will now be sorted by date, title
?>

[ikmyer]

Thank you so much mjdamato,

 

actually i did need the $counter when building the statement array otherwise it wouldn't build the 2d array.  I only added

<?php
// Obtain a list of columns
foreach ($statement as $key => $row) {
    $date[$key]  = $row['date'];
    $title[$key] = $row['title'];
}

array_multisort($date, SORT_ASC, $title, SORT_ASC, $statement);
?>

 

to what I had and I was able to get the results i needed.  Thank you for responding !!!

[Psycho]

actually i did need the $counter when building the statement array otherwise it wouldn't build the 2d array. 

 

Silly me! you're absolutely right. I typically refrain from using a counter when creating a "natural" indexed array as it can only introduce problems. Here is a correct solution without using $counter:

 

while ($record = mysql_fetch_assoc($income)) {

  $statement[] = (
    'date' => $record['date'],
    'title' => $record['first_name'],
    'note' => $record['note'],
    'check_num' => $record['check_number'],
    'expense' => 0,
    'income' => $record['amount']
  );

}

[Barand]

Perhaps more succinct way ie to use a UNION on the 2 select statements and retrieve a single result set.

 

Note I added a couple of dummy cols to the second select statement so the rows in each query gave the same columns

 

<?php
$sql = "SELECT income.date, income.amount, income.check_number, income.note, clients.first_name, clients.last_name 
        FROM income 
        INNER JOIN clients ON income.income_client_id=clients.client_id 
        WHERE income.date BETWEEN '$date1' and  '$date2' 
        AND income_user_id = $user_id

        UNION
        
        SELECT expenses.date, expenses.amount, 0, expenses.note, expense_type.exp_name, '' 
        FROM expenses 
        INNER JOIN expense_type ON expenses.exp_exp_type_id=expense_type.exp_type_id 
        WHERE expenses.date BETWEEN '$date1' and  '$date2' 
        AND exp_user_id = $user_id
        
        ORDER BY date";
?>

[ikmyer]

interesting...  I will try that.  Thanks for the suggestion!