MySQL Set-up Structure - Help!!!

carcasskind

Hey All,

I'm not a regular MySQL user - I do a lot more Flash work, and recently I have started a small business which needs a reasonably complex (for me at least Wink

) database structure.

The site is a CD mail-order operation, and requires a certain level of functionality (explained below):

The ability for all the Artists to be displayed alphabetically, and found through a search box.

The Artists displayed have to be links which link to pages written on the fly (using PHP) to display the albums we have available, with Album Cover Images, Track Listing, Review and Link to MP3 sample or myspace and price.

Below is an Image of what I mean:

Artist View (taken from MySQL database):

User Clicks on Artist (album image, main body of text/review, track listing, and album name, and price(not shown) all taken from mysql database, page is written by a universal php script):

I am using a paid host, which offers CPanel, MySQL version 4.1.* and PhpMyAdmin 2.9.0.2. I use Macromedia Studio 8 Pro.

I am mainly looking for help and advice on how to structure the database. Currently, I am thinking that it will need two tables (?) one for artist names, and another for the artist name (relating tables, is that what they call it?), albums, images, review text, and links, and of course Price.

Also, and Order button will be placed somewhere within the album selection frame-like div and somehow I was hoping it would be passed the variables from the page - such as $Artist and $Album_name and $price, put those variables pre-completed into a form, and let the user do the rest, like enter address details etc.

Sorry about the crappy explanation, I'm trying to wrap my head around what I have to do. Help and advice would be much appreciated.

Thanks in advance,
Nick Chhabra
Insipid Records

carcasskind

I thought about it some more yesterday, and I think I know how to structure the database, but I still don't understand how to write the PHP scripts. Anhow, the MySQL database structure should look like this:

I know I need to use PHP to connect to the database, query what artists I have starting with a specific letter or number (passed on from a previous php page where the user has selected a letter from the alphabet or a number), make the resulting artists hyperlinks to another PHP page that catches the artist name, does another query by matching the both a_Id's and displays a page which contains the data stored in the database (artist name, albums, price etc etc).

Can anyone guide me on this?

Thanks again,
Nick Chhabra
Insipid Records

MrBlueSky

I suggest you add an album_id to the album-table as primary key. Otherwise you can't uniquely and efficiently refer to an album.

About the way to implement your idea: I'm afraid you will have to learn some PHP to develop your application.

Check out this excellent online book which will get you up to speed quickly: http://www.jimsannex.com/Studies/CD_WebProgramming/webdb/index.htm

(BTW: I like the layout of your pages)

(BTW2: You could consider to store the album image in a file, and the file name in the dbase. It takes some more work, but it is propably faster.)

carcasskind

Thanks for the reply Blue! I was wondering whether the a-Id field in the album_and_info table would need to be a primary key, but I had the notion that it automatically increments - if this is true than I won't be able to have more than one album per artist. I'm not sure whether it actually does Auto-Increment though.

The other thing I'm having a lot of trouble with is importing a CSV file into my Artist_id table, I can't seem to find the Insert text fields into table link that should be under the structure view. It's very frustrating Confused

Thanks for your help,
Nick Chhabra

carcasskind

Never mind about the CSV import, I found that it was an error with the Dark blue/orange colour scheme, it only shows SQL in Firefox, not CSV and CSV with LOAD DATA. Stupid themes Razz

.

Indeed I am storing my images as filepaths, e.g. /images/$artist_$albumName.jpg and music filepath as /music/$albumName.mp3

I successfully have uploaded the CSV file for my first table (artist_Id), but the second table, in which I have to match the A_ID to every artist/album entry is tedious and time consuming, as I don't know how to batch replace every artist name with the relevant artist Id. it'd be fine if each artist had one album, but because of multiple albums under one artist it looks like this:

*sigh*

If anyone can help guide me in the right direction in regards to quickening this process, I may not commit suicide. Laughing

carcasskind

OK, I think I have an idea on how to the second table quickly. It would mean using SQL.

For example, say I uploaded my first table using phpMyadmins CSV import, which contains unique artist names, and relevant a_Id's (artist_ids). Now that's done, I work on my second table and leave only a_id blank. Would it be possible for me to say to MySQL, populate the a_id field by matching the artist from table 2, to the artist and hence, a_Id field in table 1, and populating every row which has the artist in table 2, with the related a_id from table 1?

is this possible - it must be!

Help much appreciated, this must be a SQL command!!!

Regards
Nick Chhabra

MrBlueSky

carcasskind

Okies, thanks for your reply Blue!

Here's what my table structures look like:

ARTIST VIEW (for CD and Vinyl Only):

ALBUM INFO TABLE (for CD and Vinyl Only):

Also, I've added the artist_Name field to the information table as well, so the match thing should be possible right? Very Happy

Just wondering if this is the right structure. Comments/Help much appreciated! Smile

carcasskind

My CVS looks like the table structure as well, forgot to add that to the last post.

Help much appreciated Smile

carcasskind

Come on guys, there's got to be someone out there who can help me out with this problem, I'm pretty sure it isn't very complex at all... just a few lines of SQL code...

All I need to do is compare two tables within a database, each of which contain a field which is identical(artist_name), then I want to populate the field a_Id in the second table, with the value of a_Id found in the first table according to the artist_name. You can see the structure of my tables in the post above the last!!!

Thanks for your replies Blue, you seem to be the only member out of the 20000+ that has any interest in helping a confused bloke out.

Active community my arse.