PHP looping MySQL table-CodePudding

I am creating a dashboard where a user can see their form submissions and no one else's submissions.

The database looks like this:

        db_frm_item_metas
| meta_value | field_id | item_id |
-----------------------------------
|     90     |    57    |   33    |
|     01     |    56    |   33    |
|   100000   |    58    |   33    |
|     90     |    57    |   34    |
|     01     |    56    |   34    |
|   100001   |    58    |   34    |
|     91     |    57    |   35    |
|     01     |    56    |   35    |
|   100000   |    58    |   35    |
|     90     |    57    |   36    |
|     01     |    56    |   36    |
|   100002   |    58    |   36    |
-----------------------------------

The numbers 90 and 91 are id's assigned to the company and are dictated by field_id 57. The user within the company is dictated by field_id 56. Finally, the form number is field_id 58. Each form that is submitted is grouped by item_id.

I think the problem is with the MySQL query. I think it is too specific and isn't taking the form submission into account.

    $formID = "SELECT * FROM db_frm_item_metas WHERE field_id = 58 ORDER BY meta_value DESC";
    $results = $wpdb->get_results($formID);
        
    foreach ($results as $result){      
        echo '<span  style="float: left; clear: left; margin-bottom: 20px;"><p >' . $companyID . "-"  . $userID . "-". $result->meta_value . '</p>' . $getFormItemID . ' ' . $result->item_id . ' </span>';
        }

If I remove "WHERE field_id = 58" from the query, how do I return that value within the loop? Or should I be looping through multiple MySQL queries, if that's possible in one loop?

The ideal solution is to print the values like the following for user 90-01:

<p>90-01-100002</p>
<p>90-01-100001</p>
<p>90-01-100000</p>

and for user 91-01:

<p>91-01-100000</p>

I am very new to PHP and don't understand the syntax well. Please be considerate.

CodePudding user response：

Try this SQL:

SELECT 
    t1.meta_value AS t1_value, 
    t2.meta_value AS t2_value, 
    t3.meta_value AS t3_value
FROM db_frm_item_metas AS t1
INNER JOIN db_frm_item_metas AS t2
    USING (item_id)
INNER JOIN db_frm_item_metas AS t3
    USING (item_id)
WHERE 
    t1.field_id = 57
    AND t2.field_id = 56
    AND t3.field_id = 58

CodePudding user response：

CREATE TABLE db_frm_item_metas (
  `meta_value` varchar(10),
  `field_id` varchar(10),
  `item_id` varchar(10)
);

INSERT INTO db_frm_item_metas
  (`meta_value`, `field_id`, `item_id`)
VALUES
  ('90', '57', '33'),
  ('01', '56', '33'),
  ('100000', '58', '33'),
  ('90', '57', '34'),
  ('01', '56', '34'),
  ('100001', '58', '34'),
  ('91', '57', '35'),
  ('01', '56', '35'),
  ('100000', '58', '35'),
  ('90', '57', '36'),
  ('01', '56', '36'),
  ('100002', '58', '36');

SELECT 
    MAX(IF(`field_id` = 57,`meta_value`, '')) mv1,
    MAX(IF(`field_id` = 56,`meta_value`, '')) mv2,
    MAX(IF(`field_id` = 58,`meta_value`, '')) mv3
FROM db_frm_item_metas
GROUP BY `item_id`
HAVING mv1 = '90' AND mv2 = '01'

mv1 | mv2 | mv3   
:-- | :-- | :-----
90  | 01  | 100000
90  | 01  | 100001
90  | 01  | 100002

db<>fiddle here

your code would them look like

$results = $wpdb->get_results(
  $wpdb->prepare(
    "
     SELECT 
         MAX(IF(`field_id` = 57,`meta_value`, '')) mv1,
         MAX(IF(`field_id` = 56,`meta_value`, '')) mv2,
         MAX(IF(`field_id` = 58,`meta_value`, '')) mv3
     FROM db_frm_item_metas
     GROUP BY `item_id`
     HAVING mv1 = %s AND mv2 = %s    ",
    array( '90',  '01' )
  )
);

if ( $results ) {
  foreach ( $results as $result) {
        echo '<span  style="float: left; clear: left; margin-bottom: 20px;"><p >' . . $result->mv1 . . "-"  . $result->mv2 . "-". $result->mv3 . '</p>' . ' </span>';
  }
}