mysqli循环中的php循环

Question

I have one mysql table which contains 2 columns and looks like this...

-------------------------------------------
| sku                 | superseded_sku    |
+---------------------+-------------------+
| part1               | part2             |
| part2               | part3             |
| part3               | part4             |
| part5               | part6             |
| part6               | part7             |

the table basically shows where products sold by a company have been replaced by something newer. My task is to reshape the table to resemble the following...

-------------------------------------------
| sku                 | superseded_sku    |
+---------------------+-------------------+
| part1               | part4             |
| part2               | part4             |
| part3               | part4             |
| part5               | part7             |
| part6               | part7             |

Thus eradicating the need to shuffle through the list to find the current replacement.

I have written the following which has one basic downfall in that if a product had been replaced more times that I have allowed for in my code then it wouldn't look that far. I have tried many times to include a while loop, a do {} while loop and a foreach loop but so far I've failed massively.

Here's my code as it stands

<?php    
    // Error reporting on

    ini_set("display_errors", 1);
    ini_set("error_reporting", E_ALL);

    // variables

    // Page variable
    $P = $_REQUEST["p"];

    // variables for latest BritPart list

    $dbhost = 'localhost';
    $dbuser = 'userame';
    $dbpass = 'password';
    $dbselect = 'superseded';

    $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbselect);
    if (mysqli_connect_error()) {
        die('Connect Error (' . mysqli_connect_errno() . ') '
        . mysqli_connect_error());
        }

    // Start throwing data about  
    $compare = "SELECT * FROM mar15 ORDER BY sku";

    echo "<h3 style=\"text-align: center;\">Supersedes Tables</h3>
    <div class=\"output\" style=\"width: 940px;\">
    <table cellspacing=\"0\" class=\"db-table\" style=\"width: 940px; border-radius: 5px 5px 0 0\"><thead><tr>
    <th width=\"398\">sku</th>
    <th width=\"270\">superseded_sku</th>
    <th width=\"270\">supersede_length</th>
    </tr></thead>
    <tbody>";

    if ($result = mysqli_query($mysqli,"$compare")) {
        while($row = mysqli_fetch_array($result)) {

            $sku = $row['sku'];
            $ss1 = $row['superseded_sku'];
            $ssl = "1";

    // ---------------------------------------------------------------
    // I want to loop through this section until it doesn't find a match
    // ---------------------------------------------------------------

            $query1 = "SELECT superseded_sku FROM mar15 WHERE sku = \"$ss1\"";
                if ($result1 = mysqli_query($mysqli,"$query1")) {
                    while($row1 = mysqli_fetch_array($result1)) {
                        $ss2 = $row1['superseded_sku'];
                        if($ss2 != $ss1) {
                            $ssl++;
                            $ss1 = $ss2;
                        }
                    }
                }

    // ---------------------------------------------------------------
    // Instead I've done this as I don't know how to do what I want
    // ---------------------------------------------------------------

            $query1 = "SELECT superseded_sku FROM mar15 WHERE sku = \"$ss1\"";
                if ($result1 = mysqli_query($mysqli,"$query1")) {
                    while($row1 = mysqli_fetch_array($result1)) {
                        $ss2 = $row1['superseded_sku'];
                        if($ss2 != $ss1) {
                            $ssl++;
                            $ss1 = $ss2;
                        }
                    }
                }

            $query1 = "SELECT superseded_sku FROM mar15 WHERE sku = \"$ss1\"";
                if ($result1 = mysqli_query($mysqli,"$query1")) {
                    while($row1 = mysqli_fetch_array($result1)) {
                        $ss2 = $row1['superseded_sku'];
                        if($ss2 != $ss1) {
                            $ssl++;
                            $ss1 = $ss2;
                        }
                    }
                }

            $query1 = "SELECT superseded_sku FROM mar15 WHERE sku = \"$ss1\"";
                if ($result1 = mysqli_query($mysqli,"$query1")) {
                    while($row1 = mysqli_fetch_array($result1)) {
                        $ss2 = $row1['superseded_sku'];
                        if($ss2 != $ss1) {
                            $ssl++;
                            $ss1 = $ss2;
                        }
                    }
                }

            $query1 = "SELECT superseded_sku FROM mar15 WHERE sku = \"$ss1\"";
                if ($result1 = mysqli_query($mysqli,"$query1")) {
                    while($row1 = mysqli_fetch_array($result1)) {
                        $ss2 = $row1['superseded_sku'];
                        if($ss2 != $ss1) {
                            $ssl++;
                            $ss1 = $ss2;
                        }
                    }
                }

            $query1 = "SELECT superseded_sku FROM mar15 WHERE sku = \"$ss1\"";
                if ($result1 = mysqli_query($mysqli,"$query1")) {
                    while($row1 = mysqli_fetch_array($result1)) {
                        $ss2 = $row1['superseded_sku'];
                        if($ss2 != $ss1) {
                            $ssl++;
                            $ss1 = $ss2;
                        }
                    }
                }

            $query1 = "SELECT superseded_sku FROM mar15 WHERE sku = \"$ss1\"";
                if ($result1 = mysqli_query($mysqli,"$query1")) {
                    while($row1 = mysqli_fetch_array($result1)) {
                        $ss2 = $row1['superseded_sku'];
                        if($ss2 != $ss1) {
                            $ssl++;
                            $ss1 = $ss2;
                        }
                    }
                }

            $query1 = "SELECT superseded_sku FROM mar15 WHERE sku = \"$ss1\"";
                if ($result1 = mysqli_query($mysqli,"$query1")) {
                    while($row1 = mysqli_fetch_array($result1)) {
                        $ss2 = $row1['superseded_sku'];
                        if($ss2 != $ss1) {
                            $ssl++;
                            $ss1 = $ss2;
                        }
                    }
                }

            $query1 = "SELECT superseded_sku FROM mar15 WHERE sku = \"$ss1\"";
                if ($result1 = mysqli_query($mysqli,"$query1")) {
                    while($row1 = mysqli_fetch_array($result1)) {
                        $ss2 = $row1['superseded_sku'];
                        if($ss2 != $ss1) {
                            $ssl++;
                            $ss1 = $ss2;
                        }
                    }
                }

            $query1 = "SELECT superseded_sku FROM mar15 WHERE sku = \"$ss1\"";
                if ($result1 = mysqli_query($mysqli,"$query1")) {
                    while($row1 = mysqli_fetch_array($result1)) {
                        $ss2 = $row1['superseded_sku'];
                        if($ss2 != $ss1) {
                            $ssl++;
                            $ss1 = $ss2;
                        }
                    }
                }

            $query1 = "SELECT superseded_sku FROM mar15 WHERE sku = \"$ss1\"";
                if ($result1 = mysqli_query($mysqli,"$query1")) {
                    while($row1 = mysqli_fetch_array($result1)) {
                        $ss2 = $row1['superseded_sku'];
                        if($ss2 != $ss1) {
                            $ssl++;
                            $ss1 = $ss2;
                        }
                    }
                }

            $query1 = "SELECT superseded_sku FROM mar15 WHERE sku = \"$ss1\"";
                if ($result1 = mysqli_query($mysqli,"$query1")) {
                    while($row1 = mysqli_fetch_array($result1)) {
                        $ss2 = $row1['superseded_sku'];
                        if($ss2 != $ss1) {
                            $ssl++;
                            $ss1 = $ss2;
                        }
                    }
                }

            $query1 = "SELECT superseded_sku FROM mar15 WHERE sku = \"$ss1\"";
                if ($result1 = mysqli_query($mysqli,"$query1")) {
                    while($row1 = mysqli_fetch_array($result1)) {
                        $ss2 = $row1['superseded_sku'];
                        if($ss2 != $ss1) {
                            $ssl++;
                            $ss1 = $ss2;
                        }
                    }
                }

    // --------------------------------------------------------------------------------------

        echo "<tr";
            if($ssl > 1) {
                echo " style=\"background: #ff0;\"";
            }
        echo ">
                <td width=\"398\">$sku</td>
                <td width=\"270\">$ss1</td>
                <td width=\"270\">$ssl</td>
                </tr>";
    }
    $result->close();
    }
    echo "</tr></tbody></table><a href=\"#\" class=\"export linkbutton\" style=\"margin: 10px 0 0 0; display: block; text-align: center;\">Export CSV</a></div>";

    mysqli_close($mysqli);

?>

You can see that I've commented where I want a loop to sit, I've no idea how to make one work however, my redneck methods work but I'm not happy with them and wish to improve my skills. Those aren't my real user/pass before anybody asks.

Answer 1

Normally, I wouldn't advocate having database queries inside loops; it is inefficient and can cause excessive load on your database server.

That said, I don't see much alternative unless you can refactor your data model. What you have is a parent/child relationship within a single table ("superseded_sku" references a child record of the same table). This is bad for what you are trying to do as you never know beforehand if the child record has any children of it's own.

Assuming refactoring is not feasible, you want to run the query, check if there are matching records, update the query and repeat until there are no more records.

Try something like this...

// get initial result set (don't forget to escape your variables)
$query = "SELECT superseded_sku FROM mar15 WHERE sku = '" .
        mysqli_real_escape_string($mysqli, $ss1) . "'";
$result = mysqli_query($mysqli, $query);

// loop while we have results
while(mysqli_num_rows($result) > 0) {
    $row = mysqli_fetch_array($result);

    // double check the row is valid
    if (empty($row["superseded_sku"])) {
        break;
    }
    $ss1 = $row["superseded_sku"];

    // run the query again to check for further superseded skus
    $query = "SELECT superseded_sku FROM mar15 WHERE sku = '" .
        mysqli_real_escape_string($mysqli, $ss1) . "'";
    $result = mysqli_query($mysqli, $query);
}

I would seriously advise against using this approach as a long term measure, as it does not scale well at all. You will almost certainly encounter server resource issues or timeouts, especially if your product list continues growing. You will need to give some thought to refactoring the data if this is something you plan to use in the future.