Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    Regular Coder
    Join Date
    Sep 2005
    Posts
    153
    Thanks
    34
    Thanked 0 Times in 0 Posts

    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:

    Code:
    <?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
    Last edited by sonofjack; 11-30-2010 at 04:13 PM.

  • #2
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,863
    Thanks
    160
    Thanked 2,224 Times in 2,211 Posts
    $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?
    Last edited by abduraooft; 11-30-2010 at 11:42 AM.
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #3
    Regular Coder
    Join Date
    Sep 2005
    Posts
    153
    Thanks
    34
    Thanked 0 Times in 0 Posts
    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?

  • #4
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,863
    Thanks
    160
    Thanked 2,224 Times in 2,211 Posts
    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.
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #5
    Regular Coder
    Join Date
    Sep 2005
    Posts
    153
    Thanks
    34
    Thanked 0 Times in 0 Posts
    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

    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?

  • #6
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,863
    Thanks
    160
    Thanked 2,224 Times in 2,211 Posts
    Do you still get all the records even after you fill/select all form elements and then submit?
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #7
    Regular Coder
    Join Date
    Sep 2005
    Posts
    153
    Thanks
    34
    Thanked 0 Times in 0 Posts
    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";
    }
    
    
    ?>

  • #8
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,863
    Thanks
    160
    Thanked 2,224 Times in 2,211 Posts
    Don't you have the same repeat of column values in your table? Could you show some sample data from your table?
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #9
    Regular Coder
    Join Date
    Sep 2005
    Posts
    153
    Thanks
    34
    Thanked 0 Times in 0 Posts

    Macintosh

    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

    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 03:55 PM.

  • #10
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,863
    Thanks
    160
    Thanked 2,224 Times in 2,211 Posts
    $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.
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • Users who have thanked abduraooft for this post:

    sonofjack (11-30-2010)

  • #11
    Regular Coder
    Join Date
    Sep 2005
    Posts
    153
    Thanks
    34
    Thanked 0 Times in 0 Posts
    Do you mean as well as?

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

  • #12
    Regular Coder
    Join Date
    Sep 2005
    Posts
    153
    Thanks
    34
    Thanked 0 Times in 0 Posts
    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.


    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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •