3

I have a table called Stats. Stats has 4 columns: id, ip, count, and date. count is the number of clicks the ip has clicked on the ads on my site. Each time the user clicks on an ad, their count number will increase by 1. How do I increase their count number by 1 and update that in the database? Here is my code and it's not working for some reason. When I click on the div, it doesn't refresh...so the whole block of code isn't executing. Note that I've already captured the user's ip when they entered my site, this is the part where if they clicked my ad, the count is incremented and updated in the database.

<script>
    $(document).ready(function()
    {
        $("#ad").click(function()
        {
            <?php
                $queryTot = "SELECT `count` AS total FROM Stats WHERE ip = '$ip'";
                $resultTot = $db->query($queryTot);
                $data = $resultTot->fetch_assoc();
                $count = $data["total"] + 1;
                $insert = $db->prepare("UPDATE Stats(count) WHERE ip = '$ip' VALUES(?)");
                $insert->bind_param('i', $count);
                $insert->execute();
                $insert->close();
            ?>
            location.reload();
        })
    })

</script>
Zakaria Acharki
  • 66,747
  • 15
  • 75
  • 101
Bytes
  • 691
  • 1
  • 7
  • 22
  • Good you use prepared stmts. But you fall short non the less. All variables should be parameterised including the IP as well. – MyGGaN Aug 11 '15 at 19:59
  • 1
    Oh wow, where to begin? I could start with saying that mixing php and javascript is bad practise. But, not only that.. your code just won't work the way it's written. Use an AJAX request to update the database in the background instead. – faerin Aug 11 '15 at 20:00
  • I also have to say this question is possibly duplicated with [Basic PHP and AJAX](http://stackoverflow.com/questions/5298401/basic-php-and-ajax) – victorf Aug 11 '15 at 20:32

2 Answers2

0

There is a lot of points to consider in your answer.

But very possibly you should use an AJAX solution to do it, and avoid every SQL queries in your HTML pages, because keeping SQL queries there definitely is not a good pratice, according all basic security and code maintenance POVs.

It is not possible to re-write your code here rightly without knowing your project structure, or even your idea, and you must take this answer as an important start point.

Basically, you must define in your server-side application a method which returns pure data, in a JSON format for example, and then use AJAX to access this method according an event (a click, for example), receive its response and modify your client-side, probably with jQuery and JS.

I hope this answer can help you.

victorf
  • 978
  • 2
  • 17
  • 35
  • So if I use AJAX, would it be the one to modify my database? – Bytes Aug 11 '15 at 20:12
  • Yes, you would create an AJAX for access an PHP method, through an URL with parameters that you defined in AJAX. Then, this method in server-side will modify you DB and bring a response for you AJAX (client-side). Take an example here: http://jsfiddle.net/danmana/y6XgY/ and don't forget to choose my answer (; – victorf Aug 11 '15 at 20:15
  • there is an full example with AJAX + PHP + MySQL http://www.w3schools.com/php/php_ajax_database.asp – victorf Aug 11 '15 at 20:17
  • The example above show a pure AJAX, but you can use AJAX by jQuery, which is more friendly IMHO. Take one more example here: http://www.sitepoint.com/demos/ajax/ – victorf Aug 11 '15 at 20:19
  • And I have to say: this question is possibly duplicated with http://stackoverflow.com/questions/5298401/basic-php-and-ajax (: – victorf Aug 11 '15 at 20:30
0

I've written a short example for you that you could continue to build on to accomplish what you need. Here's the basic idea.

HTML

<input type="hidden" id="ip" value="<?php echo $_SERVER['REMOTE_ADDR'];?>"/>

jQuery

var ip = $('#ip').val();

$(document).ready(function(){
   $('#ad').on('click',function(){
      $.ajax({
          type: 'POST',
          url: 'ajaxUpdateDatabase.php',
          data: 'ip='+ ip,
          success: function(response){
              console.log(response)
              //send the user to the ad's page here for example
              //you could use location.href='url-to-add-here';
              //or if you really want to reload the page for a reason I fail to understand, use location.reload();
              }
          });
      });
   });

PHP (ajaxUpdateDatabase.php)

//please note that using UPDATE requires that there is previously an entry with this IP address.
//example using PDO... 

$sql = 'SELECT * FROM stats WHERE ip = ?';
$stmt = $pdo->prepare($sql);
$stmt->execute(array($_POST['ip']));

if($stmt -> rowCount() > 0){
    $sql = 'UPDATE stats SET count = count + 1 WHERE ip = ?';
    $stmt = $pdo->prepare($sql);
    $stmt->execute(array($_POST['ip']));
    }
else{
    //ip-address was not found in the database
    //insert query goes here
    }
faerin
  • 1,915
  • 17
  • 31
  • What if I already got the user's i.p in the beginning and checked to see if they're already in the database. Do I need to write the first part? Also, on the success can I refresh the page using location.reload()? – Bytes Aug 11 '15 at 20:21
  • Sure, if you already has the user's IP the code I mentioned above should wortk. I would recommend writing a small control in the file ajaxUpdateDatabase.php to check whether IP exists and then update. If it doesn't exist you insert it. Please note, the PHP file with the queries is a different file from where the HTML and the jQuery is located. Why would you want to refresh the page? Shouldn't the user be sent to the ad location instead? The AJAX requests runs in the background, no page refresh is needed for the sake of updating the database. – faerin Aug 11 '15 at 20:25
  • The reason why I want the page to refresh is because I'm trying to make a system where it captures the user's ip address, checks their click count in the database. If the click count exceeds 1, then ads won't be shown. If it doesn't ads will be shown. So when the user clicks an ad, the page refreshes to check if the click count is > 1. If it's not, then ads will be shown. So basically a system to protect against click bombing. I've been at it for 4 days now, and I can't figure out how to implement it right... – Bytes Aug 11 '15 at 20:43
  • Well, in that case your logic is not right. Why not just just doing that check when you loop out/display your ads on the page? The code example I showed will simply increase the count by 1 for an existing IP address.. or add a new entry for a new IP address in a smooth way. – faerin Aug 11 '15 at 20:48
  • I have code that already checks ip and checks count number, and it works good in php. My only problem is increasing count and updating the database when a user clicks on the div where my ad is located. – Bytes Aug 11 '15 at 20:52
  • Do I copy the bottom part or both parts? Since I already capture the i.p in my code? – Bytes Aug 11 '15 at 22:05
  • Also, the ad is from adsense, so I don't know the exact ad page. I want to refresh the page so my if statement will check if the count number is greater than 1. If it is, then ads will not be shown – Bytes Aug 11 '15 at 22:14