Update Database Statement - Simple Question

programming
1

Hi all,

I know this is a very simple question, that how can i update my database to MYSQL? I would like to update certain user and user ID into certain collumns. Is my syntax correct? Thanks for looking it for me :slight_smile: here is my code:

Login Table:
“User”,“userid”
“John”,“jon”

Php code:
<?..
$query = “UPDATE Login SET User=$user,userid=$userid WHERE User,userid”;
mysql_query($query);
?>

2

I’m not sure what exactly i need to put in the WHERE condition as I do not have any id column that other tutorial websites suggest me to do so. Can I just ignore it?

3

I think you need an ‘INSERT’ not an update - are you looking to add a new record?

Update updates an existing record - such as…

UPDATE Login SET User=$user WHERE userid = $userid

You probably want an insert

INSERT INTO Login (user, userid) VALUES ($user, $userid)

4

Thanks for you all reply. I’m looking for UPDATE instead of INSERT. I have this set of user, userid, pwd, address…but only user and userid both need to be updated. As ahmednuaman suggested, my code will be look like this:

$query =“UPDATE Login SET User = ‘$user’, userid = ‘$userid’ WHERE User = ‘$user’”;

Is this correct or without WHERE condition, can I do this way?

$query =“UPDATE Login SET User = ‘$user’, userid = ‘$userid’”;

5

Well usually your userid should not change - but if your system needs to change it then…

$query =“UPDATE Login SET User = ‘$user’, userid = ‘$userid’ WHERE User = ‘$user’”;

should work fine.

Do not use UPDATE without a WHERE clause - otherwise it will update ALL users with the same data (all users will be John, jon)! Unless of course you want to change all users to the same data or you only have 1 record.

Which one of your Login table columns is the primary key? Is it unique?

6

[quote=Charleh;2343433]Well usually your userid should not change - but if your system needs to change it then…

$query =“UPDATE Login SET User = ‘$user’, userid = ‘$userid’ WHERE User = ‘$user’”;

should work fine.

Do not use UPDATE without a WHERE clause - otherwise it will update ALL users with the same data (all users will be John, jon)! Unless of course you want to change all users to the same data or you only have 1 record.

Which one of your Login table columns is the primary key? Is it unique?[/quote]

Thanks for your quick reply, Charleh! Primary key should be User. Does it mean, WHERE is equal to Primary key of the table collumn? Another question is what if i have User, userid, address and tel? Will it be the same as this:

$query =“UPDATE Login SET User = ‘$user’, userid = ‘$userid’, address=’$address’, tel=’$tel’ WHERE User = ‘$user’”;

Another great explaination from you is “Do not use UPDATE without a WHERE clause” as non of the online tutorial mention about this warning. Thanks again :smiley:

7

Yes you can specify any column to update - you can always check which rows you are going to update by using SELECT first

So if you do

SELECT * FROM Login WHERE User = ‘John’

You will see 1 record - if you now change the SELECT to UPDATE instead

UPDATE Login SET user = ‘something’ WHERE User = ‘John’

You have the same 1 record but you are updating instead of just selecting.

The WHERE column can be ANY value - you can use an expression of any kind - so

SELECT * FROM Login WHERE user LIKE ‘%j%’

Will give you all records where the username has a ‘j’ in it - (% is wildcard)

In the same way

UPDATE Login SET someColumn = 1 WHERE user LIKE ‘%j%’

Will update all records where the user has a ‘j’ in it.

You can do all sorts of expressions - and some SQL can get complex, like so…


SELECT *, CASE count(*) WHEN 1 THEN 'found 1' WHEN 0 THEN 'found none' ELSE 'found more than 1' END as Roughcount FROM Table1 t1 INNER JOIN Table2 t2 on t1.column1 = t2.column1 INNER JOIN (SELECT TOP 1 * FROM Table3 WHERE user like '%something%') as u1 ON t2.column2 = u1.column1 WHERE dateadd(d, 1, t1.addeddate) > now()

It’s when things get pages long that it starts getting complicated :stuck_out_tongue:

8

Wow! Thank you so much for the explanation. I’m now have a big picture of the basic MYSQL command. There is this question, forgive me if im asking too much to ask :P, that since you said i can define any value to be set in WHERE condition, can I make add more values in there? for example,

$query =“UPDATE Login SET User = ‘$user’, userid = ‘$userid’, address=’$address’, tel=’$tel’ WHERE User = ‘$user’”, userid = ‘$userid’, address=’$address’, tel=’$tel’";

in that case (my opinion) that all the values will be updated into the database.

9

Remember: The WHERE is used to filter the records

The SET clause sets the value of a field

$query =“UPDATE Login SET User = ‘$user’, userid = ‘$userid’, address=’$address’, tel=’$tel’ WHERE User = ‘$user’”, userid = ‘$userid’, address=’$address’, tel=’$tel’";

This wouldnt work - the syntax is not right so you would need to fix that

$query =“UPDATE Login SET User = ‘$user’, userid = ‘$userid’, address=’$address’, tel=’$tel’ WHERE User = ‘$searchuser’ AND userid = ‘$searchuserid’ AND address=’$searchaddress’ AND tel=’$searchtel’”;

Assuming you aren’t using the same variables for the SET and the WHERE that will work. If you use the same variables like you have done in your query it wont give you any rows to modify

So basic overview

SELECT *, <column1>, <some more columns> FROM <Table> WHERE <any valid expression>

UPDATE <table> SET <column> = ‘value’ WHERE <any valid expression>

INSERT INTO <Table> (<list of columns>) VALUES (<list of values)

DELETE FROM <table> WHERE <any valid expression>

Examples:

SELECT column1, column2, column3 FROM table1 WHERE (column1 LIKE ‘%hello%’ AND column2 = 3) OR column3 = -1

UPDATE table1 SET column1 = ‘hello’ WHERE column2 = 1 OR column3 = 1

INSERT INTO table1 (column1, column2, column3) VALUES (‘hello’, 50, -1)

DELETE FROM table1 WHERE column2 + 2 = 2 – (expression, add 2 to column2!)

10

Thanks for your great explanation, Charleh :smiley: I have copied all your words into a textfile for future reference! Thank you so much again. Have a nice day.