SQL UPDATE JOIN Syntax Error ?

[PHPFAN10]

Hello,

 

I have an SQL statement that is failing.

 

Basically it needs to UPDATE `user_activation_key` to NULL on the `users_status` table but also UPDATE `user_status` on the `users` table based on the condition that the activation key (which is taken from a $_GET) matches an activation key in the `users_status.user_activation_key` and that the `users_status.user_uid` also matches the user id in the `users.user_uid`.

 

Probably best to just show the SQL statement, i am new to joins so not good yet at knowing how to write the SQL statements properly and correctly.

 

                UPDATE
                  users.user_status,
                  users_status.user_activation_key
                SET
                  user_status = ".USER_STATUS_ACTIVE.",
                  user_activation_key = NULL
                JOIN
                  users
                ON
                  users_status.user_activation_key = ?
                AND
                  users_status.user_uid = users.user_uid LIMIT 1

 

When i visit the script in my browser the SQL statement fails as i have written it incorrectly and not sure what i am doing wrong, here is the error my catch block and exception error handler logs:

 

[15/04/2012 16:06:30] - exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 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 'JOIN
                  users
                ON
                  users_statu' at line 7' in C:\wamp\www\user-verify.php:73
Stack trace:
#0 C:\wamp\www\user-verify.php(73): PDOStatement->execute(Array)
#1 {main}

 

Thanks for any help!

[jbonnett]

Try this...

 

                UPDATE
                SET
                  users.user_status = ".USER_STATUS_ACTIVE.",
                  users_status.user_activation_key = ?,
                  users_status.user_uid = users.user_uid
                JOIN
                  users_status
                LIMIT 1

 

The syntax is...

                UPDATE
                  table1
                LEFT JOIN
                  table2
                ON
                  table2.column = table1.column
                SET
                  table1.column1 = newvalue
                WHERE
                  table2.column
                IS NULL
                LIMIT 1

[PHPFAN10]

Hi,

 

This query would not work because there is no ON clause.

 

It can only be allowed to update based on the condition that the `user_activation_key` in the `users_status` can be found based on value from $_GET and also based on the condition that the `user_uid` in the `users_status` table matches the `user_uid` on the `users` table,  these condtions need to match. The query you gave has no ON clause.

 

Thanks for your help.

[jbonnett]

Ok then try this...

                UPDATE
                  users
                JOIN
                  users_status
                ON
                  users_status.user_activation_key = ?
                SET
                  users.user_status = ".USER_STATUS_ACTIVE.",
                  users_status.user_activation_key = NULL
                AND
                  users_status.user_uid = users.user_uid LIMIT 1

[jbonnett]

Or you can try something along the lines of...

                ON
                  users.users_status = users_status.user_activation_key

[PHPFAN10]

Hi,

 

I run the query in Navicat and entered an activation key that i knew existed in database to see if it would work and got the following error:

 

[Err] 1221 - Incorrect usage of UPDATE and LIMIT

 

UPDATE
`users`
JOIN
`users_status`
ON
`users_status.user_activation_key` = '959e1a8dc42fd836a84160a7ec45ad12221fef3c'
SET
`users.user_status` = 1,
`users_status.user_activation_key` = NULL
AND
`users_status.user_uid` = `users.user_uid` LIMIT 1

 

Thank you,

phpfan

[jbonnett]

Do you actually need LIMIT as the query is only dealing with one UID as the UID is unique there is only 1? 

 

UPDATE
`users`
JOIN
`users_status`
ON
`users_status.user_activation_key` = '959e1a8dc42fd836a84160a7ec45ad12221fef3c'
SET
`users.user_status` = 1,
`users_status.user_activation_key` = NULL
WHERE
        `users.user_uid` = 

[PHPFAN10]

Hi,

 

If i remove the LIMIT 1, i get the following error:

 

[Err] 1054 - Unknown column 'users.user_status' in 'field list'

 

Now this table does 100% infact exist on the `users` table. I think it is trying to look in the wrong place maybe in the query 

 

Thanks

phpfan

[jbonnett]

UPDATE
`users`
JOIN
`users_status`
ON
`users_status.user_activation_key` = '959e1a8dc42fd836a84160a7ec45ad12221fef3c'
SET
`users.user_status` = 1,
`users_status.user_activation_key` = NULL
WHERE
        `users.user_uid` = 

[PHPFAN10]

DELETE

[PHPFAN10]

I dont know what happened then,

 

I tried with the WHERE clause aswell and got same error.

 

[Err] 1054 - Unknown column 'users.user_status' in 'field list'

 

UPDATE
`users`
JOIN
`users_status`
ON
`users_status.user_activation_key` = '959e1a8dc42fd836a84160a7ec45ad12221fef3c'
SET
`users.user_status` = 1,
`users_status.user_activation_key` = NULL
WHERE
`users_status.user_uid` = users.user_uid

 

Thanks

phpfan

[PHPFAN10]

YAY!

 

This worked, thank you!

 

I remove the ticks ` and its all dandy now 

 

UPDATE
users
JOIN
users_status
ON
users_status.user_activation_key = '959e1a8dc42fd836a84160a7ec45ad12221fef3c'
SET
users.user_status = 1,
users_status.user_activation_key = NULL
WHERE
users_status.user_uid = users.user_uid

 

Thank you!

phpfan

[jbonnett]

Great!! what is it that your working on?

[PHPFAN10]

Well, it was working to some extent but not quite now in my actual php code for some reason, getting an error:

 

 

[15/04/2012 18:24:34] - exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '' for key 'user_activation_key'' in C:\wamp\www\user-verify.php:53
Stack trace:
#0 C:\wamp\www\user-verify.php(53): PDOStatement->execute(Array)
#1 {main}

 

Not sure why as the user activation key column is unique so not sure what it means Doh!

 

but to answer your question,

Great!! what is it that your working on?

i did build a fully pledged member system site when i first started to learn PHP, since then i have learnt a ton and decided to do another membership site based on web development, but with better database design, more efficent coding, using PHP PDO etc, not learnt OOP yet and won't be until i feel i have learnt more about PHP PDO first.

[jbonnett]

Before we go any further do you want a ready built one if so

download:http://evolt.org/node/60384/

mods:http://jpmaster77forum.conceptbb.com/f4-hacks-and-modifications

 

and what I created using it

http://jb-server.co.uk/websites/George%20Little/

[PHPFAN10]

Hi,

 

I will take a look so i can study the code and learn from it but the only way to learn is by doing it yourself i love a challenge, i could use many scripts but love the challenge doing it myself.

 

But thanks for the links will take a good look at them

 

Also will mark this topic solved as know why im getting that error.

 

Thanks

phpfan

[jbonnett]

I learnt a lot from this script, it took me a long time to find as well.

[jbonnett]

I think I know why its not working where "ON" and "SET" for "users_status.user_activation_key =" is both = nothing e.g.

 

UPDATE
users
JOIN
users_status
ON
users_status.user_activation_key = ' '
SET
users.user_status = 1,
users_status.user_activation_key = NULL
WHERE
users_status.user_uid = users.user_uid

 

are you using ".USER_STATUS_ACTIVE." again if so where is the variable sign ".$USER_STATUS_ACTIVE." or ".$_GET[uSER_STATUS_ACTIVE]."

[PHPFAN10]

Hi,

 

I did put a key in the quotes intentially on this line users_status.user_activation_key = ' ' i put a key in it knowing it exists in database aswell, this part .USER_STATUS_ACTIVE.", that is a constant from the constants.php file and is included and does work.

 

I have asked on another site and i we are getting close to the problem but thanks for helping, much appreciated!

 

Cheers!

phpfan.

 

PS: Yeah reading other peoples scripts is a great way to learn web development and then testing yourself is the only real way you will ever get to understand how something works, hence why i love the challenge.

 

[jbonnett]

Here's something I just made don't know if it works, should do though

 

create a new table called user_activation

CREATE TABLE user_activation
(
id int,
user_uid int,
user_activation_key varchar(150)
)

and then

ALTER TABLE users
ADD activation_status varchar

 

sql

 

UPDATE
users
JOIN
user_activation
ON
user_activation.user_activation_key = '.$_REQUEST[user_activation_key].'
SET
users.activation_status = 'activated'
WHERE
user_activation.user_uid = '.$_REQUEST[uid].'

then make another statement with some php after the first query

if(!mysql_query($sql)){
not done
}else{
$sql2 = "
DELETE
FROM
        user_activation
WHERE
        id = '.$_REQUEST[id].'
";
}

 

then just add the second query

 

mysql_query($sql2);

[PHPFAN10]

Here's something I just made don't know if it works, should do though

 

create a new table called user_activation

CREATE TABLE user_activation
(
id int,
user_uid int,
user_activation_key varchar(150)
)

and then

ALTER TABLE users
ADD activation_status varchar

 

sql

 

UPDATE
users
JOIN
user_activation
ON
user_activation.user_activation_key = '.$_REQUEST[user_activation_key].'
SET
users.activation_status = 'activated'
WHERE
user_activation.user_uid = '.$_REQUEST[uid].'

then make another statement with some php after the first query

if(!mysql_query($sql)){
not done
}else{
$sql2 = "
DELETE
FROM
        user_activation
WHERE
        id = '.$_REQUEST[id].'
";
}

 

then just add the second query

 

mysql_query($sql2);

 

Hi,

 

Thanks for your time and effort, i marked this topic solved as i am getting help somewhere else on it now, dont mean to sound nasty or anything by that.

 

Something on what you posted:

 

activation key is always 40 characters in length as i use sha1, therefore because it will always be the same length you would use CHAR(40) not VARCHAR(150) as your wasting space and although i don't know all the technicalities, it's poor practice/design and to my knowledge can slow down the database.

 

Using $_REQUEST, now i'm no expert but i always learnt to avoid using $_REQUEST as much as possible, i use $_POST, $_GET, $_COOKIE individually when needed.

 

Also i am not using the old mysql, i am using PHP PDO.

 

But thanks, still appreciate all your time and effort.

 

phpfan