我正在尝试将一个变量存储到我的数据库中。 我已经在我的数据库表中创建了一个应该存储变量的字段。我创建的变量是$total,它存储计算,如下面的代码所示:
<?php
$user_query=mysql_query("select * from book where status != 'Archive'")or die(mysql_error());
while($row=mysql_fetch_array($user_query)){
$id=$row['book_id'];
$cat_id=$row['category_id'];
$book_copies = $row['book_copies'];
$borrow_details = mysql_query("select * from borrowdetails where book_id = '$id' and borrow_status = 'pending'");
$row11 = mysql_fetch_array($borrow_details);
$count = mysql_num_rows($borrow_details);
$total = $book_copies - $count;
/* $total = $book_copies - $borrow_details;
echo $total; */
?>
我已经尝试使用以下代码存储它
<?php mysql_query("INSERT INTO book (borrow_total) VALUES('$total')");?>
虽然你的问题本身并不完整,但我想更新所选书籍的"borrow_total"字段。因此,您只需要运行 UPDATE 查询,而不是插入,如下所示:
<?php mysql_query("UPDATE book SET borrow_total=$total WHERE book_id=$id"); ?>
希望这对:)有所帮助
看起来您要更新书籍表。在这种情况下,这样的事情是正常的:-
<?php
$user_query = mysql_query("select * from book where status ! = 'Archive'")or die(mysql_error());
while($row = mysql_fetch_array($user_query))
{
$id = $row['book_id'];
$cat_id = $row['category_id'];
$book_copies = $row['book_copies'];
$borrow_details = mysql_query("select * from borrowdetails where book_id = '$id' and borrow_status = 'pending'");
$row11 = mysql_fetch_array($borrow_details);
$count = mysql_num_rows($borrow_details);
$total = $book_copies - $count;
mysql_query("UPDATE book SET borrow_total = $total WHERE book_id = $id");
}
?>
如果存在唯一键,则可以使用 INSERT 进行更新。例如,假设book_id是书表的唯一键:-
<?php
$user_query = mysql_query("select * from book where status ! = 'Archive'")or die(mysql_error());
while($row = mysql_fetch_array($user_query))
{
$id = $row['book_id'];
$cat_id = $row['category_id'];
$book_copies = $row['book_copies'];
$borrow_details = mysql_query("select * from borrowdetails where book_id = '$id' and borrow_status = 'pending'");
$row11 = mysql_fetch_array($borrow_details);
$count = mysql_num_rows($borrow_details);
$total = $book_copies - $count;
mysql_query("INSERT INTO book (book_id, borrow_total) VALUES('$id', '$total') ON DUPLICATE KEY UPDATE borrow_total = VALUES(borrow_total)");
}
?>
这是尝试进行插入,查找已经存在的记录,以便使用要插入的值更新borrow_total列。当您想要在 1 个语句中更新数百条记录时,这可能很有用。
但是您可能可以使用一个SQL语句进行整个循环/更新,如下所示:-
UPDATE book b
INNER JOIN
(
SELECT book_id, COUNT(*) AS borrowed_count
FROM borrowdetails
WHERE borrow_status = 'pending'
GROUP BY book_id
) bd
ON b.book_id = db.book_id
SET b.borrow_total = b.borrow_total - bd.borrowed_count
where b.status != 'Archive'
请注意,如上所述,MYSQL_* 函数已被弃用(即,对它们的支持几乎消失了,它们很快就会从 php 中删除),所以最好切换到 MYSQLI_* 函数或 pdo。我在这里使用了MYSQL_*函数只是为了与您的问题保持一致。
你应该
使用mysqli_query();
函数而不是mysql_query();
...尝试mysqli_ query();
函数来执行查询。
如果要在mySQL表中插入新记录,则
<?php mysql_query("INSERT INTO book (borrow_total) VALUES('$total')");?>
它应该可以工作,如果您想更新 MySQL 表中的一行,那么
<?php mysql_query("UPDATE book SET borrow_total=$total WHERE book_id=$id"); ?>