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 3 of 3

Thread: Query trouble

  1. #1
    New Coder
    Join Date
    Aug 2002
    Posts
    86
    Thanks
    2
    Thanked 1 Time in 1 Post

    Query trouble

    Hi

    Im having trouble with the following query. Keeps producing an error and I cant seem to get it to work

    Any ideas people

    PHP Code:
    public function getDownloads($product_id) {
            
            
    $idsql "SELECT download_id FROM " DB_PREFIX "product_to_download" "WHERE product_id = '" $product_id "'";
            
    $id $this->db->query($idsql);
            
            
    $sql "SELECT * FROM " DB_PREFIX "download d LEFT JOIN " DB_PREFIX "download_description dd ON (d.download_id = dd.download_id)" "WHERE d.download_id = $id";
            
            
    $query $this->db->query($sql);
            return 
    $query->rows;    
        } 

  • #2
    New Coder
    Join Date
    Aug 2002
    Posts
    86
    Thanks
    2
    Thanked 1 Time in 1 Post
    I have this code working now

    But it fails when there are more than 2 results drawn from the sub-query.

    Anyone know how to fix this ?

    PHP Code:
    public function getDownloads($product_id) {
            
            
    $sql "SELECT * FROM " DB_PREFIX "download d LEFT JOIN " DB_PREFIX "download_description dd ON (d.download_id = dd.download_id)" "WHERE d.download_id = (SELECT download_id FROM " DB_PREFIX "product_to_download WHERE product_id = '$product_id')";
            
            
    $query $this->db->query($sql);
            return 
    $query->rows;    
        } 

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,133
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    There is no reason at all to use a sub-query in this query.

    Try:
    Code:
    $sql = "SELECT specific, list, of, fields 
         . " FROM " . DB_PREFIX . "product_to_download AS p " 
         . " INNER JOIN DB_PREFIX . "download AS d ON p.download_id = d.download_id "
         . " LEFT JOIN " . DB_PREFIX . "download_description AS dd " ON d.download_id = dd.download_id " 
         . " WHERE p.product_id = '$product_id' ";
    Never use SELECT * especially when more than one table is involved. Doing so is likely to get you multiple duplicate fields which may or may not have the correct values of the fields you are after.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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