Question 20268 in Mysql Question posted in
The official MySQL documentation can be found here.

PHP for each function to print td – Mysql

(Solution found, check at the end)
I have a database table called CONTRIBUTION FEES

category year amount program
ID FEE 1 5 USD SWT
TUITION FEE 1 50 USD SWT
TUITION FEE 2 50 USD SWT
TUITION FEE 3 50 USD SWT
EXAMINATION 2 10 USD SWT

And i use the following php codes with mysqli to select data and display in html table

<?php  
           echo'<table class="table table-bordered table-sm" id="table">
      <thead>';
          
 include $_SERVER["DOCUMENT_ROOT"] . "/config.php"; 
                // Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$query = "SELECT GROUP_CONCAT(DISTINCT year) AS years FROM `CONTRIBUTION FEES` WHERE program = '$program'";
$result = $conn->query($query);
  $row = $result->fetch_assoc();
  $years = explode(',', $row['years']);
  if($row['years'] != ''){
 echo' <tr>
    <th>CATEGORY</th>';
     foreach ($years as $year){
     echo' <th>YEAR '.$year.'</th>';
            }
   echo' 
    <th>ACTIONS</th>
   </tr>';
  }

         
echo'
      </thead>
      <tbody>';
            


           
$query = "SELECT GROUP_CONCAT(DISTINCT category) AS categories,GROUP_CONCAT(DISTINCT year) AS years,GROUP_CONCAT(amount) AS amounts FROM `CONTRIBUTION FEES` WHERE program = '$program' GROUP BY category";
$result = $conn->query($query);
        if ($result->num_rows > 0) {
  while($row = $result->fetch_assoc()){
  $years = explode(',', $row['years']);
    $amounts = explode(',', $row['amounts']);
    $categories = explode(',', $row['categories']);
    
  foreach ($categories as $category) {
        echo '<tr>
            <td>' . $category . ' </td>';
           foreach ($years as $key => $year) {
          $amount = isset($amounts[$key]) ? $amounts[$key] : '0';
           echo '<td>' . $amount. '</td>';
}
     echo '<td><button>Edit</button></td>
    </tr>';
}
    
  }
    }else{
  
    }

  

 echo'
   </tbody>
    </table>';
     ?>

The challenge I am facing is to display each contribution to its associated year. Currently, the html table looks like this

CATEGORY YEAR 1 YEAR 2 YEAR 3 ACTIONS
EXAMINATION 10 USD Edit
GRADUATION 20 USD Edit
ID FEE 5 USD Edit
TUITION FEE 50 USD 50 USD 50 USD Edit

As you can see, GRADUATION is displayed in Year 1 column instead of Year 3 column and Examination is display in Year 1 instead of Year 2.

Format I want

CATEGORY YEAR 1 YEAR 2 YEAR 3 ACTIONS
EXAMINATION 10 USD Edit
GRADUATION Edit
ID FEE 5 USD Edit
TUITION FEE 50 USD 50 USD 50 USD Edit

I was able to find a solution, Read @Khang Tran Answer.

       echo '<tr>
            <td>' . $category . ' </td>';
    foreach ($years as $year) {
   $yearKey = array_search($year, $categoryYears);

  if ($yearKey !== false && isset($amounts[$yearKey])) {
       $amount = $amounts[$yearKey];
   } else {
       $amount = '0';
  }

    echo '<td>' . $amount . '</td>';
}

     echo '<td><button>Edit</button></td>
    </tr>';
}```
Tags:

3

Answers


  1. 0

    You need to get the returned SQL query into the correct form first.

    Schema (MySQL v5.7)

    create table abc (category varchar(20), year int, amount varchar(20), program varchar(3));

insert into abc values

('ID FEE' , 1  , '5 USD' , 'SWT'  ),
('TUITION FEE' ,   1  , '50 USD',  'SWT'  ),
('TUITION FEE' ,   2  , '50 USD' ,  'SWT'  ),
('TUITION FEE' ,   3  , '50 USD' ,  'SWT'  ),
('EXAMINATION' ,   2  , '10 USD' ,  'SWT'  );

    Query #1

    SELECT 
category AS categories, 
group_concat(year) AS years,
group_concat(amount) AS amounts
FROM abc
group by category;
    categories years amounts
    EXAMINATION 2 10 USD
    ID FEE 1 5 USD
    TUITION FEE 1,2,3 50 USD,50 USD,50 USD

    View on DB Fiddle

    In the PHP, use if to place either the amount or an empty cell.

    Login or Signup to reply.
  2. 0

    You need to use the years of the first query in the second loop. So first, you change the name of the second $years, such as $categoryYears.

    This is my solution. Hope it’s useful for you

    //....
$categoryYears = explode(',', $row['years']);
$amounts = explode(',', $row['amounts']);
$categories = explode(',', $row['categories']);

foreach ($categories as $category) {
    echo '<tr>
            <td>' . $category . ' </td>';
    //loop for all years of the first query
    foreach ($years as $key => $year) {
        //get the index of year in list years of the category
        if ($yearKey = array_search($year, $categoryYears) !== false) {
            $amount = isset($amounts[$yearKey]) ? $amounts[$yearKey] : '0';
        } else {
            $amount = '0';
        }
   
        echo '<td>' . $amount. '</td>';
    }

//....
    Login or Signup to reply.
  3. 0

    I mean you can do 2 DB queries:

    • first: get min and max year values
    • second: get data grouped by category
    <?php  
    $query = "SELECT MIN(year) min_year, MAX(year) max_year FROM abc;";
    
    $stmt = $pdo->prepare($query);
    $stmt->execute();
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    $min_year = $row['min_year'];
    $max_year = $row['max_year'];
    
    printf('From: %d to: %d ' . PHP_EOL, $min_year, $max_year);
    
    $query = "SELECT category, json_objectagg(year, amount) amounts FROM abc GROUP BY category;";
    
    $stmt = $pdo->prepare($query);
    $stmt->execute();
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo $row['category'];
        $amounts = json_decode($row['amounts'], 1);
        for($y=$min_year; $y <= $max_year; $y++) {
            echo ' | ';
            echo $amounts[$y] ?? '---';
        }
        echo PHP_EOL;
    }

    https://phpize.online/sql/mysql57/7cb0020c019531fd962153876fa7736f/php/php82/163a64b88cdb2cd514224eaa72d3c240/

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search