database help

[sevster]

July 26th, 2005, 2:14 pm

I have a database with 101 columns. 1 cd title 50 of them are cd track names. and the other 50 are for the URL of the sample's of the track...most of the cd's don't have that many tracks or sample's, so they are left empty in the database.

so it's like:

Title | track 1 | track 2 | track 3 .... | Sample 1 | Sample 2 | Sample 3....


not all cd's have 50 tracks, but I have 50 colums for tracks, just in case.

what i need to do, is write code to display the track names which are not empty, and also have track1 have a link to sample1 and track2 have a link to sample2 ... etc....but only if the sample column for that specific track is not empty, or else just dispay the track with no link.

how can i do this?

Thanks for the help!

Sevster

July 26th, 2005, 6:43 pm

Well, for starters, that is a horrible table design, and will cause more headaches than you need (like the one you are experiencing right now).

I would break that table down to 3 seperate tables:
artist, album and track, that look like this:

Code: [ Download ] [ Select ]

CREATE TABLE `artist` (
 `intId` int(11) NOT NULL auto_increment,
 `strName` varchar(100) NOT NULL default 'unknown',
 PRIMARY KEY (`intId`)
) TYPE=MyISAM COMMENT='Artist Info' AUTO_INCREMENT=1 ;


CREATE TABLE `album` (
 `intId` int(11) NOT NULL auto_increment,
 `intArtistId` int(11) NOT NULL default '0',
 `strAlbumTitle` varchar(100) NOT NULL default 'unknown',
 `intYear` mediumint(4) NOT NULL default '1900',
 PRIMARY KEY (`intId`,`intArtistId`)
) TYPE=MyISAM COMMENT='Album info' AUTO_INCREMENT=1 ;


CREATE TABLE `track` (
 `intId` int(11) NOT NULL auto_increment,
 `intAlbumId` int(11) NOT NULL default '0',
 `strTrackTitle` varchar(100) NOT NULL default 'unknown',
 `strTrackLink` varchar(100) NOT NULL default '#',
 PRIMARY KEY (`intId`,`intAlbumId`)
) TYPE=MyISAM COMMENT='Track info' AUTO_INCREMENT=1 ;

1. CREATE TABLE `artist` (
2.  `intId` int(11) NOT NULL auto_increment,
3.  `strName` varchar(100) NOT NULL default 'unknown',
4.  PRIMARY KEY (`intId`)
5. ) TYPE=MyISAM COMMENT='Artist Info' AUTO_INCREMENT=1 ;
8. CREATE TABLE `album` (
9.  `intId` int(11) NOT NULL auto_increment,
10.  `intArtistId` int(11) NOT NULL default '0',
11.  `strAlbumTitle` varchar(100) NOT NULL default 'unknown',
12.  `intYear` mediumint(4) NOT NULL default '1900',
13.  PRIMARY KEY (`intId`,`intArtistId`)
14. ) TYPE=MyISAM COMMENT='Album info' AUTO_INCREMENT=1 ;
17. CREATE TABLE `track` (
18.  `intId` int(11) NOT NULL auto_increment,
19.  `intAlbumId` int(11) NOT NULL default '0',
20.  `strTrackTitle` varchar(100) NOT NULL default 'unknown',
21.  `strTrackLink` varchar(100) NOT NULL default '#',
22.  PRIMARY KEY (`intId`,`intAlbumId`)
23. ) TYPE=MyISAM COMMENT='Track info' AUTO_INCREMENT=1 ;

This allows you to store each element seperately, yet still keep them linked.
The artist table stores only an auto-incremented ID and the Artist name.

The album table stores an auto-increment Album ID, the ID for the artist it belongs to (the ID from the artist table), and Album Title.

The track table stores the auto-increment track ID, the Album ID (From the album table), the track title and the link for the track.

Using this type of layout, allows you to search for all songs from a particular artist:

Code: [ Download ] [ Select ]

SELECT strTrackTitle, strTrackLink FROM track, album, artist WHERE artist.strName LIKE '%SEARCHSTERING%' and album.intArtistId=artist.intId and track.intAlbumId=album.intId

where SEARCHSTRING is an artist name (or part of one)

Or search for tracks from a particular album:

Code: [ Download ] [ Select ]

SELECT strTrackTitle, strTrackLink FROM track, album WHERE album.strAlbumTitle LIKE '%SEARCHSTERING%' and track.intAlbumId=album.intId

where SEARCHSTRING is an album title (or part of one)

Or to just search for a song by title:

Code: [ Download ] [ Select ]

SELECT strTrackTitle, strTrackLink FROM track WHERE track.strTrackTitle LIKE '%SEARCHSTERING%'

where SEARCHSTRING is an track title (or part of one)

There are other ways to search, those are just a few easy examples.

[Rabid Dog]

July 27th, 2005, 2:25 am

Ah normalization, you gotta love it

July 27th, 2005, 2:40 am

I lurve the database normalization

[sevster]

July 27th, 2005, 8:10 am

very interesting....thanks so much for that detailed explanation scienceofspock...i truly appreciate it. i'm going to try your suggestion now and see what happens

thanks again,

sevster

[sevster]

July 27th, 2005, 9:21 am

hmm, this is very nice, but looks like I'm not going to be able to change the way my database is. is there anyway that to do what i need using my current database which I stated above?

July 27th, 2005, 9:45 am

why can't you change it? even if you already have lots of data already, you can make a new table, test it, then copy your old information over either manually or using a script. In my experience, all the programming skills in the wolrd can't compensate for a lack of good design.

[sevster]

July 27th, 2005, 9:55 am

because my problem is only creating the URL links for each track name. everything else works. and my question about creating the URL links still isn't answered if I go through all that trouble and fix the database.

July 27th, 2005, 9:55 am

You really, really, really should learn relational database design (like ScienceOfSpock suggested); but, if you honestly can't change it, and you want your code to be as bad as the database design, try this:

PHP Code: [ Download ] [ Select ]

$albums = mysql_query('SELECT * FROM albums');
 
if (!$albums) {
 
   die('Unable to find albums in database: '. mysql_error());
 
}
 
 
 
while ($album = mysql_fetch_array($albums)) {
 
   $title = $album['title'];
 
 
 
   echo "<p><b>$title</b><br>\n";
 
 
 
   for ($i=1; $i <= 50; $i++) {
 
      $track = $album['track'. $i];
 
      $sample = $album['sample'. $i];
 
     
 
      if ($track) {
 
         if ($sample) {
 
            echo "<a href=\"$sample\">$track</a><br>\n";
 
         } else {
 
            echo "$track<br>\n";
 
         }
 
      }
 
   }
 
   
 
   echo "</p>\n";
 
}

1. $albums = mysql_query('SELECT * FROM albums');
2.  
3. if (!$albums) {
4.  
5.    die('Unable to find albums in database: '. mysql_error());
6.  
7. }
8.  
9.  
10.  
11. while ($album = mysql_fetch_array($albums)) {
12.  
13.    $title = $album['title'];
14.  
15.  
16.  
17.    echo "<p><b>$title</b><br>\n";
18.  
19.  
20.  
21.    for ($i=1; $i <= 50; $i++) {
22.  
23.       $track = $album['track'. $i];
24.  
25.       $sample = $album['sample'. $i];
26.  
27.      
28.  
29.       if ($track) {
30.  
31.          if ($sample) {
32.  
33.             echo "<a href=\"$sample\">$track</a><br>\n";
34.  
35.          } else {
36.  
37.             echo "$track<br>\n";
38.  
39.          }
40.  
41.       }
42.  
43.    }
44.  
45.    
46.  
47.    echo "</p>\n";
48.  
49. }

[sevster]

July 27th, 2005, 10:45 am

that works quite well actually...

why is my database design so bad? I mean sometimes you just have to deal with what kind of database your client already has....so my client had a huge database which he had created in a bad way...so i have to work with it, right? and even if the database is bad, why do you consider this code "just as bad as my database design"?

I'm familiar with programming....especially in c++ and I can tell that even if I was using the database design like the one science of spock suggested, we would still need something like the code you wrote above, which i am very thankful for.


thanks again,

Sevster

July 27th, 2005, 11:12 am

sevster wrote:

why is my database design so bad?

In short, this design is not very flexible if you need to change something in the future. For example, what happens when you run into a CD with more than 50 tracks? And then what happens if you want to store the length of each track in the database? Well, I wouldn't want to add another 50 columns to a table. If you do relational database design, you only have to add one more column, and the number of tracks per CD is unlimited. Also, all of the unused NULL values make the database file size bigger too IIRC.

sevster wrote:

I mean sometimes you just have to deal with what kind of database your client already has....so my client had a huge database which he had created in a bad way...so i have to work with it, right?

If it was me, I'd definitally work on redoing the database with a cleaner design, even if it takes a day or two, it will be worth it in the end if you have to use it a lot in the future.

sevster wrote:

and even if the database is bad, why do you consider this code "just as bad as my database design"?

Because it also assumes there will be no more than 50 tracks in a CD, but you are right, the code isn't much longer than it would be with a relational database design, it is just not as flexible.

If you don't need to store the artist, etc. in the database, you can simplify ScienceOfSpocks suggestion. Here's my version:

Code: [ Download ] [ Select ]

CREATE TABLE albums (
ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Title VARCHAR(200) NOT NULL
);

CREATE TABLE tracks (
ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
AlbumID INT NOT NULL,
Title VARCHAR(200) NOT NULL,
SampleURL VARCHAR(200) NOT NULL
);

1. CREATE TABLE albums (
2. ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
3. Title VARCHAR(200) NOT NULL
4. );
6. CREATE TABLE tracks (
7. ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
8. AlbumID INT NOT NULL,
9. Title VARCHAR(200) NOT NULL,
10. SampleURL VARCHAR(200) NOT NULL
11. );

You can then use this code to do the same thing:

PHP Code: [ Download ] [ Select ]

$albums = mysql_query('SELECT * FROM albums');
 
if (!$albums) {
 
    die('Unable to find albums in database: '. mysql_error());
 
}
 
 
 
while ($album = mysql_fetch_array($albums)) {
 
   $albumID = $album['ID'];
 
    $albumTitle = $album['Title'];
 
   
 
    echo "<p><b>$albumTitle</b><br>\n";
 
   
 
    $tracks = mysql_query("SELECT * FROM tracks WHERE AlbumID='$albumID'");
 
    if (!$tracks) {
 
      die('Unable to find tracks in database: '. mysql_error());
 
   }
 
   
 
    while ($track = mysql_fetch_array($tracks)) {
 
        $trackTitle = $track['Title'];
 
        $trackSample = $track['SampleURL'];
 
       
 
      if ($trackSample) {
 
         echo "<a href="$trackSample">$trackTitle</a><br>\n";
 
      } else {
 
         echo "$trackTitle<br>\n";
 
      }
 
    }
 
   
 
    echo "</p>\n";
 
}

1. $albums = mysql_query('SELECT * FROM albums');
2.  
3. if (!$albums) {
4.  
5.     die('Unable to find albums in database: '. mysql_error());
6.  
7. }
8.  
9.  
10.  
11. while ($album = mysql_fetch_array($albums)) {
12.  
13.    $albumID = $album['ID'];
14.  
15.     $albumTitle = $album['Title'];
16.  
17.    
18.  
19.     echo "<p><b>$albumTitle</b><br>\n";
20.  
21.    
22.  
23.     $tracks = mysql_query("SELECT * FROM tracks WHERE AlbumID='$albumID'");
24.  
25.     if (!$tracks) {
26.  
27.       die('Unable to find tracks in database: '. mysql_error());
28.  
29.    }
30.  
31.    
32.  
33.     while ($track = mysql_fetch_array($tracks)) {
34.  
35.         $trackTitle = $track['Title'];
36.  
37.         $trackSample = $track['SampleURL'];
38.  
39.        
40.  
41.       if ($trackSample) {
42.  
43.          echo "<a href="$trackSample">$trackTitle</a><br>\n";
44.  
45.       } else {
46.  
47.          echo "$trackTitle<br>\n";
48.  
49.       }
50.  
51.     }
52.  
53.    
54.  
55.     echo "</p>\n";
56.  
57. }

Yes, it is a little more code, but it is a ton more flexible than the other design.

[sevster]

July 27th, 2005, 11:33 am

both of you have been very helpful to me. I am greatly appreciative of this. Thank you so much guys.

one other thing I may add to this ...

the code you give me, I am modifying it to meet my needs. besides displaying the cd's, when you click on a specific cd, it opens up a new page and gives you more details about it....this new page, I wrote some code which actually creates a new php page on it's own and then displays it. But it creates one php page per cd, which I know is the wrong way to do it. Other than creating a new page, how can I go about doing what I need?

Thanks again,

Sevster

July 27th, 2005, 11:59 am

sevster wrote:

the code you give me, I am modifying it to meet my needs. besides displaying the cd's, when you click on a specific cd, it opens up a new page and gives you more details about it....this new page, I wrote some code which actually creates a new php page on it's own and then displays it. But it creates one php page per cd, which I know is the wrong way to do it. Other than creating a new page, how can I go about doing what I need?

I'm not sure I understand what you mean by "creates one php page per cd". Do you mean you wrote a PHP script that creates a new file for every CD? Or each CD is opening in a separate window and you want them to open in the same window? Or something else?

Are the extra CD details stored in the database along with the other 101 columns?

[sevster]

July 27th, 2005, 12:02 pm

ryanb wrote:

I'm not sure I understand what you mean by "creates one php page per cd". Do you mean you wrote a PHP script that creates a new file for every CD? Or each CD is opening in a separate window and you want them to open in the same window? Or something else?

Are the extra CD details stored in the database along with the other 101 columns?

LOL! unfortunately they are....but I can retrieve them...that's not a problem.

I wrote a php script that creates a new file for every cd, yes. they open in the same window....but what I want, is to be able to do the same thing, but without having to create a separate file per cd.

July 27th, 2005, 12:12 pm

If you have an ID field in your database, you can pass the ID of the CD you want to display in the URL. For example:

PHP Code: [ Download ] [ Select ]

$albumID = $album['ID'];
 
$albumTitle = $album['Title'];
 
 
 
echo '<a target="albuminfo" href="albuminfo.php?id='. $albumID .'">'. $albumTitle .'</a>';

1. $albumID = $album['ID'];
2.  
3. $albumTitle = $album['Title'];
4.  
5.  
6.  
7. echo '<a target="albuminfo" href="albuminfo.php?id='. $albumID .'">'. $albumTitle .'</a>';

And then in the albuminfo.php file, get the CD with that ID in the database and display the info:

PHP Code: [ Download ] [ Select ]

$albumID = $_GET['id'];
 
 
 
$album = mysql_query("SELECT * FROM albums WHERE ID='$albumID'");

1. $albumID = $_GET['id'];
2.  
3.  
4.  
5. $album = mysql_query("SELECT * FROM albums WHERE ID='$albumID'");