Head SubHead

MySQL database
1

I made a data base with head, sub_head, item 1,item2 and want the display to be
Head
Subhead Item1 Item2
subhead Item1 Item2
Head
Subhead Item1 Item2
subhead Item1 Item2… wher the subhead would be two or more.

Currently the code is correctly
Head
Subhead Item1 Item2
subhead Item1 Item2

but ignoring the rest of data.

<?php 
  $sum=0;
    $sql = "SELECT * FROM budget ";
    $result_data=mysqli_query($conn, $sql);
       while ($row_data = mysqli_fetch_assoc($result_data)) {

        $head=$row_data['head'];

            echo "<tr>";
            echo "<td class='tg-0pky'>" .$head. "</td>";
            echo "</tr>";

            $sql = "SELECT * FROM budget WHERE head='$head'";
            $result_data=mysqli_query($conn, $sql);
            while ($row_data = mysqli_fetch_assoc($result_data)) {

            $sub_head=$row_data['sub_head'];
            $sub_budget=$row_data["sub_budget"];
            $budget_paydate=$row_data["budget_paydate"];
            $sub_actual=$row_data["sub_actual"];
            $sub_date=$row_data["sub_date"];
            $remarks=$row_data["remarks"];
            echo "<tr>";

            echo "<td class='tg-0pky'>" .$sub_head. "</td>";
            echo "<td class='tg-0pky'>" .$budget_paydate. "</td>";
            echo "<td class='tg-0pky'>" .$sub_budget. "</td>";
            echo "<td class='tg-dvpl'>" .$sub_actual. "</td>";
            echo "<td class='tg-dvpl'>" .$sub_date. "</td>";
            echo "<td class='tg-dvpl'>" .$remarks. "</td>";
            echo "</tr>";
                     }
    
      
           }
2

Don’t run select queries inside of loops. This is very inefficient, due to the time it takes for each round-trip communication between php and the database server, vs the time it actually takes to execute the query on the database server.

Also, the first query is getting all the data, not just the unique head(ing) values, so, you won’t get the expected result from the current method, and the reason for the output stopping after the first pass through the outer loop is because you are reusing the variable holding the result from the first query, in the code for the second query. That variable no longer contains a value that the outer loop expects.

Just change the first/single query so that it gets the data you want in the order that you want it (almost every select query should have an ORDER BY term.) To produce the output, with the heading only displayed once, you can either use a variable to hold the ‘last’ heading value, then detect and save the new value each time it changes, or I like to index/pivot the data using the heading value as the main index in an array of arrays, when you retrieve the data, then simply loop over this array of arrays to produce the output.

Some other points -

  1. List out the columns you are selecting in the query. This will insure you are only selecting the data you need and it will cause your query/code to be self documenting. Anyone reading the query/code will be able to tell what you are trying to do, without needing your database table definition.
  2. Don’t copy variables to other variable without any purpose, just use the original variables the data is in. Unless you are doing this to get typing practice, to improve your speed and accuracy, the 7 lines of code copying variables to other variables don’t add any value to what you are doing.
  3. The head(ings) should be defined in a separate table. This will produce heading id values. You would store the ids in any table holding data related to the headings. This will use the last amount of data storage, produce the fastest queries, and allow the heading names to be edited without requiring all the data using the headings to be updated.
3

totally lost would appreciate more help by example if possible

4

To index/pivot the fetched data, from a single query, using the ‘head’ value as the index, see this example code -

// retrieve data
$data = [];
while ($row = mysqli_fetch_assoc($result))
{
	// index/pivot data using the head value
	$data[$row['head']][] = $row;
}

At the point of producing the output, see this example code -

// produce output
echo "<table>";

foreach($data as $head=>$arr)
{
	echo "<tr>";
	echo "<td class='tg-0pky' colspan='6'>$head</td>";
	echo "</tr>";
	
	// output the data under each head value
	foreach($arr as $row)
	{
		echo "<tr>";
		echo "<td class='tg-0pky'>{$row['sub_head']}</td>";
		echo "<td class='tg-0pky'>{$row["budget_paydate"]}</td>";
		echo "<td class='tg-0pky'>{$row["sub_budget"]}</td>";
		echo "<td class='tg-dvpl'>{$row["sub_actual"]}</td>";
		echo "<td class='tg-dvpl'>{$row["sub_date"]}</td>";
		echo "<td class='tg-dvpl'>{$row["remarks"]}</td>";
		echo "</tr>";
	}
}

echo '</table>';
1 Like
5

thank you vey much for your time, appreciate it

6

It worked :smile:
but could you please explain this

7

That’s a two-dimensional array assignment statement. What it is doing is this -

Using $data[] = $row; would create numerically indexed, 0, 1, 2, … elements in $data. Using $data[ $row['head'] ] = $row; would create elements in $data having an index value of whatever is in $row[‘head’], with each new element for any specific ‘head’ index value replacing the previous element. By adding a 2nd array level, the on the end, making an array of arrays, or a sub-array, each new $row is stored as numerically indexed, 0, 1, 2, … elements in the main array of whatever ‘head’ index value the $row of data contains.

If you examine the data, with the following code, it should be clear(er) what is occurring -

echo '<pre>'; print_r($data); echo '</pre>';
1 Like