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
Leaderboard based on stringy array in database
Moderator: General Moderators
Re: Leaderboard based on stringy array in database
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?
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
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 ^_^
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
Yeah... And?Karlw wrote:That could potentially mean that 10,000 x 1,000 = 10,000,000 rows in the new table..
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.
- 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
Take tasairis's advice and do it quickly. Your current implementation will cause you never ending grief (or whoever comes after you).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 ^_^
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.
Re: Leaderboard based on stringy array in database
tasairis wrote:Yeah... And?Karlw wrote:That could potentially mean that 10,000 x 1,000 = 10,000,000 rows in the new table..
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
I always just assumed that a larger table = alot to process
Karl
Re: Leaderboard based on stringy array in database
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 wrote:I always just assumed that a larger table = alot to process
Re: Leaderboard based on stringy array in database
Ah i never realised that.
Thankyou so much for your help!
Thankyou so much for your help!