Hi folks,
I've been reading the forums here for a few days, as I've taken it upon myself to create a small database and reading from it/writing to it.

Have found answers to a few early problems, but now I've really hit the wall. It's been a few years since I worked with a mySQL-database, and too long since I tried learning myself php. But you have to learn sometime, right? So I tried again.

Here's an outline of the problem, hope someone can offer up a few tips to get me going again.

I'm trying to create a database containing setlists.
Let me explain. An artist plays a show, I want to record the venue, date, setlist and also the lyrics for the songs.

This is my layout:

Mysql-database:

Table - SetLists:

* SetListID, Int, PRIMARY KEY, AutoInc, NotNull.
* Date, Date.
* Venue, Text.
* Town, Text.
* Country, Text.

Table - SongsInSet.
* TrackID, Int, PRIMARY KEY, AutoInc, NotNull.
* SetID, Int
* Track, Int
* Title, VarChar.

Table - Songs.
* Title, PRIMARY KEY, VarChar.
* ReleasedOn, Text.
* ReleaseYear, Year.
* Lyrics, LongText.

What I want to do:
SetLists are unique, but the same songs will appear on several setlists. Therefore I've split songs into it's own table.
(As you probably see, databasedesign was never my best subject)

So far I've filled the database with a few testdata via MySqlControlCenter. But I want to fill it via an online admin-script later on.

I've got a query in my php-script, that gets all setlist from a given year. The Date is then put as a hyperlink, with which I send the SetListID as a variable to the page that's supposed to send out all the songs from that set.

PHP Code:


$db = mysql_connect("localhost", -connection here);
mysql_select_db("dbname",$db);

$result = mysql_query("SELECT * FROM view_SetLists WHERE view_SetLists.Date BETWEEN
'1990-01-01' AND '1990-12-30';",$db);
if ($myrow = mysql_fetch_array($result)) 

{



  do {

    printf("<a class=\"setlinker\" href=\"%s?id=%s\">%s</a> - %s - %s, %s\n", "setlist.php", $myrow["SetListID"], $myrow["Date"], $myrow["Venue"], $myrow["Town"], $myrow["Country"]);


  } while ($myrow = mysql_fetch_array($result));


} else {
    echo "Sorry, no records were found!";    
} 


The called page gulps out most of the info, (ie, all from the SetList-table), but only the first song of the Tracklist...

And I think the problem here lies in my phpscript. I'm not to good at looping either .

Output-page (just the code)

PHP Code:

$id = $_GET['id'];


$db = mysql_connect("localhost", dbconnection);
mysql_select_db("dbname",$db);

if ($id) 

{



  $sql = "SELECT * FROM view_SetLists, view_SongsInSet, view_Songs WHERE 
view_SetLists.SetListID=$id AND view_SongsInSet.SetID=$id;";
  $result = mysql_query($sql);    
$myrow = mysql_fetch_array($result);
}



echo $myrow["Date"];
echo $myrow["Venue"];
echo $myrow["Track"];
echo $myrow["Title"]; 


I hope someone can shed some light on this, I'm stuck..

If I understand you corectly, try this:

PHP Code:

while($myrow = mysql_fetch_array($result,ASSOC)) {
  echo $myrow["Date"]; 
  echo $myrow["Venue"]; 
  echo $myrow["Track"]; 
  echo $myrow["Title"]; 
} 


You're correct, you need to loop or you'll only ever get the first record, but it's not that difficult. The added code find the number of records, then increments a variable and displays another record each time, creating a loop.

PHP Code:


 $id = $_GET['id'];


$db = mysql_connect("localhost", dbconnection);
mysql_select_db("dbname",$db);

if ($id)

{

$sql = "SELECT * FROM view_SetLists, view_SongsInSet, view_Songs WHERE
view_SetLists.SetListID=$id AND view_SongsInSet.SetID=$id;";
$result = mysql_query($sql);    

// gives us the number of results
$num=mysql_numrows($result);

// starts at 0 and starts the loop
$i=0;
while ($i < $num) {

echo mysql_result($result,$i,"Date");
echo mysql_result($result,$i,"Venue");
echo mysql_result($result,$i,"Track");
echo mysql_result($result,$i,"Title"); 

// increments the loop
$i++;

// ends the while statement
}

// ends the outer if statement
} 


Hopefully I didn't have any typos :-)

To clarify what I'm trying to do - the table SetList contains one Set, with many songs. So only the contents of the table Songs need to be looped... I think...
This is exactly why I asked you guys I've been sort of like this guy -> the last three days.