mysqli循环内的php循环 [英] php loop within mysqli loop
本文介绍了mysqli循环内的php循环的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个mysql表，其中包含2列，看起来像这样...
-----------------------------------------------|sku |superseded_sku |+ --------------------- + ------------------- +|第1部分|第2部分||第2部分|第3部分||第3部分|第4部分||第5部分|第6部分||第6部分|第7部分| 该表基本上显示了公司出售的产品在何处已被更新的产品所替代.我的任务是重塑表格，使其类似于以下内容……
-----------------------------------------------|sku |superseded_sku |+ --------------------- + ------------------- +|第1部分|第4部分||第2部分|第4部分||第3部分|第4部分||第5部分|part7 ||第6部分|part7 | 因此消除了重新整理列表以查找当前替换项的需要.
我写了下面的文章，它有一个基本的缺点:如果产品被替换的次数超出了我在代码中允许的次数，那么它看起来就不会那么远了.我已经尝试过很多次，包括一个while循环，一个do {} while循环和一个foreach循环，但是到目前为止，我已经失败了.
这是我现在的代码
<?php//错误报告ini_set("display_errors"，1);ini_set("error_reporting"，E_ALL);//变数//页面变量$ P = $ _REQUEST ["p"];//最新BritPart列表的变量$ dbhost ='本地主机';$ dbuser ='用户名';$ dbpass ='密码';$ dbselect ='已取代';$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbselect);如果(mysqli_connect_error()){die('连接错误('.mysqli_connect_errno().')'.mysqli_connect_error());}//开始抛出有关的数据$ compare ="SELECT * FROM mar15 ORDER BY sku";echo< h3 style = \" text-align:center; \>取代表格</h3>< div class = \输出\"样式= \宽度: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>";如果($ result = mysqli_query($ mysqli，"$ compare")){while($ row = mysqli_fetch_array($ result)){$ sku = $ row ['sku'];$ ss1 = $ row ['superseded_sku'];$ ssl ="1";//---------------------------------------------------------------//我要遍历此部分，直到找不到匹配项//---------------------------------------------------------------$ query1 =从mar15 WHERE sku中选择superseded_sku = \" $ ss1 \";如果($ result1 = mysqli_query($ mysqli，"$ query1")){while($ row1 = mysqli_fetch_array($ result1)){$ ss2 = $ row1 ['superseded_sku'];if($ ss2！= $ ss1){$ ssl ++;$ss1 = $ss2;}}}//---------------------------------------------------------------//相反，我已经这样做了，因为我不知道该怎么做//---------------------------------------------------------------$ query1 =从mar15 WHERE sku中选择superseded_sku = \" $ ss1 \";如果($ result1 = mysqli_query($ mysqli，"$ query1")){while($ row1 = mysqli_fetch_array($ result1)){$ ss2 = $ row1 ['superseded_sku'];if($ ss2！= $ ss1){$ ssl ++;$ ss1 = $ ss2;}}}$ query1 =从mar15 WHERE sku中选择superseded_sku = \" $ ss1 \";如果($ result1 = mysqli_query($ mysqli，"$ query1")){while($ row1 = mysqli_fetch_array($ result1)){$ ss2 = $ row1 ['superseded_sku'];if($ ss2！= $ ss1){$ ssl ++;$ ss1 = $ ss2;}}}$ query1 =从mar15 WHERE sku中选择superseded_sku = \" $ ss1 \";如果($ result1 = mysqli_query($ mysqli，"$ query1")){while($ row1 = mysqli_fetch_array($ result1)){$ ss2 = $ row1 ['superseded_sku'];if($ ss2！= $ ss1){$SSL++;$ ss1 = $ ss2;}}}$ query1 =从mar15 WHERE sku中选择superseded_sku = \" $ ss1 \";如果($ result1 = mysqli_query($ mysqli，"$ query1")){while($ row1 = mysqli_fetch_array($ result1)){$ ss2 = $ row1 ['superseded_sku'];if($ ss2！= $ ss1){$ ssl ++;$ ss1 = $ ss2;}}}$ query1 =从mar15 WHERE sku中选择superseded_sku = \" $ ss1 \";如果($ result1 = mysqli_query($ mysqli，"$ query1")){while($ row1 = mysqli_fetch_array($ result1)){$ ss2 = $ row1 ['superseded_sku'];if($ ss2！= $ ss1){$ ssl ++;$ ss1 = $ ss2;}}}$ query1 =从mar15 WHERE sku中选择superseded_sku = \" $ ss1 \";如果($ result1 = mysqli_query($ mysqli，"$ query1")){while($ row1 = mysqli_fetch_array($ result1)){$ ss2 = $ row1 ['superseded_sku'];if($ ss2！= $ ss1){$ ssl ++;$ ss1 = $ ss2;}}}$ query1 =从mar15 WHERE sku中选择superseded_sku = \" $ ss1 \";如果($ result1 = mysqli_query($ mysqli，"$ query1")){while($ row1 = mysqli_fetch_array($ result1)){$ ss2 = $ row1 ['superseded_sku'];if($ ss2！= $ ss1){$ ssl ++;$ ss1 = $ ss2;}}}$ query1 =从mar15 WHERE sku中选择superseded_sku = \" $ ss1 \";如果($ result1 = mysqli_query($ mysqli，"$ query1")){while($ row1 = mysqli_fetch_array($ result1)){$ ss2 = $ row1 ['superseded_sku'];如果($ss2 ！= $ss1){$ ssl ++;$ ss1 = $ ss2;}}}$ query1 =从mar15 WHERE sku中选择superseded_sku = \" $ ss1 \";如果($ result1 = mysqli_query($ mysqli，"$ query1")){while($ row1 = mysqli_fetch_array($ result1)){$ ss2 = $ row1 ['superseded_sku'];if($ ss2！= $ ss1){$ ssl ++;$ ss1 = $ ss2;}}}$ query1 =从mar15 WHERE sku中选择superseded_sku = \" $ ss1 \";如果($ result1 = mysqli_query($ mysqli，"$ query1")){while($ row1 = mysqli_fetch_array($ result1)){$ ss2 = $ row1 ['superseded_sku'];if($ ss2！= $ ss1){$ ssl ++;$ ss1 = $ ss2;}}}$ query1 =从mar15 WHERE sku中选择superseded_sku = \" $ ss1 \";如果($ result1 = mysqli_query($ mysqli，"$ query1")){while($ row1 = mysqli_fetch_array($ result1)){$ ss2 = $ row1 ['superseded_sku'];if($ ss2！= $ ss1){$ ssl ++;$ ss1 = $ ss2;}}}$ query1 =从mar15 WHERE sku中选择superseded_sku = \" $ ss1 \";如果($ result1 = mysqli_query($ mysqli，"$ query1")){while($ row1 = mysqli_fetch_array($ result1)){$ ss2 = $ row1 ['superseded_sku'];if($ ss2！= $ ss1){$ ssl ++;$ ss1 = $ ss2;}}}//--------------------------------------------------------------------------------------回声< tr";if($ ssl> 1){回声"style = \" background:#ff0; \";}回声>< 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;显示:块；text-align:center; \>导出CSV</a</div>";mysqli_close($ mysqli);?> 您可以看到我已经评论了我想要放置循环的位置，但是我不知道如何进行工作，我的乡下人方法可以工作，但是我对它们不满意并且希望提高自己的技能.在任何人问之前，这些都不是我的真实用户/通行证.
解决方案
通常，我不会提倡在循环内进行数据库查询.它效率低下，并可能导致数据库服务器上的负载过大.
也就是说，除非您可以重构数据模型，否则我看不出其他选择.您所拥有的是单个表中的父/子关系("superseded_sku"引用同一表的子记录).这对于您尝试做的事情是不利的，因为您永远不会知道子记录是否拥有自己的子记录.
假设重构不可行，您要运行查询，检查是否有匹配的记录，更新查询并重复直到没有更多记录为止.
尝试这样的事情...
//获得初始结果集(别忘了转义变量)$ query =从mar15 WHERE sku ='中选择superseded_sku.mysqli_real_escape_string($ mysqli，$ ss1).'";$ result = mysqli_query($ mysqli，$ query);//在有结果的时候循环while(mysqli_num_rows($ result)> 0){$ row = mysqli_fetch_array($ result);//仔细检查该行是否有效如果(empty($ row ["superseded_sku"])){休息;}$ ss1 = $ row ["superseded_sku"];//再次运行查询以检查是否有进一步的替代$ query =从mar15 WHERE sku ='中选择superseded_sku.mysqli_real_escape_string($ mysqli，$ ss1).'";$ result = mysqli_query($ mysqli，$ query);} 我强烈建议您不要将这种方法用作长期措施，因为它根本无法很好地扩展.您几乎肯定会遇到服务器资源问题或超时，尤其是在您的产品列表持续增长的情况下.如果您打算将来使用此数据，则需要考虑一下重构数据的问题.
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.
解决方案
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.
这篇关于mysqli循环内的php循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
