Php Mysql - Populate Html Table Numbered Rows Based On Whether They Match Row Number

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; t

Solution 1:

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

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 ($devicesas$device) {
        if ($u == $device['ustart']) {
            $output = $device['server'];
            break;
        }
    }
    echo$output;
    ...
}

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

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)

1. 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.

2. 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.

3. 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.

4. 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";