Using Joins to Eliminate Multiple SELECTs in Loop

Question

I'm trying to teach myself PHP/mysql by building a joe's goals clone, if you will.

Basically each user has multiple goals, and each day they record how many times a certain event occurred. For example, say my goal is to drink only 1 cup of coffee per day. If I had 3 today (oops!), I'd record 3 "checks" for today. I use a table called 'checks' to hold the check count for each day.

I have the following tables, and sample inserts:

CREATE TABLE `users` (
  `user_id` int(5) NOT NULL AUTO_INCREMENT,
  `user_email` varchar(50) NOT NULL,
  `user_name` varchar(25) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;

-- Dumping data for table `users`
INSERT INTO `users` VALUES (1, '[email protected]', 'xxx');
INSERT INTO `users` VALUES (2, '[email protected]', 'SomeGuy');

CREATE TABLE `goal_settings` (
  `goal_id` int(5) NOT NULL AUTO_INCREMENT,
  `user_id` int(5) NOT NULL,
  `goal_description` varchar(100) NOT NULL,
  PRIMARY KEY (`goal_id`),
  KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- Dumping data for table `goal_settings`
INSERT INTO `goal_settings` VALUES (1, 1, 'Run 1k');
INSERT INTO `goal_settings` VALUES (2, 1, 'Read 20 pages');
INSERT INTO `goal_settings` VALUES (3, 2, 'Cups of Coffee');

CREATE TABLE `checks` (
  `check_id` int(40) NOT NULL AUTO_INCREMENT,
  `goal_id` int(5) NOT NULL,
  `check_date` date NOT NULL,
  `check_count` int(3) NOT NULL,
  PRIMARY KEY (`check_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- Dumping data for table `checks`
INSERT INTO `checks` VALUES (6, 1, '2012-03-02', 3);
INSERT INTO `checks` VALUES (2, 1, '2012-03-01', 2);
INSERT INTO `checks` VALUES (3, 2, '2012-03-01', 1);
INSERT INTO `checks` VALUES (5, 1, '2012-02-29', 1);

The output I'd like has goal_ids as rows and a range of dates as columns (like a week view calendar).

goal_id   |  2012-03-01 | 2012-03-02 | 2012-03-03 | ... 2012-03-08 |
--------------------------------------------------------------------
1            2             3           0            ... 0
2            1             0           0            ... 0   

Please note that when no checks exist for a given goal on a given day, 0 is returned instead of NULL.

I was able to get it working, poorly, using PHP. Truncated code, but I hope it shows basically what I tried: [$goal_ids is an array holding all goals associated with a user. $num_days is the number of days (i.e. columns) to be displayed, and $goal_days is an array used to hold the days we're looking to get info for].

$mysqli = new mysqli('xxx','xxx','xxx','goals');
$stmt = $mysqli->stmt_init();
$stmt = $mysqli->prepare("SELECT checks.check_count AS check_count 
FROM `checks` WHERE goal_id = ? AND check_date = ?");

for($i=0; $i<=$goal_count - 1; $i++){  
  echo '<tr id="'.$goalid.'">';
      for($j=0; $j <=$num_days; $j++){
          $checkdate = $goal_days[$j];
          $goalid = (integer) $goal_ids[$i];
            if (!$stmt->bind_param("ii", $goalid, $checkdate)) {
              echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
            }
            if (!$stmt->execute()) {
              echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
              }
              $stmt->bind_result($check_count);
                if($stmt->fetch()){
              echo "<td>".$check_count."</td>";
             }
             else{
              echo '<td>0</td>';
             }
          }
   echo "</tr>";
} 
echo "</table>";
$stmt->close();

This is obviously inefficient because for m goals and n days, it makes m x n select statements.

From reading, it seems like I'm basically trying to make a pivot table, but I've read that they are inefficient also, and I'm guessing what I'm doing is better handled by PHP than by doing a pivot table?

That leaves me with joins, which is what I think I'm asking for help with. I have considered creating a new column for every day, but I think it's not ideal. I'm open to totally changing the schema if necessary.

I hope I've been clear enough. Any help or pointers in the right direction would be greatly appreciated.

Thanks!

Answer 1

If I understand correctly your problem then I would suggest you should do a regular select in which each combination of goal_id and check_date will get a record in the result set, and then at the client side you will make a column for each check_date say by having an array of arrays and insert the checkcount in it.

This should at least be faster than having m x n select statements.

For more efficiency your sql can sort it by the goal_id and check_date, this will cause the records to be grouped together.

Here is an example of the sql statement:

SELECT check_date, goal_id, check_count FROM checks ORDER BY goal_id, check_date 

Here is PHP sample code, assuming you have an array of arrays "$array_of_arrays" (initialized to zero to avoid the null problem) with the outer key being the goal_id and the inner key being the check_date:

while ($row = mysqli_fetch_result($result)){
     $row_goal_id = $row["goal_id"];
     $row_check_date = $row["check_date"];
     $array_of_arrays[$row_goal_id][$row_check_date] = $row["check_count"];
}

And then you can use the array of arrays to do what you like, say for if you wish to output as an HTML table example then join the inner array with </td><td> and the outer array with </td></tr><tr><td>.

An example of how to create and initialize the "$array_of_arrays" array would be as follows (assuming you have an array $goals containing all the goals and an array $dates containing all the dates, if you don't know then in advance you can fetch them from the checks table by doing a SELECT DISTINCT)

 $array_of_arrays = array();   
 foreach ($goals as $key=>$value){
         $array_of_arrays[$value] = array();
         foreach ($checks as $key1=>$value1){
                  $array_of_arrays[$value][$value1] = 0;
         }
   }

A similar approach can be used to generate the final HTML table as follows:

$final_array = array();
foreach ($array_of_arrays as $key=>$value){ 
    $final_array[$key] = implode("</td><td>", $value);
}
$final_str = implode("</td></tr><tr><td>", $final_array);
$table_str = "<table><tr><td>" . $final_str . "</td></tr></table>";         

Answer 2

Consider adding a table of days (or creating temporary one at runtime) holding just consecutive dates or dates you need. You could then get a nice list of check counts using a single query:

SELECT g.goal_id, d.day, COALESCE(c.check_count,0) as check_count 
FROM
  goal_settings g 
JOIN 
  days d
LEFT JOIN 
  checks c 
ON c.goal_id = g.goal_id AND c.check_date = d.day
WHERE 
  g.user_id = 1 
  AND d.day BETWEEN '2012-03-01' AND '2012-03-03'
ORDER BY g.goal_id, d.day

resulting in a rowset like:

goal_id  | day        | check_count
    1    | 2012-03-01 |      2
    1    | 2012-03-02 |      3
    1    | 2012-03-03 |      0
    2    | 2012-03-01 |      1
    2    | 2012-03-02 |      0
    2    | 2012-03-03 |      0

And then fetch those rows in a loop with php to build a nice html table - if goal_id changed then print new row and so on.