PHP MYSQL-根据行号是否匹配来填充HTML表编号的行 [英] PHP MYSQL - Populate HTML table numbered rows based on whether they match row number
问题描述
因此，基本上，我正在尝试制作数据中心电缆图.我们有一个excel电子表格，但这不容易更新，也不容易搜索.我在MySQL数据库中有三个表； db是:机架，表是:机柜，设备和&数据中心-每个表中的每一行都代表它的含义.机柜表上有一列，指示其在U中的高度(某些数据中心中的某些机柜比其他机柜高).因此，当php绘制机柜时，它将以相应的高度绘制机柜.到目前为止，一切工作都与对数据中心及其包含的机柜以及它们各自的高度进行分组有关.我的问题是，每个机柜似乎只能装一个以上的设备.这是整个页面，下面是MySQL DB的设置:
So, basically I'm trying to make a datacenter cab diagram. We have an excel spreadsheet, but this is not easy to update, nor easily searchable. I have three tables in a MySQL DB; the db is: racks, the tables are: cabinets, devices, & datacenters - each row in each of the tables represents what it is. The cabinet table has a column that designates how tall it is in U (some cabinets in some datacenters are taller than others). So, when the php draws a cabinet, it draws the cabinet in the respective height. So far, everything works in regards to grouping the datacenters and their containing cabinets and their respective heights. My issue is that I can't seem to populate more than one device per cabinet. This is the entire page, and the MySQL DB setup is below that:
<SCRIPT LANGUAGE="JavaScript" type="text/javascript">
<!--
function clickHandler(e)
{
var targetId, srcElement, targetElement;
if (window.event) e = window.event;
srcElement = e.srcElement? e.srcElement: e.target;
if (srcElement.className == "Outline")
{
targetId = srcElement.id + "d";
targetElement = document.getElementById(targetId);
if (targetElement.style.display == "none")
{
targetElement.style.display = "";
srcElement.src = "images/minus.gif";
}
else
{
targetElement.style.display = "none";
srcElement.src = "images/plus.gif";
}
}
}
document.onclick = clickHandler;
-->
</SCRIPT>
<noscript>You need Javascript enabled for this page to work correctly</noscript>
<?
function sql_conn()
{
$username="root";
$password="root";
$database="racks";
$server="localhost";
@mysql_connect($server,$username,$password) or die("<h2 align=\"center\" class=\"red\">[<img src=\"images/critical.gif\" border=\"0\">] Unable to connect to $server [<img src=\"images/critical.gif\" border=\"0\">]</h2>");
@mysql_select_db($database) or die("<h2 align=\"center\" class=\"red\">[<img src=\"images/critical.gif\" border=\"0\">] Unable to select $database as a database [<img src=\"images/critical.gif\" border=\"0\">]</h2>");
}
sql_conn();
$sql_datacenters="SELECT * FROM `datacenters`";
$sql_devices="SELECT * FROM `devices`";
$result_datacenters=mysql_query($sql_datacenters);
$result_devices=mysql_query($sql_devices);
$j=0;
echo "<table border='1' style='float:left;'>";
while ($datacenters_sqlrow=mysql_fetch_array($result_datacenters))
{
echo "<tr><td>";
echo "<h2 class='black' align='left'>";
echo "<IMG SRC='images/plus.gif' ID='Out" . $j . "' CLASS='Outline' STYLE='cursor:hand;cursor:pointer'>"; // fancy icon for expanding-collapsing section
echo " " . $datacenters_sqlrow['rack'] . ": " . $datacenters_sqlrow['cagenum'] . "</h2>"; // datacenter name and cage number
echo "<div id=\"Out" . $j . "d\" style=\"display:none\">"; // opening of div box for section that is to be expanded-collapsed
echo "<h3>" . $datacenters_sqlrow['notes'] . "</h3>"; // datacenter notes
$sql_cabinets="SELECT * FROM `cabinets` WHERE `datacenter` = '$datacenters_sqlrow[0]' ORDER BY `cabinetnumber` ASC";
$result_cabinets=mysql_query($sql_cabinets);
while ($cabinets_sqlrow=mysql_fetch_array($result_cabinets))
{
$sql_devices="SELECT * FROM `devices` WHERE `datacenter` = '$datacenters_sqlrow[0]' AND `cabinet` = '$cabinets_sqlrow[1]' ORDER BY `ustartlocation` ASC";
$result_devices=mysql_query($sql_devices);
$num_devices=mysql_numrows($result_devices);
echo "<table border='1' style='float:left;'>"; // opening of table for all cabinets in datacenter
echo "<tr><td colspan='2' align='middle'>" . $cabinets_sqlrow[1] . "</td></tr>"; // cabinet number, spans U column and device name column
while($row = mysql_fetch_array($result_devices))
{
$server = $row['devicename'];
$ustart = $row['ustartlocation'];
}
for ($i = 0; $i < $cabinets_sqlrow[2]; $i++) // iterates through number of U in cabinet
{
$u = $cabinets_sqlrow[2] - $i; // subtracts current $i value from number of U in cabinet since cabinets start their numbers from the bottom up
echo "<tr>";
echo "<td width='15px' align='right'>$u</td>"; // U number
echo "<td width='150px' align='middle'>";
if ($u == $ustart) // determines if there is a device starting at this U
{echo $server;} // device name
else
{echo "empty";} // empty space in cabinet
echo "</td>";
echo "</tr>";
}
$server="";
$ustart="";
echo "</table>"; // closes table opened in row 65
}
echo "</td></tr>";
echo "</div>"; // close for div box that needs expanding-collapsing by fancy java
$j++; // iteration for the fancy java expand-collapse
}
echo "</table>";
mysql_close();
?>
这是MySQL设置:
-- phpMyAdmin SQL Dump
-- version 3.5.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Nov 02, 2012 at 02:13 AM
-- Server version: 5.5.25
-- PHP Version: 5.4.4
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
--
-- Database: `racks`
--
-- --------------------------------------------------------
--
-- Table structure for table `cabinets`
--
CREATE TABLE `cabinets` (
`id` tinyint(3) NOT NULL AUTO_INCREMENT,
`cabinetnumber` varchar(25) NOT NULL,
`numberofu` varchar(3) NOT NULL,
`datacenter` tinyint(3) NOT NULL,
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
--
-- Dumping data for table `cabinets`
--
INSERT INTO `cabinets` (`id`, `cabinetnumber`, `numberofu`, `datacenter`) VALUES
(1, '0101', '45', 2),
(2, '0102', '45', 2),
(3, '0101', '50', 1),
(4, '0102', '50', 1),
(5, '0103', '50', 1);
-- --------------------------------------------------------
--
-- Table structure for table `datacenters`
--
CREATE TABLE `datacenters` (
`id` tinyint(3) NOT NULL AUTO_INCREMENT,
`rack` varchar(20) NOT NULL,
`cagenum` varchar(255) NOT NULL,
`notes` longtext NOT NULL,
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
--
-- Dumping data for table `datacenters`
--
INSERT INTO `datacenters` (`id`, `rack`, `cagenum`, `notes`) VALUES
(1, 'CAGE1', '', ''),
(2, 'CAGE2', '', ''),
(3, 'CAGE3', '', ''),
(4, 'CAGE4', '', ''),
(5, 'CAGE5', '', ''),
(6, 'CAGE6', '', ''),
(7, 'CAGE7', '', '');
-- --------------------------------------------------------
--
-- Table structure for table `devices`
--
CREATE TABLE `devices` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`devicename` varchar(255) NOT NULL,
`datacenter` varchar(255) NOT NULL,
`cabinet` varchar(255) NOT NULL,
`frontorrear` tinyint(3) NOT NULL,
`ustartlocation` varchar(255) NOT NULL,
`usize` varchar(255) NOT NULL,
`spare1` varchar(255) NOT NULL,
`spare2` varchar(255) NOT NULL,
`spare3` varchar(255) NOT NULL,
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
--
-- Dumping data for table `devices`
--
INSERT INTO `devices` (`id`, `devicename`, `datacenter`, `cabinet`, `frontorrear`, `ustartlocation`, `usize`, `spare1`, `spare2`, `spare3`) VALUES
(1, 'SERVER1', '1', '0101', 1, '33', '1', '', '', ''),
(2, 'SERVER2', '1', '0102', 1, '36', '1', '', '', ''),
(3, 'SERVER3', '1', '0101', 1, '40', '2', '', '', '');
推荐答案
要直接解决该问题(我将介绍更多内容)，请遍历整个设备列表，然后 -遍历所有内容后-尝试显示它们.因此，您只显示了最终触摸过的设备.
To directly address the problem (I'll get to more in a little bit), you are iterating through the full list of devices and then - after you're done looping through them all - you attempt to display them. Because of this, you're only displaying the final device that was touched.
您当前被截断的代码为:
Your current code, truncated, is:
while($row = mysql_fetch_array($result_devices)) {
$server = $row['devicename'];
$ustart = $row['ustartlocation'];
}
for ($i = 0; $i < $cabinets_sqlrow[2]; $i++) {
$u = $cabinets_sqlrow[2] - $i;
...
if ($u == $ustart) {
echo $server;
}
...
}
如果我了解您要执行的操作，则需要将每个设备存储到设备"数组中，并在for循环的每次迭代期间遍历该设备.尝试类似的东西:
If I understand what you're trying to do, you'll need to store each device into a "devices" array and loop through it during each iteration of your for loop. Try something like:
$devices = array();
while($row = mysql_fetch_array($result_devices)) {
$devices[] = array(
'server' => $row['devicename'],
'ustart' => $row['ustartlocation']
);
}
for ($i = 0; $i < $cabinets_sqlrow[2]; $i++) {
...
$output = 'empty';
foreach ($devices as $device) {
if ($u == $device['ustart']) {
$output = $device['server'];
break;
}
}
echo $output;
...
}
可以使用ustartlocation作为数组的索引来完成同一任务的更优雅的方式，但是这将要求ustartlocation对于单个设备/服务器是唯一的:
A more elegant way to accomplish this same task can be done using the ustartlocation as the index of the array, but it will require that the ustartlocation is unique to an individual device/server:
$devices = array();
while($row = mysql_fetch_array($result_devices)) {
$devices[$row['ustartlocation']] = $row['devicename'];
}
for ($i = 0; $i < $cabinets_sqlrow[2]; $i++) {
...
echo (isset($devices[$u]) ? $devices[$u] : 'empty');
...
}
此方法将不再需要每次都遍历设备列表，但同样-它要求ustartlocation是唯一的.
This method will drop the need to loop through the list of devices each time, but again - it requires that the ustartlocation is unique.
旁注(其他非针对答案的评论)
Side Notes (additional, non-answer-specific critiques)
-
在代码的开头，您执行
$sql_devices="SELECT * FROMdevices";和$result_devices=mysql_query($sql_devices);，但从不使用此对象.可以并且应该将其删除，因为它是一个额外的(相当繁重的)查询.
At the beginning of your code you execute
$sql_devices="SELECT * FROMdevices";and$result_devices=mysql_query($sql_devices);, but never use this object. It can and should be removed as it's one extra (fairly heavy) query.
在第二个while循环中，您具有行$num_devices=mysql_numrows($result_devices);.没有PHP函数mysql_numrows()，我相信这是 mysql_num_rows() 函数的错字(或者您有一个自定义编写的函数来执行相同的操作.此外，从不使用$num_devices变量，因此实际上可以完全删除此行.
In the second while-loop you have the line $num_devices=mysql_numrows($result_devices);. There is no PHP-function mysql_numrows(), I believe this is a typo for the mysql_num_rows() function (that, or you have a custom-written function to do the same thing. Also, the $num_devices variable is never used so this line could actually be removed entirely.
您正在使用旧的且已被弃用的mysql_功能(请查看这些功能的任何文档页面顶部的警告消息；这是 mysqli_ 或 PDO 方法.
You're using the old and becoming-deprecated mysql_ functions (check out the warning-message on the top of any of the doc-pages for these functions; here's mysql_connect() for reference). I, as well as the community, recommend you to upgrade to the mysqli_ or PDO methods.
您的代码易于处理未经消毒的SQL错误，不特别限于SQL注入，因为它似乎并不表示您直接从用户输入中获取输入，但也没有排除该因素.例如，如果cabinet或datacenter值包含单引号怎么办?由于您使用的是mysql_方法，因此建议您先在 mysql_real_escape_string() 中将它们包装起来，然后再在数据库调用:$sql_cabinets="SELECT * FROM cabinets WHERE datacenter = '" . mysql_real_escape_string($datacenters_sqlrow[0]) . "' ORDER BY cabinetnumber";
Your code is open to unsanitized-SQL errors, not specifically limited to SQL-injection as it doesn't appear you're taking input directly from user-input, but also not ruling this factor out. For instance, what would happen if a cabinet or datacenter value contained a single-quote? Since you're using mysql_ methods, I suggest you wrap each with mysql_real_escape_string() prior to using them in the database calls: $sql_cabinets="SELECT * FROM cabinets WHERE datacenter = '" . mysql_real_escape_string($datacenters_sqlrow[0]) . "' ORDER BY cabinetnumber";
这篇关于PHP MYSQL-根据行号是否匹配来填充HTML表编号的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
