CODEWITHSUNDEEP
phpmysqlsql
Raphael_b
Raphael_b

Reputation: 1510

PHP: multiple SELECT COUNT with

I am making multiple count using the same parameter. I would like to know if there is a way of doing only one request (to be more efficient)? My COUNT are as follows: 

// Get number of MEMBERS in roster of this roster manager
    $stmt = $mysqli->prepare("SELECT    DISTINCT COUNT(rm.id_membre)
            FROM roster_par_membre rm   
            JOIN roster_par_membre rm2  
                WHERE rm.id_roster = rm2.id_roster
                AND rm2.level = 1
                AND rm2.id_membre = ?");

    $stmt->bind_param('i', $id_manager);  
    $stmt->execute(); 
    $stmt->store_result();
    $stmt->bind_result($nombre_total_membre);
    $stmt->fetch(); 

// Get number of Alerts for this roster manager
    $stmt = $mysqli->prepare("SELECT COUNT(id_alerte)
                FROM alerte 
                WHERE modified_by = ?");

    $stmt->bind_param('i', $id_manager);  
    $stmt->execute(); 
    $stmt->store_result();
    $stmt->bind_result($nombre_total_alerts);
    $stmt->fetch(); 

// Get number of Rosters for this roster manager
    $stmt = $mysqli->prepare("SELECT COUNT(id_roster)
                FROM roster_par_membre
                WHERE id_membre = ?
                AND level = 1");

    $stmt->bind_param('i', $id_manager);  
    $stmt->execute(); 
    $stmt->store_result();
    $stmt->bind_result($nombre_total_rosters);
    $stmt->fetch();

Upvotes: 0

Views: 142

Answers (1)

Wissam El-Kik
Wissam El-Kik

Reputation: 2525

If you don't want to keep the 3 queries separate, you can create a separate table to handle these statistics:

CREATE TABLE `statistics` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `members_in_roster` int(11) DEFAULT '0',
  `number_of_alerts` int(11) DEFAULT '0',
  `number_of_roster_per_manager` int(11) DEFAULT '0',
  `id_membre` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

You'll link these numbers to a id_membre.

Also, you can use MySQL Triggers to update these numbers automatically to avoid doing 3 queries. You'll have to use one query to get all the results you need.

For instance, you can use the following trigger every time the Alerts table is updated:

DELIMITER $$
CREATE TRIGGER before_alerts_update
    BEFORE UPDATE ON alerts
    FOR EACH ROW BEGIN

    UPDATE statistics
    SET number_of_alerts = IFNULL(number_of_alerts, 0) + 1
    WHERE modified_by = NEW.id_membre;
END$$
DELIMITER ;

If you want to keep the calculations in PHP instead of using MySQL triggers, it's not wrong. Keep in mind that using multiple triggers might slow down your database's performance. For instance, in the example mentioned above, every time the "alerts" table is updated, an UPDATE query is triggered. If you don't need to have these statistics numbers in real-time, this solution is an "overkill". It'll be better if you do the query once the user requests these numbers.

Upvotes: 1

Related Questions