[RESOLVED] INSERT syntax - basic question

ejwf

Basic question - I might be going about this the wrong way but this is what I want to do.....

Instead of deleting a user record from the database completely I want to make it 'not-current'.
I have added a couple of extra field into the database to give me date/time information and a username.
I want to do a web based user interface to select a user from a list and say 'make this user not-current'.......

All is going OK until I get to the final part of process this INSERT...

what I want to say is

$query = "INSERT INTO tblUser (Removed,RemovedBy) 
		VALUES ('$now()','$username')
		WHERE CName = $User AND ClientRef = $ClientId";
$result = mysql_query($query)
or die(mysql_error());

but I get the error message
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE CName = george harrison AND ClientRef = 410' at line 3

what am I doing wrong - can I not use a WHERE clause in an Insert query ?

thanks

mrhappiness

ejwf wrote:
can I not use a WHERE clause in an Insert query ?

would be quite senseless, wouldn't it?

which data should the WHERE reference to?
what do you want to achieve?

http://dev.mysql.com/doc/mysql/en/insert.html

ejwf

Writing it in English instead of php - this is what I want to say

Look in tblUser
Find a record where the field CName is equal to the variable $User, and the field ClientRef is equal to the variable $ClientId
When you find that record Insert into it 2 pieces of information - in the field Removed, enter the current date and time, and in the field RemovedBy enter the information set in the variable $username

It makes sense to me - I just don't know how to write this in php.........

madwormer2

You wanna use UPDATE i think

mrhappiness

so you want to change sth. that already exists?
UPDATE is the way to go

ejwf

OK, trying UPDATE.......
I guess this is just me getting the syntax wrong but still getting SQL error message
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'harrison AND ClientRef = 410' at line 2
I've looked in the mysql manual under the UPDATE syntax and I just cannot see where I am going wrong...........
code I have is

$query = "UPDATE tblUser SET Removed=now(), RemovedBy =$username
		WHERE CName=$User AND ClientRef=$ClientId";
$result = mysql_query($query)
or die(mysql_error());

TobyRT

By my understanding you have to use single quotes around variables - I've had trouble like this before and it's only when i've swapped quote marks around that it's worked.

try RemovedBy = '$username' WHERE CName = '$User' etc etc

ejwf

that's done it !........ blinking quote marks !
thank you