mysqli循环中的php循环

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.

douhu5837
douhu5837 我尝试过尝试用1个查询完成它,但无济于事。这个方法给我正确的输出如上所述,列表是〜2,500个产品。使用JOIN我结束了436种产品,这不是理想的结果,我需要一个输出,所有产品仍然列出但每个superseded_sku将是最新的。例如。如果当前A被B替换但是被C替换而D被D替换则A将指向D,因此B和C将错过每个产品的中间人。在某些情况下,这会深入6或7级,因此我想要一个循环。
5 年多之前 回复
dtgv52982
dtgv52982 在任何情况下,您最好找出正确的查询以获得所需的输出(可能),而不是您当前的方法
5 年多之前 回复
douhui7136
douhui7136 也许你想要LEFTJOIN。不确定你想要的输出是什么。
5 年多之前 回复
dopcpc9207
dopcpc9207 我尝试使用JOIN,但它导致返回的行少于我原来的行,但没有解决问题。除非你有比我更好的编写查询的方法。SELECTx.sku,x.superseded_skuFROMmar15xJOINmar15yONy.sku=x.superseded_sku
5 年多之前 回复
douqiang5163
douqiang5163 您应该使用连接使用单个查询。只要您有嵌套查询并且内部查询使用外部查询中的数据,连接几乎总是更有效。一个查询,而不是1+n个查询。哦,并尝试使用更好的变量名称。ss1和ssl在视觉上非常混乱......
5 年多之前 回复

2个回答



通常,我不主张在循环内部进行数据库查询; 它是低效的,可能会导致数据库服务器负载过重。</ p>

尽管如此,除非您可以重构数据模型,否则我没有其他选择。 你拥有的是单个表中的父/子关系(“superseded_sku”引用同一个表的子记录)。 这对你正在尝试做的事情有害,因为如果子记录中有任何子记录,你事先就不知道。</ p>

假设重构不可行,你想运行查询, 检查是否有匹配的记录,更新查询并重复直到没有更多记录。</ p>

尝试这样的事情...... </ p>

  //得到初始结果集(不要忘记转义变量)
$ query =“SELECT superseded_sku FROM mar15 WHERE sku ='”。
mysqli_real_escape_string($ mysqli,$ ss1)。 “'”;
$ result = mysqli_query($ mysqli,$ query);

//循环,同时我们有结果
while(mysqli_num_rows($ result)&gt; 0){
$ row = mysqli_fetch_array($ 结果);

//仔细检查行是否有效
if(empty($ row [“superseded_sku”])){
break;
}
$ ss1 = $ row [“superseded_sku”] ;

//再次运行查询以检查进一步取代的skus
$ query =“SELECT superseded_sku FROM mar15 WHERE sku ='”。
mysqli_real_escape_string($ mysqli,$ ss1)。 “'”;
$ result = mysqli_query($ mysqli,$ query);
}
</ code> </ pre>

我会严肃地建议不要长期使用这种方法 测量,因为它根本不能很好地扩展。 您几乎肯定会遇到服务器资源问题或超时,特别是如果您的产品列表继续增长。 如果您计划将来使用这些数据,则需要考虑重构数据。</ p>
</ div>

展开原文

原文

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.

doutao1171
doutao1171 从技术上讲,数据应该逐月相反并且缩小,这项工作从未像现在这样完成。 我估计将来它只会走两步但不能100%肯定(部件可以每月取代3或4次)。 基于此,它确实使我的生活更轻松,因为这是提供数据的方式,重构只会在每个月为工作增加额外的工时,从而有效地使工作成为一项手动任务。 谢谢你,先生。
5 年多之前 回复



我没有看到任何更新语句所以我想这不是问题。</ p>

我也不知道循环是否是最有效的解决方案,但是如果要循环,则需要循环,直到 superseded_sku </ code>列中也没有 sku </ code>列。</ p>

使用嵌套的select语句很容易检查,例如:</ p>

  SELECT superseded_sku \  nFROM mar15 
WHERE superseded_sku IN(
SELECT DISTINCT sku FROM mar15

</ code> </ pre>

此查询将获取 superseded_sku </ code中的所有元素 > sku </ code>列/中需要替换的列的列。</ p>

因此,当此sql语句返回的行数超过0时,您需要进行 更多替换。</ p>

你可以更进一步,并将它与你的 UPDATE </ code>语句结合起来,但这应该可以让你开始。</ p> \ n </ div>

展开原文

原文

I don't see any update statements so I guess that is not the problem.

I also don't know if a loop is the most efficient solution, but if you are going to loop, you want to loop until there are no elements in the superseded_sku column that also exist in the sku column.

That is easy to check with a nested select statement, something like:

SELECT superseded_sku
FROM mar15
WHERE superseded_sku IN (
  SELECT DISTINCT sku FROM mar15
)

This query will get you all elements in the superseded_sku column that have a counterpart in the sku column / that need to be replaced.

So while this sql statement returns more than 0 rows, you need to make more replacements.

You could probably take this a step further and combine it with your UPDATE statement as well but this should get you started.

douhao3562
douhao3562 我不打算使用UPDATE语句,结果表以html呈现,表格底部的链接将在我们需要时将其导出为csv。 这里的想法是,随着我们已经巨大的(30,000+)产品列表的增长,我可以不时地替换表格。 我只需要一种方法让它尽可能深地挖掘返回的每一行。
5 年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐