ikmyer Posted August 9, 2007 Share Posted August 9, 2007 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. Link to comment https://forums.phpfreaks.com/topic/64078-solved-join-2-select-statements-to-create-1-recordset/ Share on other sites More sharing options...
Psycho Posted August 9, 2007 Share Posted August 9, 2007 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 ?> Link to comment https://forums.phpfreaks.com/topic/64078-solved-join-2-select-statements-to-create-1-recordset/#findComment-319388 Share on other sites More sharing options...
ikmyer Posted August 9, 2007 Author Share Posted August 9, 2007 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 !!! Link to comment https://forums.phpfreaks.com/topic/64078-solved-join-2-select-statements-to-create-1-recordset/#findComment-319432 Share on other sites More sharing options...
Psycho Posted August 9, 2007 Share Posted August 9, 2007 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'] ); } Link to comment https://forums.phpfreaks.com/topic/64078-solved-join-2-select-statements-to-create-1-recordset/#findComment-319478 Share on other sites More sharing options...
Barand Posted August 9, 2007 Share Posted August 9, 2007 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"; ?> Link to comment https://forums.phpfreaks.com/topic/64078-solved-join-2-select-statements-to-create-1-recordset/#findComment-319612 Share on other sites More sharing options...
ikmyer Posted August 10, 2007 Author Share Posted August 10, 2007 interesting... I will try that. Thanks for the suggestion! Link to comment https://forums.phpfreaks.com/topic/64078-solved-join-2-select-statements-to-create-1-recordset/#findComment-320428 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.