aragonthebest aragonthebest - 2 months ago 10
PHP Question

php + sqllite3: Error by prepered update binding, when using IS NULL/ IS NOT NULL

So I have to use a prepared statement, otherwise there will be other errors.

I don't know why, but it wasn't possible to prepare two update statements at the same time like this:

$sql_update_users =<<<EOF
UPDATE users SET username = :username, full_name = :full_name, is_private = :is_private , is_following = 3 , updated_on = :time WHERE user_id = :usernameId;

UPDATE users SET following_on = IfNull(following_on, :time) WHERE user_id = :usernameId;
EOF;

//Datei für update preparen
$smt2 = $db->prepare($sql_update_users);

//bindings...
//execute...


^would not work.... :(

so I had to sepperate them:

//preper update
$smt2 = $db->prepare("UPDATE users SET following_on = :time WHERE user_id = :usernameId, following_on IS NULL");

$smt2->bindParam(':usernameId', $usernameId);
$smt2->bindParam(':time', $time);

$smt2->execute();


^This will throw an error. I already replaced the "AND" with "," because, that was also a Problem.


Call to a member function bindParam() on boolean


However, this works:

$smt2 = $db->prepare("UPDATE users SET following_on = IfNull(following_on, :time) WHERE user_id = :usernameId");


^This works.
The Problem is, now I have to update a column, if there are multiple entrys in the row NULL and NOT NULL. And since i get the Error when using "IS NULL" and "IS NOT NULL", I don't know, what to do.

EDIT1

So I remember know, why "AND" won't work. so this line won't work (no error, just doing nothing):

UPDATE users SET was_follower = :sync_id AND unfollowed_me_on = :time


This line will work:

UPDATE users SET was_follower = :sync_id, unfollowed_me_on = :time


So i need a solution, to make "AND" work, or a solution to use IS NULL and IS NOT NULL with ","

CL. CL.
Answer
UPDATE users SET following_on = :time WHERE user_id = :usernameId, following_on IS NULL

In this query, the comma must be an AND.

UPDATE users SET was_follower = :sync_id AND unfollowed_me_on = :time

In this query, the AND must be a comma (if you want to change unfollowed_me_on) or WHERE (if unfollowed_me_on determines which rows are to be updated).