Must be an easier way.....or not?

[Stevis2002]

Hi all,

I am creating a site that lists album reviews.
At the moment i am making a page called (name of artisit).php, and then listing all their albums on that page, with a small description.
Then i am linking each album, and creating a page which shows album cover and track listing etc.
So i have 1 page for each artist, and then 1 page for each of the artists albums.
All of which i am hand entering.

Is there a more efficient way of doing this?

Thanks

[Mortier]

Yes, I think the best way is using a database, like MySQL. If you have this on your website/server you're fine with that

I suppose you have no experience with that;

Then you can use 1 page (
And that would list e.g. all albums

(I think u mean this:) and once u click on an album you'll get more detailed info.

PHP can make a script (which can be like 50 lines) that will list all the albums using the (MySQL)database.

Then you can give each album a link (you only have to do it once and php will do this for all of your database items== your albums)

And each link will carry the unique ID of that album which will load the data as the tracks and the album cover.

So that can be indeed, much and much more efficient -> if I understand you correctly.

[Stevis2002]

[!--quoteo(post=368559:date=Apr 25 2006, 07:05 PM:name=Mortier)--][div class=\'quotetop\']QUOTE(Mortier @ Apr 25 2006, 07:05 PM) [snapback]368559[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Yes, I think the best way is using a database, like MySQL. If you have this on your website/server you're fine with that

I suppose you have no experience with that;

Then you can use 1 page (
And that would list e.g. all albums

(I think u mean this:) and once u click on an album you'll get more detailed info.

PHP can make a script (which can be like 50 lines) that will list all the albums using the (MySQL)database.

Then you can give each album a link (you only have to do it once and php will do this for all of your database items== your albums)

And each link will carry the unique ID of that album which will load the data as the tracks and the album cover.

So that can be indeed, much and much more efficient -> if I understand you correctly.
[/quote]


Thanks for that. That is exactly what i mean.
I did think about that, and i did start storing info into a database.

Everything went in ok apart from the image bit.
I got them uploading into the image folder on my server, but then i couldn't work out how to get a link to them from the location into the database.

Well, i did get them linking, but only using the full path, eg. c:/domains/xxxx.com/wwwroot/images/
I asked for help on these forums, but i couldn't reeally get my head around it, even though the man was very helpful.

Then, how do i get the info displayed from the db, into a table?
I can get it to display, but i don't know how to display it in a table, (is it possible tyo layout the table etc, and then just get the db to extract the info into the row i want?

Many Thanks

Also, Is it really better to store images on the web server and not in the database?

[Mortier]

Well you could store images in the database (to start with the last).

I suppose you use a MySQL database so I'm going to write an example script here

First your database:

name the TABLE albums, for example

use the following colums:

- id (make sure it does auto-increment and it must be unique)
- name (album name)
- track1(number of tracks in album)
- track2
- track3
- add as many track colums as you need
- albumcover (name of the album e.g. albumcover.jpg)

You can always add more data types.

Then we go to the PHP script.

[code]
<?

//connect to mysql and your mysql database first before you do this

$select_albums = mysql_query('SELECT * FROM albums'); //get info from database

//we use a while loop, because else only 1 recorder (meaning 1 album can be displayed, this way we'll get them all)

while($album = mysql_fetch_assoc($select_albums))
{
//here we make a list of all albums, everything within the while will be repeated so be aware of that when adding styles etc
echo '<a href="mypage.com?action=ViewAlbum&id=' . $album['id] . '">' . $album['name'] . '</a>';

}

if($_GET['action'] == ViewAlbum)
{
$select_album = mysql_query('SELECT * FROM albums WHERE id="' . $_GET['id'] . '"'); //locates the id from the url so mysql knows which album to select

$album = mysql_fetch_assoc($select_album); // no while needed because we only view info of 1 album at once

echo '<img src="images/' . $album['albumcover'] . '"><br>';
echo $album['name'];


}

[/code]

well I've tried to keep it short but this is how your simplified system could look like.

I hope you use MySQL as database, would be the easiest.

If you have any questions, ask them.
Don't really know how to estimate your knowledge of PHP (and MySQL?) but I hope this makes sense :)

Good luck

[Yesideez]

Just read this topic with interest and would like to add a little if I may.

Storing the data in a database is the best idea and I would like to suggest a similar way of storing the data by using two tables. First you have the main table that stores information about the artists. Let's call the table "artists":
[code]CREATE TABLE `artists` (
`artistid` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR( 50 ) NOT NULL ,
`genre` VARCHAR( 20 ) NOT NULL ,
`comment` TEXT NOT NULL ,
PRIMARY KEY ( `artistid` )
) TYPE = MYISAM;[/code]
This would be used to store information about the artist. Of course you could have a lot more fields than I've used. Then you'd have another tables containing information about each album, let's call it "albums":
[code]CREATE TABLE `albums` (
`albumid` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`artistid` MEDIUMINT UNSIGNED NOT NULL ,
`title` VARCHAR( 80 ) NOT NULL ,
`tracks` TEXT NOT NULL ,
PRIMARY KEY ( `albumid` )
) TYPE = MYISAM;[/code]
Every time you add an album you will have to add the artist first, therefore generating a unique artistid from the table "artists" which would then be added into the artistid field in the table "albums".

As for the track listings you'd store them in the TEXT fields separated by either a carriage return or a character not used like the "|" symbol - use explode() to split them up for easy displaying.

When it comes to linking to the images of the album covers you'd use the unique id fields. These will be numbers. Store these covers on the server something like this:
[code]images/sm1.jpg
images/lg1.jpg
images/sm2.jpg
images/lg2.jpg[/code]
...and so-on. The "sm" file would be the thumbnail and the "lg" would be the full-sized image. This way each artist could have a unique picture and each album can as well.

That's the way I'd be tempted to do it.

Additional post:

To show an image of the artist use something like this:
[code]$searchstr="Queen";
$fetch=mysql_fetch_array(mysql_query("SELECT * FROM artists WHERE `name`='$searchstr'"));
$artistid=$fetch[artistid];
echo '<a href="viewartist.php?id='.$artistid.'"><img src="gfx/artists/sm'.$artistid.'.jpg"></a>';[/code]
To display all of the albums by an artist you'd use something like this:
[code]$query=mysql_query("SELECT * FROM albums WHERE `artistid`='$artistid'");
while ($fetch=mysql_fetch_array($query)) {
  $albumid=$fetch[albumid];
  echo '<a href="viewalbum.php?id='.$albumid.'"><img src="gfx/albums/sm'.$albumid.'.jpg"></a>';
}[/code]
That's pretty much all I can think of for the moment. I'll keep an eye on this topic and help where I can.

[Yesideez]

By the way, the above two snippets of code would show thumbnails which can be clicked to take the user to the artist/album. A sample script (let's try viewalbums.php) could look something like this:
[code]<?php
  $albumid=intval($_REQUEST['id']); //Get albumid from URL
  include("dbconnect.php"); //Connect to the database
  $fetch=mysql_fetch_array(mysql_query("SELECT * FROM albums WHERE `albumid`='$albumid'"));
  $albumid=$fetch[albumid];
  $artistid=$fetch[artistid];
  $albumtitle=$fetch[title];
  $tracks=explode("|",$fetch[tracks]); //Split the track listing into an array
  $trackshtml="";
  for ($i=0;$i<count($tracks);$i++) {
    $trackshtml.=$i.': '.$tracks[$i].'<br>';
  }
  $fetch=mysql_fetch_array(mysql_query("SELECT name FROM artists WHERE `artistid`='$artistid'"));
  $artistname=$fetch[name];
?>
<html>
<head>
  <title><?=$albumtitle?> by <?=$artistname?></title>
</head>
<body>
  Cover:<br>
  <img src="gfx/albums/lg<?=$albumid?>.jpg" border="0"><br>
  Tracks:<br>
  <?=$trackshtml?>
</body>
</html>[/code]