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?
Advertisement
Answer
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.