How to get multiple queries done in one single query

Question

How can I make the following queries done in one single query and get the result just the way it is in below?

// Begining of January

$ob = mysql_query(" SELECT SUM(salary_amount) AS total FROM teacherexpense WHERE  month(disburse_date)='01' AND  year(disburse_date)='$year' ");
$nt = mysql_fetch_assoc($ob);
$salaryamount= $nt['total'];


$ob = mysql_query(" SELECT SUM(other_expense_amount) AS expenseamount FROM otherexpense WHERE month(other_expense_date)='01' AND year(other_expense_date)='$year'  ");
$nt = mysql_fetch_assoc($ob);
$expenseamount= $nt['expenseamount'];

$jk = mysql_query(" SELECT SUM(amountpaid) AS revenue FROM studentpayment1 WHERE month(received_date)='01' AND year(received_date)='$year' ");
$t = mysql_fetch_assoc($jk);
$revenue= $t['revenue'];

$ob = mysql_query(" SELECT SUM(other_earning_amount) AS otherearningamount FROM otherearning WHERE month(other_earning_date)='01' AND year(other_earning_date)='$year' ");
$nt = mysql_fetch_assoc($ob);
$otherearningamount= $nt['otherearningamount'];


$January= ($revenue+$otherearningamount)-($salaryamount+$expenseamount);
// End of January

You could use UNION, each row will represent different query. — Dejan Marjanović
– Dejan Marjanović, Commented Dec 28, 2011 at 9:36

Daniel Persson · Accepted Answer · 2011-12-28 09:35:08Z

1

Stuff it in a Stored Procedure? The database drivers for PHP won't let you run several queries separated with ; for security reasons.

answered Dec 28, 2011 at 9:35

Daniel Persson

2,3571 gold badge13 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

macjohn · Accepted Answer · 2011-12-28 09:41:41Z

1

Have you tried mysqli drivers instead of mysql?

Take a look at: mysqli_multi_query

Executes one or multiple queries which are concatenated by a semicolon.

answered Dec 28, 2011 at 9:41

macjohn

1,80315 silver badges18 bronze badges

Comments

Dejan Marjanović · Accepted Answer · 2011-12-28 23:05:19Z

1

SELECT 'withdrawals' t, SUM( amount ) sum
FROM withdrawals
UNION
SELECT 'statement' t, SUM( amount ) sum
FROM statement

enter image description here

while($row = mysql_fetch_assoc($result))
{
    $total[$row['t']] = $row['sum'];
}

echo $total['withdrawals']; # 100
echo $total['statement']; # 624.x

edited Dec 28, 2011 at 23:05

answered Dec 28, 2011 at 9:43

Dejan Marjanović

19.4k7 gold badges54 silver badges67 bronze badges

Comments

Tudor Constantin · Accepted Answer · 2011-12-28 09:37:36Z

0

You could use MySQL UNION - but you'll have to iterate over the resultset, because in that case you will get 4 records instead of one/statement group

answered Dec 28, 2011 at 9:37

Tudor Constantin

26.9k7 gold badges54 silver badges73 bronze badges

Collectives™ on Stack Overflow

How to get multiple queries done in one single query

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related