我正在开发一个应用程序,该应用程序根据从另一个表中选择的值更新表中的数据。我在网上搜索过,并试图修改一些样本以满足我的需求,但仍然不起作用。
以下是我的一些代码:
$sync_2 = "
select pin,ddt_1
from purchases
where (amount_to_repay-amount_repaid)>0
and status like 'U%'
and counter=1
";
$sync_2_res = mysqli_query($link, $sync_2);
$row = mysqli_fetch_assoc($sync_2_res);
$data = mysqli_num_rows($sync_2_res);
$i;
for ($i = 0; $i <= $data; $i++) {
$pin = $row['pin'];
$ddt = $row['ddt_1'];
$sql = "
update deductions
set amount = '$ddt_1'
where pin = '$pin';
$result = mysqli_query($link,$sql);
}
什么都没有。我必须改变循环的类型吗?。
试试这个代码,它应该在一个查询中完成整个操作:
$query = "
UPDATE `deductions` `d`
JOIN `purchases` `p` ON `p`.`pin` = `d`.`pin`
SET `d`.`amount` = `p`.`ddt_1`
WHERE
(`p`.`amount_to_repay` - `p`.`amount_repaid`) > 0
AND `p`.`status` LIKE 'U%'
AND `p`.`counter` = 1
";
$result = mysqli_query($link, $query);
正如您所给出的,在sql语句中将变量$ddt_1更改为$ddt可能存在问题
for($i=0;$i<=$data;$i++)
{
$pin=$row['pin'];
$ddt=$row['ddt_1];
$sql="update
deductions
set amount='$ddt'
where pin='$pin';
$result=mysqli_query($link,$sql);
}
改进的PHP示例
$mysqli = new mysqli ( "localhost", "root", "", "test" );
$sql = "SELECT pin , ddt_1 from purchases WHERE amount_to_repay-amount_repaid > 0 AND status like 'U%' AND counter = 1";
$sql2 = "update deductions set amount = '%s' where pin = '%s'; ";
$result = $mysqli->query($sql);
$row = null ;
while ($row = $result->fetch_assoc())
{
$mysqli->query(sprintf ( $sql, $row ['pin'], $row ['ddt_1'] ));
}
旧帖子
更换
$ddt=$row['ddt_1];
带
$ddt=$row['ddt_1'];
更换
set amount='$ddt_1'
带
set amount='$ddt'
我希望这能帮助
感谢
:)
首先要注意的是,mysqli_fetch_assoc()只提取一行,即光标所在的行。然后它将光标移动一行。当光标到达数据末尾时,mysqli_fetch _asso()返回NULL。
因此,为了迭代一组结果,人们通常使用while loop,因为这是最简洁的:
$sync_2_res = mysqli_query($link, $sync_2);
if($sync_2_res == null) {
echo "The sync2 query failed: " . mysqli_error($link);
exit();
}
while($row = mysqli_fetch_assoc($result) {
// do something with $row
}
检查查询是否有效并返回结果而不是FALSE也是一个好主意。
接下来,我认为第二个查询中有一个拼写错误:$dtt_1
应该是$dtt
,你真的确定amount
字段应该设置为字符串吗?
无论如何,以下是我如何重写你的代码:
$sync_2= "select pin,ddt_1 from purchases "
. "where (amount_to_repay-amount_repaid)>0 "
. "and status like 'U%' and counter=1";
$sync_2_res=mysqli_query($link,$sync_2);
// check the query worked
if(!$sync_2_res) {
echo "The sync2 query failed: " . mysqli_error($link);
exit();
}
while($row = mysqli_fetch_assoc($result) {
// do something with $row
$pin=$row['pin'];
$ddt=$row['ddt_1'];
// note change from $ddt_1 to $dtt
$sql="update deductions set amount='$ddt' where pin='$pin';";
$result=mysqli_query($link,$sql);
// check query worked
if(!$result) {
echo "The 2nd query failed: " . mysqli_error($link);
}
}