checking row value and if certain value update a diff row

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
fresh
Forum Contributor
Posts: 259
Joined: Mon Jun 14, 2004 10:39 am
Location: Amerika

checking row value and if certain value update a diff row

Post 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 :)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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..
User avatar
fresh
Forum Contributor
Posts: 259
Joined: Mon Jun 14, 2004 10:39 am
Location: Amerika

hey

Post 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}'
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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`
User avatar
fresh
Forum Contributor
Posts: 259
Joined: Mon Jun 14, 2004 10:39 am
Location: Amerika

oh

Post 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 :)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.. :)
User avatar
fresh
Forum Contributor
Posts: 259
Joined: Mon Jun 14, 2004 10:39 am
Location: Amerika

hey

Post 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 :)
User avatar
johnperkins21
Forum Contributor
Posts: 140
Joined: Mon Oct 27, 2003 4:57 pm

Post by johnperkins21 »

It looks like you can possibly do an elseif as well.

http://dev.mysql.com/doc/mysql/en/IF_Statement.html
User avatar
fresh
Forum Contributor
Posts: 259
Joined: Mon Jun 14, 2004 10:39 am
Location: Amerika

hey

Post 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:
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

fresh, you need to take away the comma in front of the WHERE keyword
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

:oops: heh
User avatar
fresh
Forum Contributor
Posts: 259
Joined: Mon Jun 14, 2004 10:39 am
Location: Amerika

haha

Post by fresh »

feyd, I feel your pain :oops:

thanks weirdan & feyd :)
Post Reply