PHP MYSQL -根据是否与行号匹配来填充HTML表编号的行

Question

所以，基本上我正在尝试制作一个数据中心驾驶室图。我们有一个excel电子表格，但这不容易更新，也不容易搜索。我在MySQL数据库中有三个表；数据库是:机架，表是:机柜、设备和数据中心--每个表中的每一行代表它是什么。机柜表有一列指定它在U中的高度(某些数据中心中的一些机柜比其他数据中心的机柜更高)。因此，当php绘制一个橱柜时，它以相应的高度绘制橱柜。到目前为止，一切都与分组数据中心和它们的容纳柜以及它们各自的高度有关。我的问题是，我似乎不能在每个机柜中填充多个设备。这是整个页面，MySQL DB安装程序位于该页面的下面：

<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', '', '', '');

Answer 1

为了直接解决这个问题(稍后我将介绍更多内容)，您需要遍历设备的完整列表，然后在遍历完所有设备之后，尝试显示它们。因此，您只能显示被触摸的最后一个设备。

您当前的代码截断为：

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;
    }
    ...
}

如果我理解您的意图，您需要将每个设备存储到一个"devices“数组中，并在for循环的每次迭代中遍历该数组。尝试如下所示：

$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对于单个设备/服务器是唯一的：

$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是唯一的。

旁注(附加的、非特定答案的评论)

1. 在代码开始时，您可以执行$sql_devices="SELECT * FROM设备";和$result_devices=mysql_query($sql_devices);，但决不使用此对象。可以也应该删除它，因为它是一个额外的(相当繁重的)查询。
2. 在第二个while-loop中，有一行$num_devices=mysql_numrows($result_devices);。没有PHP函数mysql_numrows()，我相信这是mysql_num_rows()函数的拼写错误(或者你有一个自定义编写的函数来做同样的事情。此外，$num_devices变量从不使用，因此实际上可以使用旧的和即将弃用的mysql_函数删除此行(请查看这些函数的任何文档页面顶部的警告消息；这里是mysql_connect()作为参考)。我和社区建议你升级到methods.
3. Your代码是开放给未清理的SQL错误的mysqli_或PDO，不只限于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";