php - Dynamic rowspan while fetching records from database -
ename sal tom 100 tom 200 bill 100 bill 250 bill 450 bill 400 this query , html structure has given above output.
<?php $sql = "select * emp "; $result= mysql_query($sql); while($row=mysql_fetch_array($result)) { <tr > <td rowspan="" ><?php echo $row['ename']; ?></td> <td><?php echo $row['esal']?></td> </tr> <? }?> how can following output:
ename sal tom 100 200 bill 100 250 450 400
sorry poor english: here had answered question how show data database dynamic rowspan. again let me try answer question. first lest work on mysql query.
mysql work:
in mysql query have not queried order by. because in real life, can not expect after records of tom, bills record there. example take following insertion.
insert test_work(ename, sal) values("tom", 100), ("bill", 450), ("bill", 100), ("tom", 200), ("bill", 250), ("bill", 400), ("james", 50); select * test_work; result:
+-------+------+ | ename | sal | +-------+------+ | tom | 100 | | bill | 450 | | bill | 100 | | tom | 200 | | bill | 250 | | bill | 400 | | james | 50 | +-------+------+ so mysql query should order ename. here each person's sal should ordred . our query:
select * emp order ename, sal; coding:
- the whole task can divide 3 parts.
- mysql data fetch , storing in array.
- calculating rowspan
- printing
mysql datafetching:
during data fetching mysql server should try use mysql_fetch_assoc function instead of mysql_fetch_array . because mysql_fetch_assoc return ename , sal. mysql_fetch_array return array indexes ename, sal, 0, 1.
# connect mysql server # , select database, on # work. $conn = mysql_connect('', 'root', ''); $db = mysql_select_db('test'); # query data database. $query = 'select * test_work order ename, sal'; $result = mysql_query($query); # intialize array, # store fetched data. $sal = array(); $emp = array(); # loop on fetched data, , save # data in array. while($row = mysql_fetch_assoc($result)) { array_push($emp, $row['ename']); array_push($sal, $row['sal']); } calculating row span:
# intialize array, store # rowspan user. $arr = array(); # loop on sal array ($i = 0; $i < sizeof($sal); $i++) { $empname = $emp[$i]; # if there no array employee # create elemnt. if (!isset($arr[$empname])) { $arr[$empname] = array(); $arr[$empname]['rowspan'] = 0; } $arr[$empname]['printed'] = "no"; # increment row span value. $arr[$empname]['rowspan'] += 1; } when print_r arr array output be:
array ( [bill] => array ( [rowspan] => 4 [printed] => no ) [james] => array ( [rowspan] => 1 [printed] => no ) [tom] => array ( [rowspan] => 2 [printed] => no ) ) printing rowspan:
echo "<table cellspacing='0' cellpadding='0'> <tr> <th>ename</th> <th>sal</th> </tr>"; for($i=0; $i < sizeof($sal); $i++) { $empname = $emp[$i]; echo "<tr>"; # if row not printed print. # , make printed value "yes", # next time not printed. if ($arr[$empname]['printed'] == 'no') { echo "<td rowspan='".$arr[$empname]['rowspan']."'>".$empname."</td>"; $arr[$empname]['printed'] = 'yes'; } echo "<td>".$sal[$i]."</td>"; echo "</tr>"; } echo "</table>"; code optimization:
now can combine rowspan calculation , mysql data fetching. because during saving fetched data in array can calculate rowspan. our final code:
<!doctype html> <html> <head> <style> table tr td, table tr th{ border: black 1px solid; padding: 5px; } </style> </head> <body> <?php # connect mysql server # , select database, on # work. $conn = mysql_connect('', 'root', ''); $db = mysql_select_db('test'); # query data database. $query = 'select * test_work order ename, sal'; $result = mysql_query($query); # $arr array ful during # printing $arr = array(); # intialize array, # store fetched data. $sal = array(); $emp = array(); #%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%# # data saving , rowspan calculation # #%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%# # loop on fetched data, , save # data. while($row = mysql_fetch_assoc($result)) { array_push($emp, $row['ename']); array_push($sal, $row['sal']); if (!isset($arr[$row['ename']])) { $arr[$row['ename']]['rowspan'] = 0; } $arr[$row['ename']]['printed'] = 'no'; $arr[$row['ename']]['rowspan'] += 1; } #%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% # data printing # #%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%# echo "<table cellspacing='0' cellpadding='0'> <tr> <th>ename</th> <th>sal</th> </tr>"; for($i=0; $i < sizeof($sal); $i++) { $empname = $emp[$i]; echo "<tr>"; # if row not printed print. # , make printed value "yes", # next time not printed. if ($arr[$empname]['printed'] == 'no') { echo "<td rowspan='".$arr[$empname]['rowspan']."'>".$empname."</td>"; $arr[$empname]['printed'] = 'yes'; } echo "<td>".$sal[$i]."</td>"; echo "</tr>"; } echo "</table>"; ?> </body> </html> result:
Comments
Post a Comment