使用Foreach循环查询表中的数据

Question

Trying to generate table with data of query result. Currently there are three Candidates in the database which I want to display in the table.Problem is given code displays only two candidates data at a time not all the three.

Sample of output:

<?php 
      global $wpdb;
      $event_id = $_GET['id'];
      $candidates= $wpdb->get_results("select field_name,field_value from wpzk_cf7dbplugin_submits where field_name in ('FirstName','Lastname','Email','Organization','Designation','Address','City') and submit_time in (SELECT submit_time FROM wpzk_cf7dbplugin_submits where field_value = '$event_id')");
      if(count($candidates) >= 1)
      {   
          $header = array(
              'Firstname' => 'First Name',
              'Lastname' => 'Last Name',
              'Organization' => 'Organization',
              'Designation' => 'Designation',
              'Email' => 'Email',
              'Address' => 'Address',
              'City' => 'City',
          );

          echo "<table>";

          echo "<tr>";
          foreach ($header as $head) {
              echo sprintf("<th>%s</th>",$head);
          }
          echo "</tr>";
          $customResult = array();
          $column = 1;
          $row = 0;
          foreach($candidates as $candidate)
          {
              $customResult[$row][$candidate->field_name] = $candidate->field_value;
              if(++$column == 7){
                  $row++;
              }
          }

          foreach ($customResult as $result) {
              echo "<tr>";
              foreach ($header as $field => $value) {
                  echo sprintf("<td>%s</td>",$result[$field]);
              }
              echo "</tr>";
          }

          echo "</table>";
      }
      else
      {
        echo "No Registration For This Event";
      }
?>

/*Echo result of $candiates array is given below*/
   
 Array
    (
        [0] => stdClass Object
            (
                [field_name] => Firstname
                [field_value] => Shayan
            )

        [1] => stdClass Object
            (
                [field_name] => Lastname
                [field_value] => Mahmood
            )

        [2] => stdClass Object
            (
                [field_name] => Organization
                [field_value] => CyberSolutions
            )

        [3] => stdClass Object
            (
                [field_name] => Designation
                [field_value] => Software Developer
            )

        [4] => stdClass Object
            (
                [field_name] => Email
                [field_value] => [e-mail]
            )

        [5] => stdClass Object
            (
                [field_name] => Address
                [field_value] => targetarea01
            )

        [6] => stdClass Object
            (
                [field_name] => City
                [field_value] => London
            )

        [7] => stdClass Object
            (
                [field_name] => Firstname
                [field_value] => John
            )

        [8] => stdClass Object
            (
                [field_name] => Lastname
                [field_value] => Smith
            )

        [9] => stdClass Object
            (
                [field_name] => Organization
                [field_value] => Tech Solutions
            )

        [10] => stdClass Object
            (
                [field_name] => Designation
                [field_value] => Software Quality Analyst
            )

        [11] => stdClass Object
            (
                [field_name] => Email
                [field_value] => [e-mail]
            )

        [12] => stdClass Object
            (
                [field_name] => Address
                [field_value] => area02
            )

        [13] => stdClass Object
            (
                [field_name] => City
                [field_value] => New York
            )

        [14] => stdClass Object
            (
                [field_name] => Firstname
                [field_value] => Nelson
            )

        [15] => stdClass Object
            (
                [field_name] => Lastname
                [field_value] => Mandela
            )

        [16] => stdClass Object
            (
                [field_name] => Organization
                [field_value] => Web Solutions
            )

        [17] => stdClass Object
            (
                [field_name] => Designation
                [field_value] => Web Developer
            )

        [18] => stdClass Object
            (
                [field_name] => Email
                [field_value] => [e-mail]
            )

        [19] => stdClass Object
            (
                [field_name] => Address
                [field_value] => area03
            )

        [20] => stdClass Object
            (
                [field_name] => City
                [field_value] => Berlin
            )

    )

Please help if anyone know where in the code something is wrong

</div>

Answer 1

the data is coming from wordpress database which not guaranteed sorted like your header in this case you need to create a custom array which hold all fields and it's value and then call with header columns something like that

Try this

// created header array
$header = array(
    'Firstname' => 'First Name',
    'Lastname' => 'Last Name',
    'Organization' => 'Organization',
    'Designation' => 'Designation',
    'Email' => 'Email',
    'Address' => 'Address',
    'City' => 'City',
);

echo "<table>";

echo "<tr>";
// display headers
foreach ($header as $head) {
    echo sprintf("<th>%s</th>",$head);
}
echo "</tr>";

// creating custom Result
$customResult = array();
foreach($candidates as $candidate)
{
    $customResult[$candidate->field_name] = $candidate->field_value;
}

echo "<tr>";
// now display result like header order.
foreach ($header as $field => $value) {
    echo sprintf("<td>%s</td>",$customResult[$field]);
}
echo "</tr>";

echo "</table>";

UPDATE

Solution # 2 for multiple result try blow Code but this may not work correct

$header = array(
        'Firstname' => 'First Name',
        'Lastname' => 'Last Name',
        'Organization' => 'Organization',
        'Designation' => 'Designation',
        'Email' => 'Email',
        'Address' => 'Address',
        'City' => 'City',
    );
echo "<table>";

echo "<tr>";
foreach ($header as $head) {
    echo sprintf("<th>%s</th>",$head);
}
echo "</tr>";

$customResult = array();
$column = 1;
$row = 0;
foreach($candidates as $candidate)
{
    $customResult[$row][$candidate->field_name] = $candidate->field_value;
    if(++$column == 7){
        $row++;
    }
}

foreach ($customResult as $result) {
    echo "<tr>";
    foreach ($header as $field => $value) {
        echo sprintf("<td>%s</td>",$result[$field]);
    }
    echo "</tr>";
}

echo "</table>";