Execute mysql query multiple times based on the number of values in a PHP variable

Question

I have a mysql table for capturing transactions. I am trying to display data in multiple php/html tables based on the available categories in the same table. sample data from my table is given below.

Date       Category     Particulars      type        Amount 
-----------------------------------------------------------
01.12.17   Donation     Received from X  Receipt     500.00
10.12.17   Donation     Received from Y  Receipt     200.00  
15.12.17   Medical Aid  Paid to A        Payment     100.00
20.12.17   Expense      Building Rent    Payment      50.00

Is it possible to execute by below query multiple times dynamically based on the distinct value available in category field. This is to populate query data in multiple tables in a single PHP page.

select * from my_table where category = 'Donation';
select * from my_table where category = 'Medical Aid';
...
...

and the expected result is like below...

Date       Category     Particulars      type        Amount 
-----------------------------------------------------------
01.12.17   Donation     Received from X  Receipt     500.00
10.12.17   Donation     Received from Y  Receipt     200.00
-----------------------------------------------------------

-----------------------------------------------------------
Date       Category     Particulars      type        Amount 
-----------------------------------------------------------
15.12.17   Medical Aid  Paid to A        Payment     100.00
-----------------------------------------------------------

-----------------------------------------------------------
Date       Category     Particulars      type        Amount 
-----------------------------------------------------------
20.12.17   Expense      Building Rent    Payment      50.00

The values in the category table my change. I trying to display the output of the first query in a separate html table and the second one on another.

yes , first you get the category value and store in one variable and than create dynamic query in loop . — Nimesh Patel
– Nimesh Patel, Commented Dec 27, 2017 at 6:24

Tim Biegeleisen · Accepted Answer · 2017-12-27 06:35:17Z

You should generally strive to issue the fewest number of queries possible, since this will minimize the number of network calls and overhead in your PHP application. I would recommend just using a single query with an ORDER BY clause on the category:

SELECT *
FROM my_table
WHERE category IN ('Donation', 'Medical Aid', ...)
ORDER BY category;

When you iterate the result set in PHP, the records for each category will be grouped together, and you can create a new HTML table for each category.

$sql = "SELECT * FROM my_table ...";    // your query goes here
$result = $conn->query($sql);
$category = NULL;

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        if ($category != $row['category']) {
            if ($category != NULL) {
                echo '</table>';
            }
            echo '<table>';
            $category = $row['category'];
        }

        echo '<tr>';
        echo '<td>' . $row['date'] . '</td>';
        echo '<td>' . $row['category'] . '</td>';
        // and other columns in the table
        echo '</tr>';
    }
    echo '</table>';    // close the final table
}

The above script is just intended to point you in the right direction. The HTML you actually want might be more complex than this.

Jonny · Accepted Answer · 2017-12-27 07:17:55Z

0

I just Ran a Query for each catigory then output in its own table.

$getCategories = "select category from my_table";
$catResults = $mysqli->query($catResults);
if ($catResults->num_rows > 0){
foreach ($catResults as $catRes){ 
$myQuery= "select * from my_table where category = '".$catRes."'";
$myResults = $mysqli->query($myResults);
if ($myResults->num_rows > 0){
while($row = $myResults->fetch_assoc()){  
$myResults = $row['date'];
$myResults = $row['Donation'];
$myResults = $row['particulars'];
$myResults = $row['type'];
$myResults = $row['amount'];
echo "<table style=\"width:100%\">"
echo "<tr>";
echo "<th>".$catRes."</th>";
echo "</tr>";
echo "<tr>";
echo "<td>".$row['time']."</td>";    
echo "<td>".$row['Donation']."</td>";  
echo "<td>".$row['particulars']."</td>";     
echo "<td>".$row['type']."</td>";   
echo "<td>".$row['amount']."</td>"; 
echo "</tr>"; }}}}

edited Dec 27, 2017 at 7:17

answered Dec 27, 2017 at 6:45

Jonny

1,3371 gold badge16 silver badges27 bronze badges

1 Comment

Prajith A S Over a year ago

what will do in case i dont know how many number of categories available in the field.

TarangP · Accepted Answer · 2017-12-27 06:26:55Z

-1

First Get All Category List

select Category from my_table

Store it in array.Than Use Foreach Statement to execute All Query.

answered Dec 27, 2017 at 6:26

TarangP

2,7385 gold badges24 silver badges42 bronze badges

Collectives™ on Stack Overflow

Execute mysql query multiple times based on the number of values in a PHP variable

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related