Help - With Download Count

[elbeer]

I am try to create a function on another page but I require the download count without duplicates for a certain period.

This is the code I have to count the downloads after a certain date:

Code: [Select]

$dbresult = $smcFunc['db_query']('', "

SELECT

ID_LOG

FROM {db_prefix}down_downloads_log

WHERE ID_MEMBER = " . $user_info['id']. " AND date >= $subsperiod ");

//$totalRow = $smcFunc['db_fetch_assoc']($dbresult);

$totalRow['total'] = mysql_num_rows($dbresult);

$count=$totalRow['total'];

What I am requesting from you is how to I re write this query so that duplicate downloads are only counted as 1

Can you help?

[SMFHacks]

Change to

Code: [Select]


Code:

$dbresult = $smcFunc['db_query']('', "

SELECT

count(distinct id_file) as total

FROM {db_prefix}down_downloads_log

WHERE ID_MEMBER = " . $user_info['id']. " AND date >= $subsperiod ");

      $totalRow = $smcFunc['db_fetch_assoc']($dbresult);



$count=$totalRow['total'];


[elbeer]

Awesome thanks and you got a bump on your topic on the other site.

[SMFHacks]

Nice!

[elbeer]

This code you gave me doesnt seem to work with the updated database.

Any chance you could check it for me please.

[SMFHacks]

What do you mean by updated database? Do you get an error?

[elbeer]

No mate. It just does not return the result of the count

[SMFHacks]

Can you show me the exact code you are using?

[elbeer]

I have this code in Downloads2.template.php

Code: [Select]

$dbresult = $smcFunc['db_query']('', "
SELECT
count(distinct ID_LOG) as total
FROM {db_prefix}down_downloads_log
WHERE ID_MEMBER = " . $user_info['id'] . " AND date > ($datea) ");
//$totalRow = $smcFunc['db_fetch_assoc']($dbresult);

$totalRow['total'] = mysql_num_rows($dbresult);

$counttotal=$totalRow['total']-1;
$tot=$totalRow['total'];

Where $datea is the end date of a subscription less 30days using this code:

Code: [Select]

//////////////////////////////////////////////////////Gets end date of subscription

$request = $smcFunc['db_query']('', '
  SELECT end_time
  FROM {db_prefix}log_subscribed
  WHERE id_member = {int:member}',
  array(
    'member' => $user_info['id'],
  )
);
if ($smcFunc['db_num_rows']($request) != 0)
{
  list ($result) = $smcFunc['db_fetch_row']($request);
}
else
  $result = false;
$smcFunc['db_free_result']($request);
$enddate=$result;



[SMFHacks]

You should change this back

Code: [Select]

//$totalRow = $smcFunc['db_fetch_assoc']($dbresult);

$totalRow['total'] = mysql_num_rows($dbresult);
To

Code: [Select]

$totalRow = $smcFunc['db_fetch_assoc']($dbresult);



[elbeer]

Nope still not counting them