Rebuild this function

Hi guys,

I have this function below which works until I there are more than 1 result pulled from the second inside query.

I need to modify it so it loops through the results passed from the middle query, outputting an array of results each time.

Bit stuck on this bit though so need some help ? :confused:


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

You are obviously using some kind of custom database class so without knowing what this is doing it would be difficult to help


$query = $this->db->query($sql);


And what do you expect to return from your function? A raw result set from the db or do you plan to massage the resulting data into another structure before returning it?

Hi Spookster,

Sorry for the long delay in replying lol

This is the custom class function


public function query($sql) {
$resource = mysql_query($sql, $this->connection);

if ($resource) {
if (is_resource($resource)) {
$i = 0;

$data = array();

while ($result = mysql_fetch_assoc($resource)) {
$data[$i] = $result;

$i++;
}

mysql_free_result($resource);

$query = new stdClass();
$query->row = isset($data[0]) ? $data[0] : array();
$query->rows = $data;
$query->num_rows = $i;

unset($data);

return $query;
} else {
return true;
}
} else {
trigger_error('Error: ' . mysql_error($this->connection) . '<br />Error No: ' . mysql_errno($this->connection) . '<br />' . $sql);
exit();
}
}

I think this is some codes from opencart.

Im not really sure what you are up to, but based from the function name.
d.download_id = (middle query) will output a single row even though the middle query has more than 1 result

d.download_id IN (middle query) to get all the downloads for a product id


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 IN (SELECT download_id FROM " . DB_PREFIX . "product_to_download WHERE product_id = '$product_id')";

$query = $this->db->query($sql);
return $query->rows;
}

Hi Spookster,

Sorry for the long delay in replying lol

This is the custom class function


So as spookster was saying, what are you wanting to do with the data you get back?

You're not helping yourself at all here.