PHP: multiple SELECT COUNT with

Question

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();

Because you're executing each statement separately you're also having to bind the parameter separately. There really isn't a better way in this example. — Jay Blanchard, Jan 20 '15 at 13:25
Take a look at this: http://stackoverflow.com/questions/1775168/multiple-select-statements-in-single-query. You also can combine your statements into one and execute it at one time. — Gino Pane, Jan 20 '15 at 13:30
@JayBlanchard thanks for your answer. I leave it this way then. Have a nice day — Raphael_b, Jan 20 '15 at 14:11
Have you tried that @GinoPane? That requires more than just casually concatenating multiple queries. — Jay Blanchard, Jan 20 '15 at 14:14
Wow, a PHP question that consistently used `mysqli` *and* prepared statements? That's refreshing. That said, your first and 3rd queries might be able to be merge but your 2nd query pulling from `alerte` might be hard to factor in. If you get really desperate, you might glue them all together with `UNION ALL`? or using the method in @GinoPane's link. — Mr. Llama, Jan 20 '15 at 15:41

score 1 · Accepted Answer · answered Jan 20 '15 at 15:30

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.

PHP: multiple SELECT COUNT with

1 Answers1