Page 1 of 1

checking row value and if certain value update a diff row

Posted: Wed Jul 14, 2004 4:38 pm
by fresh
hey,

heres my current script:

Code: Select all

$sql = "UPDATE `users` SET `points` = `points`+ {$pts} WHERE `username` = '{$uname}'";
@mysql_query($sql) or die(mysql_error());
as you can see Im writing the value of $pts to the points row of a specified user, via the var $uname... my question is, once the points have been added, I need a script that checks that same points row, for that same user and if the points are lets say higher than 1000 then, the script I need would update the row, rank, replacing the default value of new guy, to lets say big guy, or something... can some one show me some example scripts for this??? thanks in advance :)

Posted: Wed Jul 14, 2004 5:06 pm
by feyd
(untested)

Code: Select all

UPDATE `users` SET `points` = `points` + {$pts}, `rank` = IF( `points` >= 1000, 'big boy', `rank` ) WHERE `username` = '{$uname}'
although I wouldn't store their rank directly in the tables. It can be 'join'-ed when a query is done on the user..

hey

Posted: Wed Jul 14, 2004 5:16 pm
by fresh
alright that works :) thank you... so why wouldnt you throw that in the db?? and also, is it possible to add multiple if statements to that same query string?? I need to check to see if they qualify for atleast 3 more rank levels... thanks feyd :)

would it be like this:

Code: Select all

UPDATE `users` SET `points` = `points` + {$pts}, `rank` = IF( `points` >= 1000, 'big boy', `rank`) || IF(`points` >= 2000,'im a man now', `rank` ) WHERE `username` = '{$uname}'

Posted: Wed Jul 14, 2004 6:01 pm
by feyd
I'd have a table for ranks, storing the name, and qualification required for it. i.e.

Code: Select all

`rank_title` VARCHAR(50),
`rank_req` INT(10) UNSIGNED
and of course your users table..
then in a select (stab in the dark here)

Code: Select all

SELECT u.*, r.`rank_title` `rank`,MAX( r.`rank_req` ) FROM `users` u LEFT JOIN `ranks` r ON u.`points` < r.`rank_req` GROUP BY r.`rank_req`

oh

Posted: Wed Jul 14, 2004 6:08 pm
by fresh
that seems pretty crazy... i think I will stick with what I have now, lol

hey feyd, could you show me how to add more if statements to that same string (the one above your last post), would I use ||, &&, or else??? and would it need to be on all one line and in the same format??? thanks man :)

Posted: Wed Jul 14, 2004 6:28 pm
by feyd
it's probably more like:

Code: Select all

UPDATE `users`
SET `points` = `points` + &#123;$pts&#125;,
`rank` = IF( `points` >= 3000, 'big boy3', `rank` ),
`rank` = IF( `points` >= 2000, 'big boy2', `rank` ),
`rank` = IF( `points` >= 1000, 'big boy', `rank` ),
WHERE `username` = '&#123;$uname&#125;'
although it may work like this:

Code: Select all

UPDATE `users`
SET `points` = `points` + &#123;$pts&#125;,
`rank` = IF( `points` >= 3000, 'big boy3', IF( `points` >= 2000, 'big boy2', IF( `points` >= 1000, 'big boy', `rank` ) ) ),
WHERE `username` = '&#123;$uname&#125;'
not sure about either though.. :)

hey

Posted: Wed Jul 14, 2004 6:31 pm
by fresh
thanks alot feyd :) I'll give them both a shot, I appreciate the help man ;)

edit: feyd, I tested it and it gave me this error:
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 `username` = 'test'' at line 5
heres my script:

Code: Select all

$sql = "
UPDATE `users` SET `points` = `points` + '{$pts}',
`rank` = IF( `points` >= 20000, 'man', `rank` ),
`rank` = IF( `points` >= 10000, 'teen', `rank` ),
`rank` = IF( `points` >= 4000, 'boy', `rank` ),
WHERE `username` = '{$uname}'";

@mysql_query($sql) or die(mysql_error());
do you see why it may be doing this... thanks :)

Posted: Wed Jul 14, 2004 6:46 pm
by johnperkins21
It looks like you can possibly do an elseif as well.

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

hey

Posted: Wed Jul 14, 2004 6:51 pm
by fresh
thanks for that link, but I dont understand the way they explain the syntax, I need examples because I don't get it otherwise, Im the same way with batch and the way they explain that syntax... :oops:

Posted: Wed Jul 14, 2004 6:59 pm
by Weirdan
fresh, you need to take away the comma in front of the WHERE keyword

Posted: Wed Jul 14, 2004 7:00 pm
by feyd
:oops: heh

haha

Posted: Wed Jul 14, 2004 7:08 pm
by fresh
feyd, I feel your pain :oops:

thanks weirdan & feyd :)