Want to show the total downloads

  • Posts: 386
  • Thank you received: 7
  • Hikashop Business
4 years 3 months ago #314327

-- HikaShop version -- : 4.2.2
-- Joomla version -- : 3.9.14
-- PHP version -- : 7.2.x
-- Error-message(debug-mod must be tuned on) -- : total downloads per product

Hello,

I want to show the total times a product is already downloaded of each product on product listing img description I know the total number is in the table hikashop_download. I found this to query it but it doesn't seem to work

$query = "SELECT `download_number` FROM `#__hikashop_download` WHERE `file_id` = '" . $file->file_id . "'";
$db->setQuery($query);
$downloads = $db->loadResult();

Please help..
Thanks.

Please Log in or Create an account to join the conversation.

  • Posts: 81509
  • Thank you received: 13064
  • MODERATOR
4 years 3 months ago #314330

Hi,

Note that in the download table, you have one row per file per order.
So your query doesn't return the total number of downloads for a file but the number of downloads of a file for one of the orders at random.
You would need to modify the query to sum the download numbers with a group by.
stackoverflow.com/questions/6105767/mysql-group-by-sum

Also, this query supposes that the variable $file->file_id exists. I don't know where you add that code but supposing that it is in the , view file listing_img_description there is no $file variable there you can use. So either you didn't post the whole code you wrote or that would explain why the query doesn't return anything. In listing_img_description, you would first have to run a first MySQL query on the hikashop_file table to get the list of files of the current product based on file_ref_id equal to $this->row->product_id and the file_type equal to "file". That would get you the file_id for each file of the current product and then you could run the query on hikashop_download to get the number of downloads for each file.

Please Log in or Create an account to join the conversation.

  • Posts: 386
  • Thank you received: 7
  • Hikashop Business
4 years 3 months ago #314647

Hello,

Still a bit struggling with the totals.
But in my phpmyadmin i now get a list of every file_id with the total downloads grouped. (see attachment)
How can i call the right id for a product with the download number within the product listing img view after i call on this first out of the database?

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
$sql = "SELECT `pa8ju_hikashop_download`.`file_id`, SUM(`pa8ju_hikashop_download`.`download_number`), `pa8ju_hikashop_file`.`file_ref_id` ,`pa8ju_hikashop_file`.`file_id`, `pa8ju_hikashop_product`.`product_id` FROM `pa8ju_hikashop_file`, `pa8ju_hikashop_download`, `pa8ju_hikashop_product` WHERE`pa8ju_hikashop_product`.`product_id`= `pa8ju_hikashop_file`.`file_ref_id` AND `pa8ju_hikashop_download`.`file_id` =`pa8ju_hikashop_file`.`file_id`GROUP BY `pa8ju_hikashop_download`.`file_id` AND `file_id` = '" . $file->file_id . "' ";  
$result = mysqli_query($conn, $sql);
if ($result->num_rows > 0) 
while($row = $result->fetch_assoc()){
echo "Aantal downloads: " .$row["download_number"]. "";

} else {
    echo "0 results";
}

mysqli_close($conn);
?>
Hope you can help.

Thanks

Attachments:
Last edit: 4 years 3 months ago by nicolas.

Please Log in or Create an account to join the conversation.

  • Posts: 81509
  • Thank you received: 13064
  • MODERATOR
4 years 3 months ago #314648

Hi,

Well, first, I would recommend to use Joomla's functions, use LEFT JOINs and aliases. It will be much better.
For example:

$query = "SELECT SUM(d.download_number) AS total FROM #__hikashop_file AS f 
LEFT JOIN #__hikashop_download AS d ON d.file_id = f.file_id
LEFT JOIN #__hikashop_product AS p ON p.product_id = f.file_ref_id
WHERE p.product_id = " . (int) $this->row->product_id . "
GROUP BY d.file_id";
$db = JFactory::getDBO();
$db->setQuery($query);
$download = $db->loadObject();
echo $download->total;

Please Log in or Create an account to join the conversation.

Time to create page: 0.055 seconds
Powered by Kunena Forum