CODEWITHSUNDEEP
phpmysql
user3397800
user3397800

Reputation: 13

3 MYSQL query using 1 query and limit

Here is my php code:

 $trsql = "SELECT * from members order by id desc";
  $trresult = mysql_query($trsql);
  $count = mysql_num_rows($trresult);
  while ($trrow = mysql_fetch_array($trresult)) {
  $bill_membertotal = mysql_query("SELECT * FROM billing WHERE suite='$trrow[suite]' order by bill_id desc limit 1"); 
  $bill_membercount = mysql_fetch_array($bill_membertotal);
  $check_membertotal = mysql_query("SELECT SUM(net) AS checker FROM billing WHERE suite='$trrow[suite]'"); 
  $check_membercount = mysql_fetch_assoc($check_membertotal);

echo $trrow['col1'] $trrow['col2'] $trrow['col1'] $bill_membercount['balance']  $check_membercount['checker'] ;
}

member table: [id suite col1 col2 col 3] billing table: [bill_id suite gross fee net balance]

Question: How to make this 3 query using 1 query? I know about MYSQL Join function. But how to do in this case?

Upvotes: 1

Views: 87

Answers (4)

Joachim Isaksson
Joachim Isaksson

Reputation: 181087

This would seem to give the expected result; since you only want the last bill for one field and MySQL doesn't have analytic functions, a subquery will solve that field easier than a join.

SELECT id member_id, col1, col2, col3,
  (SELECT balance 
   FROM billing b1 
   WHERE b1.suite=m.suite 
   ORDER BY bill_id DESC LIMIT 1) balance,
   SUM(net) AS checker
FROM members m
LEFT JOIN billing b
  ON m.suite = b.suite
GROUP BY col1,col2,col3,id
ORDER BY id DESC

An SQLfiddle to test with.

Upvotes: 1

rsakhale
rsakhale

Reputation: 1036

Not sure, but give a try on this as this is what is expected by your report

http://sqlfiddle.com/#!2/7ccc6/3

Query:

SELECT m.*, COUNT(b.id) as member_count, SUM(net) as checker
FROM member m
JOIN billing b ON m.suite = b.suite
GROUP BY b.suite

Upvotes: 1

Adrian Preuss
Adrian Preuss

Reputation: 3113

Not tested:

$query = "SELECT
    `member`.`col1` AS `col1`,
    `member`.`col2` AS `col2`,
    `bill`.`balance` AS `balance`,
    SUM(`bill`.`net`) AS `checker`
FROM
    `members` AS `member`,
    `billing` AS `bill`,
WHERE
    `bill`.`suite`=`member`.`suite`
ORDER BY
    `member`.`id`,
    `bill`.`bill_id`
DESC";
$results    = mysql_query($query);
$count      = mysql_num_rows($results);

while($entry = mysql_fetch_array($results)) {
    printf('%s, %s, %s, %s, %s', $entry['col1'], $entry['col2'], $entry['col1'], $entry['balance'], $entry['checker']);
}

mysql_* is marked deprecated

Upvotes: 1

zion ben yacov
zion ben yacov

Reputation: 725

select * , 
        (select count(*) from members) as members_count,
        (select count(*) from billing where suite = bill.suite) as bill_count,
        (select count(*) from billing where suite = check.suite) as check_count,
from members members
inner join billing  bill on members.suite = bill.suite
inner join billing check members.suite = check.suite

Upvotes: 1

Related Questions