[SOLVED] insert into mysql with condition

[tommy445]

Would someone help me refine my statement:

 

$sql="INSERT INTO site_clients (client_account_number) values (1234) where (client_first_name='".$_POST['client_first_name']."' and  client_last_name= '".$_POST['client_last_name'].'");

 

Just in case: I have a form which preloads client's first and last names in a readonly text box and a blank text box for the account number.  I want to update the db with the account number. 

 

Please help.

[gizmola]

Insert is for new rows.  You should not have a where clause.  It looks like what you want to do is an UPDATE. The syntax is totally different.

 

UPDATE site_clients SET client_account_number = 1234 WHERE .... etc.

[Mchl]

What if you have two clients called 'John Smith'?

[tommy445]

Mchl

The form that clients enter their account numbers results from a log-in which is geared around the client_id which text type is hidden.

 

gizmola

Sorry, the account numbers will be entered into a text field... So how is this:

UPDATE site_clients SET client_account_number = '".$_POST['client_account_number']."' WHERE (client_first_name='".$_POST['client_first_name']."' and  client_last_name= '".$_POST['client_last_name'].'");

 

[Mchl]

This query will update all clients called 'John Smith' with same account number... If you have a client ID available why won't you use it?

[gizmola]

Basically yes that code is about right, although Mchl makes a really good point about the key to the table.  If the table has a primary key like an auto_increment id, then you're best off using that to identifty the row you want to update.

[tommy445]

Yes, I understand... Although each account number will be unique, why the extra typing when I could just use the id.

 

Thank you both for your help!

 

 

[Mchl]

Although each account number will be unique[..]

 

Not after you run this query

 

Imagine you have this data in your table

clientID| name | surname | account

-----------------------------------

1      | John |  Smith |    1111

2      | John |  Smith |    2222

3      | John |  Smith |    3333

4      | John |  Smith |    4444

 

If you'd run query like

UPDATE table SET account = 1234 WHERE name = "John" AND surname = "Smith"

you'd end up with

clientID| name | surname | account

-----------------------------------

1      | John |  Smith |    1234

2      | John |  Smith |    1234

3      | John |  Smith |    1234

4      | John |  Smith |    1234

 

Not exactly what you want I guess...

 

Of course you might have UNIQUE index created on 'account' field

I didn't check but I think the result would be either an error or:

clientID| name | surname | account

-----------------------------------

1      | John |  Smith |    1234

2      | John |  Smith |    2222

3      | John |  Smith |    3333

4      | John |  Smith |    4444

 

that's somewhat better, but how to change account numbers for other Johns?