Leaderboard based on stringy array in database

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
Karlw
Forum Newbie
Posts: 4
Joined: Tue Feb 23, 2010 7:21 pm

Leaderboard based on stringy array in database

Post by Karlw »

Hi there, im new here. My name is Karl, im 21 years old and have been using PHP since as long as i can remember.

I joined this forum hoping that I could get a little help

Basically, a website that I help do php coding for, has a feature where they can read a virtual item 'Book'.

The database is setup like the following :-

table: booksread

id / user_id / bookslist / bookstotal




bookstotal is an int that basically +1 everytime a book is read. Easy to order for a leaderboard for that.

bookslist is only added to when a unique book is read, it takes the id number of the book and appends it to the bookslist in the following form :-

concat('.$itemid.',bookslist)

So a books read would look something similar to :-

.23..35..13..11..1.163..1456..234..356..833.


I was thinking that I could order the rows in the query by the string length of the bookslist value,but could cause errors in the following way :-

.1..2.
.14245.

That scenario would think that the bottom one is longer than the top one.


Is there anything out there I could do to make it so that I can order this correctly?

Thanks in advance

Karl
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Leaderboard based on stringy array in database

Post by requinix »

The problem is the database, not the code.

Get rid of the "bookslist" field. Instead, create another table with two fields: id/user id and book id. Each row in that new table corresponds to a certain book a certain user has read.

See where that's going?
Karlw
Forum Newbie
Posts: 4
Joined: Tue Feb 23, 2010 7:21 pm

Re: Leaderboard based on stringy array in database

Post by Karlw »

Hi there,

thank you for your reply.

Yeah that is the logical solution, the only problem is that with having 10,000 users, and 1,000 books,

That could potentially mean that 10,000 x 1,000 = 10,000,000 rows in the new table..

Im thinking that probably the best approach is maybe add an extra INT to the current table totalling the number of uniques, simlar to the booknum INT


Thank you for your help ^_^
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Leaderboard based on stringy array in database

Post by requinix »

Karlw wrote:That could potentially mean that 10,000 x 1,000 = 10,000,000 rows in the new table..
Yeah... And?

Databases are meant to handle that much data. It's supposed to happen. In fact, 10M rows isn't that much in the grand scheme of things.
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Leaderboard based on stringy array in database

Post by AbraCadaver »

Karlw wrote:Hi there,

thank you for your reply.

Yeah that is the logical solution, the only problem is that with having 10,000 users, and 1,000 books,

That could potentially mean that 10,000 x 1,000 = 10,000,000 rows in the new table..

Im thinking that probably the best approach is maybe add an extra INT to the current table totalling the number of uniques, simlar to the booknum INT


Thank you for your help ^_^
Take tasairis's advice and do it quickly. Your current implementation will cause you never ending grief (or whoever comes after you).
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Karlw
Forum Newbie
Posts: 4
Joined: Tue Feb 23, 2010 7:21 pm

Re: Leaderboard based on stringy array in database

Post by Karlw »

tasairis wrote:
Karlw wrote:That could potentially mean that 10,000 x 1,000 = 10,000,000 rows in the new table..
Yeah... And?

Databases are meant to handle that much data. It's supposed to happen. In fact, 10M rows isn't that much in the grand scheme of things.

Brilliant thankyou :) Have followed you advice


I always just assumed that a larger table = alot to process


Karl
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Leaderboard based on stringy array in database

Post by requinix »

Karlw wrote:I always just assumed that a larger table = alot to process
That's true, but database engines optimize for this kind of lookup table. As long as you put indexes on the two IDs (or declare as foreign keys) everything will work well.
Karlw
Forum Newbie
Posts: 4
Joined: Tue Feb 23, 2010 7:21 pm

Re: Leaderboard based on stringy array in database

Post by Karlw »

Ah i never realised that.

Thankyou so much for your help!
Post Reply