MYSQL :: Scoreboard Ranking System

Ranking System
I've made a forum and i want to have a ranking system with it. I managed to display a postcount but i also want a few images to be displayed, like if you have made more then 50 posts, it displays 50.jpg or something, how can i accomplish this, in another way then just str_replace()ing all the number of posts?
And how do i display 2, like if you have 50 posts, 50.jpg and if you have 60 posts, 60.jpg.

Ranking System: Retrieving Results Around Your Rank
I'm building a ranking system for a game. I'm trying to determine the best way to select a players rank, and then display the records around that players ranking.

For instance:

Username | Rank
steve | 6000
bob | 5000
jane | 4000
chris | 3000
brian | 2000
tim | 1000
molly | 0

Assuming chris is the current player, I want to select his rank, and then run a query that returns the two players ranked higher than him, and the two players ranked lower than him. So the query would return the following:

bob | 5000
jane | 4000
chris | 3000 //I'd rather leave his own rank out.. but it's not bad like this.
brian | 2000
tim | 1000

What I can't figure out how to do, is determine the row that is specific to Chris's rank. I would need to define a starting point (-2 rows from Chris), and an end point (+2 rows from Chris), order by and limit the results... what I can't figure out though, is how to get the specific row number to start from.

For instance, lets say Chris is row 583 of my database... how do I retrieve that number?

Ranking Scores
I need to create a query that will sort a bunch of scores in ascending order and then add a column with their rank code from 1 to however many is in the table.
the link below shows an example image of what i need to happen.

A Ranking Leaderboard
I am trying to design and implement an online leaderboard in MySQL and PHP.  Maybe it is because of lack experience in this field but I am really confused on the basics on how this could be achieved.

Correct me if I'm wrong but data is not stored in any order as such in a SQL db.  It is the query you write/code which does the sorting.  How then is it possible to have a leaderboard with rankings on?

For example if I had a basic table with fields of name and points.

You could write a basic query to display the top 10 (or whatever limit you choose) users by points, ascending or desc, etc.  However if you then have a leaderboard with thousands of users on a leaderboard, this wouldn't be practical.  You could obviously pull an individuals record up by using WHERE and the name.

How would I get SQL to output the users ranking?  Surely I cannot do it with an extra field called ranking as everytime the ranking changed ALL records would need to be updated...

Surely this kind of thing has been done before.  I just can't imagine doing this with SQL due to the nature data is stored and then later sorted.

Ranking Results
Im trying to display baseball team stats, along with the ranking for that particular league.
for example, there are 5 teams in the league, Team A has 12 HR, B has 15 HR, C has 3 HR, D has 3, and E has 20.
I want to show on each individual team page their stat, with the rtanking for the league.

Ranking MySQL
I have a query that extracts the last 7 records from a database based on its criteria and have it ordered by its date. What i want to do is add a column in the select statement and put in the numbers from 1 to 7 into the column in this added column.

I'm using the Space(1) as Number function but i am wondering how do I put 1 to 7 in the table. Its pretty much like a ranking system.

Ranking Users
i'm trying to rank the users in our site.
table contains:
- userid
- userrates
and with thousands of records.
member with the highest rate goes #1, next is #2 and so on.
i want the query to be like:
Code:
<something goes here> WHERE userid = '$userid' order by userrates desc
from the code above it will show the rank of this user.
then the next user logsin and it shows again his/her ranking.
hope someone understands this
any input is appreaciated

Ranking Leaderboard In MySQL!?
I am trying to design and implement an online leaderboard in MySQL and PHP. Maybe it is because of lack experience in this field but I am really confused on the basics on how this could be achieved.

Correct me if I'm wrong but data is not stored in any order as such in a SQL db. It is the query you write/code which does the sorting. How then is it possible to have a leaderboard with rankings on?

For example if I had a basic table with fields of name and points.

You could write a basic query to display the top 10 (or whatever limit you choose) users by points, ascending or desc, etc. However if you then have a leaderboard with thousands of users on a leaderboard, this wouldn't be practical. You could obviously pull an individuals record up by using WHERE and the name.

How would I get SQL to output the users ranking? Surely I cannot do it with an extra field called ranking as everytime the ranking changed ALL records would need to be updated...

Surely this kind of thing has been done before. I just can't imagine doing this with SQL due to the nature data is stored and then later sorted.

Ranking Query (self Join)
I would like to get a rank (row # of ordered results) of an article based on its average rating in relation to all other articles. I have a ratings table that holds all of the individual votes cast by users (ratingID, articleID, userID, rating). So I need to order the articles by AVG(ratings.rating) and then come up with an individual article's row # to find its ranking.

I know this involves joining the ratings table on itself with a greater than or less than operator, but i'm completely lost when it comes to self-joins. I know this is easy, but there is a small trick to it and i'm clueless.

Ranking For Multiple Columns
A query in my application return several columns with amounts, Now i need to add a ranking to all those columns seperatly.

So the result should be something like this:

ID     Amount1  Ranking1  Amount2 Ranking2
----   -------- --------- ------- --------
135     123.34      4       23.00     3
184     160.23      1       60.89     2
845     140.22      3      100.20     1
987     155.00      2        1.20     4

The ID isnt important here.

The only solution i can think of now is to create a (temp) table, populate it with the query, and then sort it for each ranking and fill the ranking one by one.

Is there a way to get the ranking filled in  the same pass as the amounts are calculated?

Sorting And Put Ranking In Field
I have 3 field in my table Ranking, Name and Score. Is it possible to sort all record to order by Score and put Ranking no. to each record by using query not much?

Ranking Student Grade? With Subquery/subselect?
I am a mySQL newbie here and have some problem defining the mySQL 4.0.14
or 3.23 SQL to get student grade ranking where tied grade have the same
rank.

I used to set it through MS Access 2002 and use this kind of query:

SELECT nilai.studentNIS, nilai.studenttestmark,
(SELECT COUNT(*) FROM tblStudentGrades
WHERE [studenttestmark]>[Nilai].[studenttestmark];)+1 AS NomorUrut FROM
tblStudentGrades AS nilai ORDER BY nilai.studenttestmark DESC;

I've been looking around mySQL documentation and read that subquery can
be redefined as INNER JOIN or using two SQL statement via variable? I
have no idea on the basics of how to set it out though.

Could one of you please help give a me a sample on how this kind of
query should be done on mySQL? Is it possible to do it in single line?
And without having to use PHP/Perl scripts?

Or maybe I should have approach it differently?

Full-Text Search: Truncation Operator And Relevance Ranking?
We would like to begin supporting the truncation operator on our website's search engine--however, we still require the results to be sorted by relevance. If a client enters "televis*" into the search engine, and we perform the search in boolean mode, pages containing "television", "televise", "televised", etc will be returned. However, because it is a boolean mode search, all rows will have a relevancy value of 1.

A user-provided "workaround" on the MySQL site said to use the same keywords but NOT in boolean mode to get a usable relevance ranking. However, a standard fulltext search for "televis" or "televis*" will return no rows.

How Do I Get System Date
I have a Timestamp which shows the Date and Time. I would like to change it to just show the Date. How do I do this using phpMyAdmin?

Getting System Date
I was wondering how to set up table to column to get the system date for a date stamp. Could someone help me with this.

I tried
date date SYSDATE

with no luck

Best Way To Set Up Membership System
My site will have users that sign up and can then become part of multiple groups (think similar to Yahoo Groups or something like that). How would be the best way to set up the database/table structure so queries could be run to find both all the groups that a user is a member of and also get membership lists for each individual group?

System Tables?
Is there documentation on the system tables? I couldn't seem to find anything when I did a search on system tables.

I know about the information schema, but I want to know about the tables the information schema uses to build the views it contains.

Registration System
I have my database and HTML forms made. When I click submit to submit the registration it doesnt go to to the database it comes to a page with all of my database stuff on it. Can someone help? I have the page register.html made with the forms on it. When you click on submit its supposed to go to register.php which will send the info to the database. But is doesnt. Here is the register.php code:

System Tables
When I create a new DB it's empty. Are there a base set of system tables / functions and so forth created?

Privilege System
Is there a way to limit update access for a user to a single record? I have a database table that contains various information about people. That information is contained in a record for each person. I want people to have read access to the whole table but update access only to their own record. Is this possible and if so how would I set it up?

System Log For Triggers
Is there a system log file which can be checked to see the histroy of triggers. I am trying to debug a trigger that is not executing when it should.

Rating System
i want to do a rating system like youtube's where it averages out the total ratings. i'd store each of the ratings per each row and have them seperated by comma's then in my code put them in to an array.

i also wanted to store the usernames of the voters in the database in the same fashion (without displaying them though) as above say instead of using cookies (as they can be deleted). would method this be feasible? or would this say overload the database and cause slow down?

Booking System
Hi, does anyone know of a site (tutorialised / open source) that offers a PHP-MySQL Online Booking System (similar to a Hotel Booking System)

Privilege System
Can I change structure on "mysql" database to implement new Privilege System?
Views named "user", "host" etc will return old structure style.

System Variable
i am connected to the server and
i build some tables etc etc. everything is fine.

i run the command "show variables". it executes fine and shows me the values
of all the variables on the server. now if i try and change the value of the
variable tmpdir using the set command, it tells me unknown "error 1193:
system variable tmpdir". help needed here........

another point, the variable "tmpdir", points to the dir c:/windows/temp. i
put a text file in that dir and try to load some data from that file into a
table, and i get an error "file not found".

System Variable.
i am running mysql version 4.0.16-nt. now, i am connected to the server and
i build some tables etc etc. everything is fine.

i run the command "show variables". it executes fine and shows me the values
of all the variables on the server. now if i try and change the value of the
variable tmpdir using the set command, it tells me unknown "error 1193:
system variable tmpdir". help needed here........

another point, the variable "tmpdir", points to the dir c:/windows/temp. i
put a text file in that dir and try to load some data from that file into a
table, and i get an error "file not found".

System.OutOfMemoryException
sql 2 error: Exception of type 'System.OutOfMemoryException' was thrown.

this is the error I am getting using Mysql connect and Visual Studio.

My program is set on a loop and after the program has opened and closed around a million connections in my error logs i see this error about 80 or 90 times then it gives me a timeout error until I stop the loop. If i try to restart it it will continue to fail unless I restart the computer containing the database. Does anyone know what is causing this or how I can fix it?

Edited 1 time(s). Last edit at 07/18/2007 12:39PM by Joe Gates.

Voting System
***********************
| game_id | discussion_id |
***********************

SELECT game_id, discussion_id, count( * ) FROM `games_voting` AS votes
GROUP BY votes.game_id, votes.discussion_id

using the above query to generate a count of votes grouped by game and then user who got the votes
right now it returns all the users that received votes for each game, i'd like to only receive the user with the top vote count

Scoring System
Im sure some of you have come across 'neave' games?

The scoring system is setup as below:
CREATE TABLE games_pacman (
name VARCHAR(20) PRIMARY KEY NOT NULL,
score INT(11) DEFAULT '0' NOT NULL,
ip VARCHAR(15)
);

CREATE TABLE games_banned_ip (
ip VARCHAR(15) PRIMARY KEY NOT NULL
);

Clearly, it is possible to ban ip addresses from using the score system.

But, can anyone tell me please if it is possible to set it up to ban certain names from being used?

System Configuration
I'm currently rebuilding my home system - a couple of linux boxes with Debian.
Although I don't have a high load on any of my systems, I am going to set up a separate box with my mail server and another for my web services. I'm wondering if there is an arguement for or against putting MySQL over on the mail box or even putting it on its own box?Assuming the arguement is for putting MySQL on a different box, are there any pitfalls, tips, tricks I should consider before starting this project?

Heavy Traffic System
I am developing a very heavy traffic site. It may happen that at same time (exactly same) two user will try to register with the same username. What will be the approach to prevent this situation. Which user will be able to register and which one will be denied? Do I need to use any locking facility? If so is it a wise decision to lock entire table?

How To Get Today System Date?
Can anyone tell me how to fire a query in MySQL to get todays system Date.
I want to Subtract yesterday's date from Today's date.I want to check that if todays date is greater than yesterdays date i have to delete yesterdays date.

What Is The Best Content Managment System?
I'd like to speed up queries of the form...

SELECT ... WHERE lower(mytable.name)=?

...by using an index.

Online Booking System
Im working on a dog walking site for my girlfriend and she wants to have an online booking system for walks.

Can i use MySQL & PHP to create this or is the a language more suited to this form of application.

Song Request System
I'm new to MySQL and have been racking my brains at this for hours, studying the MySQL documentation, etc, and have given up.

All I want is some form of PHP/MySQL integration on my website that allows visitors to Reuqest Songs, and my DJ's be able to view all the reuqests in a page.

It'd just involve adding values to a database (song name and song title) then getting a page to query them out and display them again.

Would someone be so kind to write me a very very basic script that will do what I require?

Standardized Naming System ?
is there a common naming system for db objects ?

Like:

1) Tables: mytable, tblmytable, tbl_mytable

2) Indices: idx_anindex

3) Columns: int_somenumber, date_lastupdate

4) id for the numerical primary key e.g. table customers.id
and then for referencing foreign keys
table addresses : addresses.customer_id or addresses.customer_fk

OK, I know I could name them the way I want but perhaps there is some
kind of common sense in this regard ?

System Variable Tx_isolation
The System Variable tx_isolation is used for what? What this actually means,

Development Server

System Variable Value
tx_isolation READ-COMMITTED

Production Server
trx_isolation REPEATABLE-READ

What will be the difference between these two thing?

System Requirements Of MySQL
I need minimum system requirements for installing MySQL server. This is for
service definition. Please provide me the info/link from where I can get the
info.

Recommended System Specs?
I was wondering if anybody could point me to any documentation or could suggest some info on a small MySQL server that I'm setting up.

This will be running Windows and IIS (i know, i know) and be for a maximum of 20 users; nothing special.

Any suggestions for processor and RAM or things to look out for? It will be running the MySQL Community Server 5.0.

How Do I Set Up My System As A Testing Server
I work on a Windows XP Pro. Platform and I am having trouble setting my system up as a Testing Server for PHP pages. I want to be able to test scripts and build my database. I tried downloading and installing MYSQL and PHP but it still does not work. Is there some kind of Configuration I need to do in the PHP.ini file?

System Error 1067
I am just starting mysql and when I go to "net start mysql" I receive this System Error 1067, and that the system could be not started.

Client Login System
I am working on making a client login system. I would like client's to be able to login on my site and just basically see their individual invoices, pay with Paypal, see site progress, that kind of thing. I have some ideas as how to do it, but I was wondering if anyone had some suggestions?

ASP And MySQL Membership System?
I'm trying to set up an ASP and mySQL (or Microsoft Access if absolutely necessary).

It needs to allow users to register, certain pages to be "members only," and it needs to have an email password reminder system.

I have a good deal of beginning experience with coding (I've studied the beginnings of C++, VB, javascript, CSS, and have sort of worked with PHP on a discussion board).

Anyone have a GOOD and somewhat UPDATED and RECENT tutorial?

External System Call
Is it possible to trigger external system calls from mysql db? I want to start an external script after a certain value has been set in DB?

Unkown System Variable
I try to code a store proc. but I always get compiler error:

"unkown system variable 'no_more_data' ".....

Posting System With Time
I need to build a system that posts a message on a given time.
Example

I write a message -> it goes into a db with al the messages posted for the day -> the next day al the messages will be posted in another db for the frontpage at a given time.

So you get msg 1 at 12.00 at the fp
msg2 at 14.00 at 14.00 at the fp
etc.