J. Sm J. Sm - 1 month ago 11
SQL Question

MySQL query can it be done better

I am currently doing the follow for the last 6 months but it seems like it could be done way better (less lines of code and possibly more optimized)

$monthones = mktime(0, 0, 0, date("n") - 1, 1);
$monthonef = mktime(0, 0, 0-1, date("n"), 1);

$query = "SELECT COUNT(*) FROM users WHERE type = '1' AND status = '1' AND (date >= '".$monthones."' AND date <= '".$monthonef."');";
$result = $pdo->query($query);
$monthone = $result->fetchColumn();

$query = "SELECT COUNT(*) FROM users WHERE type = '3' AND status = '1' AND (date >= '".$monthones."' AND date <= '".$monthonef."');";
$result = $pdo->query($query);
$monthone2 = $result->fetchColumn();

$monthtwos = mktime(0, 0, 0, date("n") - 2, 1);
$monthtwof = mktime(0, 0, 0-1, date("n") - 1, 1);

$query = "SELECT COUNT(*) FROM users WHERE type = '1' AND status = '1' AND (date >= '".$monthtwos."' AND date <= '".$monthtwof."');";
$result = $pdo->query($query);
$monthtwo = $result->fetchColumn();

$query = "SELECT COUNT(*) FROM users WHERE type = '3' AND status = '1' AND (date >= '".$monthtwos."' AND date <= '".$monthtwof."');";
$result = $pdo->query($query);
$monthtwo2 = $result->fetchColumn();


And I've got the same queries for rest 4 months. Any suggestions how I can improve this? I was thinking of combining the queries and start counting while looping the results? Or would that be worse?

Answer

Seems like it would be more efficient to run one query against the database, one pass through the data.

For the date range conditions, rather than subtracting a second, and using a

<= last second of month

I prefer to use a pattern using

<  first second of following month

If the logic in the original code is correct for generating the "start" and "finish" boundaries, we only need seven boundary values, the first second of each month:

$month00 = mktime(0, 0, 0, date("n") - 0, 1);
$month01 = mktime(0, 0, 0, date("n") - 1, 1);
$month02 = mktime(0, 0, 0, date("n") - 2, 1);
$month03 = mktime(0, 0, 0, date("n") - 3, 1);
$month04 = mktime(0, 0, 0, date("n") - 4, 1);
$month05 = mktime(0, 0, 0, date("n") - 5, 1);
$month06 = mktime(0, 0, 0, date("n") - 6, 1);

And then the query can be something like this:

$query = "
SELECT SUM(u.type = '1' AND u.date >= '$month01' AND u.date < '$month00') AS monthone
     , SUM(u.type = '3' AND u.date >= '$month01' AND u.date < '$month00') AS monthone2

     , SUM(u.type = '1' AND u.date >= '$month02' AND u.date < '$month01') AS monthtwo
     , SUM(u.type = '3' AND u.date >= '$month02' AND u.date < '$month01') AS monthtwo2

     , ...
     , ...

     , SUM(u.type = '1' AND u.date >= '$month06' AND u.date < '$month05') AS monthsix
     , SUM(u.type = '3' AND u.date >= '$month06' AND u.date < '$month05') AS monthsix2

  FROM users u
 WHERE u.status = '1'
   AND u.type IN ('1','3')
   AND u.date >= '$month06'
   AND u.date  < '$month00'
";

The ... would be replaced by repeating the expression patterns to return columns monththree/monththree2, monthfour/monthfour2, monthfive/monthfive2

Note that this uses MySQL shorthand, the expression inside the SUM() aggregate is evaluated to 1 (if the condition is TRUE) or a 0 (if the condition is FALSE) or NULL.

We would have to fetch the whole row to get all the counts. We'd have to ditch the fetchColumn() and use a fetch() instead.

$result = $pdo->query($query);
$row = $result->fetch(PDO::FETCH_ASSOC);

If we need the values in your scalar variables, so we don't have to change subsequent code:

$monthone  = $row['monthone'];
$monthone2 = $row['monthone2'];
$monthtwo  = $row['monthtwo'];
$monthtwo2 = $row['monthtwo2'];
...
...
$monthsix  = $row['monthsix'];
$monthsix2 = $row['monthsix2'];