...

View Full Version : Query trouble



waps2
01-08-2012, 03:20 PM
Hi

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

Any ideas people



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

waps2
01-08-2012, 04:21 PM
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 ?



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

Old Pedant
01-08-2012, 08:57 PM
There is no reason at all to use a sub-query in this query.

Try:


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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum