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();
?>
Here is the MySQL setup:
-- 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', '', '', '');