Inserting record to already existing table column problems

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jramaro
Forum Commoner
Posts: 58
Joined: Tue Jun 26, 2007 7:46 am

Inserting record to already existing table column problems

Post by jramaro »

Hi,
I'm using a Mysql database and came across something.
I can insert and select records etc from database with PHP, but i came across this new problem when making a script.

the scenario is = I have a database and table with fields like say

record_id | first_name | last_name | email | comments | date
-----------------------------------------------------------------------------


and say everything is all inserted already except the 'comments' field .

If I want that to be a field that can always be overwritten or to be NULL or inserted at anytime the user wants .


I tried UPDATE php , but i ran into a problem like this

Code: Select all

$query = "UPDATE users SET comments = '"  . $comments .  "WHERE comments = NULL ";

it wouldnt do anything, and i also noticed that even if it had ,it would be inserting that user's comments into ALL the NULL comments fields.

Do you know how i can update or insert into a field that starts as NULL , without affecting other users table columns?

Thank you!
User avatar
louie35
Forum Contributor
Posts: 144
Joined: Fri Jan 26, 2007 8:40 am
Location: Dublin
Contact:

Post by louie35 »

your sql is not right should be:

Code: Select all

$query = "UPDATE users SET comments = '"  . addslashes($comments) .  "' WHERE comments = NULL ";
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

Code: Select all

$query = "UPDATE users SET comments = '"  . $comments .  "WHERE comments = NULL "; 
1. As the previous poster corrected noted you need to quote and escape $comments. However, you ought to use mysql_real_escape_string() rather than addslashes().

2. You can't use "= NULL". The correct SQL syntax for a NULL comparison is "IS NULL".

I would do...

Code: Select all

$query  = "UPDATE `users` ";
$query .= "SET ";
$query .= "`users`.`comments` = '"  . mysql_real_escape_string($comments) .  "' ";
$query .= "WHERE 1 ";
$query .= "AND `users`.`comments` IS NULL";
Post Reply