John Davidson

Message:

I have two tables and I want to count the number of rows in a single query.

Here is what I have so far and is working but has multiple query's and is not ok.

PHP Code:

     $q1=$db->query("SELECT id FROM edev_useri");

     $q2=$db->query("SELECT groupid FROM edev_useri WHERE groupid = 6");

     $q3=$db->query("SELECT groupid FROM edev_useri WHERE groupid = 7");

     $q4=$db->query("SELECT id FROM edev_cereri");

       $useri  = $db->numRows($q1);

       $dev    = $db->numRows($q2);

       $ben    = $db->numRows($q3);

       $cereri = $db->numRows($q4);

     $data=array();

     $dashStats = array(

      'useri'  => $useri,

      'dev'    => $dev,

      'ben'    => $ben,

      'cereri' => $cereri

      ); 

     $data[]=$dashStats;

     $json_data = array('dashStats' => $data); 

      echo json_encode($json_data);

---

My Answer:
To count the number of rows in a single query for multiple tables, you can use a single query with multiple subqueries. Here is an example:

php
$query = $db->query("
SELECT
(SELECT COUNT(id) FROM edev_useri) as useri,
(SELECT COUNT(groupid) FROM edev_useri WHERE groupid = 6) as dev,
(SELECT COUNT(groupid) FROM edev_useri WHERE groupid = 7) as ben,
(SELECT COUNT(id) FROM edev_cereri) as cereri
");

$row = $query->fetch_assoc();

$dashStats = array(
'useri' => $row['useri'],
'dev' => $row['dev'],
'ben' => $row['ben'],
'cereri' => $row['cereri']
);

$json_data = array('dashStats' => $dashStats);
echo json_encode($json_data);


This query will count the number of rows in each table and for specific conditions in a single query and return the results in a JSON format.