Display results left to right in table cells

MySQL database
#1

Hello -

I’m working with a database that has a list of records and I am looking to display some of that data in a table, organized by username, from left to right instead of top to bottom. This is the table I’m working with:

--------------------------------------
| ID | SubmitBy | SubmitDate | Score |
--------------------------------------
| 01 | User1    | June 10    | 90    |
--------------------------------------
| 02 | User2    | June 10    | 88    |
--------------------------------------
| 03 | User1	| June 12    | 93    |
--------------------------------------
| 04 | User2    | June 12    | 97    |
--------------------------------------
| 05 | User3    | June 13    | 89    |
--------------------------------------

What I need to do is display the scores for each user from older records to newer records, left to right, on a table that is organized by the user’s name, like so:

------------------------------------
| User  | Score1 | Score2 | Score3 |
------------------------------------
| User1 | 90     | 93     | 97     |
------------------------------------
| User2 | 88     | 97     |        |
------------------------------------
| User3 | 89     |        |        |
------------------------------------

To make this more complicated, the table has 10 scores on it, so for any user that doesn’t have 10 scores in the database table I need to have the remaining table cells populated / created to fill in the rest of the table code for that row.

I’m not exactly sure how to craft the MySQL request for this, nor the code to display the data the way I need to. I originally thought I could just do a “SELECT * FROM table ORDER BY SubmitBy” and then try to use a “foreach”, but I can’t figure out how to craft the request properly it seems - and that might not be the proper way to do this either.

Any help would be greatly appreciated!

#2

You would ORDER BY SubmitBy, SubmitDate to get the rows in the result set in ascending user and date order (hopefully the SubmitDate column is a DATE datatype, with a yyyy-mm-dd format, so that they will sort correctly.)

You would then index/pivot the data using the SubmitBy column as the main array index when you fetch the data (if you are using the PDO database extension, there’s a fetch mode that will index/pivot the data for you.) This will give you a sub-array of scores per SubmitBy index. As an array, this would look like -
$result[‘User1’] = [90,93,97];
$result[‘User2’] = [88,97];
$result[‘User3’] = [89];

You would then use two nested foreach(){} loops to loop over the data and produce the output. The first loop would give the SubmitBy index and the sub-array of scores. The second loop would loop over the sub-array of scores for the current SubmitBy value.

To fill out each row with 10 values, you can count the number of entries in the current sub-array of scores, fill an array with the necessary number of empty values, merge the sub-array with the array of empty values, then loop over the resulting array of values to produce the output.

#3

Thank you for your response!

Unfortunately, I think you’ve gone a bit over my experience with some of this. When you say “index/pivot” - can you explain that a bit more? If you are saying what I think you are saying, I usually index tables like this by their record ID number - but if that’s not what you are saying, could you elaborate (if that is what you are saying, can it be done by not indexing the SubmitBy column)?

I’m not coding in PDO - all my coding has been done by MySQLi code; how would this be different?

Admittedly my experience with arrays (at least in this manner) is a bit limited. In as far as creating an array “with the necessary number of empty values” - how would I merge that with an existing array?

#4

Is an operation you perform on the data when you fetch it, to transform the data from one format to another. Normally, when you fetch the rows of data from an sql query you would get something that looks like -

['SubmitBy'=>'User1','Score'=>90];
['SubmitBy'=>'User1','Score'=>93];
['SubmitBy'=>'User1','Score'=>97];
['SubmitBy'=>'User2','Score'=>88];
['SubmitBy'=>'User2','Score'=>97];
['SubmitBy'=>'User3','Score'=>89];

However, to produce the output that you want, you need the data formatted per html table row, which looks like this -

$result['User1'] = [90,93,97];
$result['User2'] = [88,97];
$result['User3'] = [89];

To do that using the mysqli extension, you would fetch the rows from the query like this -

// index/pivot the data using the SubmitBy column as the main array index when you fetch the data
foreach($stmt as $row)
{
	$result[$row['SubmitBy']][] = $row['Score'];
}

To produce the html table output from the resulting data, it would look like this -

<style>
table, th, td {
  border: 1px solid black;
}
</style>
<?php
$total_col = 10;
echo "<table>";
echo "<tr><td>User</td>";
foreach(range(1,$total_col) as $col)
{
	echo "<td>Score$col</td>";
}
echo "</tr>";
foreach($result as $index=>$arr)
{
	echo "<tr><td>$index</td>";
	foreach(array_merge($arr,array_fill(0,$total_col-count($arr),'')) as $score)
	{
		echo "<td>$score</td>";
	}
	echo "</tr>";
}
echo "</table>";
#5

Hello again -

Thank you so much for the help so far. The end result is exactly what I’m looking for. Unfortunately, however, I’m not having much success with my query itself in grabbing the correct data from the table. Typically when I do a query I phrase it this way (which I suppose is the ‘old way’):

$query ="SELECT * FROM table WHERE variable = var";
$result = mysqli_query($conn, $query);

I haven’t used prepared statements to grab data from the database generally - only when I’m adding data to it. So I’m not having a lot of success working out the proper query to get the data that the foreach statement is looking for. What I’ve tried so far is this:

$query = "SELECT SubmitBy, Score FROM user_scores WHERE SubmitBy = ? AND Score = ?";
$stmt = $connect->prepare($query);
$stmt->bind_param('si', $id, $ti);
$stmt->execute();
$result = $stmt->get_result();

What I’m getting is just the top row displaying the header row info, without any data. I’m certain I’m not phrasing this correctly, but I can’t figure out where I’m going wrong.

#6

If that’s ^ the query that gets the data for the users and their scores that you wanted at the start of this thread, why are you now trying to match data for a specific user and a specific score - WHERE SubmitBy = ? AND Score = ? This will at most match one row of data.

#7

Ahh… good point. I saw the $stmt in the foreach example and my mind just went off in that direction. However, if I go back and use mysqli for the query, like so (I had to be specific in which rows I queried, or the result would display the SubmitDate and ID in the table also):

$query = "SELECT SubmitBy, Score FROM user_scores ORDER BY SubmitBy DESC";
$result = mysqli_query($conn, $query);

I end up with an error - Invalid argument supplied for foreach() when attempting to use

foreach($query as $row) {
     $result[$row['SubmitBy']][] = $row['Score'];
}

and the displayed data shows up like this:

------------------------------------
| User  | Score1 | Score2 | Score3 |
------------------------------------
|   0   | User1  |   90   |        |
------------------------------------
|   1   | User1  |   93   |        |
------------------------------------
|   2   | User1  |   97   |        |
------------------------------------
|   3   | User2  |   88   |        |
------------------------------------
|   4   | User2  |   97   |        |
------------------------------------
|   5   | User3  |   89   |        |
------------------------------------
#8

The $query variable you are trying to use in that foreach() loop is the sql query statement, which is a string. It needs to be the result from the mysqli_query() call. Unfortunately, $result is the variable name that’s used later in the code and is expected to be the output from that foreach() loop. Rather than to rewrite the rest of the code, a different intermediate variable name needs to be used for the result from the mysqli_query() call and the input to that foreach() loop.

#9

Okay, got it (not sure why I didn’t see that myself honestly).

That fixed the issue and the data is displaying as I hoped - thank you very much.

I do have one more question on this though. How could I grab an additional bit of data from a record using this example to use in addition to what’s there now? For example, if I wanted to display a graphic instead of the actual Score, and then have the Score be a pop-up title of the graphic, what would I need to do for that? So, for example, if my table was this:

----------------------------------------------
| ID | SubmitBy | SubmitDate | Score | Grade |
----------------------------------------------
| 01 | User1    | June 10    | 90    |   A   |
----------------------------------------------
| 02 | User2    | June 10    | 88    |   B   |
----------------------------------------------
| 03 | User1	| June 12    | 93    |   A   |
----------------------------------------------
| 04 | User2    | June 12    | 97    |   A   |
----------------------------------------------
| 05 | User3    | June 13    | 89    |   B   |
----------------------------------------------

and instead of using Score I used Grade in the above php code, how could I grab the Score data as an additional variable to have an end result like this:

------------------------------------
| User  | Score1 | Score2 | Score3 |
------------------------------------
| User1 | <img src=A.jpg title='90'>     | <img src=A.jpg title='93'>     | <img src=A.jpg title='97'>     |
------------------------------------
| User2 | <img src=B.jpg title='88'>     | <img src=A.jpg title='97'>     |        |
------------------------------------
| User3 | <img src=B.jpg title='89'>     |        |        |
------------------------------------

I’ve already figured out how to add the img tag and get it to display the A/B/C grade data (as image) already, but I can’t figure out how to grab the Score as an additional bit of data to add as the Title of the image. I’ve tried a few things, but can’t find a way to extract that Score data specifically as a separate result.

I know this seems a little redundant, but this has other applications elsewhere I’d like to use it on.

#10

You would add the Grade column to the query -

$query = "SELECT SubmitBy, Score, Grade FROM user_scores ORDER BY SubmitBy DESC";

When you index/pivot the data, instead of just storing the value of one column, you would store the whole $row -

foreach($whatever as $row)
{
	$result[$row['SubmitBy']][] = $row;
}

At the point of looping over $result, instead of getting a single value, you will get the $row of data, which will either be an actual row, a true value, or due to the array_merge/array_fill, an empty string, which is a false value -

	foreach(array_merge($arr,array_fill(0,$total_col-count($arr),'')) as $row)
	{
		if($row)
		{
			echo "<td><img src='{$row['Grade']}.jpg' title='{$row['Score']}'></td>\n";
		}
		else
		{
			echo "<td></td>\n";	
		}
	}
Sponsor our Newsletter | Privacy Policy | Terms of Service