如何使用动态rowspan显示数据库中的数据 [英] How to show data from database with dynamic rowspan
问题描述
位置:id_location
位置
组件:id_comopnent
id_location
comonen
sub_component:id_sub_component
id_comopnent
sub_component
如何获得以下输出:
location
| ----- ------ | ------------- |
|子组件|
| ----------- | ------------- |
| |数据|
|数据| ------------- |
| |数据|
| ----------- | ------------- |
| |数据|
| | ------------- |
|数据|数据|
| | ------------- |
| |数据|
| ----------- | ------------- |
位置
| ----------- | ------------- |
|子组件|
| ----------- | ------------- |
| |数据|
|数据| ------------- |
| |数据|
| ----------- | ------------- |
| |数据|
|数据| ------------- |
| |数据|
| ----------- | ------------- |
位置(根据数据)
这是我的代码 p>
<?php
$ dsn =mysql:host = localhost; dbname = kampus_hijau;
$ dbc =新的PDO($ dsn,'root','');
$ sql1 =SELECT * FROM Lokasi ORDER BY id_location;
$ stmt1 = $ dbc-> prepare($ sql1);
$ stmt1-> execute();
while($ row1 = $ stmt1-> fetch(PDO :: FETCH_ASSOC)){
$ location ++;
echoLocation $ location:。$ row1 ['location'];
?>
< table width =469border =1>
< tr bgcolor =#00FFFF>
< th width =109class =roundedscope =col> Component< / th>
< th width =248class =roundedscope =col> Sub Component< / th>
Nilai< / th>
< / tr>
<?php
$ query =SELECT * FROM sub_component,component where sub_component.id_component = component.id_component and component.id_location ='$ data [id_location]'order by component。 id_location;
$ stmt = $ dbc-> prepare($ query);
$ stmt-> execute();
$ b $ while($ row = $ stmt-> fetch(PDO :: FETCH_ASSOC)){
$ result [$ row ['component']] [] = $ row ['sub_component ];
foreach($ result as $ id => $ invoices){
echo'< td rowspan ='。计数($发票)。 '>'。 $ id。 < / TD>;
$ count = 0;
foreach($ invoices as $ invoice){
if($ count!= 0){
echo'< tr>';
}
回显< td> $发票< / td>;
回声< / tr>;
$ count ++;
}
}
?>
< / table>
<?php
}
?>
以及我得到的输出:
$ b $使用该代码,输出形式是适当的,但数据不合适,它总是显示前一个数据在下一个表中(红色框是重复
的数据)。如何修复它?
首先抱歉我的英文不好。
在您的查询中,不是按 id_location 进行订单,而是按组件名称排序。这样你就可以轻松地添加动态的rowspan。我没有绞死你的连接程序,但我已经改变了你的第二部分。请检查。我知道有3到4个循环。但是,如果有人发现更好的算法,请告诉我。
$ sql1 =SELECT * FROM Lokasi ORDER BY id_location;
$ stmt1 = $ dbc-> prepare($ sql1);
$ stmt1-> execute();
while($ row1 = $ stmt1-> fetch(PDO :: FETCH_ASSOC)){
$ location ++;
echoLocation $ location:。$ row1 ['location'];
?>
<?php
$ query =SELECT *
FROM sub_component,
component
WHERE sub_component.id_component = component.id_component
AND component.id_location ='$ data [id_location]'
ORDER BY component.component_name;
$ stmt = $ dbc-> prepare($ query);
$ stmt-> execute();
#声明两个emty数组
$ component = array(); #将存储组件
$ sub_component = array(); #将存储子组件
$ loop = 0;
#现在,如果有任何数据是从预设查询中提取的,那么填写上面声明的数组
#。
while($ row = $ stmt-> fetch(PDO :: FETCH_ASSOC)){
$ component [$ loop] = $ row ['component'];
$ sub_component [$ loop] = $ row ['sub_component'];
$ loop = $ loop + 1;
}
#如果没有数据提取,那么我告诉
#没有获取数据。
if(!sizeof($ component)){
echo'Empty Data';
} else {
print< table width ='469px'border ='1'>
< tr bgcolor ='#00FFFF'>
< th width ='109'class ='rounded'scope ='col'> Component< / th>
< th width ='109'class ='rounded'scope ='col'> Sub Component< / th>
< / tr>;
#现在我们的主逻辑开始打印动态rowspan
#开始循环。
#这里的重点是用于循环
$ tmp_arr = array();
$ main_assoc_arr = array(); ($ i = 0; $ i< sizeof($ sub_component); $ i ++){
$ b $ array_push($ tmp_arr,$ sub_component [$ i]);
;
#如果我们已经到达最后一个元素
#,并且在$ main_assoc_arr中,组件不存在
#那么我们将它们存储为如下。
if($ i =(sizeof($ sub_component)-1)
&&!array_key_exists($ component [$ i],$ main_assoc_arr)){
$ main_assoc_arr [$ component [$ i]] = array();
$ main_assoc_arr [$ component [$ i]] = $ tmp_arr;
#恢复阵列。
$ tmp_arr = array();
#也跳出循环
break;
}
#如果当前组件不等于
#Next组件,那么
如果($ component [$ i]!= $ component [$ i +1]){
$ main_assoc_arr [$ component [$ i]] = array();
$ main_assoc_arr [$ component [$ i]] = $ tmp_arr;
#恢复阵列。
$ tmp_arr = array();
}
}
#现在我们打算用rowspan打印表格。
foreach($ main_assoc_arr as $ comp => $ sub_comp){
$ printed = 0;
$ rowspan = sizeof($ sub_comp);
foreach($ sub_comp as $ elm){
print< tr>;
#确保列在每个循环中不会打印
#,因为它包含动态数组。
if(!$ printed){
print< td rowspan ='$ rowspan'> $ comp< / td>;
}
print< td> $ elm< / td>
打印< / tr>;
}
}
print< / table>;
}
?>
Iam newbie in php programing, i have some problames with showing data from mysql database using php and html. this is my tables :
location :id_location
location
component :id_comopnent
id_location
comonen
sub_component :id_sub_component
id_comopnent
sub_component
How can i get following output:
location
|-----------|-------------|
| component |sub component|
|-----------|-------------|
| | Data |
| Data |-------------|
| | Data |
|-----------|-------------|
| | Data |
| |-------------|
| Data | Data |
| |-------------|
| | Data |
|-----------|-------------|
location
|-----------|-------------|
| component |sub component|
|-----------|-------------|
| | Data |
| Data |-------------|
| | Data |
|-----------|-------------|
| | Data |
| Data |-------------|
| | Data |
|-----------|-------------|
Location (according to the data)
this is my code
<?php
$dsn = "mysql:host=localhost;dbname=kampus_hijau";
$dbc = new PDO($dsn, 'root', '');
$sql1 = "SELECT * FROM Lokasi ORDER BY id_location";
$stmt1 = $dbc->prepare($sql1);
$stmt1->execute();
while ($row1 = $stmt1->fetch(PDO::FETCH_ASSOC)) {
$location++;
echo "Location $location : ".$row1['location'];
?>
<table width="469" border="1">
<tr bgcolor="#00FFFF">
<th width="109" class="rounded" scope="col">Component</th>
<th width="248" class="rounded" scope="col">Sub Component</th>
<th>Nilai</th>
</tr>
<?php
$query = "SELECT * FROM sub_component,component where sub_component.id_component=component.id_component and component.id_location='$data[id_location]' order by component.id_location";
$stmt = $dbc->prepare($query);
$stmt->execute();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$result[$row['component']][] = $row['sub_component'];
}
foreach($result as $id => $invoices) {
echo '<td rowspan='. count($invoices) . '>' . $id . '</td>';
$count = 0;
foreach ($invoices as $invoice) {
if ($count != 0) {
echo '<tr>';
}
echo "<td>$invoice</td>";
echo "</tr>";
$count++;
}
}
?>
</table>
<?php
}
?>
and this the output that i get :
with that code, the output form is appropriate, but the data does not appear appropriate, it's always show the data previous in the next table (red box is the data that is repeated ). how can i fix it ?
First of all sorry for my poor english.
In your query, instead of doing order by id_location, do order by component name . This way you can add dynamic rowspan easily. I have not chnged your connection program, but I have changed your second part. Please check. I know there is 3 to 4 loops. But if any body found better algo please tell me.
$sql1 = "SELECT * FROM Lokasi ORDER BY id_location";
$stmt1 = $dbc->prepare($sql1);
$stmt1->execute();
while ($row1 = $stmt1->fetch(PDO::FETCH_ASSOC)) {
$location++;
echo "Location $location : ".$row1['location'];
?>
<?php
$query = "SELECT *
FROM sub_component,
component
WHERE sub_component.id_component=component.id_component
AND component.id_location='$data[id_location]'
ORDER BY component.component_name";
$stmt = $dbc->prepare($query);
$stmt->execute();
# Declare two emty array
$component = array(); # Will store the components
$sub_component = array(); # Will store the sub components
$loop = 0;
# Now if any data is fetched from previsous query, then fill
# the above declared arrays.
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$component[$loop] = $row['component'];
$sub_component[$loop] = $row['sub_component'];
$loop = $loop + 1;
}
# If no data fetched then I m telling
# No data fetched.
if (!sizeof($component)) {
echo 'Empty Data';
} else {
print "<table width='469px' border='1'>
<tr bgcolor='#00FFFF'>
<th width='109' class='rounded' scope='col'>Component</th>
<th width='109' class='rounded' scope='col'>Sub Component</th>
</tr>";
# Now our main logic starts to print dynamic rowspan
# Go for a loop.
# Here the imporant is to use for loop
$tmp_arr = array();
$main_assoc_arr = array();
for ($i = 0; $i < sizeof($sub_component); $i++) {
array_push($tmp_arr, $sub_component[$i]);
# If we have reached the last element
# and in $main_assoc_arr the comonent is not exist
# Then we will store them as following.
if ( $i = (sizeof($sub_component)-1)
&& !array_key_exists($component[$i], $main_assoc_arr)) {
$main_assoc_arr[ $component[$i] ] = array();
$main_assoc_arr[ $component[$i] ] = $tmp_arr;
# Restore the array.
$tmp_arr = array();
# Also get out of the loop
break;
}
# If the present component is not equal to the
# Next component then
if ($component[$i] != $component[$i+1]) {
$main_assoc_arr[ $component[$i] ] = array();
$main_assoc_arr[ $component[$i] ] = $tmp_arr;
# Restore the array.
$tmp_arr = array();
}
}
# Now we are going to print the table with rowspan.
foreach ($main_assoc_arr as $comp=>$sub_comp) {
$printed = 0;
$rowspan = sizeof($sub_comp);
foreach ($sub_comp as $elm) {
print "<tr>";
# Manke sure that the column will not print
# in each loop as it conatins dynamic array.
if (!$printed) {
print "<td rowspan='$rowspan'>$comp</td>";
}
print "<td>$elm</td>"
print "</tr>";
}
}
print "</table>";
}
?>
这篇关于如何使用动态rowspan显示数据库中的数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!