Query is returning all records instead of what I select
I'm currently trying to get this little search engine to work and I know my PHP code must be wrong somewhere. I can do the query okay with SQL via PHPMyAdmin but I can't seem to get the same result via my PHP page.
My tables are
tracks (trackid, tracktitle)
albums (albumid, albumname)
composers (composerid, composername)
I currently have 2 tracks, 2 composers and 12 albums entered for test purposes.
When I try and use my form to either bring up tracks by a certain composer or from a certain album, it lists the two tracks several times assigned to each composer and every album.
My code is:
DISTINCT won't work well when selecting multiple columns since it looks for a DISTINCT row. If the trackid column is UNIQUE, then you don't need that "distinct" there.
Quote:
echo "DEBUG SQL: " . $sql . "<hr/>\n";
Now, what do you get for the above line?
__________________
Quote:
The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)
I did find an error in the form which I've now amended. ($cid=$composer['composers.id']; should have been $cid=$composer['composers.composerid']
However
The code with the form is
Code:
$composers = @mysql_query('SELECT composers.composerid, composers.composername FROM composers');
if(!$composers){exit('<p>Unable to obtain composer list from the database.</p>');}
$albums = @mysql_query('SELECT albums.albumid, albums.albumname FROM albums');
if(!$albums){exit('<p>Unable to obtain album list from the database.</p>');}?>
<form action = "tracklist.php" method="post"><p>View tracks satisfying the following criteria:</p>
<label>By composer:
<select name="cid" size="1"><option selected value="">Any Composer</option>
<?php while($composer = mysql_fetch_array($composers)){
$cid=$composer['composers.composerid'];
$cname=htmlspecialchars($composer['composername']);
echo "<option value='$cid'>$cname</option>\n";}
?>
</select></label><br />
<label>By album:
<select name="aid" size="1"><option selected value="">Any Album</option>
<?php while($alb = mysql_fetch_array($albums)){
$aid=$alb['albums.albumid'];
$aname=htmlspecialchars($alb['albumname']);
echo "<option value='$aid'>$aname</option>\n";}
?></select></label><br />
<label>Containing text:<input type="text" name="searchtext" /></label><br />
<input type="submit" value="Search" /></form>
If I select just an album from the form query I get this DEBUG message
Code:
DEBUG SQL: SELECT tracks.trackid, tracks.tracktitle, composers.composername, albums.albumname FROM tracks, composers, albums WHERE 1=1 AND tracks.albumid = 1
But the results list just contains the one track that is on that album many times next to every album title and composer name.
If I search on just composer then I get everything with the DEBUG message
Code:
DEBUG SQL: SELECT tracks.trackid, tracks.tracktitle, composers.composername, albums.albumname FROM tracks, composers, albums WHERE 1=1 AND tracks.composerid = 1
So there is something amiss with my filtering here. Any ideas?
If I just select a composer then I get all tracks from all albums.
If I just select an album, or just select an album with composer, then I get just the one track listed (as there is only one track on that album at the moment) but repeated next to all composers and all albums.
I'm guessing it's an error in here somewhere... but I can't work out what
Code:
$select = ' SELECT tracks.trackid, tracks.tracktitle, composers.composername, albums.albumname';
$from = ' FROM tracks, composers, albums';
$where = ' WHERE 1=1';
$cid = $_POST['cid'];
if ($cid !=''){ // A composer is selected
$where .= " AND tracks.composerid = $cid ";}
$aid = $_POST['aid'];
if ($aid != ''){ // An album is selected
$where .= " AND tracks.albumid = $aid ";}
$searchtext = $_POST['searchtext'];
if($searchtext !=''){
$where .= " AND tracks.tracktitle LIKE '%$searchtext%'";}
?>
<table>
<tr><th align=left>Track Title</th>
<th align=left>Composer</th>
<th align=left>Album</th></tr>
<?php
$sql = $select . $from . $where;
echo "DEBUG SQL: " . $sql . "<hr/>\n";
$tracks = @mysql_query( $sql );
if (!$tracks) {
echo '</table>';
exit('<p>Error retrieving tracks from database!<br />'.
'Error: ' . mysql_error() . '</p>');
}
while ($track = mysql_fetch_array($tracks)) {
echo "<tr valign='top'>\n";
$id = $track['trackid'];
$tracktitle = htmlspecialchars($track['tracktitle']);
$composername = htmlspecialchars($track['composername']);
$albumtitle = htmlspecialchars($track['albumname']);
echo "<td width=150>$tracktitle</td>";
echo "<td width=150>$composername</td>";
echo "<td width=300>$albumtitle</td>";
echo "<td><a href='edittrack.php?id=$id'>Edit</a> | " .
"<a href='deletetrack.php?id=$id'>Delete</a></td>\n";
echo "</tr>\n";
}
?>
COMPOSERS
composerid composername
1 Composer One
2 Composer Two
ALBUMS
albumid albumname
1 Album One
2 Album Two
3 Album Three
4 Album Four
5 Album Five
6 Album Six
7 Album Seven
8 Album Eight
9 Album Nine
10 Album Ten
11 Album Eleven
12 Album Twelve
TRACKS
trackid albumid composerid tracktitle
8 12 1 Song One
10 1 1 Song Two
If I do a search on just Composer One then I should get
Song One Composer One Album One
Song Two Composer One Album Twelve
But instead I get
Code:
Track Title Composer Album
Song One Composer One Album One
Song Two Composer One Album One
Song One Composer Two Album One
Song Two Composer Two Album One
Song One Composer One Album Two
Song Two Composer One Album Two
Song One Composer Two Album Two
Song Two Composer Two Album Two
and so on for Album Three, Four, Five up to Twelve
Last edited by sonofjack; 11-30-2010 at 02:55 PM..
$cid = $_POST['cid'];
if ($cid !=''){ // A composer is selected
$where .= " AND composerid = $cid ";}
$aid = $_POST['aid'];
if ($aid != ''){ // An album is selected
$where .= " AND albumid = $aid ";}
So do I need to include things like tracks.albumid = albums.albumid and so forth?
Instead of WHERE 1=1 I've put $where = ' WHERE albums.albumid = tracks.albumid AND composers.composerid = tracks.composerid'; and it works a treat. And best of all, it makes complete sense to me now.
Code:
$select = ' SELECT tracks.trackid, tracks.tracktitle, composers.composername, albums.albumname';
$from = ' FROM tracks, composers, albums';
$where = ' WHERE albums.albumid = tracks.albumid AND composers.composerid = tracks.composerid';
$cid = $_POST['cid'];
if ($cid !=''){ // A composer is selected
$where .= " AND tracks.composerid = $cid ";}
$aid = $_POST['aid'];
if ($aid != ''){ // An album is selected
$where .= " AND tracks.albumid = $aid ";}
$searchtext = $_POST['searchtext'];
if($searchtext !=''){
$where .= " AND tracks.tracktitle LIKE '%$searchtext%'";}
?>
Thanks abduraooft for your help and patience.
Much appreciated
Before you post, read our: 
