I have a PHP/MySQL query that returns the following:
array ( 'name' => 'Jess', 'month' => '2020-03-31 12:28:00', 'count' => '1', ) array ( 'name' => 'Bob', 'month' => '2020-04-31 12:28:00', 'count' => '2', ) array ( 'name' => 'Tom', 'month' => '2020-05-31 12:28:00', 'count' => '2', ) array ( 'name' => 'Bob', 'month' => '2020-05-31 12:28:00', 'count' => '2', )
The months return in an ordered fashion (E.g. January records are always before February records). However, not every user will have a result every month (see above).
And I want my data to present such as this, in an html table:
Month Jess Bob Tom March 1 April 2 May 2 2
Here is my (non working) attempt:
<?php
/*loop through all customers and print out each induvidual users performance*/
$con = mysqli_connect("localhost","root","","perfmon");
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
exit();
}
//main query for customer names
if ($customers = mysqli_query($con, "SELECT Distinct(customer_name) FROM slog ORDER BY customer_name DESC;")) {
while($row=mysqli_fetch_assoc($customers))
{
$name = $row["customer_name"];
print("<b>".$name." </b><br>");
$total = 0;
$cur_month = NULL;
$namepos = array();
$th = "<th>Month</th>";
$tr = "";
$npcount = 0;
//Loop over the customer names, and pull the modified count per user.
if ($user_perf = mysqli_query($con, "select modified_by as name, created_date as month, count(modified_by) as count from slog where customer_name = '$name' group by modified_by, MONTH(created_date) order by month;")) {
while($row=mysqli_fetch_assoc($user_perf))
{
//Assign variables from mysql results
$month = date("F",strtotime($row["month"]));
$name = $row["name"];
$count = $row["count"];
$total += $row["count"];
//Only add the month once!
if($cur_month != $month){
$cur_month = $month;
$tr .= "</tr><tr><td>" . $cur_month. "</td>";
//print($cur_month . "<br>");
}
//store the username 'position' to build a table (this determines how many spaces are needed.)
if(!array_key_exists($name,$namepos))
{
$namepos[$name] = $npcount;
$npcount += 1;
$th .= "<th>" .$name . "</th>";
}
//add details to tr in correct pos
for( $i = 0; $i < $namepos[$name]; $i++){
$tr .= "<td></td>";
}
$tr .= "<td> ".$count." </td>";
//print(" ".$name . " " . $count . " <br>");
}
print("<table border='1'><tr>". $th . "</tr>" . $tr . "</table>");
print("<br>Total: ".$total." <br><br> ");
mysqli_free_result($user_perf);
}
}
mysqli_free_result($customers);
}
mysqli_close($con);
?>
Which unfortunately results in results such as this:-
What would be the best way to achieve this? I have tried storing the position of each user in the table headers, but then it is difficult to know how many empty columns to add before and the entry (see image above).
Advertisement
Answer
just a quick update. I have managed to get what I desired through the following code
<?php
/*loop through all customers and print out each induvidual users performance*/
$con = mysqli_connect("localhost","root","","perfmon");
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
exit();
}
//main query for customer names
if ($customers = mysqli_query($con, "SELECT Distinct(customer_name) FROM slog ORDER BY customer_name DESC;")) {
while($row=mysqli_fetch_assoc($customers))
{
//Name of customer
$name = $row["customer_name"];
//Print customer name
print("<b>".$name." </b><br>");
//Used to display total jobs
$total = 0;
$user_list = array("Month");
$monthlist = array();
$st_array = array();
//Loop over the customer names, and pull the modified count per user.
if ($user_perf = mysqli_query($con, "select modified_by as name, created_date as month, count(modified_by) as count from slog where customer_name = '$name' group by modified_by, MONTH(created_date) order by month;")) {
while($row=mysqli_fetch_assoc($user_perf))
{
$month = date("F",strtotime($row["month"]));
$name = $row["name"];
$count = $row["count"];
$total += $row["count"];
//make our left column (months)
if(!in_array($month, $monthlist)){
array_push($monthlist, $month);
}
//Collect all unqiue users, to make our headers array. also stores the order theyre in.
if(!in_array($name, $user_list)){
array_push($user_list, $name);
}
//make a 2d array of all data
$tmp = [$month, $name, $count];
array_push($st_array, $tmp);
}
/**Process fetched data **/
$row = "";
$previous_pos = 1;
$fe = True;
//Print each unique user as a table header
print("<table border='1'>");
print("<tr>");
foreach ($user_list as $th){
print("<th>".$th."</th>");
}
print("</tr>");
//Loop the month array
foreach ($monthlist as $td){
//Loop the user array
foreach ($user_list as $usr){
//Loop our "2d" array (an array containing Month,User,Count)
foreach($st_array as $array_2d){
//If we match the correct month and user, return the count value. This ensures the users return in the correct order.
if ($array_2d[0] == $td && $array_2d[1] == $usr){
//Pos - Get the current position of the selected user.
//Establish how many empty entries need to come before. E.g. User in col 1 has no empty entries infront. User in column2 has 1 entry empty infront (if column 1 is empty).
$pos = array_search($usr, $user_list);
if ($previous_pos != $pos){
$row .= str_repeat("<td>0</td>", ($pos-$previous_pos-1));
}
//Assign our previous position
$previous_pos = $pos;
//If our first entry isn't in column 1, add an extra 0.
if($pos!==1 && $fe)
$row .= "<td>0</td>";
$fe = False;
//Add the data
$row .= "<td>".($array_2d[2])."</td>"; //change to [1] for debug
}
}
}
//Print the table row.
print("<tr><td>".$td ."</td>" . $row ."</tr>");
//Reset our variables.
$row = "";
$fe=True;
$previous_pos = 1;
$first_result = True;
}
print("</table></br>");
mysqli_free_result($user_perf);
}
}
mysqli_free_result($customers);
}
mysqli_close($con);
?>
Essentially I created 3 arrays. An array of users, an array of months, and an array of all data (3d array). I use the months and users to record the order in which the tables should be presented. I then loop through my 3d array and print the relevant data.
Despite the downvotes, I would like to thank you all for offering your help. If you have any recommendations please let me know.