Page 1 of 1

Leaderboard based on stringy array in database

Posted: Tue Feb 23, 2010 7:35 pm
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

Re: Leaderboard based on stringy array in database

Posted: Tue Feb 23, 2010 8:00 pm
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?

Re: Leaderboard based on stringy array in database

Posted: Wed Feb 24, 2010 6:54 am
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 ^_^

Re: Leaderboard based on stringy array in database

Posted: Wed Feb 24, 2010 9:20 am
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.

Re: Leaderboard based on stringy array in database

Posted: Wed Feb 24, 2010 9:25 am
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).

Re: Leaderboard based on stringy array in database

Posted: Wed Feb 24, 2010 10:30 am
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

Re: Leaderboard based on stringy array in database

Posted: Wed Feb 24, 2010 10:57 am
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.

Re: Leaderboard based on stringy array in database

Posted: Wed Feb 24, 2010 6:00 pm
by Karlw
Ah i never realised that.

Thankyou so much for your help!