Web Hosting Forum | Lunarpages

Author Topic: How To: Add tables to your existing Database  (Read 25739 times)

Offline Admin

  • ‹ber Jedi
  • *****
  • Posts: 2530
    • Lunarpages Web Hosting
How To: Add tables to your existing Database
« on: April 08, 2004, 07:00:11 AM »
Posted for Chelle.  Thank you so much for doing this :)
 
======================================
 
Feedback/Suggestions will be much appreciated.

If youíre on the Shuttle plan, youíve been given one database.  Perhaps youíve installed the nifty board/Nuke/shopping cart/phpMyChat via Cpanel, using the one click, no worries install.  Now you want something else on your site that requires a database.  So you simply do another one click install, but it tells you you need a database.  Oh no!

Fear not.  If you donít want to buy another database, this How To will show you how to add to your existing database.  Basically what youíll be doing is adding tables to your existing database.  First things first, getting acquainted with your database tools.

On CPanel, you have an icon that says MySQL Databases.  Click on that.




If you installed anything using CPanel, the database was automatically created for you.  Per the figure, the user name is the user name you were given when you first got your Lunarpages account.  The data base name is the name of your database.  The syntax is that your user name is the prefix for anything related to the database, as you can see in the figure.  In this figure, chelle is the user name created for the database.  

As a review, syntax is as follows:

Lunar-Pages-User-Name_Database-Name
AND
Lunar-Pages-User-Name_Database-User-Name

*NOTE: the prefix isnít attached to the passwords.  Bear that in mind when you edit any config files.


The one click install already created a user.  You can see this in the figure above. This picture shows the users in the database nuke, which is usern2_chelle.  It shows the user, the privileges granted, and the option to Delete the user.  usern2_chelle has all privileges.  For the things your installing the user you put into your config files needs to have the ĎAll Privilegesí box checked.  For simplicity, you can use the same user that the one click install created.  If this is fine, skip down to Using phpMyAdmin.  

If you want to create a different user, follow the following steps:

To create a user for your database

Scroll down to the next section.



Enter a username and a password, then click Add User.  You should see the new user when the page refreshes.  For this example, usern2_chelle will be the user for the database.

Now you have to add this user to the database.  Basically you're letting the database know that this user is ok'ed to manipulate the data stored in there.  Scroll up till you see this

 

[orange arrow indicators]  Select the user you just created in the drop down box.  Make sure the ĎAll Privilegesí box is checked.  Then click Add user.  You should see your new user when the page refreshes. Now you're set as far as the user is concerned.

Using phpMyAdmin

Scroll to the bottom of the page, and click on phpMyAdmin [red circle].



 Youíll get a pop up asking for a user name and password.  Use the user name and password given to you by Lunar Pages.  A new page will open up.  Sometimes, Iíll get an error page.  Just refresh and phpMyAdmin should load. Once logged in, you should get a screen like this:



This tool will help you manage the data in your database.  Click where indicated on the figure [yellow arrows].  If you click Databases you'll see your database name, click on that. Depending on what you installed will determine what you see.  In my case, I had Nuke installed, so I saw rows of nuke_filename where nuke is the prefix for all the table entries associated with my Nuke install.  These are table entries.



 If you let your mouse settle over the icons, it will tell you their function.



1st: Browse will let you browse the content of that table.
2nd:Search will let you search through the data in that table
3rd:Insert will let you insert a new instance of data into the table
4th:Properties will show you the table properties of the fields in it
5th:Drop: the red trashcan deletes the whole table, everything
6th:Empty: the white trashcan just empties out the data in the table, but leaves the table still there.

A breakdown of terminology:

Table: A database is made up of tables, that contain information
Field: is a value in the table. For instance, when you register somewhere, Name is a field within the a table. A table can have many fields.
Data: specific values held in fields. What you input. So the data in the field ĎNameí would be Joe Smith.

Creating Tables in phpMyAdmin

The file that actually creates tables is the .sql file that should be in the files you've downloaded.  Most times it's in its own folder called sql.  Now there are one of two things you're going to be doing.  You're either going to edit the .sql file or you'll leave it alone.  If you're installing another of the same thing [in my case, I have two instances of Nuke installed on my site], you'll need to edit the .sql file.  It's really simple.  If you're not, then you just need to know where the .sql file is located.  [If this applies, you can skip to the next section].

When you have to edit the .sql file

Using Nuke as an example:  the file that creates the database for Nuke is nuke.sql.  A quick way to create a new set of tables for an additional instance of nuke is to open nuke.sql in an editor, click on search/find and enter the word nuke and have it replaced with a new name.  For simplicity sake, I replaced nuke with nuke2.  Save your new .sql file under a new filename.  

The reason you're doing this is because you already have tables in your database with the 'nuke' prefix.  So to have a totally different  instance of nuke on your site, you will have to give its tables a different prefix.  To see an example, compare

http://portal.sansdreamscape.net and

http://sansdreamscape.net/chronicle

They look the same [theme being the same was my doing], but the data is different. A good way to see this is to look at the Forums module. The posts, and even the boards themselves aren't the same because the data for each has their own 'home.'


Uploading the .sql File

Now, in your phpMyAdmin window, click the link in the lower left hand corner 'Query window'  A new window will pop up.  



Click on import files [middle tab at the top].  Click Browse and choose the .sql file you've changed [if you're installing two of the same thing] or the .sql file that came with the downloaded files.  In this case, the new file saved was nuke2.sql.  Then click go.



After a moment, your page should refresh and you should get a message informing you of how many new tables were recreated.  On your left, the frame should refresh, showing the new tables as well.



You've just created the database for your what you wanted to install.  Now, follow the rest of the installation instructions.  In this case, for my second installation of nuke, I have all the tables it needs [the nuke2 tables].  The config.php file for that instance should look like:

Code: [Select]
$dbhost = "localhost";
$dbuname = "usern2_chelle";
$dbpass = "your_chosen_password_here_without_usern2_prefix";
$dbname = "usern2_nuke";
$prefix = "nuke2";
$user_prefix = "nuke2";
$dbtype = "MySQL";
$sitekey = "your_chosen_site_key";
$gfx_chk = 6;
$subscription_url = "";


Essentially you can copy your original config file and just change the prefix and user prefix unless you created a different user.  Just make sure the user you put in your config file has been added to the database and has all privileges. [See above steps].

Congrats, you've just added table to your existing database!  Go you :thumb:

Offline DaveW

  • Galactic Royalty
  • *****
  • Posts: 468
    • http://techgrouptk.com
How To: Add tables to your existing Database
« Reply #1 on: April 17, 2004, 03:21:18 PM »
Nice tutorial and addition to a very popular and most asked question Kathleen :yey:
My mind is like a lightning bolt-One flash and its gone.  TechGroupTK - Looking for Solutions? /ACS-Relay for Life

leighsww

  • Guest
How To: Add tables to your existing Database
« Reply #2 on: April 17, 2004, 03:29:45 PM »
WOW, awesome and extensive tutorial!  Good work, Chelle!!  :thumb:  :yey:

Offline Chelle

  • Spaceship Captain
  • *****
  • Posts: 103
How To: Add tables to your existing Database
« Reply #3 on: May 01, 2004, 09:38:52 AM »
Just noticed the feedback.  :)   Thanks, I'm glad I could contribute!
"The axe forgets.  The tree remembers" -African Proverb

Offline MPG

  • Spaceship Navigator
  • *****
  • Posts: 85
    • http://www.multiplayergames.com
How To: Add tables to your existing Database
« Reply #4 on: June 23, 2004, 03:30:26 PM »
Thank you for this!  Its great. :thumb:

Offline martino

  • Pong! (the videogame) Master
  • *****
  • Posts: 25
Re: How To: Add tables to your existing Database
« Reply #5 on: June 25, 2004, 06:26:53 PM »
Click on import files [middle tab at the top].  Click Browse and choose the .sql file you've changed [if you're installing two of the same thing] or the .sql file that came with the downloaded files.  In this case, the new file saved was nuke2.sql.  Then click go.

I am missing something on this part. I have the phpbb on my inital database. I cant seem to find the sql file that i dowloaded. CAn you help me or elaborate cause i am having a bit of difficulty understanding. Thanks in advance.

Offline Chelle

  • Spaceship Captain
  • *****
  • Posts: 103
How To: Add tables to your existing Database
« Reply #6 on: June 26, 2004, 11:42:16 AM »
Sure!  Basically what this means is this.  The file that creates the tables in your database is a .sql file.  For php Nuke, the filename is nuke.sql. SOme other scripts will actually do everything for you after you enter in some key information.  Using Hush's How To found here  phpbb [I'm assuming you're wanting to add another phpbb?] creates the tables in the database for you based on your input on the install.php page.

Quote
INSTALLING PHPBB

You will need the following information ready:

+ Your database name (this will be in the format username_databasename)
+ Your database User (this will be in the format username_databaseuser)
+ Your database password (this will simply be as you typed it)

Start by opening a new browser and typing in the address to your PHPBB directory and the install file:
http://www.yourdomain.com/phpBB2/install/install.php

This will load a simple form for you to fill out. The import fields are:

DATABASE TYPE: MySQL 4.xx
DATABASE SERVER: localhost
DATABASE NAME: - per my example in the how to: usern2_databasename
DATABASE USER: - per my example in the how to: usern2_databaseusername
DATABASE PASSWORD: - password, with no prefix

The remaining settings are personal to you, and should be straight forward to complete.

You will then need to click the submit button at the bottom of the page. This will process the information and is meant to

update the configuration file for PHPBB. In my experience, however, this is normally stopped by permissions. Do not worry,

simply select to down load the file and install manually.

As you click the submit button you will be prompted to save the configuration file, select a folder where you will easily

find the file. Once downloaded, you must upload this file to your PHPBB folder:
http://www.yourdomain.com/phpBB2

Now delete the following folders within your phpBB2 folder:

install
contrib

Finally, open up a browser window and type the address for your PHPBB:
http://www.yourdomain.com/phpBB2



So a few things to check:  The folder you're putting your new phpbb in.  Is it created?

If so, then all you need to do is upload all of your phpbb files into that folder.  Per the above, type into your browser the address to get to the install.php page.

Fill in your info.  When you get to table prefix..choose a prefix different than what your current phpbb uses.  For example, if your current one uses phpbb then use something like bb2.  For security, you might want to use something that isn't overtly obvious.  I hope that clears up your confusion.  Keep posting till you get it the way you want it! :D
"The axe forgets.  The tree remembers" -African Proverb

Offline Chelle

  • Spaceship Captain
  • *****
  • Posts: 103
How To: Add tables to your existing Database
« Reply #7 on: June 26, 2004, 11:43:09 AM »
Quote from: MPG
Thank you for this!  Its great. :thumb:


Aww shucks.  :D   I'm glad people have found it helpful.
"The axe forgets.  The tree remembers" -African Proverb

Offline Campus Frenzy Dot Com

  • Spacescooter Operator
  • *****
  • Posts: 44
    • http://www.campusfrenzy.com
.sql
« Reply #8 on: June 28, 2004, 09:11:08 AM »
I cant seem to find the .sql file.. i only have phpbb installed.. can anyone help?

Offline martino

  • Pong! (the videogame) Master
  • *****
  • Posts: 25
Re: .sql
« Reply #9 on: June 28, 2004, 10:17:46 AM »
Quote from: Campus Frenzy Dot Com
I cant seem to find the .sql file.. i only have phpbb installed.. can anyone help?


Thats the same problem I am having. I also only have phpbb installed, and now i want to install the poll script, but i cant find the .sql file to change it. Can somebody help please. Thanks in advance.

Offline Chelle

  • Spaceship Captain
  • *****
  • Posts: 103
How To: Add tables to your existing Database
« Reply #10 on: June 28, 2004, 12:50:52 PM »
FOr both martino and Campus Frenzy Dot Com:

If there isn't an .sql file in the files you downloaded, there's a chance that the script you wanting to add creates the tables itself, much like phpbb does when you run the install.php page.  Martino, I"ll go look at the poll script to double check and get back to you.

CFDC:  what script are you trying to install?
"The axe forgets.  The tree remembers" -African Proverb

Offline Chelle

  • Spaceship Captain
  • *****
  • Posts: 103
How To: Add tables to your existing Database
« Reply #11 on: June 28, 2004, 01:06:04 PM »
Martino:  If you're trying to add Advanced Poll, the installer script creates the tables for you.  The only thing you need to worry is that you have the file config.inc.php correct.

If you're trying to add phpESP there is a .sql file, but I think you would have found that  :)


If you're trying to add phpsurveyor, it has a script that creates tables for you.  Again, be sure your configuration settings are correct and you should be good to go.

Let me know how it goes for you.
"The axe forgets.  The tree remembers" -African Proverb

Offline Campus Frenzy Dot Com

  • Spacescooter Operator
  • *****
  • Posts: 44
    • http://www.campusfrenzy.com
Umm in particular the 4images gallery
« Reply #12 on: June 28, 2004, 04:31:08 PM »
Umm in particular the 4images gallery  :D

Offline Campus Frenzy Dot Com

  • Spacescooter Operator
  • *****
  • Posts: 44
    • http://www.campusfrenzy.com
How To: Add tables to your existing Database
« Reply #13 on: June 28, 2004, 04:33:42 PM »
as well as phpadsnew

Offline Chelle

  • Spaceship Captain
  • *****
  • Posts: 103
How To: Add tables to your existing Database
« Reply #14 on: June 28, 2004, 05:17:11 PM »
CFDC:  According to the install file for 4images, the install script will create the tables for you.  So all you have to worry about is making sure the config file is correct.i.e. that your database name, username, password are all correct.  After running the install script, you should be right as rain.


For phpadsnew:  Same thing.  It has an installer script that does the work for you.  As long as you fill in the correct settings, you should be fine.

Hope this clears things up for you.
"The axe forgets.  The tree remembers" -African Proverb

 

Share |