...

View Full Version : Resolved Query is returning all records instead of what I select



sonofjack
11-30-2010, 09:43 AM
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:



<?php
include('connect.php');
$select = ' SELECT DISTINCT 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 composerid = $cid ";}

$aid = $_POST['aid'];
if ($aid != ''){ // An album is selected
$where .= " AND 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";
}
?>


Please can anyone out there help?
Thanks

abduraooft
11-30-2010, 10:40 AM
$select = ' SELECT DISTINCT tracks.trackid, tracks.tracktitle, composers.composername, albums.albumname';
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.


echo "DEBUG SQL: " . $sql . "<hr/>\n";
Now, what do you get for the above line?

sonofjack
11-30-2010, 12:09 PM
I've removed the DISTINCT bit (that was there because I was originally selecting from a lookup table). Thanks for pointing it out

However, I'm still getting all the same results, i.e. all tracks next to all composers next to all albums.
The debug message is:

DEBUG SQL: SELECT tracks.trackid, tracks.tracktitle, composers.composername, albums.albumname FROM tracks, composers, albums WHERE 1=1


Something is going a bit wrong. Shouldn't it also include the other $where bits too?

abduraooft
11-30-2010, 01:01 PM
DEBUG SQL: SELECT tracks.trackid, tracks.tracktitle, composers.composername, albums.albumname FROM tracks, composers, albums WHERE 1=1
Means, there's nothing present in the POST array. Please post the HTML of your form.

sonofjack
11-30-2010, 02:08 PM
Hi

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



$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



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



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?

abduraooft
11-30-2010, 02:19 PM
Do you still get all the records even after you fill/select all form elements and then submit?

sonofjack
11-30-2010, 02:28 PM
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



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


?>

abduraooft
11-30-2010, 02:31 PM
Don't you have the same repeat of column values in your table? Could you show some sample data from your table?

sonofjack
11-30-2010, 02:42 PM
All very generic titles for testing purposes...


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



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

abduraooft
11-30-2010, 03:02 PM
$from = ' FROM tracks, composers, albums';

Ah wait.. you need to add much more conditions to JOIN the tables, otherwise it'll return a huge set of result from all the tables.

sonofjack
11-30-2010, 03:05 PM
Do you mean as well as?



$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?

sonofjack
11-30-2010, 03:12 PM
Got it!!

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.




$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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum