Structuring SQL Output for single and 2 dimensional array

Question

I am working on a PHP program which requires a series of SQL related outputs and I was hoping that SQL could do most of the work form me.

I have a table structured for the sake of this question with this type of data and headers

Note : ID is used for specific entries and is Auto Incremented
Note : Title Section is used to isolate data for specific projects
Note : Person may be duplicated for a specific project
Note : Date will not be duplicated but not necessarily be organized
Note : For the purpose of this question assume data is not organized as nicely and Ordering to occur in the SQL statements

When I use phpmyadmin sql database

SELECT * FROM `This_Table` WHERE `Title` = 'Project_1' ORDER BY `Working_Date` ASC, `Worker_Name` ASC 

This is the correct output that I want.

Here comes the issue, I want to use something close to array_agg but there was confusion about making arrays of arrays (2 dimension arrays) but what I want to do is use a SQL query on the new table such that I can generate the following arrays.

QUESTION : Can SQL provide an output that would allow for me to have the following arrays based on the above datasets?

#Unique Dates Organized ASC
$Date = array('2020-04-19', '2020-04-20', '2020-04-21', '2020-04-22', '2020-04-23', '2020-05-26', '2020-06-01');

#Names which by default will be unique in their own sub array by date 
#so in short a 2 dimensional array with Date then Persons
$Person = array(array('Joe', 'Mark'), array('Mark'), array('Joe', 'Mark'), array('Joe', 'Mark'), array('Mark'), array('Joe'), array('Joe'))

It sounds like a ton of work but if SQL could give me that kind of output accurately it would make some of my coding significantly easier. At this point I have to output the rows of data from SQL into my PHP and build the arrays within the PHP code itself rather than have SQL output the arrays like this and for larger databases it takes a significant amount of time to build the array itself let alone calling for unique data.

Answer 1

Please find below code and explanation to generate each of the arrays.

1.The first array is pretty straight forward.Fetch distinct records from DB and load the array.The code is below,

 <?php
 $link = mysqli_connect("localhost", "my_user", "my_password", "world");

 $query = "select distinct date from projects order by date asc";
 $result = mysqli_query($link, $query);

 /* numeric array */
 $datearray = mysqli_fetch_array($result, MYSQLI_NUM);
 ?>

2. The second one, which is array of array(rather dynamic array) is slightly tricky. I will break down the approach first, Step1. MYSQL group_concat aggregation is used to transpose rows to column.Refer Db fiddle link to check the details -https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=5dd9a070c1d9a19bc16a5f4384672cc6

MySQL query result set is as below, grouped by date in ascending order of date. names Joe,Mark Joe Joe,Mark

Step 2. In php, read the transposed name records returned and use "explode" to load array by splitting a comma separated string. This will be done for each records returned in loop. Step 3. The array from the above is further loaded to another array.

 <?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");

$query = "select names from (select dates,group_concat(person) as 'names' from projects group by dates order by dates asc) tab";
$result = mysqli_query($link, $query);
$namesarray = [];
while($row = mysqli_fetch_assoc($result))
    {
      $namestring = $row['names']
      $arrayofarray = explode(',', $$namestring); 
      $namesarray = array($arrayofarray);
    }
?>

PS : php code has been tested for any syntax error.SQL queries were tested successfully on MySQL 8.0 Database.The table name is assumed to be projects.

Please refer DBfiddle link to review the SQL query https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=85f2f88b2ec25ce1d8479a5f5df4d9bb