3
\$\begingroup\$

Follow up question for: Conditional row count across 4 tables
After getting some excellent help from 200_success, i managed to output the SUM() total of my query using the following code:

get_results returns standard mysqli_assoc_array:

$row = $this->get_results($query);
$sum = $row[0];
 foreach($sum as $sum) {
    echo $sum;
}

Is this the best way to handle the result, are there any drawbacks / performance hits to doing it this way, how would you handle this querys result?

Query

$query = "SELECT SUM(inactive_count) AS inactive_count
FROM (
    SELECT COUNT(article_active) AS inactive_count
        FROM wcx_articles WHERE article_active = 0
    UNION ALL
    SELECT COUNT(video_active)
        FROM wcx_videos WHERE video_active = 0
    UNION ALL
    SELECT COUNT(app_active)
        FROM wcx_apps WHERE app_active = 0
    UNION ALL
    SELECT COUNT(link_active)
        FROM wcx_links WHERE link_active = 0
) AS wcx_objects";
\$\endgroup\$
3
  • \$\begingroup\$ Can you run your query? Seems like it's missing GROUP BY clauses which I thought were needed when you are selecting an aggregate function like SUM() or COUNT()... \$\endgroup\$
    – Phrancis
    Jun 26, 2014 at 19:49
  • \$\begingroup\$ The query works fine, "SUM(), COUNT(), AVG(), MIN(), MAX(), etc. are aggregate functions that requires you to specify a GROUP BY, unless you're using them on every column in your SELECT-list" - stackoverflow.com/a/19222737/3754261 \$\endgroup\$
    – CodeX
    Jun 26, 2014 at 20:04
  • \$\begingroup\$ sqlfiddle.com/#!8/03d95/1 - Fiddle by 200_success \$\endgroup\$
    – CodeX
    Jun 26, 2014 at 20:06

1 Answer 1

5
\$\begingroup\$

MySQL

Your query looks fine, I'm not sure it could be further optimized of itself. What I would suggest though is to plug it into a stored procedure, and there are a few good reasons to do this.

  1. Cleaner PHP script by only having your behavioral code in your script while just calling the procedure from there.

  2. Easier to modify, e.g., if you found yourself using this SQL script in multiple PHP scripts and you had to make a change to it, you would have to change it everywhere, as opposed to just changing the stored procedure once.

  3. Better SQL optimization. The way you have it now, every time you run the PHP script it sends an ad-hoc request to the SQL engine and it has to figure out the optimization each time. By storing the SQL script in the RDBMS the engine will optimize it ahead of time, so it will respond quicker each time.

Create procedure

You can run this just once:

DELIMITER |    
CREATE PROCEDURE wcx_objects() AS    
BEGIN    

SELECT SUM(inactive_count) AS inactive_count
FROM (    
    SELECT COUNT(article_active) AS inactive_count    
        FROM wcx_articles WHERE article_active = 0    
    UNION ALL    
    SELECT COUNT(video_active)    
        FROM wcx_videos WHERE video_active = 0    
    UNION ALL    
    SELECT COUNT(app_active)    
        FROM wcx_apps WHERE app_active = 0    
    UNION ALL    
    SELECT COUNT(link_active)    
        FROM wcx_links WHERE link_active = 0    
) AS wcx_objects;    

END|    
DELIMITER ;

Call Procedure in PHP

Then from PHP all you need to do to get your result set is write this:

$query = "CALL wcx_objects();"
\$\endgroup\$
7
  • \$\begingroup\$ I have never heard of a stored procedure and googling has come back with some very interesting info, list of pros and cons here code.tutsplus.com/articles/… I work a lot with functions / classes and will probably build a function that does the same thing as my above code with the ability to pass in variables making it reusable for other SUM()'s. Just out of interest where would i place this procedure if i wanted to call it in a function within a class, does it need a separate file? \$\endgroup\$
    – CodeX
    Jun 26, 2014 at 21:28
  • \$\begingroup\$ I don't know much about PHP but from my understanding you could call it from anywhere in your PHP script where you would otherwise write your whole SQL query. As far as storing it, it's stored in the RDBMS itself at this location: yourdatabasename.information_schema.routines \$\endgroup\$
    – Phrancis
    Jun 26, 2014 at 21:32
  • 1
    \$\begingroup\$ Oh, wow looking at phpmyadmin i can see it has an add routine button that allows you to create a procedure / function, that is very interesting indeed and something i will experiment with, thankyou \$\endgroup\$
    – CodeX
    Jun 26, 2014 at 21:37
  • \$\begingroup\$ Cool. I have a tendency to lean towards letting SQL do as much of the data crunching as possible, since it's what it does best, but I'm biased because I'm a SQL programmer. As they say, when all you have is a hammer, everything looks like a nail lol. \$\endgroup\$
    – Phrancis
    Jun 26, 2014 at 21:39
  • 1
    \$\begingroup\$ Haha, indeed, I rely on php too much I think, MySQL can do a lot more than I realised and it's something I need to put more time into, thanks for the inspiration \$\endgroup\$
    – CodeX
    Jun 26, 2014 at 21:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.