Build a multidimensional PHP array out of MySQL table data linked by values in one column

Question

I am using a Wordpress plugin (Advanced CF7 DB). It puts the results of the contact form into the MySQL database so it can be displayed on the front end of the website.

There are two tables with data like this.

Table 1:

id     | created
-------------------------------
1      |   2020-09-21 13:04:09  
2      |   2020-09-22 13:04:09    
3      |   2020-09-23 13:04:09    
4      |   2020-09-24 13:04:09    
5      |   2020-09-25 13:04:09  

Table 2:

data_id| name         | value
-------------------------------
1      |   fname      |   Joe  
1      |   sname      |   Smith    
1      |   age        |   19    
1      |   display    |   yes    
1      |   comment    |   Here is my comment    
2      |   fname      |   Jenny    
2      |   sname      |   Ford    
2      |   age        |   20    
2      |   display    |   no    
2      |   comment    |   Here is my comment    
3      |   fname      |   Hazel   
3      |   sname      |   Brown    
3      |   age        |   15    
3      |   display    |   yes   
3      |   comment    |   Here is my comment

The id in table 1 corresponds to the data_id in table 2.

I need to display the comment on the website in two separate lists (for people above and below 18), and only display comments where there is a row with 'name = display' and 'value = yes' for the corresponding data_id.

Example:

Comments from people under 15:

ID: 3
Name: Hazel Brown
Comment: Here is my comment

Comments from people over 18

ID: 1
Name: Joe Smith
Comment: Here is my comment

Would the best approach to be to put the information into a multidimensional PHP array where I can manipulate it, or should I try to use MySQL to get the information in the format I need it?

Answer 1

I'm not sure why your table is formatted that way in the first place, but you could devise your row values into columns first, then using HAVING clause to filter by your criteria:

Here's a sample:

SELECT 
    t2.dataid,
    MAX(IF(t2.name = 'fname', t2.value, NULL)) AS `fname`, 
    MAX(IF(t2.name = 'sname', t2.value, NULL)) AS `sname`,
    MAX(IF(t2.name = 'age', t2.value, NULL)) AS `age`,
    MAX(IF(t2.name = 'display', t2.value, NULL)) AS `display`,
    MAX(IF(t2.name = 'comment', t2.value, NULL)) AS `comment`,
    t1.created

FROM table2 t2 
JOIN table1 t1 ON t2.dataid = t1.id

GROUP BY t2.dataid, t1.created
HAVING display = 'yes' AND age >= 18
# age <= 15 whatever your condition for age

Right after that it's just a matter of fetching it. Sidenote: I'm not a wordpress guru, but it's fairly straightforward, execute the query -> fetch the results, loop them in your markup.

global $wpdb;
$query = '<your query here>';
$results = $wpdb->get_results($query);
foreach ($results as $row) {
    echo 'ID: ', $row->dataid;
    echo 'Name: ', $row->fname, ' ', $row->sname;
    echo 'Comment: ', $row->comment;
}

It's just a sample, you can squeeze in your markup html whichever you like.