Combine JQuery/PHP to log clicks into database?

database, jquery, php

Solution

You can do something like this (untested):

Define a javascript variable to track the order of the clicks, outside your click function:

var order = 0;

Add this into your click function, at the bottom:

order++;
var sessionID = $("input[name='sessionID']").val();  // assuming you have sessionID as the value of a hidden input
var query = $("#query").text();    // if 'query' is the id of your searchbox
var pos = $(this).index() + 1;     // might have to modify this to get correct index
$.post("logClick.php", {sessionID:sessionID, query:query, pos:pos, order:order});

In your php script called "logClick.php" (in the same directory):

<?php
    // GET AJAX POSTED DATA
    $str_sessionID = empty($_POST["sessionID"]) ? '' ; $_POST["sessionID"];
    $str_query = empty($_POST["query"]) ? '' ; $_POST["query"];
    $int_pos = empty($_POST["pos"]) ? 1 ; (int)$_POST["pos"];
    $int_order = empty($_POST["order"]) ? 1 ; (int)$_POST["order"];

    // CONNECT TO DATABASE
    if ($str_sessionID && $str_query) {
        require_once "dbconnect.php";    // include the commands used to connect to your database. Should define a variable $con as the mysql connection

        // INSERT INTO MYSQL DATABASE TABLE CALLED 'click_logs'
        $sql_query = "INSERT INTO click_logs (sessionID, query, pos, order) VALUES ('$str_sessionID', '$str_query', $int_pos, $int_order)";
        $res = mysql_query($sql_query, $con);
        if (!$res) die('Could not connect: ' . mysql_error());
        else echo "Click was logged.";
    }
    else echo "No data found to log!";
?>

You can add a callback function as a third parameter for the $.post() ajax method if you want to see if errors occured in the script:

$.post("logClick.php", {sessionID:sessionID, query:query, pos:pos, order:order},
    function(result) {
      $('#result').html(result);  // display script output into a div with id='result'
      // or just alert(result);
    })
);

EDIT: If you need the value of the order variable to persist between page loads because you paginated your results, then you can pas the value of this variable between pages using either GET or POST. You can then save the value in a hidden input and easily read it with jQuery. (Or you could also use cookies).

Example (put this in every results page):

<?php
   $order = empty($_POST["order"]) ? $_POST["order"] : "0";
   $html="<form id='form_session' action='' name='form_session' method='POST'>
        <input type='hidden' name='order' value='$order'>
    </form>\n";
   echo $html;
?>

In your jQuery, just change `var order = 0;` to

var order = $("input[name='order']").val();

Then, when a user clicks on a page link, prevent the default link action, set the order value and the form action, and then submit the form using javascript/jQuery:

$("a.next_page").click(function(event) {
  event.preventDefault();
  var url = $(this).attr("href");
  $("input[name='order']").val(order);
  $("#form_session").attr('action', url).submit();
});

All the 'next' and 'previous' pagination links must be given the same class (namely 'next_page' (in this example).

EDIT: If your pagination is as follows:

<div class='pagination'>
    <ul><li><a href='page1.url'>1</a></li>
        <li><a href='page2.url'>2</a></li>
    </ul>
</div>

then just change this:

$("div.pagination a").click(function(event) {
etc.

Problem

Original source