Need Advice about Setting Up My First DB

[MysticKnight]

Okay, I've done my homework, read a few books, upgraded my FutureQuest account to include 6 MySQL databases and I'm ready to go!

I have a website that's a small directory of 120 companies that support the amusement park industry. The companies are separated into 6 different categories. I think this website is a perfect candidate to be my first PHP/MySQL project.

Does anyone have any advice about how I should get started? I would particularly like a step-by-step list, but I would understand if nobody wants to spend that much time on my project.

Here's the address to the website that I want to convert...

http://www.amusenet.com

My first hurdle is importing the data that I have at the website right now into the database. I do have a flat text file that I could use as the source, but I have no idea how to tell the database to import it.

Thanks in advance for helping out a PHP/MySQL newbie!

Catch ya later, Bill

[Arthur]

OK, let's begin with the first step, designing and making the database table(s).
What do you want to store in the database?
- a unique identifier (a number)
- the category
- the name of the company
- the URL of their website
- a description
- whether there's a picture for the company
- the location/name of the picture
- anything else?

I don't think it needs to be normalized (the category names could be put in a seperate table).

Now you need to determine how long each field should be to define the table. For example the id number should be an integer, should be automatically incremented, should never be null, is the primary key. The description is a text field of length x, can not be null, etc.

Once you have written down on paper what your table should look, start looking at what you have. What does the flat text file look like? For importing a flat text file you want it look something like;
field1 <delimiter> field2 <delimiter> etc.
where the delimiter is something like a semi-colon. This should match your table description, although for instance the id/primary key could be added later.

Next step; create the table in MySQL, e.g.
CREATE TABLE companies (
id int(3) NOT NULL auto_increment,
name varchar(40) NOT NULL,
etc.,etc.
)

Next step; import the flat text file. You can use 'mysqlimport' for this, or &quot;LOAD DATA INFILE&quot; from the MySQL command line. The command line switches depend on the format of the text file.

That's in a nutshell how to get started, if you need more information, just post your questions here.

HTH,
Arthur

[MysticKnight]

First, thanks for the quick reply Arthur! If I can ever return the favor by helping out with a Photoshop or website problem, please let me know (info@billelgin.com).

I don't have any questions about your post yet, but I'm sure I will. My new FutureQuest account with the database access should be ready later today.

I'll post progress updates here.

Thanks again. Bill

[dank]

Here's an excellent tutorial that will walk you through many of the steps to getting started with PHP and MySQL:

http://www.webmasterbase.com/article.php?aid=228&amp;pid=0

Dan

[MysticKnight]

Thanks for the tip Dan. I've already printed it out and I'll go back and read through it after I post this message.

Status Report:

FutureQuest has modified my account so I have 6 databases ready to use. It took me some time to figure out that I need to &quot;activate&quot; the databases. However, after reading another post here, it was a piece of cake.

At this point, I'm working on creating the database tables. I'll have to spend some time figuring out the different settings involved in this.

As far as importing the data goes, it is a flat text file with each field separated by ^ with a carriage return at the end to indicate one record.

I'll add an update here soon.

Thanks again for all the help.

Best regards, Bill

[MysticKnight]

Okay, I've played around with MySQL and I've got a question for anyone that wants to answer it...

What do you use as your MySQL interface?

I've been simply logging on to MySQL through Telnet and then using MySQL commands. It reminds me of using a database from 1931 ( maybe not that long ago :) ).

Anyway, is there a graphical user interface for MySQL or is Telnetting commands the best (or only) way to go?

One last question... Can I (or should I) change the default names of the databases that FutureQuest assigned to me?

Best regards, Bill

[dank]

What do you use as your MySQL interface? http://www.phpwizard.net/projects/phpMyAdmin/
http://gossamer-threads.com/scripts/mysqlman/

I believe I recall Terra saying it's possible to drop one of your databases and re-create it under a new name, but I wouldn't try that unless he confirms it is acceptable and will work.[nbsp][nbsp];)[nbsp][nbsp] Besides, I don't see any reason for doing so, as the database name is really irrelevant, so far as I know.

Dan

[Terra]

The database names follow a specific naming convention and cannot be changed...

It's is really the only efficient way to manage thousands of databases and the underlying grants...

--
Terra
--$db = 'xacme';--
FutureQuest

[MysticKnight]

Okay, I've created my table for 'companies' inside one of my databases and it includes these fields for each company listed...

ID
Category
Company
URL
Description
Icon (yes or no)
IconURL
Member (yes or no)

Remember, I have about 120 companies that I need to import from a flat text file. Each field is separated with ^ and there's a carriage return at the end of each record.

In a previous message, I was told to use mysqlimport or LOAD DATA INFILE to import the data from my flat text file. I don't know anything about these functions or how to use them, but I can see a couple potential problems already...

The original text file does not have several of the new fields that I created (such as ID and Member). Also, I didn't create the fields in the same order that they are in the text file.

What's my next step?

Also, is there a good reference book or website for MySQL that would allow me to look up the different fuctions that are available and how to use them (such as mysqlimport)?

Thanks in advance for any help.

Best regards, Bill

[dank]

My preferred method is to open the original text file in Excel, specify what the column delimiters are (^), then rearrange or add columns as necessary, and finally highlight all the data columns/rows (if you have added blank columns corresponding to the database table at the beginning or end, make sure to highlight those as well) and copy them into a new text file (never overwrite your original data).[nbsp][nbsp]You will now have a tab delimited (you could always do a search and replace to change tabs to pipes or something, but that doesn't seem like a good use of time :) ) file with everything in the right order and an additional tab signifying an empty column.[nbsp][nbsp]If you use the MySQLMan link above, it has a very nice text file import utility which will finish things off.[nbsp][nbsp]No need to mess around in telnet or with phpMyAdmin's somewhat shaky upload/import routines (as much as I prefer PHP over Perl, Perl seems to have a large edge in the upload/import area).

(Dan)

p.s.[nbsp][nbsp]Terra, when I said, &quot;as the database name is really irrelevant, so far as I know,&quot; I meant irrelevant to the user, not to the overall system.[nbsp][nbsp]No disrespect intended.[nbsp][nbsp]:)[nbsp][nbsp]I could've sworn you told Jimbo at one point it could be done, though...

[Rich]

ID
Category
Company
URL
Description
Icon (yes or no)
IconURL
Member (yes or no) The &quot;Icon(y/n)&quot; field is redundant and not required. If the IconURL field is not blank, Icon must be 'yes'. Also, if it is important to know whether or not the company is a &quot;Member&quot; it might also be important to know (later) the date they became a member and the date they no longer became a member (and maybe even why).

Rich

[Jonese1]

I just bought the following book this weekend:

PHP and MySQL Web Development
by Luke Welling, Laura Thomson
(Paperback)

It got a five-star rating at Amazon.com. So far, I've read about 350 pages (it's a big 'un--896 pages), and I've found it to be very helpful. Its focus is mainly e-commerce, and it explains how to create shopping carts, discussion boards, etc. It also has a very good overview of security issues and solutions.

Even though I am not running a commercial site, I've found the book's discussion of PHP and MySQL basics very clear and helpful.

[wharris]

Question about LOAD DATA INFILE . . . at one point, I recall being advised that this function didn't work on FutureQuest servers and that I had to use mysqlimport. Not a big deal, but LOAD DATA INFILE is more convenient in that you don't have to exit the database server to do the import.

Comment about the import itself . . . second Dan's suggestion to use Excel to arrange the import file to match the created MySQL table. I don't generally do the ID column in Excel, however. It's easier to leave it out of both the table and flat file, do the import, and then ad the ID column with this . . .

ALTER TABLE yourtable ADD ID integer not null PRIMARY KEY auto_increment FIRST;

This assumes your existing flat file doesn't already include this column . . .

Wayne