php - 我的 PHP MYSQL 查询未以 HTML 格式返回所有结果 - 需要显示演出表内容的完整历史记录，仅显示部分内容
问题描述
问题
我有一个历史演出选项卡，我将其添加到连接到演出数据库的页面中，并在其中有一个 MySQL 查询，旨在获取数据库上早于 2 小时前开始的所有演出。
这是为了让正在进行的演出在开始时间后的 2 小时内显示在即将到来的演出选项卡中，以便人们在去查看即将发生的事情时仍然可以看到正在进行的演出的条目。
历史演出选项卡应显示存储在网站上的演出的完整历史，可以追溯到第一次演出。
我已经通过 phpmyadmin 直接在数据库上测试了查询，它运行正常。向我展示了可以追溯到表格开头的所有历史演出条目。
但是，当我在我正在工作的 php 页面上运行查询时，它并没有显示 gig 表上的所有数据库条目。它似乎完全忽略了最近一天的演出，即使它们都出现在 phpmyadmin sql 查询测试中。
有人可以告诉我在尝试通过下面的 PHP 代码输出演出条目的整个历史时我犯了什么愚蠢的错误吗？
注意：在整个查询中使用左连接，因为我想显示所有演出，即使某些外键连接在其他表上为 NULL。
编码
<div id="HistoricalGigs" class="tabcontent">
<br><br>
<?php
$stmt = $pdo->prepare("SELECT gig.id AS gigid, gig.artist_id, gig.gig_date, gig.gigname, artist.artistname, artist.profileimgURL, gig.venue_id, gig.stage_id, venue.venuename, festival.festivalid AS festivalid, festival.festivalname, stage.stageid AS stageid, stage.stagename, events.id AS eventid, events.eventname
from gig
left join venue
on gig.venue_id = venue.id
left join artist
on gig.artist_id = artist.id
left join events
on gig.event_id = events.id
left join festival
on gig.festival_id = festival.festivalid
left join stage
on gig.stage_id = stage.stageid
WHERE gig.gig_date <= NOW() - INTERVAL 2 hour
order by gig.gig_date desc");
$stmt->execute();
$result = $stmt-> fetchAll();
if ($stmt->rowCount() > 0) {
$currentboxid = 0;
foreach ($result as $row) {
$gigdate = $row["gig_date"];
$ukgigdate = date("l, d F Y - g:i A", strtotime($gigdate));
$eventid = $row["eventid"];
$currentboxid = $currentboxid + 1
?>
<html>
<div id="gigboxforanimate<?php echo $currentboxid;?>" class="hidden">
<?php if ($eventid > 0) { ?>
<p class="gigbox">
<?php } else if ($row["festivalid"] > 0){?>
<p class="gigboxfestival">
<?php } else echo '<p class="gigboxblue">';?>
<a href="/gigdetail.php?gigid=<?php echo $row["gigid"];?>" class="gigdatesstyled"><?php echo $ukgigdate;?></a> <br><br>
<strong><a href="/gigdetail.php?gigid=<?php echo $row["gigid"];?>" class="gignamesstyled"><?php echo $row["gigname"];?></a></strong><br><br>
<?php
if (empty($row["profileimgURL"])) {
$profilepic = '/images/artistdetailprofileimages/placeholder.svg';
$artistname = $row["artistname"];
echo '<a href="/gigdetail.php?gigid='.$row["gigid"].'"> <img srcset="'.$profilepic.' 200w,
'.$profilepic.' 500w"
sizes="(max-width: 500px) 200px,
500px"
src="'.$profilepic.'" alt="'.$artistname.'"></a><br><br>';
} else {
$profilepic = $row["profileimgURL"];
$artistname = $row["artistname"];
echo '<a href="/gigdetail.php?gigid='.$row["gigid"].'"> <img srcset="'.$profilepic.' 250w,
'.$profilepic.' 500w"
sizes="(max-width: 500px) 250px,
500px"
src="'.$profilepic.'" alt="'.$artistname.'"></a><br><br>';
}
?>
<a href="/artistdetail.php?artistid=<?php echo $row["artist_id"];?>" class="artiststyled"><?php echo $row["artistname"];?></a><br>
<?php if ($row["venue_id"] > 0){?>
<a href="/venuedetail.php?venueid=<?php echo $row["venue_id"];?>" class="venuestyled"><?php echo $row["venuename"]. "<br>";?></a>
<?php } else if ($row["festivalid"] > 0){?>
<a href="/festivaldetail.php?festivalid=<?php echo $row["festivalid"];?>" class="festivalstyled"><?php echo $row["festivalname"]. "<br>";?></a>
<?php } else echo '';?>
<?php if ($eventid > 0) {
?>
<br><strong>Played as a part of: <a href="/eventdetail.php?eventid=<?php echo $eventid;?>" class="eventnamessmallstyled"><?php echo $row["eventname"];?></a></strong><?php
} else echo '' ?>
<?php if ($row["stageid"] > 0){?>
<br><br><strong>Played on stage: <a href="/stagedetail.php?stageid=<?php echo $row["stageid"];?>" class="festivalnamessmallstyled"><?php echo $row["stagename"];?></a></strong><br>
<?php } else echo '';?>
<html></p><br>
<center>.- - ... ..- ... -. .. .-.-.- -.-. --- --</center><br>
</div>
</html>
<script>
$(function() {
$('#gigboxforanimate<?php echo $currentboxid; ?>').slideDown(1000);
});
</script>
<?php
}
} else {
echo "0 results";
}
?>
</div>
解决方案
解决方案建议
如果您运行 avar_dump()而不是print_r()并将结果包装在 html 中的预格式化标签中，
<pre><?php var_dump($array); ?></pre> 您应该能够更轻松地将所需的 JSON 结果与现有数组内容进行比较。根据需要比较不正确的第一条记录，并本地化数组中的值以查看其格式或显示不正确的位置。
生成的解决方案：
插入 fiddle JavaScript 部分中提供的 JSON 后，仅供参考，请参阅 html 输出和 JSON - 在 ( https://jsfiddle.net/r0nL8h36/3/ )处实际未用于渲染
小提琴显示从 JSON 数据集中成功呈现丢失的记录，随后转换为 PHP 数组。
将 fiddle 中的 JSON 转换为有效的 PHP 数组，因为 fiddle 链接中的 JSON 当前有效。逻辑解释的数组字段值有效性可能存在问题。但是，使用 JSON 运行代码会产生所需的结果。
带有空结果集的 PHP Snippet - 将 JSON 的转换结果粘贴到 php 数组中，然后运行以生成结果 html。
http://sandbox.onlinephpfunctions.com/code/5b44d09e962eab381dc8a931b98ffa689e4c02c1
答案小提琴中结果集的 JSON 片段
{
"0":{
"gigid":"222",
"artist_id":"17",
"album_id":"",
"gig_date":"2021-08-15 19:30:00",
"gigname":"Dolbro Dan's Acoustic Sunday Session (online livestream)",
"artistname":"Dolbro Dan",
"profileimgURL":"https://atsusni.com/images/artistdetailprofileimages/artistid-17.jpg",
"venue_id":"268",
"stage_id":"",
"venuename":"Rabbit Rooms",
"festivalid":"",
"festivalname":"",
"stageid":"",
"artistname":"",
"eventid":"",
"eventname":""
},
"1":{
"gigid":"257",
"artist_id":"135",
"gig_date":"2021-08-15 00:00:00",
"gigname":"Kila",
"artistname":"Kíla",
"profileimgURL":"",
"venue_id":"",
"stage_id":"2",
"venuename":"",
"festivalid":"1",
"festivalname":"Stendhal Festival",
"stageid":"2",
"artistname":"Karma Valley Stage",
"eventid":"",
"eventname":""
},
"2":{
"gigid":"284",
"artist_id":"160",
"gig_date":"2021-08-15 00:00:00",
"gigname":"Drew Makes Noise",
"artistname":"Drew Makes Noise",
"profileimgURL":"",
"venue_id":"",
"stage_id":"6",
"venuename":"",
"festivalid":"1",
"festivalname":"Stendhal Festival",
"stageid":"6",
"artistname":"Henry McCullough Stage",
"eventid":"",
"eventname":""
},
"3":{
"gigid":"278",
"artist_id":"154",
"gig_date":"2021-08-14 23:30:00",
"gigname":"ferals",
"artistname":"ferals",
"profileimgURL":"",
"venue_id":"",
"stage_id":"5",
"venuename":"",
"festivalid":"1",
"festivalname":"Stendhal Festival",
"stageid":"5",
"artistname":"Wooly Woodland Stage",
"eventid":"",
"eventname":""
},
"4":{
"gigid":"273",
"artist_id":"149",
"gig_date":"2021-08-14 23:00:00",
"gigname":"And So I Watch You From Afar",
"artistname":"And So I Watch You From Afar",
"profileimgURL":"",
"venue_id":"",
"stage_id":"1",
"venuename":"",
"festivalid":"1",
"festivalname":"Stendhal Festiva",
"stageid":"1",
"artistname":"Stevie Martin Stage",
"eventid":"",
"eventname":""
},
"5":{
"gigid":"285",
"artist_id":"161",
"gig_date":"2021-08-14 23:00:00",
"gigname":"Queen & Disco",
"artistname":"Queen & Disco",
"profileimgURL":"",
"venue_id":"",
"stage_id":"3",
"venuename":"",
"festivalid":"1",
"festivalname":"Stendhal Festival",
"stageid":"3",
"artistname":"Air Stage",
"eventid":"",
"eventname":""
},
"6":{
"gigid":"277",
"artist_id":"153",
"gig_date":"2021-08-14 22:00:00",
"gigname":"The Bonnevilles",
"artistname":"The Bonnevilles",
"profileimgURL":"",
"venue_id":"",
"stage_id":"5",
"venuename":"",
"festivalid":"1",
"festivalname":"Stendhal Festival",
"stageid":"5",
"artistname":"Wooly Woodland Stage",
"eventid":"",
"eventname":""
},
"7":{
"gigid":"283",
"artist_id":"159",
"gig_date":"2021-08-14 22:00:00",
"gigname":"NewDad",
"artistname":"NewDad",
"profileimgURL":"",
"venue_id":"",
"stage_id":"6",
"venuename":"",
"festivalid":"1",
"festivalname":"Stendhal Festival",
"stageid":"6",
"artistname":"Henry McCullough Stage",
"eventid":"",
"eventname":""
},
"8":{
"gigid":"286",
"artist_id":"162",
"gig_date":"2021-08-14 22:00:00",
"gigname":"flash Harry",
"artistname":"flash Harry",
"profileimgURL":"",
"venue_id":"",
"stage_id":"2",
"venuename":"",
"festivalid":"1",
"festivalname":"Stendhal Festival",
"stageid":"2",
"artistname":"Karma Valley Stage",
"eventid":"",
"eventname":""
},
"9":{
"gigid":"266",
"artist_id":"143",
"gig_date":"2021-08-14 21:00:00",
"gigname":"Paddy Casey",
"artistname":"Paddy Casey",
"profileimgURL":"",
"venue_id":"",
"stage_id":"3",
"venuename":"",
"festivalid":"1",
"festivalname":"Stendhal Festival",
"stageid":"3",
"artistname":"Air Stage",
"eventid":"",
"eventname":""
},
"10":{
"gigid":"272",
"artist_id":"148",
"gig_date":"2021-08-14 21:00:00",
"gigname":"Duke Special",
"artistname":"Duke Special",
"profileimgURL":"",
"venue_id":"",
"stage_id":"1",
"venuename":"",
"festivalid":"1",
"festivalname":"Stendhal Festival",
"stageid":"1",
"artistname":"Stevie Martin Stage",
"eventid":"",
"eventname":""
},
"11":{
"gigid":"276",
"artist_id":"152",
"gig_date":"2021-08-14 20:20:00",
"gigname":"Kyoto Love Hotel",
"artistname":"Kyoto Love Hotel",
"profileimgURL":"",
"venue_id":"",
"stage_id":"5",
"venuename":"",
"festivalid":"1",
"festivalname":"Stendhal Festival",
"stageid":"5",
"artistname":"Wooly Woodland Stage",
"eventid":"",
"eventname":""
},
"12":{
"gigid":"261",
"artist_id":"138",
"gig_date":"2021-08-14 20:00:00",
"gigname":"Ryan McMullan",
"artistname":"Ryan McMullan",
"profileimgURL":"",
"venue_id":"",
"stage_id":"2",
"venuename":"",
"festivalid":"1",
"festivalname":"Stendhal Festival",
"stageid":"2",
"artistname":"Karma Valley Stage",
"eventid":"",
"eventname":""
},
"13":{
"gigid":"265",
"artist_id":"142",
"gig_date":"2021-08-14 19:00:00",
"gigname":"Susan O'Neill",
"artistname":"Susan O’Neill",
"profileimgURL":"",
"venue_id":"",
"stage_id":"3",
"venuename":"",
"festivalid":"1",
"festivalname":"Stendhal Festival",
"stageid":"3",
"artistname":"Air Stage",
"eventid":"",
"eventname":""
},
"14":{
"gigid":"271",
"artist_id":"147",
"gig_date":"2021-08-14 19:00:00",
"gigname":"Soda Blonde",
"artistname":"Soda Blonde",
"profileimgURL":"",
"venue_id":"",
"stage_id":"1",
"venuename":"",
"festivalid":"1",
"festivalname":"Stendhal Festival",
"stageid":"1",
"artistname":"Stevie Martin Stage",
"eventid":"",
"eventname":""
},
"15":{
"gigid":"260",
"artist_id":"137",
"gig_date":"2021-08-14 18:15:00",
"gigname":"The Henry Girls",
"artistname":"The Henry Girls",
"profileimgURL":"",
"venue_id":"",
"stage_id":"2",
"venuename":"",
"festivalid":"1",
"festivalname":"Stendhal Festival",
"stageid":"2",
"artistname":"Karma Valley Stage",
"eventid":"",
"eventname":""
},
"16":{
"gigid":"282",
"artist_id":"158",
"gig_date":"2021-08-14 18:15:00",
"gigname":"Scála Strings",
"artistname":"Scála Strings",
"profileimgURL":"",
"venue_id":"",
"stage_id":"6",
"venuename":"",
"festivalid":"1",
"festivalname":"Stendhal Festival",
"stageid":"6",
"artistname":"Henry McCullough Stage",
"eventid":"",
"eventname":""
},
"17":{
"gigid":"264",
"artist_id":"141",
"gig_date":"2021-08-14 17:15:00",
"gigname":"Cormac Neeson",
"artistname":"Cormac Neeson",
"profileimgURL":"",
"venue_id":"",
"stage_id":"3",
"venuename":"",
"festivalid":"1",
"festivalname":"Stendhal Festival",
"stageid":"3",
"artistname":"Air Stage",
"eventid":"",
"eventname":""
},
"18":{
"gigid":"270",
"artist_id":"146",
"gig_date":"2021-08-14 17:15:00",
"gigname":"Mary Coughlan",
"artistname":"Mary Coughlan",
"profileimgURL":"",
"venue_id":"",
"stage_id":"1",
"venuename":"",
"festivalid":"1",
"festivalname":"Stendhal Festival",
"stageid":"1",
"artistname":"Stevie Martin Stage",
"eventid":"",
"eventname":""
},
"19":{
"gigid":"275",
"artist_id":"151",
"gig_date":"2021-08-14 16:30:00",
"gigname":"The Craic Inn",
"artistname":"The Craic Inn",
"profileimgURL":"",
"venue_id":"",
"stage_id":"5",
"venuename":"",
"festivalid":"1",
"festivalname":"Stendhal Festival",
"stageid":"5",
"artistname":"Wooly Woodland Stage",
"eventid":"",
"eventname":""
},
"20":{
"gigid":"259",
"artist_id":"136",
"gig_date":"2021-08-14 16:25:00",
"gigname":"ROE",
"artistname":"ROE",
"profileimgURL":"",
"venue_id":"",
"stage_id":"2",
"venuename":"",
"festivalid":"1",
"festivalname":"Stendhal Festival",
"stageid":"2",
"artistname":"Karma Valley Stage",
"eventid":"",
"eventname":""
},
"21":{
"gigid":"281",
"artist_id":"157",
"gig_date":"2021-08-14 16:15:00",
"gigname":"Linley Hamilton Band",
"artistname":"Linley Hamilton Band",
"profileimgURL":"",
"venue_id":"",
"stage_id":"6",
"venuename":"",
"festivalid":"1",
"festivalname":"Stendhal Festival",
"stageid":"6",
"artistname":"Henry McCullough Stage",
"eventid":"",
"eventname":""
},
"22":{
"gigid":"267",
"artist_id":"3",
"gig_date":"2021-08-14 15:30:00",
"gigname":"Dani Larkin",
"artistname":"Dani Larkin",
"profileimgURL":"https://atsusni.com/images/artistdetailprofileimages/artistid-3.jpg",
"venue_id":"",
"stage_id":"1",
"venuename":"",
"festivalid":"1",
"festivalname":"Stendhal Festival",
"stageid":"1",
"artistname":"Stevie Martin Stage",
"eventid":"",
"eventname":""
},
"23":{
"gigid":"274",
"artist_id":"150",
"gig_date":"2021-08-14 14:45:00",
"gigname":"Benjamin Amos",
"artistname":"Benjamin Amos",
"profileimgURL":"",
"venue_id":"",
"stage_id":"5",
"venuename":"",
"festivalid":"1",
"festivalname":"Stendhal Festival",
"stageid":"5",
"artistname":"Wooly Woodland Stage",
"eventid":"",
"eventname":""
},
"24":{
"gigid":"280",
"artist_id":"156",
"gig_date":"2021-08-14 14:45:00",
"gigname":"The Ocelots",
"artistname":"The Ocelots",
"profileimgURL":"",
"venue_id":"",
"stage_id":"6",
"venuename":"",
"festivalid":"1",
"festivalname":"Stendhal Festival",
"stageid":"6",
"artistname":"Henry McCullough Stage",
"eventid":"",
"eventname":""
},
"25":{
"gigid":"263",
"artist_id":"140",
"gig_date":"2021-08-14 14:00:00",
"gigname":"Rosborough",
"artistname":"Rosborough",
"profileimgURL":"",
"venue_id":"",
"stage_id":"3",
"venuename":"",
"festivalid":"1",
"festivalname":"Stendhal Festival",
"stageid":"3",
"artistname":"Air Stage",
"eventid":"",
"eventname":""
}
}
PHP 代码 - 没有数据
<div id="HistoricalGigs" class="tabcontent">
<br><br>
<?php
if (true) {
$currentboxid = 0;
$rows = [];
foreach ($rows as $row) {
$gigdate = $row["gig_date"];
$ukgigdate = date("l, d F Y - g:i A", strtotime($gigdate));
$eventid = $row["eventid"];
$currentboxid = $currentboxid + 1
?>
<div id="gigboxforanimate<?php echo $currentboxid;?>" class="hidden">
<?php if ($eventid > 0) { ?>
<p class="gigbox">
<?php } else if ($row["festivalid"] > 0){?>
<p class="gigboxfestival">
<?php } else echo '<p class="gigboxblue">';?>
<a href="/gigdetail.php?gigid=<?php echo $row["gigid"];?>" class="gigdatesstyled"><?php echo $ukgigdate;?></a> <br><br>
<strong><a href="/gigdetail.php?gigid=<?php echo $row["gigid"];?>" class="gignamesstyled"><?php echo $row["gigname"];?></a></strong><br><br>
<?php
if (empty($row["profileimgURL"])) {
$profilepic = '/images/artistdetailprofileimages/placeholder.svg';
$artistname = $row["artistname"];
echo '<a href="/gigdetail.php?gigid='.$row["gigid"].'"> <img srcset="'.$profilepic.' 200w,
'.$profilepic.' 500w"
sizes="(max-width: 500px) 200px,
500px"
src="'.$profilepic.'" alt="'.$artistname.'"></a><br><br>';
} else {
$profilepic = $row["profileimgURL"];
$artistname = $row["artistname"];
echo '<a href="/gigdetail.php?gigid='.$row["gigid"].'"> <img srcset="'.$profilepic.' 250w,
'.$profilepic.' 500w"
sizes="(max-width: 500px) 250px,
500px"
src="'.$profilepic.'" alt="'.$artistname.'"></a><br><br>';
}
?>
<a href="/artistdetail.php?artistid=<?php echo $row["artist_id"];?>" class="artiststyled"><?php echo $row["artistname"]??'';?></a><br>
<?php if ($row["venue_id"] > 0){?>
<a href="/venuedetail.php?venueid=<?php echo $row["venue_id"];?>" class="venuestyled"><?php echo $row["venuename"]??''. "<br>";?></a>
<?php } else if ($row["festivalid"] > 0){?>
<a href="/festivaldetail.php?festivalid=<?php echo $row["festivalid"];?>" class="festivalstyled"><?php echo $row["festivalname"]. "<br>";?></a>
<?php } else echo '';?>
<?php if ($eventid > 0) {
?>
<br><strong>Played as a part of: <a href="/eventdetail.php?eventid=<?php echo $eventid;?>" class="eventnamessmallstyled"><?php echo $row["eventname"]??'';?></a></strong><?php
} else echo '' ?>
<?php if ($row["stageid"] > 0){?>
<br><br><strong>Played on stage: <a href="/stagedetail.php?stageid=<?php echo $row["stageid"];?>" class="festivalnamessmallstyled"><?php echo $row["stagename"]??'';?></a></strong><br>
<?php } else echo '';?>
</p><br>
<center>.- - ... ..- ... -. .. .-.-.- -.-. --- --</center><br>
</div>
<script>
$(function() {
$('#gigboxforanimate<?php echo $currentboxid; ?>').slideDown(1000);
});
</script>
<?php
}
} else {
echo "0 results";
}
?>
</div>