Hi,
Say, I am building a small gaming portal where people can play small games and score points.
a player can play different games and the scores are logged in.
now, given a user ID, how do I build my dataset such that we can retrieve a table in this sort
user_id | game_id | rank
now, the rank is relative just to that game.
i'm finding it extremely hard, that is why i am calling for some help.
this far, i've been able to fetch a similar table for a user only when the game_id was provided. I used variables in mysql for my small dysfunctional query.
8 3806
odd that nobody replied to this. it was definitely something interesting.
anyways, i found this link to be very useful for the purpose Emulating Analytic (AKA Ranking) Functions with MySQL: Part 2
as for my case, i had information spread over several tables, so in the end my query was something like - 3 nested select statements, a couple of inner joins and use of some variables inside 'if' clause. quite complex for my standards.
and then cropped up another problem - the query was working great, but not when used through php. after wasting much of my time, i found that it requires a variable to be initialized beforehand as well. dunno why.b
pbmods 5,821
Recognized Expert Expert
now, given a user ID, how do I build my dataset such that we can retrieve a table in this sort
user_id | game_id | rank
SELECT `user_id`, `game_id`, `rank` FROM `table` ORDER BY `rank` ASC;
as for my case, i had information spread over several tables, so in the end my query was something like - 3 nested select statements, a couple of inner joins and use of some variables inside 'if' clause. quite complex for my standards.
and then cropped up another problem - the query was working great, but not when used through php. after wasting much of my time, i found that it requires a variable to be initialized beforehand as well. dunno why.b
Try creating a view. That will make your life a lot easier. http://dev.mysql.com/doc/refman/5.0/en/create-view.html
SELECT `user_id`, `game_id`, `rank` FROM `table` ORDER BY `rank` ASC;
That is the problem. 'rank' is not any column in my schema, and it would keep changing with newer game scores. for a simple ordering of players for all the games in order of their scores, i can use - SELECT `game_id`, `user_id`, `score` FROM `table` ORDER BY `score` GROUP BY `game_id`
which should give me something given below - except for the last row
game_id | user_id | score | rank
---------------------------------------------
1.............. ..17........200 0......1
1.............. .. 6.........1820. .....2
1.............. .28........1500 .......3
2.............. .16........3500 .......1
2.............. .19........2500 .......2
The last row is the biggest problem itself. MySQL doesnt have any built-in function for that, afaik. The link I provided in my last post agrees as well.
Try creating a view. That will make your life a lot easier. http://dev.mysql.com/doc/refman/5.0/en/create-view.html
Thanks for that. I'll definitely use views the next time I create complex queries.
pbmods 5,821
Recognized Expert Expert
That is the problem. 'rank' is not any column in my schema, and it would keep changing with newer game scores. for a simple ordering of players for all the games in order of their scores, i can use - SELECT `game_id`, `user_id`, `score` FROM `table` ORDER BY `score` GROUP BY `game_id`
which should give me something given below - except for the last row
game_id | user_id | score | rank
---------------------------------------------
1.............. ..17........200 0......1
1.............. .. 6.........1820. .....2
1.............. .28........1500 .......3
2.............. .16........3500 .......1
2.............. .19........2500 .......2
Ah. Well, look at it this way:
Your scores are already ranked; all you have to do is write the PHP to assign a value to each row when you display it.
Something like this:
[PHP]
$lastGame = null;
$gameRank = 1;
foreach($result as $row => $data)
$data['rank'] = (($data['game_id'] == $lastGame)
? $gameRank++
: (int)(($gameRan k = 1) && ($lastGame = $data['game_id']))
);
[/PHP]
Sorry for replying this late, I was held up in some other work, and almost forgot about this.
Ah. Well, look at it this way:
Your scores are already ranked; all you have to do is write the PHP to assign a value to each row when you display it.
Something like this:
[PHP]
$lastGame = null;
$gameRank = 1;
foreach($result as $row => $data)
$data['rank'] = (($data['game_id'] == $lastGame)
? $gameRank++
: (int)(($gameRan k = 1) && ($lastGame = $data['game_id']))
);
[/PHP]
My scores appear ranked (without the actual rank provided), but only for that simple query. The problem is backtracking when you have just the user ID and wish to display the game info, and user's rank (if he/she's played the game).
If I use something similar to what you suggest, and use a counter in my script to calculate ranks, then it will get extremely, extremely bloated. For larger databases, it would mean that I would have 99% of redundant data.
Emulating the oracle ranking functions, as suggested earlier seems the best way out.
pbmods 5,821
Recognized Expert Expert
If I use something similar to what you suggest, and use a counter in my script to calculate ranks, then it will get extremely, extremely bloated. For larger databases, it would mean that I would have 99% of redundant data.
You could set up a function in an include file, or work it into your data-abstraction-layer class....
Of course, you could also just use a stored routine like the one you mentioned in your OP. :)
I just tend not to like stored routines because they're less portable than php scripts (though you can add a dumpfile to your version control repository... but you have to manually create it first... grr).
Atli 5,058
Recognized Expert Expert
Hi.
If you only want to get the ranking of a single user for a single game, could you not do something like this? -
-
SELECT
-
COUNT(GameID) AS 'rank' FROM guTable
-
WHERE
-
GameID = 1
-
AND
-
Score >=
-
(
-
SELECT MAX(Score)
-
FROM guTable
-
WHERE UserID = 1
-
AND GameID = 1
-
)
-
-
Atli 5,058
Recognized Expert Expert
Changed the thread title, to better reflect its contents.
MODERATOR
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Simon |
last post by:
I'm doing a survey.
When do you think GNU/Linux will be ready for the average Joe? What
obstacles must it overcome first?
|
by: Neo-LISPer |
last post by:
Hey
Recently, I researched using C++ for game programming and here is what
I found:
C++ game developers spend a lot of their time debugging corrupted
memory. Few, if any, compilers offer completely safe modes.
Unsurprisingly, there is a very high failure rate among projects using
C++ for modern game development.
|
by: Mark |
last post by:
I am designing a game for a forum. When the user has finished playing
I need to save their data to a cookie then navigate to a page which
holds their score data (I can't have both sets of data on the same
page because I can't control the forum design). The score data is
updated with the results held in the cookie and the cookie is deleted.
I need to stop the user just typing for example
javascript:document.cookie="myScore=1000000" into the...
|
by: Ben |
last post by:
I believe I am missunderstanding how subqueries work. I simple
subquery works fine but when I wish do compare 2 or more fields at
once I don't get the results I wish.
Table A
ID First Last Other
1 A Z 1
2 B Y 2
3 C Z 3
|
by: news.microsoft.com |
last post by:
I have a user control with four option buttons depending which option button
is pressed I store a score into session object. On the load event of a
control the session object is set to 0. There is an optionchecked handler
that handles option1.checked,option2,checked etc . This is supposed to
update the sessionobject with a score ie 100
A beak point on the handler shows it is never reached. when this control is
placed on a page
Any...
| |
by: ώε↮øй |
last post by:
I have the whole group of .NET 2003 platforms sitting here from a dream I
have of building some simple web based games (much like on msn zone)
Any guidance as to where I start and what I need to learn to make even the
simplest clone of checkers or other simple games?
I'm completely lost in the ocean on where to start here
thanks for the help
|
by: 60325 |
last post by:
This is the page where I collect the data in drop-down boxes with
values of 1-10 and send it to a submitted page to do calculations.
Example:
Employee1 TeamScore(1-10)
Employee2 TeamScore(1-10)
Employee3 TeamScore(1-10)
Employee4 TeamScore(1-10)
Then I submit this page with all the values in TeamScore for every
employee and I want to
perform a calculation based on the values in the drop-down and a
|
by: ReneHernandez |
last post by:
Good afternoon,
I am trying to calculate an average over multiple fields. I've tried using the following
=Avg(IIF( = "NA",Null, Val())) + Avg(IIF( = "NA",Null, Val())) + Avg(IIF( = "NA",Null, Val()))
The above, , , etc. are text fields pertaining to an evaluation rating scale:
NA 1 2 3 4 5
|
by: gaya12 |
last post by:
In ASP and using Ms access.
I have a form which asks users to input some details. The details entered by the user will stored in a access database and also there is a calculation based on the input. The results of the overall score should display to the user inmediatly and that result also should be stored in the database, I will explain this by a small example.
Age : ............
Years as Employee : .........
If the user enter age as...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |