Multiple Databases?

[Pi_Mastuh]

I'm trying to make secret forum avatars that people have to unlock but if I have 20 entries per user it's going to clog up my database fast, so I was thinking I could use a 2nd database for avatar info. Is it possible to use multiple databases on a site? if so how? The 2nd database would only be accessed on 2 or 3 pages, but 2 would need to be accessed at once.  :-\

[Wildbug]

Yes, it's possible to use multiple databases.  You just need to use the returned resource from the *_connect() function and reference it in following database functions.  If you're using MySQL, see the mysql_[p]connect() function and note the return value.

 

Alternatively, why don't you just make an avatar table in your existing database?

[roopurt18]

Why not store this information in a second table in the original database?

 

Just create a table for each of the following:

 

users : user information

avatars_avail : the 20 available avatars

avatars_unlckd : each row connects a user to an avatar they've unlocked

[Pi_Mastuh]

My thinking is that with over 100 users and growing and if they've each unlocked 20 (and growing) avatars, it's going to use a lot of database space.

[roopurt18]

That's only 2,000 rows in one table.  MySQL is capable of handling tables with literally hundreds of thousands of rows with great speed.  Not to mention, what's the difference if you have 2,000 rows of data in one database as compared to another?

 

For what you're wanting to do, sticking with a single database is the best solution.  Just organize your tables correctly.

[Pi_Mastuh]

Ok, so when the user unlocks an avatar should I just insert into a table the user's ID and the avatar name/ID?

[pocobueno1388]

Here is a way that could work for you...although I am not sure if it is the best way.

 

Make a new table called "avatars", in that table just put the avatar information for each individual avatar, so there should be 20 rows since you have 20 avatars.

 

Now add a column to the users table called "unlocked_avs" and as they unlock them you could add the ID of the avatar that corresponds with the right avatar in the "avatar" table.

 

So if they unlock avatar #2 the column "unlocked_avs" would just be "2". Then when they unlock another avatar, like #15 the "unlocked_avs" column would look like this:

2,15

 

Then when you want to display them you can use explode to separate the numbers, then it doesn't use as much DB space.

 

I have no idea if this is the best way of doing it...just an idea.

[roopurt18]

pocobuenos idea is one way to go about it, but it's not very efficient in terms of database use.  In order to determine which avatars the user has unlocked you would need to select that row, perform PHP code, and then select more information from the database to get the avatar information.

 

If you use the 3-table design that I mentioned, and from your latest post you are correct in how to use it (the 3rd table just links a user ID to an avatar ID), you can do this:

 

SELECT a.* FROM users u, avatars_avail a, avatars_unlckd au
WHERE
  u.id=au.user_id AND
  a.id=au.avatar_id AND
  u.id=$user_id
ORDER BY
  a.name

 

That query will extract every avatar available to the user whose id is stored in $user_id.  To make the query more efficient as the avatars_unlckd table grows in size, place an index on au.user_id.