Displaying information from 2 MySQL tables on the same page

[dinog]

Hello

 

I'm new to php & mysql and I was wondering if someone could help with a problem I have. I'm creating a small site that has information on a the music used in a tv series. I have 2 tables containing the following columns:

 

EpisodeTable: EpisodeID (primary key) | SeasonID | EpisodeName

SongTable: EpisodeID | SongID (primary key) | Title | Artist | Album

 

I have created a relationship with the EpisodeID as foreign key in the songtable. Basically I want to display all songs used for each episode in a table, then the next episode in another table and so on on 1 page (which will be 1 season). I have managed to display song information for the first epsiode, but am unsure of how to do it for all episodes. I also want to split the episodes up into seasons, 1 season per page (html page). This is what I have so far

 

<?php 
include 'dbconfig.php';
include 'dbopen.php';

$query2  = "SELECT * FROM SongTable WHERE EpisodeID=1";
$result2 = mysql_query($query2);

print "<h2><a name=ep> Episode: </a></h2><p>";
print "<table width=95% border=1 cellpadding=4px cellspacing=0>";
print "<tr bgcolor=#d0d0d0>";
print "<td width=20%><strong>Song Title</strong><td width=20%><strong>Artist</strong><td width=30%><strong>Album information</strong>";
while ($row2 = mysql_fetch_assoc($result2)) {
        $title = $row2['Title'];
$artist = $row2['Artist'];
$album = $row2['Album'];
print "<tr><td>$title";
print "<td>$artist";
        print "<td>$album";

}

print "</table><p>";

include 'dbclose.php';


?>

 

How would i go about modifying the code so that this table is displayed for each episode's song information? Also how would I only display episodes from that particular season on 1 page and epsiodes from the 2nd, 3rd etc. on a different page? I intend to copy the code for each season and just change the variables like SeasonID. Any help is appreciated. I'm using Apache 2.2.8, PHP 5.2.5 and MySQL 5.0.51a on windows xp. Many thanks.

[Psycho]

<?php

include 'dbconfig.php';
include 'dbopen.php';

$query = "SELECT *
          FROM SongTable
          JOIN EpisodeTable ON EpisodeTable.EpisodeID = SongTable.EpisodeID
          ORDER BY EpisodeTable.EpisodeID";
$result = mysql_query($query2) or die(mysql_error());

$current_episode = '';

while ($record = mysql_fetch_assoc($result)) {

    //New episode record
    if ($record['EpisodeName'] != $current_episode) {

        //Close previous table (if exist)
        if ($current_episode != '') {
            print "</table><p>";
        }

        //Create new episode table
        $current_episode = $record['EpisodeName'];
        print "<h2><a name=\"ep\">Episode: $current_episode</a></h2><p>";
        print "<table width=\"95%\" border=\"1\" cellpadding=\"4px\" cellspacing=\"0\">";
        print "<tr bgcolor=\"#d0d0d0\">";
        print "<td width=\"20%\"><strong>Song Title</strong><td width=20%><strong>Artist</strong><td width=30%><strong>Album information</strong>";
    }

    //Display the song record
    print "<tr><td>{$record['Title']}</td><td>{$record['Artist']}</td><td>{$record['Album']}</td></tr>";

}

//Clost the last table
print "</table><p>";

include 'dbclose.php';

?>

[dinog]

Wow that was quick. Thanks mjdamato. I just wish I understood what you did there. I guess i'll have to study this for a while to understand it but it works brilliantly. Thanks again.

[soycharliente]

<?php
$query = "SELECT *
          FROM SongTable
          JOIN EpisodeTable ON EpisodeTable.EpisodeID = SongTable.EpisodeID
          ORDER BY EpisodeTable.EpisodeID";
?>

See if you can understand that above code after this explanation.

 

The best way to figure out what JOIN does is my playing around with it. When I first started using JOIN I would simply go into phpMyAdmin and run random queries in the SQL window and see what came back. I had a few tables that were related and had overlapping columns.

 

Here's an example:

USERS

----------

id

username

password

email

firstname

lastname

[/td]

[td]

POSTS

----------

id

user_id

title

body

 

So this is very basic. You've got a table of users and a table of posts. Now let's say that when grabbing all the posts from a database, you want to show the firstname and lastname of the user that owns that post. One way, which you started doing, would be to have 2 separate queries to grab the data. A JOIN would work best here because you can get all the information you need in 1 query because you have an overlapping element - the user's id.

 

See if you can follow:

<?php
$query = "SELECT * FROM users JOIN posts ON posts.user_id = users.id";
?>

 

So what that's doing is going through a returning rows with all the data from BOTH TABLES where the id in users and the user_id in posts are the same.

 

Does that make any sense? It's a bit complicated at first, but once you get your head around it, it can be very helpful.

[dinog]

Thanks charlieholder. It makes much more sense now.. at first I was worried that the code would join the two tables literally because I didn't know you could join information from two tables JUST for a query. That's a really handy thing to know. 1 more question: if i wanted to sort the song information in the tables by SongID, would i just change the

ORDER BY EpisodeTable.EpisodeID

to

ORDER BY SongTable.SongID

? Thanks to both of you for your help.

[soycharliente]

Yes.

 

You can also append other things to end like 'ASC' and 'DESC' to get them in ascending or descending order.

[dinog]

Thanks charlie! One more quick question: Some artist names contain accented characters. eg Rubén González. The broswer is displaying those characters as a box. Is this an issue with the encoding of the characters in the database or is it an issue with the php script with an incorrect setting?

[soycharliente]

Most likely the encoding.

 

You could try htmlspecialchars() before putting it into the db. I've never dealt with accented letter before so I really wouldn't be able to comment.

[Psycho]

Thanks charlieholder. It makes much more sense now.. at first I was worried that the code would join the two tables literally because I didn't know you could join information from two tables JUST for a query. That's a really handy thing to know. 1 more question: if i wanted to sort the song information in the tables by SongID, would i just change the

ORDER BY EpisodeTable.EpisodeID

to

ORDER BY SongTable.SongID

? Thanks to both of you for your help.

 

You are going to want to use

ORDER BY EpisodeTable.EpisodeID, SongTable.SongID

To first sort by Episodes and then sort by Songs

[dinog]

hello

my site is up and running but now i'm attempting to add a simple search feature. i've got it working almost exactly how i want it. it will display results as follows:

 

Search results:

<h2>Episode name: x</h2>

 

Title:

Artist:

Album:

 

the problem is i want to display the episode name header only once if there is more than 1 result for a particular episode.  at the moment it displays as follows:

 

Search results:

Episode name: episode 1

 

Title: track 1

Artist: artist 1

Album: album 1

 

Episode name: episode 1

 

Title: track 2

Artist: artist 2

Album: album 2

 

but i want it like this:

 

Episode name: episode 1

 

Title: track 1

Artist: artist 1

Album: album 1

 

Title: track 2

Artist: artist 2

Album: album 2

 

how do i accomplish this?

 

here is my search code if required:

<?php function searchresult() {
/**************************************************************************************
* Main Search Page - search.php
* Author: Your Name <email@something.com>
* This file searches the database
**************************************************************************************/

//Get variables from config.php to connect to mysql server
include 'dbconfig.php';
include 'dbopen.php';
//search variable = data in search box or url
if(isset($_GET['search']))
{
$search = $_GET['search'];
}

//trim whitespace from variable
$search = trim($search);
$search = preg_replace('/\s+/', ' ', $search);

//seperate multiple keywords into array space delimited
$keywords = explode(" ", $search);

//Clean empty arrays so they don't get every row as result
$keywords = array_diff($keywords, array(""));

//Set the MySQL query
if ($search == NULL or $search == '%'){
} else {
for ($i=0; $i<count($keywords); $i++) {
$query = "SELECT * FROM songtable JOIN episodetable ON episodetable.EpisodeID = songtable.EpisodeID " .
"WHERE Title LIKE '%".$keywords[$i]."%'".
" OR Artist LIKE '%".$keywords[$i]."%'" .
" OR Album LIKE '%".$keywords[$i]."%'" .
" ORDER BY songtable.EpisodeID, songtable.SongID ASC";
}

//Store the results in a variable or die if query fails
$result = mysql_query($query) or die(mysql_error());
}
if ($search == NULL or $search == '%'){
} else {
//Count the rows retrived
$count = mysql_num_rows($result);
}


//If search variable is null do nothing, else print it.
echo "<table cellspacing=0 cellpadding=10 width=\"100%\" border=0>";
if ($search == NULL) {
} else {
echo "<tr><td align=center>You searched for <b>";
foreach($keywords as $value) {
   print "$value ";
}
echo "</b>";
}

//If users doesn't enter anything into search box tell them to.
if ($search == NULL){
echo "<tr><td align=center><b>Please enter a search parameter to continue.</b></table>";
} elseif ($search == '%'){
echo "<tr><td align=center><b>Please enter a search parameter to continue.</b></table>";
//If no results are returned print it
} elseif ($count <= 0){
echo "<tr><td align=center><b>Your query \"$value\" returned no results from the database.</b></table>";
//ELSE print the data in a table
} else {

$currentcolor = "#ffffff";
while($row = mysql_fetch_array($result))
{
//echo "";
$title = $row['Title'];
$artist = $row['Artist'];
$album = $row['Album'];
$mod_artist = '';
$albumlink = $row['AlbumLink'];
$usage = $row['Usage'];
if ($title == "") {
$currentcolor = ($currentcolor=='#ffffff' ? '#f2f2f2' : '#ffffff');
echo "<tr align=left bgcolor=$currentcolor><td><div id=\"eptable\"><h2 align=center>Episode {$row['EpisodeID']}: {$row['EpisodeName']}</h2>\n<b>Title:</b><br>\n";
} else {
$currentcolor = ($currentcolor=='#ffffff' ? '#f2f2f2' : '#ffffff');
echo "<tr align=left bgcolor=$currentcolor><td><div id=\"eptable\"><h2 align=center>Episode {$row['EpisodeID']}: {$row['EpisodeName']}</h2>\n<b>Title:</b> $title<br>\n";
}
if ($artist == "") {
echo "<b>Artist:</b><br>\n";
} else {
echo "<b>Artist:</b> $artist<br>\n";
}
if ($album == "") {
echo "<b>Album:</b><br>\n";
} else {
if ($record['AA'] == "1") {
$mod_artist = $record['AAName'];
echo "<b>Album:</b> <a href=$albumlink>$mod_artist - $album</a><br>\n";
} else {
echo "<b>Album:</b> <a href=$albumlink>$artist - $album</a><br>\n"; 
}
}
echo "<b>Usage:</b> $usage</div>\n";

$row_count++;
//end while
}
echo "</table>";
//end if
}
if ($search == NULL or $search == '%') {
} else {
//clear memory
mysql_free_result($result);
}
include 'dbclose.php';
}
?>

 

thanks very much

[Psycho]

You have a lot of unnecessary code in there. For example

//search variable = data in search box or url
if(isset($_GET['search']))
{
$search = $_GET['search'];
}

 

But, then you use the variable $search anyway - whether or not you set it. I have made a lot of changes in the code below to fix some of the issues and to make the code easier to follow. I'm sure there are some syntax errors as I could not test it - but it should be close

 

<?php function searchresult() {
/**************************************************************************************
* Main Search Page - search.php
* Author: Your Name <email@something.com>
* This file searches the database
**************************************************************************************/

//search variable = data in search box or url
$search = trim($_GET['search']);

if (empty($search)) {

   //No search values entered
   echo "<table cellspacing=0 cellpadding=10 width=\"100%\" border=0>";
   echo "<tr><td align=center><b>Please enter a search parameter to continue.</b></td></tr>";
   echo "</table>";

} else {

    //Get variables from config.php to connect to mysql server
    include 'dbconfig.php';
    include 'dbopen.php';

    //Remove duplicate whitespace
    $search = preg_replace('/\s+/', ' ', $search);

    //seperate multiple keywords into array space delimited
    $keywords = explode(" ", $search);

    //Clean empty arrays so they don't get every row as result (not really needed)
    $keywords = array_diff($keywords, array(""));

    //Make values db safe
    foreach ($keywords as $key=> $value) {
        $keywords[$key] = mysql_real_escape_string($value);
    }

    //Create the query
    $titleClause  = "Title Like '%" . implode("%'\n   OR Title Like '%", $keywords) . "%'";
    $artistClause = "Artist Like '%" . implode("%'\n   OR Artist Like '%", $keywords) . "%'";
    $albumClause  = "Album Like '%" . implode("%'\n   OR Album Like '%", $keywords) . "%'";

    $query = "SELECT *
              FROM songtable
                JOIN episodetable ON episodetable.EpisodeID = songtable.EpisodeID
              WHERE $titleClause
                 OR $artistClause
                 OR $albumClause
              ORDER BY songtable.EpisodeID, songtable.SongID ASC";

    //Store the results in a variable or die if query fails
    $result = mysql_query($query) or die("The query:<br />$query<br />Produced the error:<br />".mysql_error());

    //Check returned results
    if (mysql_num_rows($result)==0) {

        //No reults returned
        echo "<table cellspacing=0 cellpadding=10 width=\"100%\" border=0>";
        echo "<tr><td align=center><b>Your query \"$value\" returned no results from the database.</b></td></tr>";
        echo "</table>";

    } else {

        echo "<table cellspacing=0 cellpadding=10 width=\"100%\" border=0>";
        echo "<tr><td align=center>You searched for <b>" . implode (", ", $keywords) . "</b></td></tr>";

        $current_title = '';

        while($row = mysql_fetch_array($result)) {

            //Alternate album row colors
            $currentcolor = ($currentcolor=='#ffffff' ? '#f2f2f2' : '#ffffff');

            if ($current_title != $row['Title']) {

                //New title
                $current_title == $row['Title'];
                echo "<tr><td style=\"text-align:center;background-color:#000000;color:#ffffff;\">\n";
                echo "<div id=\"eptable\"><h2>Episode {$row['EpisodeID']}: {$row['EpisodeName']}</h2>\n";
                echo "<b>Title:</b> {$row['Title']}</div>\n";
                echo "</td></tr>\n";

                $currentcolor=='#ffffff';
            }

            //Show the album record
    $artist = ($record['AA'] == "1")? $record['AAName'] : $row['Artist'] ;
            echo "<tr><td style=\"background-color:$currentcolor;text-align:left;\">\n";
            echo "<b>Artist:</b> {$row['Artist']}<br>\n";
            echo "<b>Album:</b> <a href={$row['AlbumLink']}>$artist - {$row['Album']}</a><br>\n"; 
            echo "</td></tr>\n";	    

}

        //Close results table
        echo "</table>\n";

    }

    //Clean up db connection
    mysql_free_result($result);
    include 'dbclose.php';
}


?>

[dinog]

Hi mjdamato

 

The code was copy/pasted from some site i found on google, which is why it may not be optimal. I had no idea how to start so i used it as a base. The code you gave me gives me the same result when two songs are from the same episode. I'm trying to group songs from the same episode in the search results so that the episode name is displayed only once, IF there is more than 1 result from a particular episode. I hope that isn't too confusing.

[Psycho]

Hmmm, it shouldn't repeat the title due to this

            if ($current_title != $row['Title']) {

                //New title
                $current_title == $row['Title'];

[dinog]

The episode name is actually different from title. The title is the title of the song. row['EpisodeName'] is the episode name. I tried changing the variables around, from Title to EpisodeName, but still have the same result. Any ideas?

[Psycho]

Try this:

 

<?php function searchresult() {
/**************************************************************************************
* Main Search Page - search.php
* Author: Your Name <email@something.com>
* This file searches the database
**************************************************************************************/

//search variable = data in search box or url
$search = trim($_GET['search']);

if (empty($search)) {

   //No search values entered
   echo "<table cellspacing=0 cellpadding=10 width=\"100%\" border=0>";
   echo "<tr><td align=center><b>Please enter a search parameter to continue.</b></td></tr>";
   echo "</table>";

} else {

    //Get variables from config.php to connect to mysql server
    include 'dbconfig.php';
    include 'dbopen.php';

    //Remove duplicate whitespace
    $search = preg_replace('/\s+/', ' ', $search);

    //seperate multiple keywords into array space delimited
    $keywords = explode(" ", $search);

    //Clean empty arrays so they don't get every row as result (not really needed)
    $keywords = array_diff($keywords, array(""));

    //Make values db safe
    foreach ($keywords as $key=> $value) {
        $keywords[$key] = mysql_real_escape_string($value);
    }

    //Create the query
    $titleClause  = "Title Like '%" . implode("%'\n   OR Title Like '%", $keywords) . "%'";
    $artistClause = "Artist Like '%" . implode("%'\n   OR Artist Like '%", $keywords) . "%'";
    $albumClause  = "Album Like '%" . implode("%'\n   OR Album Like '%", $keywords) . "%'";

    $query = "SELECT *
              FROM songtable
                JOIN episodetable ON episodetable.EpisodeID = songtable.EpisodeID
              WHERE $titleClause
                 OR $artistClause
                 OR $albumClause
              ORDER BY songtable.EpisodeID, songtable.SongID ASC";

    //Store the results in a variable or die if query fails
    $result = mysql_query($query) or die("The query:<br />$query<br />Produced the error:<br />".mysql_error());

    //Check returned results
    if (mysql_num_rows($result)==0) {

        //No reults returned
        echo "<table cellspacing=0 cellpadding=10 width=\"100%\" border=0>";
        echo "<tr><td align=center><b>Your query \"$value\" returned no results from the database.</b></td></tr>";
        echo "</table>";

    } else {

        echo "<table cellspacing=0 cellpadding=10 width=\"100%\" border=0>";
        echo "<tr><td align=center>You searched for <b>" . implode (", ", $keywords) . "</b></td></tr>";

        $current_episode_id = '';

        while($row = mysql_fetch_array($result)) {

            //Alternate album row colors
            $currentcolor = ($currentcolor=='#ffffff' ? '#f2f2f2' : '#ffffff');

            if ($current_episode_id != $row['EpisodeID']) {

                //New title
                $current_episode_id == $row['EpisodeID'];
                echo "<tr><td style=\"text-align:center;background-color:#000000;color:#ffffff;\">\n";
                echo "<div id=\"eptable\"><h2>Episode {$row['EpisodeID']}: {$row['EpisodeName']}</h2></div>\n";
                echo "</td></tr>\n";

                $currentcolor=='#ffffff';
            }

            //Show the album record
    $artist = ($record['AA'] == "1")? $record['AAName'] : $row['Artist'] ;
            echo "<tr><td style=\"background-color:$currentcolor;text-align:left;\">\n";
            echo "<b>Title:</b> {$row['Title']}<br>\n";
            echo "<b>Artist:</b> {$row['Artist']}<br>\n";
            echo "<b>Album:</b> <a href={$row['AlbumLink']}>$artist - {$row['Album']}</a><br>\n"; 
            echo "</td></tr>\n";	    

}

        //Close results table
        echo "</table>\n";

    }

    //Clean up db connection
    mysql_free_result($result);
    include 'dbclose.php';
}

?>

[dinog]

Nope, didn't work  . Same result as before.

[Psycho]

Oh, hell - I had a typo.

 

Change this:

                //New title
                $current_episode_id == $row['EpisodeID'];

 

To this: (remove the second equal sign)

                //New title
                $current_episode_id = $row['EpisodeID'];

[dinog]

Yes! Success! Thanks mjdamato!