Thread: Mysql count

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    45
    Rep Power
    1

    Mysql count


    I am trying to do a count so any member that downloads an article more than 3 times to get access denied the 4th time. I had come up with the following code, but it doesn't help me at all.
    PHP Code:
    $rest mysqli_query($GLOBALS["___mysqli_ston"], "SELECT COUNT(*) FROM snatched WHERE articleid =".sqlesc($row['id']) ." AND userid =" sqlesc($CURUSER['id'])." GROUP BY userid") or sqlerr(__FILE____LINE__);
    $rowt mysqli_fetch_row($rest);
    $count $rowt[0]; 
    Any advice?
  2. #2
  3. Forgotten Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,211
    Rep Power
    9644
    Query looks fine...

    Same question as before: what is the raw query being created and what happens if you run it in phpMyAdmin?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    45
    Rep Power
    1
    It gives COUNT(*) only 1. But i have downloaded the article from the same test user account over 8 times
    PHP Code:
    SELECT COUNT(*) FROM snatched WHERE articleid =499 AND userid =841 GROUP BY userid 
  6. #4
  7. Forgotten Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,211
    Rep Power
    9644
    And what if you remove the COUNT and GROUP BY?
    sql Code:
    SELECT * FROM snatched WHERE articleid =499 AND userid =841
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    45
    Rep Power
    1
    still only 1 line. hmm i believe i need to add in the insert/update query for the snatch table another field like acount int(10) and inside the queries $acount = acount + 1 ... or something similar. so it can actual count per article per user
  10. #6
  11. Forgotten Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,211
    Rep Power
    9644
    Go for a tracking table: one row every time something happens. So you insert a row when the user downloads an article. Track the user, article, date, and anything else that might be interesting. The benefit is more detailed records about what users do.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    45
    Rep Power
    1
    Managed to fix it. Thank you

IMN logo majestic logo threadwatch logo seochat tools logo