Help creating an array

[PleXy]

Hi everyone,

I´m creating a database where my colleagues and I can put in our plans for the nextcoming weeks.
I´ve made the script which inserts the data to the database so this far I´m all good.

However, I just can’t get the output data right!

This is how my database table is set up:

Here is the output I want:

And here’s my current script:
[php]mysql_connect(“localhost”, “username”, “password”) or die (“Error , check your server connection.”);
mysql_select_db(“database”);
$query = “SELECT B.Week, A.display_name, B.WhatsPlanned, A.avatar FROM logins_users A, trainingplanner B WHERE A.id = B.UserID order by week desc”;
$result = mysql_query($query) or die (mysql_error());

if (mysql_num_rows($result)!=0) {

echo “

”;
echo “”;

  while($row=mysql_fetch_row($result))   
   {

$Week = $row[0];
$Displayname = $row[1];
$Whatsplanned = $row[2];
$Avatar = $row[3];

echo “

”;
}

}
echo “”;
echo “

Week	Name	Planned

”;
[/php]

MANY thanks in advance!

[PleXy]

After couple hours of Googling I found something that MIGHT work.
Does anyone have an idea how I can turn my database table setup to work like this?

[php]<?php
$array = array( array(“week”=>“33”, “Name1”=>‘I have no idea what I was doing’, “Name2”=>‘Did this did that…’, “Name3”=>‘Did nothing!’ ),
array(“week”=>“34”, “Name1”=>’’, “Name2”=>’’, “Name3”=>‘Did something!’),
array(“week”=>“35”, “Name1”=>’’, “Name2”=>‘Did that did this…’, “Name3”=>’’)
);
?>

<?php if (count($array) > 0): ?> <?php foreach ($array as $row): array_map('htmlentities', $row); ?> <?php endforeach; ?>

<?php echo implode('	', array_keys(current($array))); ?>
<?php echo implode('	', $row); ?>

<?php endif; ?>[/php]

Output:

[PleXy]

Couple of more hours and I found a function where you can change the SQL query to format as you want instead of doing it in an array.

It works great in SQL Fiddle but it doesn’t work on the PHP script.
Anyone who knows what I can do to make it work in the PHP script?

The SQL Fiddle is found here: http://sqlfiddle.com/#!2/30515c/1

And the error I´m getting is:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( ‘MAX(IF(na’ at line 2

[malpamalpa]

Just a little tip, that may help without redefining your database.

You just select everything from the databse, and the loop over the table and create final array with data, that will allow you to display your table easily.

[php]
$finalArray = array();
foreach ( $results as $result ) {
array[$result->week][$result->user] = $result->WhatsPlanned;
}[/php]

Once you have such array, just iterate over it and display the data. It should do the trick and it’s not that much time consuming imho. Just remember to order the query the weeks descending and maybe names ascending and everything should be fine.

You can eventually sort the arrays. But that’s just another way to do it.