Newbie Help with MYSQL &amp; PHP

Newbie Help with MYSQL & PHP

artisticre

I am doing a project for my church while trying to teach myself MYSQL & PHP. I have a database:

CREATE TABLE songset (
songID tinyint(4) NOT NULL auto_increment,
songlink varchar(50) NOT NULL default '',
songname varchar(50) NOT NULL default '',
songdate varchar(20) NOT NULL default '',
PRIMARY KEY (songID)
) TYPE=MyISAM;

I want to be able to put the names of the songs in and then pull out a song based on the date. The problem I am running into is some songs will be used on future dates and I don't want to enter the name twice with another date. If I have only one date in the database, I can pull the songs based on the date I am looking by entering SELECT * from songset where songdate = 'I hardcode the date I want';

But what if I want two sets from two different days on the same page?

Thanks for the help in advance

dustbuster

If you want to be able to have multiple dates but only have to enter the song information once I would suggest using 2 tables

CREATE TABLE songset (
songID tinyint(4) NOT NULL auto_increment,
songlink varchar(50) NOT NULL default '',
songname varchar(50) NOT NULL default '',
PRIMARY KEY (songID)
) TYPE=MyISAM;

CREATE TABLE songdates(
songID tinyint(4) NOT NULL,
songdate varchar(20) NOT NULL default '',
PRIMARY KEY(songID,songdate)
)TYPE=MyISAM;

Then to select the songs based on the date:

SELECT s.* from songset s, songdates d where d.songdate='blah' and d.songID=s.songID

You may also want to look at linked tables but this is the way I know how to do it

Note:
PRIMARY KEY(songID,songdate) will ensure that you don't have duplicate entries for a date and a song.

kevinsequeira

NM, dustbuster just posted what i wanted to post😃

reg
kevin

artisticre

Thanks I will try this. One last question, where you say to put d.songID=s.songID, what if I only want to pull say 3 songs on a particular date instead of them all. How do you code it to pick the exact songID's?

thanks

artisticre

OK I tried your suggestion. I think it works but it doesn't pull the info out. Probably my coding:

$result = mysql_query("SELECT s.* from songset s, songdates d where d.songdate='06/22/2003' and d.songID=s.songID",$db);

$pathtype ="doc";

if ($rows = mysql_fetch_array($result)) {

do {

echo ("<table border=0 align=center width='95%' cellspacing=0 cellpadding=0>

       <tr>
<td width=25% rowspan=2 align=center><a class=label href=$rows[songlink].$pathtype>$rows[songname]</a></td>

</tr>

");

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

echo ("</table>\n");

} else {

echo "<br><br><p align=center>No Songlist Available!</p>";	
    echo "<br><br><p align=center>Try Your Search Again!</p>";

}

dustbuster

That depends on how you want to select the songs

Is it 3 random songs?
Do you know the song names?
Are you selecting from select in a form?
Are you entering in a text field the names (form)?

If you know the song name and entering it in a text field

$thesong=$HTTP_POST_VARS['songname'];
...
SELECT s.* from songset s, songdates d where d.songdate='blah' and d.songID=s.songID and s.name LIKE '%$thesong%'

The % signs are wild cards

dustbuster

What do you mean when you say it doesn't get the data?

Do you get an error?
Is there nothing on the screen?
Can you view the source and see what it says?

artisticre

No I'm not using a form. I am printing them to a page. Is it possible to print a list of songs by selecting say song A, B, C... for the day I need it

artisticre

I don't get an error. My error checking code where it says

echo " No Songlist Available!";
echo " Try Your Search Again!";

comes up. Acting like either there is no data in the table, which there is or my search criteria isn't correct

dustbuster

Can you try this just for testing purposes?

$query = "SELECT s.* from songset s, songdates d where d.songdate='06/22/2003' and d.songID=s.songID";

$result=mysql_query($query,$db) or die ("Invalid Query: ".mysql_error().' '.$query);

if (mysql_num_rows($result)>0){
while ($row=mysql_fetch_assoc($result)){
echo $row['name']."<br>";
echo $row['songname']."<br>";
}
}
else
 echo "No Songs match that criteria.";

artisticre

OK I got it to work. The only other thing I need to know how to do is if I want to list just song A,B,D,H...how do you state that in a select statement?

dustbuster

You will have to do seperate select statements if you wish to list individual by name:

$query = "SELECT * from songset where songname='A' ";
dosomething

$query = "SELECT * from songset where songname='B' ";
dosomething