使用另一个（PHP）中的值更新MySQL表

I am trying to create a PHP script which will update one table with a '1' when a date value in another table is greater or equal to the current date. This is based on an id field which is present in both tables. I have tested the following code with no success - it gives an error for the foreach loop:

我正在尝试创建一个PHP脚本,当另一个表中的日期值大于或等于当前日期时,该脚本将使用“1”更新一个表。这基于两个表中都存在的id字段。我测试了以下代码但没有成功 - 它为foreach循环提供了一个错误:

<?php

$db_conn = mysql_connect('localhost', '***', '****');
mysql_select_db('db', $db_conn);

$info = mysql_query("SELECT * FROM user_profiles");
$fetch = mysql_fetch_array($info);

foreach($fetch['user_id'] as $id) {
$result = mysql_query("SELECT id 
FROM users
WHERE EXISTS (
SELECT user_id
FROM user_profiles
WHERE DATE(profile_value) >= DATE(NOW()) 
AND users.id = user_profiles.user_id)", $db_conn);

while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$user_del_result = mysql_query("UPDATE users.block
WHERE user_id = {$row['user_id']}
SET block ='1'
LIMIT 1", $db_conn);                          
}

}
?>

Both tables contain the same amount of rows (users).

两个表都包含相同数量的行(用户)。

Any help would be great.

任何帮助都会很棒。

Edit

I have narrowed it down to the following:

我把它缩小到以下几点:

<?php

$db_conn = mysql_connect('localhost', '***', '****');
mysql_select_db('db', $db_conn);

$info = mysql_query("SELECT * FROM user_profiles");
$fetch = mysql_fetch_array($info);

while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$user_del_result = mysql_query("UPDATE users
WHERE user_id = {$row['id']}
AND WHERE Date(profile_value ) >= DATE(NOW()) 
SET block ='1'", $db_conn);                          
}
?>

However the following error is now thrown: Warning: mysql_fetch_array() expects parameter 1 to be resource, null given in C:\xampp\htdocs\Job.php on line 10

但是现在抛出以下错误:警告:mysql_fetch_array()期望参数1为资源,在第10行的C:\ xampp \ htdocs \ Job.php中给出null

Ok now I'm confused...

好的,现在我很困惑......

<?php

$db_conn = mysql_connect('localhost', '', '');
mysql_select_db('_db', $db_conn);

while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$result = mysql_query("SELECT id 
FROM users
WHERE EXISTS (
SELECT user_id
FROM user_profiles
WHERE DATE(profile_value) >= DATE(NOW()) 
AND users.id = user_profiles.user_id)", $db_conn);

$user_del_result = mysql_query("UPDATE users
WHERE user_id = {$row['id']}
AND WHERE Date(profile_value ) >= DATE(NOW()) 
SET block ='1'", $db_conn);                          
}

?>

Error Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\Job.php on line 14

错误警告:mysql_fetch_array()期望参数1是资源,在第14行的C:\ xampp \ htdocs \ Job.php中给出布尔值

1 个解决方案

#1

You are not using the foreach statement correctly. You should use it like:

您没有正确使用foreach语句。你应该像以下一样使用它:

foreach ($fetch as $user)

Where $user will contain you user record; then you can access user columns such as $user['id'].

$ user将包含您的用户记录;然后您可以访问用户列,例如$ user ['id']。

Currently, you are using an array key in the foreach statement, not an array, that's why it probably throws an error.

目前,您在foreach语句中使用数组键,而不是数组,这就是它可能抛出错误的原因。

Also, I believe that updating a table based on another table can be done in plain SQL, you don't have to involve PHP and so many queries for this.

此外,我相信基于另一个表更新表可以在纯SQL中完成,您不必涉及PHP和这么多的查询。

#1