check column if empty then do update

Question

guys i have a large database so when i want to fully update my some column i have timeout error (i attempt some method to increase timeout and fail) But my question is i want to bypass this problem i want to update my empty column in some table. so i want to use this query code but i have blank page with no error can some one tell me what problem with that or if possible pleas tell me a good method.

    $sql = 'SELECT topic_first_poster_avatar FROM ' . TOPICS_TABLE . ' WHERE topic_poster = ' . (int) $row['user_id'] . 'IF topic_first_poster_avatar = "" 
        SET topic_first_poster_avatar =  \'' . $db->sql_escape($avatar_info);
    $db->sql_query($sql);

Not sure about PHP, but why not just `UPDATE topics_table SET top_first_poster_avatar = ... WHERE top_first_poster_avatar = ''`? — plalx, Jun 07 '14 at 20:14
?! but we have to check every row with user_id to import correct data else all empty column take same info — user3718571, Jun 07 '14 at 20:19
Any time I see somebody using something named `sql_escape`, I worry about [SQL Injection](http://security.stackexchange.com/a/25710). I'm assuming that `TOPICS_TABLE` is coming from a configuration file somewhere, and is **absolutely not** modifiable by the user. Casting `user_id` to `int` is safe, but you should still be using [parameterized queries](http://stackoverflow.com/a/60496). — Clockwork-Muse, Jun 08 '14 at 02:01

score 0 · Answer 1 · answered Jun 08 '14 at 01:04

0

As plalx said in the comments, you don't need a SELECT or IF, you can specify WHERE for the update statement and have multiple contraints with AND/OR.

$sql = "UPDATE ". TOPICS_TABLE ." 
       SET topic_first_poster_avatar =  '" . $db->sql_escape($avatar_info) ."'
       WHERE topic_poster = " . (int) $row['user_id'] . " AND topic_first_poster_avatar = ''";

answered Jun 08 '14 at 01:04

Devon Bessemer

34,461
9
69
95

Please, you need to at least mention likely pitfalls/security issues, and that the OP should be using parameterized queries. Any time you have the ability to recommend best practices/a better way of doing things, you should point them out. – Clockwork-Muse Jun 08 '14 at 02:04
@Clockwork-Muse The only potential security issue I saw was the questionable sql_escape, but not knowing what that does, I couldn't really offer any advice on that. The user_id is secured as it is typecasted as an integer and I can assume the constant is safe. Prepared queries are always better but there wasn't enough information for me to see any obvious security threats. – Devon Bessemer Jun 08 '14 at 04:37

check column if empty then do update

1 Answers1