Stuck with php/mysql problem. Can't get all info from db.

Beginners - Learning PHP
#1

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.

(Had to put the php in code-brackets… using [ php ] just gives me this output: [ php:1:cf535e05a5 ] )


$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, %sn", "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 lies in my phpscript. I’m not to good at looping either ;).

Code on the called page:


$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"];

Hope someone can help me out with a few pointers, I’m totally stuck.

#2

Hi duckman,

To get all rows from a table in your database, you need to use mysql_fetch_array() which you are doing, but then use a while loop to display them.

For example:

[php]
while ($myrow = mysql_fetch_array($result))

{

echo $myrow[“Date”];
echo $myrow[“Venue”];
echo $myrow[“Track”];
echo $myrow[“Title”];

}
[/php]

You might want to structure the output in a table as this will just list everything one after the other.

[php]
echo “

n”;

while ($myrow = mysql_fetch_array($result))

{

echo “

n”;
echo “n”;
echo “n”;
echo “n”;
echo “n”;
echo “n”;

}
echo “

” . $myrow[“Date”] . “” . $myrow[“Venue”] . “” . $myrow[“Track”] . “” . $myrow[“Title”] . “
n”;
[/php]

Hope that helps. :)

#3

[quote author=“carella”]Hi duckman,

To get all rows from a table in your database, you need to use mysql_fetch_array() which you are doing, but then use a while loop to display them.

For example:

[php]
while ($myrow = mysql_fetch_array($result))

{

echo $myrow[“Date”];
echo $myrow[“Venue”];
echo $myrow[“Track”];
echo $myrow[“Title”];

}
[/php]

You might want to structure the output in a table as this will just list everything one after the other.

[php]
echo “

n”;

while ($myrow = mysql_fetch_array($result))

{

echo “

n”;
echo “n”;
echo “n”;
echo “n”;
echo “n”;
echo “n”;

}
echo “

” . $myrow[“Date”] . “” . $myrow[“Venue”] . “” . $myrow[“Track”] . “” . $myrow[“Title”] . “
n”;
[/php]

Hope that helps. :slight_smile:

The first php-bit loops all SetLists from a given year.
What I’m trying to get the second bit of phpcode, that resides in the page called by the first codebit, is this:

Show all info from the venue I chose on the previous page. The id of the venue is passed along as a variable. But what I can’t seem to do is get all the songs that belongs in that SetList.
I have to - some way or another - get the songs from the “Song”-table - that belongs to that Set (SetList-table), to loop and print…

If you get my drift…

#4

Looks to me as though you need to assign an id number to each song. Then when you add the data to the database, assign the id that corresponds to the venue. This would not be an auto_increment field as each id would be different, but just an INT row to store the id.

Table - Songs. * id INT(5). * Title, PRIMARY KEY, VarChar. * ReleasedOn, Text. * ReleaseYear, Year. * Lyrics, LongText

Then when you pass the venue id, run a query like:

SELECT FROM Songs WHERE id = '$venueid';

Hope that made some kind of sense.

#5

Thanks :slight_smile:

#6
Won't I overwrite the existing venueid for that song, or will this be some kind of tablerow that expands?

You could use an INSERT query that would add another row to the table, in this case, the same song but with a different id number. You would only update the information if you used UPDATE, which would just over write the existing row.

Then pull the songs from the database that have the corresponding id number.

Sponsor our Newsletter | Privacy Policy | Terms of Service