Update MySQL table using values in another (PHP)

Question

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

$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

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

Learn to use mysqli or PDO. With mysql you are vulnerable to SQL injection — Hamza, Feb 05 '14 at 10:32
Hi, it's: "Warning: Invalid argument supplied for foreach() in C:\xampp\htdocs\Job.php on line 13" (running on localhost) — dave88, Feb 05 '14 at 10:33
why dont you make the whole process entirely in sql? that way you can make this a sp as well — ManZzup, Feb 05 '14 at 11:42
possible duplicate of [mysql\_fetch\_array() expects parameter 1 to be resource, boolean given in select](http://stackoverflow.com/questions/2973202/mysql-fetch-array-expects-parameter-1-to-be-resource-boolean-given-in-select) — John Conde, Feb 23 '14 at 18:29

score 0 · Answer 1 · answered Feb 05 '14 at 10:32

0

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

foreach ($fetch as $user)

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

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

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.

answered Feb 05 '14 at 10:32

linkyndy

17,038
20
114
194

Thanks Andrei - what I am trying to do is create PHP script which will run as a cron task. That fixed the error. However the table is not being set with a '1'? – dave88 Feb 05 '14 at 10:37
You are doing two loops, and within the first one you are executing the same thing. You should drop the first loop as it is useless. Then, the `UPDATE` statement looks wrong. You need to specify what table to update, that is `UPDATE users`. You also do not need any `LIMIT`. You also have to make sure you are using the correct table names and columns. For certainty, open up phpMyAdmin or what you are using, an try executing your SQL. It will help debugging your query. – linkyndy Feb 05 '14 at 10:46
You are still doing the first query which you are not using anywhere. And the second query throws that error because you are supplying `$results`, which doesn't exist. Keep the query for `$result` and use that in the `while()` loop you have, and remove that `$info`/`$fetch` stuff you don't use. – linkyndy Feb 05 '14 at 11:13
The query in `$results` fetches all records, which then you iterate over in the `while` loop. Therefore you should move it outside the loop. – linkyndy Feb 05 '14 at 13:02
Thanks for your help - I think I've pretty much got it. – dave88 Feb 05 '14 at 14:21

Update MySQL table using values in another (PHP)

1 Answers1