OR & AND in DB enquiry - got the logic right?

[Callan S.]

Does the following make sure either skill is higher than 200 or skill points higher than 600, but it must by from an ID other than than $playerid?

Code: Select all

SELECT id,name FROM players WHERE skill>=200 OR skillpoints>=600 AND id <> '$playerid' order by rand() limit 1

I guess there may be efficiency tricks I haven't done as well. I will consider them if anyone has any and they aren't too scary - dealing with my code is scary enough without multi line complex yet efficient code to think about as well!

[Jackolantern]

Quick question: Do you want both of the skillpoints clauses to be attached to the AND, or just the >= 600 part? This has actually hit into something I have never come across, which is a complex conditional statement in SQL. As it looks to me right now, I think the id <> $playerid part will only apply to the >= 600 part. I am not even sure, because I don't know if MySQL queries short circuit (ie I don't know if the first skillpoints part will satisfy the requirements, the way that only one side of an || statement in PHP will).

However, you could be sure to get around this by changing both of those <= and >= to a BETWEEN clause so you can condense that part into one clause and the AND will definitely apply to the whole thing.

Although I would be interested to know exactly how this query would work. MySQL: short circuiting? Yay or nay?

[Callan S.]

Yep, short circuiting - it is selecting an id that matches $playerid.

Not sure what you mean by between - I'm checking two different values (skill and skillpoints)?

I'm guessing I might have to do something like have two DB query lines, one with skill and one with skillpoints. Then randomly choose which line I use - and if it doesn't come up with a result, use the other line.

Or might be able to use brackets?

Edit: Yeah, brackets seem to work! No more selecting the current player (ah, I can finally stop beating myself up about it...)

I'm coding in a sort of attack skill system where one of your skills will be weak (or all of your skills will be average) and it actually shows an opponent before you fight them, so you have a chance of remembering which is their weak skill. Ohh, almost turn based combat (not that I didn't know how to do that before, I just didn't see a point).

[Winawer]

You should make the order explicit by using brackets.

[Jackolantern]

Oh crap! I didn't see that they were two different stats! Yes, you should use brackets then. I have never had to use them before, but they are there for this very reason