数据库结构:站点数据表:points;{pid:自动增长,name:火车站,path:huochezhan(作为生成HTML的文件名)}
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| pid | int(8) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | NULL | |
| path | varchar(100) | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+线路数据表:sx;{sid:自动增长,xid(1为上行,0为下行),lid:所属公交线路,pid:站点ID,sortnum:排序}
+---------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+----------------+
| sid | int(8) | NO | PRI | NULL | auto_increment |
| xid | tinyint(1) | NO | | 1 | |
| lid | int(8) | NO | | 0 | |
| pid | int(8) | NO | | 0 | |
| sortnum | int(8) | NO | | 0 | |
+---------+------------+------+-----+---------+----------------+公交路线数据表:lines;{lid:自动增长,name:11路,:11lu(作为生成HTML的文件名)}
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| lid | int(8) | NO | PRI | NULL | auto_increment |
| | varchar(100) | NO | | NULL | |
| name | varchar(200) | NO | | NULL | |
+----------+--------------+------+-----+---------+----------------+现在是要为每个站点生成HTML,因为某个站点例如:火车站 会有很多公交路线经过 要把这些公交路线一一列出来例如:肺科医院 这个公交站点 的效果如下我实现的PHP代码如下:
但是效率很低
希望懂数据库的帮我优化下
或者给个最优过程$query=$db->query("SELECT path,name as stopname,pid FROM points ORDER BY pid ASC");
while($record = $db->fetch_array($query)) {
$lquery=$db->query("SELECT l.lid,l.name,l. FROM sx as s left join lines as l on s.lid=l.lid where s.pid=".$record['pid']." group by s.lid ORDER BY s.sortnum ASC");
$lineinfo = '';
while($rs = $db->fetch_array($lquery)) {
//下行
$xlArr=$db->query("SELECT p.name,p.path FROM sx AS s LEFT JOIN points AS p ON s.pid=p.pid WHERE s.lid=".$rs['lid']." and s.xid=0 ORDER BY s.sortnum ASC");
$xlstr2 = '';
while($r = $db->fetch_array($xlArr)) {
if($r['name'] == $record['stopname']) {
$xlstr2 .= '<b>'.$r['name'].'</b> - ';
}else{
$xlstr2 .= '<a href="../stops/'.$r['path'].'.html">'.$r['name'].'</a> - ';
}
}
$xlstr2 = $xlstr2 ? substr($xlstr2,0,-3) : '';
$rs['stops2'] = $xlstr2;
//上行
$xlArr=$db->query("SELECT p.name,p.path FROM sx AS s LEFT JOIN points AS p ON s.pid=p.pid WHERE s.lid=".$rs['lid']." and s.xid=1 ORDER BY s.sortnum ASC");
$xlstr = '';
while($r = $db->fetch_array($xlArr)) {
if($r['name'] == $record['stopname']) {
$xlstr .= '<b>'.$r['name'].'</b> - ';
}else{
$xlstr .= '<a href="../stops/'.$r['path'].'.html">'.$r['name'].'</a> - ';
}
}
$xlstr = $xlstr ? substr($xlstr,0,-3) : '';
$lineinfo .= '<div class="lineinfo"><h3><a href="../lines/'.$rs[''].'.html">'.$rs['name'].'</a><span><a href="../lines/'.$rs[''].'.html">查看详情</a></span></h3><u>[上行]</u> '.$xlstr.' <hr size="1" color="#FFCC66" /><u>[下行]</u> '.$xlstr2.' </div>';
}
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| pid | int(8) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | NULL | |
| path | varchar(100) | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+线路数据表:sx;{sid:自动增长,xid(1为上行,0为下行),lid:所属公交线路,pid:站点ID,sortnum:排序}
+---------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+----------------+
| sid | int(8) | NO | PRI | NULL | auto_increment |
| xid | tinyint(1) | NO | | 1 | |
| lid | int(8) | NO | | 0 | |
| pid | int(8) | NO | | 0 | |
| sortnum | int(8) | NO | | 0 | |
+---------+------------+------+-----+---------+----------------+公交路线数据表:lines;{lid:自动增长,name:11路,:11lu(作为生成HTML的文件名)}
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| lid | int(8) | NO | PRI | NULL | auto_increment |
| | varchar(100) | NO | | NULL | |
| name | varchar(200) | NO | | NULL | |
+----------+--------------+------+-----+---------+----------------+现在是要为每个站点生成HTML,因为某个站点例如:火车站 会有很多公交路线经过 要把这些公交路线一一列出来例如:肺科医院 这个公交站点 的效果如下我实现的PHP代码如下:
但是效率很低
希望懂数据库的帮我优化下
或者给个最优过程$query=$db->query("SELECT path,name as stopname,pid FROM points ORDER BY pid ASC");
while($record = $db->fetch_array($query)) {
$lquery=$db->query("SELECT l.lid,l.name,l. FROM sx as s left join lines as l on s.lid=l.lid where s.pid=".$record['pid']." group by s.lid ORDER BY s.sortnum ASC");
$lineinfo = '';
while($rs = $db->fetch_array($lquery)) {
//下行
$xlArr=$db->query("SELECT p.name,p.path FROM sx AS s LEFT JOIN points AS p ON s.pid=p.pid WHERE s.lid=".$rs['lid']." and s.xid=0 ORDER BY s.sortnum ASC");
$xlstr2 = '';
while($r = $db->fetch_array($xlArr)) {
if($r['name'] == $record['stopname']) {
$xlstr2 .= '<b>'.$r['name'].'</b> - ';
}else{
$xlstr2 .= '<a href="../stops/'.$r['path'].'.html">'.$r['name'].'</a> - ';
}
}
$xlstr2 = $xlstr2 ? substr($xlstr2,0,-3) : '';
$rs['stops2'] = $xlstr2;
//上行
$xlArr=$db->query("SELECT p.name,p.path FROM sx AS s LEFT JOIN points AS p ON s.pid=p.pid WHERE s.lid=".$rs['lid']." and s.xid=1 ORDER BY s.sortnum ASC");
$xlstr = '';
while($r = $db->fetch_array($xlArr)) {
if($r['name'] == $record['stopname']) {
$xlstr .= '<b>'.$r['name'].'</b> - ';
}else{
$xlstr .= '<a href="../stops/'.$r['path'].'.html">'.$r['name'].'</a> - ';
}
}
$xlstr = $xlstr ? substr($xlstr,0,-3) : '';
$lineinfo .= '<div class="lineinfo"><h3><a href="../lines/'.$rs[''].'.html">'.$rs['name'].'</a><span><a href="../lines/'.$rs[''].'.html">查看详情</a></span></h3><u>[上行]</u> '.$xlstr.' <hr size="1" color="#FFCC66" /><u>[下行]</u> '.$xlstr2.' </div>';
}
检索后不直接输出,而是保存在数组中
$xlArr=$db->query("SELECT p.name,p.path FROM sx AS s LEFT JOIN points AS p ON s.pid=p.pid WHERE s.lid=".$rs['lid']." and s.xid=0 ORDER BY s.sortnum ASC");
$xlstr2 = array());
while($r = $db->fetch_array($xlArr)) {
if($r['name'] == $record['stopname']) {
$xlstr2[] = '<b>'.$r['name'].'</b>';
}else{
$xlstr2[] = '<a href="../stops/'.$r['path'].'.html">'.$r['name'].'</a>';
}
}
/** 输出上行 **/
echo join(' - ', $xlstr2);
/** 输出下行 **/
echo join(' - ', array_reverse($xlstr2));如果因为单行道的原因,线路不能经过同一路段。这时你可以给线路附加一信息
只在该信息置为时才分别作上下行检索
不知道还能更优化吗?
图片在这里