Page 1 of 1

SQL query

Posted: Wed Aug 23, 2006 8:02 pm
by Dave2000
I have two questions. First...

Code: Select all

mysql_query("SELECT * FROM `users` WHERE `username` = '$username' AND `password` = '$password' LIMIT 0, 1 ");
Above is s simple SQL statement. What is the significance of inclosing the table field names in ` ? I ask because i sometimes see it done, and sometimes not.


Second question...

Code: Select all

$query = "SELECT userid,attack FROM stats ORDER BY attack DESC";
$result = mysql_query($query);
$num = mysql_numrows($result);
$i = 0;
while ($i < $num) {
$userid = mysql_result($result,$i,"userid");
$rank = $i + 1;
$query = "UPDATE stats SET attack_rank = '$rank' where userid = '$userid'";
mysql_query($query);

$i++;
}
I am making a game. I am trying to rank the field "attack" and insert the ranks into another field called attack_rank in the table. However, i can see myself ending up with a huge number of queries - especially as i have several other stats to rank each user for.

Is it possible to maybe, for each row, as the loop loops round, to add a little update string to an overall update string, which will, once ALL the loop has finished then update the database with a single query. I hope this makes sense. Or is there any other way to do this in a single query.

Thanks for any help

Shears :)

Re: SQL query

Posted: Wed Aug 23, 2006 8:18 pm
by feyd
Shears wrote:I have two questions. First...

Code: Select all

mysql_query("SELECT * FROM `users` WHERE `username` = '$username' AND `password` = '$password' LIMIT 0, 1 ");
Above is s simple SQL statement. What is the significance of inclosing the table field names in ` ? I ask because i sometimes see it done, and sometimes not.
It's considered a best practice to always backtick (that's what it's called in php) your database, table and field references. Why should you? There are times when you could wish to use a database keyword as a fieldname. Some versions of the same database brand will let you, some will not. Since backticking the reference seems to always work no matter the settings, we recommend backticking. From our fingers to your eyes. :)

Shears wrote:Second question...

Code: Select all

$query = "SELECT userid,attack FROM stats ORDER BY attack DESC";
$result = mysql_query($query);
$num = mysql_numrows($result);
$i = 0;
while ($i < $num) {
$userid = mysql_result($result,$i,"userid");
$rank = $i + 1;
$query = "UPDATE stats SET attack_rank = '$rank' where userid = '$userid'";
mysql_query($query);

$i++;
}
I am making a game. I am trying to rank the field "attack" and insert the ranks into another field called attack_rank in the table. However, i can see myself ending up with a huge number of queries - especially as i have several other stats to rank each user for.

Is it possible to maybe, for each row, as the loop loops round, to add a little update string to an overall update string, which will, once ALL the loop has finished then update the database with a single query. I hope this makes sense. Or is there any other way to do this in a single query.

Thanks for any help

Shears :)
Why store these stats at all when your select for it works just as well and is always up-to-date.

Posted: Wed Aug 23, 2006 8:47 pm
by AKA Panama Jack
About the only real reason to use backticks is because you are using MySql reserved words as field names.

http://dev.mysql.com/doc/refman/5.0/en/ ... words.html

The above is a list of reserved words used by MySql and they should never be used as field or table names.

Also, the use of backticks on field names is NOT supported by most databases. So if you are wanting to support more database types other than MySql then you should never use backticks in a query.

Basically backticks on fieldnames in MySql is a quick fix for people who used reserved words in the past for field names. You could do it with older versions of MySql and get away with it but the later versions of MySql 4 and all versions of MySql 5 do not allow it without backticks.

Re: SQL query

Posted: Wed Aug 23, 2006 8:49 pm
by Dave2000
feyd wrote:Why store these stats at all when your select for it works just as well and is always up-to-date.
Thank you feyd for your answer to my first question. Why do i need to store them? Good question. Sorry, i didn't give you enough background to the problem in my first post. I have several stats that i wish to rank(eg.attack, defence, spy) - and then, from the ranks of each of the stats, find an average rank. Each user will be given an overall rank based on their average rank. I hopes this makes sense. Maybe, maybe there is some other way i could structure the code, such that i dont need so many queries. If there is i haven't thought of it yet. In the full code i have ATM, for each user in the game there will be four queries required - this could turn into a collisal load on the game. There are also other places, such as when user's login, they can see an overview of their account - and i wish for their stats to be displayed there.

Posted: Wed Aug 23, 2006 9:04 pm
by feyd
An individual's rank would be something like

Code: Select all

SELECT COUNT(field)
FROM usertable
WHERE usertable.field < '$currentLevel'
Those could be combined into a union with the various other attributes you wish to pull rank data for as well. I don't see that kind of query taking all that long. Averaging the results would be fairly simple too: pull out the results, average them.

Posted: Wed Aug 23, 2006 9:41 pm
by paladaxar
wow...feyd says one thing:
It's considered a best practice to always backtick
and panama jack says the exact opposite:
you should never use backticks in a query
I have always trusted the things that feyd says, but panama's argument makes more sense here. Who is right?

For now, I am going to continue NOT backticking my table names. I have been reading "Learning SQL" by O'reilly and they dont backtick any names, but they dont specifically address why they dont.

Posted: Wed Aug 23, 2006 9:49 pm
by feyd
If you don't ever use keywords, you're fine in not backticking. That's all that matters. I don't really care if I'm "wrong" on this. PJ works on more databases than I often do.

Posted: Wed Aug 23, 2006 9:57 pm
by paladaxar
Cool. Thanks. No one knows everything about everything. We're all learning and growing with the technologies that we and others like us create.

Posted: Thu Aug 24, 2006 9:30 am
by Dave2000
feyd wrote:An individual's rank would be something like

Code: Select all

SELECT COUNT(field)
FROM usertable
WHERE usertable.field < '$currentLevel'
Those could be combined into a union with the various other attributes you wish to pull rank data for as well. I don't see that kind of query taking all that long. Averaging the results would be fairly simple too: pull out the results, average them.
I dont see why COUNT is needed. :?

Is it possible to update more than one row in the same query? And if so, how?

Thank you

Shears

Posted: Thu Aug 24, 2006 9:38 am
by feyd
Shears wrote:I dont see why COUNT is needed.
The posted example would be used instead pulling information from an unnecessary statistics table.
Shears wrote:Is it possible to update more than one row in the same query? And if so, how?
Your question is answered on the update pages of the manual: http://dev.mysql.com/doc/refman/4.1/en/update.html

Posted: Thu Aug 24, 2006 9:43 am
by Dave2000
Thank you. But i am unable to view the manual. I just get a blank/white screen - which it has been doing now for the last few weeks. I dont know why it doesn't display - it used to work fine.

Shears

Posted: Thu Aug 24, 2006 9:52 am
by feyd
Shears wrote:Thank you. But i am unable to view the manual. I just get a blank/white screen - which it has been doing now for the last few weeks. I dont know why it doesn't display - it used to work fine.
Strange. Can you view other pages on the site?

http://dev.mysql.com/doc/#manual
perhaps http://downloads.mysql.com/docs/refman- ... hapter.zip or http://downloads.mysql.com/docs/refman- ... ter.tar.gz?

Posted: Thu Aug 24, 2006 10:11 am
by Dave2000
OK. Thank you. They helped me to see the page. I've read through the page a few times now, but i dont think it covers the what i would like to do.

Some update queries. Is it possible to combine them into one query?

Code: Select all

mysql_query("UPDATE stats SET attack_rank = '33' where userid = '1'");

mysql_query("UPDATE stats SET attack_rank = '43' where userid = '2'");

mysql_query("UPDATE stats SET attack_rank = '18' where userid = '3'");

Posted: Thu Aug 24, 2006 10:20 am
by feyd
Unfortunately, I don't think so.