how to count clicks using php

[nyler01]

Im noob in php and i have a website which allow users to search for a keyword and give them a result as a link which able to redirect them based on their search.. and i need a script to track how many times a search result's link is clicked. I am using ms access database and odbc connection. please help. thanks

[hielo]

give them a result as a link which able to redirect them based on their search

assuming your search.php page is doing:

$query = "SELECT linkURL, linkText FROM Resources WHERE linkText LIKE '%{$searchTerm}%'";

(where Resources is your table name) and then generating:

<a href="http://remotesite.com">some text</a>

when you search the db, instead of getting that url ("http://remotesite.com"), be sure that your table has a unique ID for that Resource:

$query = "SELECT id, linkText FROM Resources WHERE linkText LIKE '%{$searchTerm}%'";

and then generate the following links instead:

<a href="search.php?id=37">some text</a>

where 37 is supposed to be the id you retrieved from your query originally. So when the user clicks on the link, the browser will go back to your search.php page, and $_GET will have the id of the link the user clicked. So, BEFORE you even do the SELECT query you would need to see if $_GET is present and if it is, then you query the db for the linkURL and then redirect the user.

<?php
$tableName='Resources';
//do your db connection stuff here


if( isset($_GET['id']) && !empty($_GET['id']) )
{
  $query=sprintf("SELECT linkURL FROM $tableName WHERE id=%d, intval($_GET['id']));  

  //here see if you get a result based on the above query. 
  ...
  
  If yes,
  {
    //then do an update statement 
    /*
    $query=sprintf("UPDATE clickTracker SET clicks = clicks+1 WHERE Resource_id=%d, intval($_GET['id'])); */

    //where $row['linkURL'] is the URL you were supposed to extract above.
    header("Location: " . $row['linkURL']);
    exit();
  }
} 

//here you would do your initial search:
$query = "SELECT linkURL, linkText FROM Resources WHERE linkText LIKE '%{$searchTerm}%'";
?>

I haven't used that particular db driver, so I can't give you a cut-and-paste example, but the theory is solid.

[nyler01]

hi hielo.. from now on i'm gonna call you my master.. your code helps a lot.. you're a genius.. i hope you could help me with another problems. and i hope this thread can help others out there.. thanks for your hel. God Bless You.

[hielo]

hi hielo.. from now on i'm gonna call you my master

As you wish little grasshopper :)

Glad to help.

Regards,
Hielo

[nyler01]

I need a php script that can count the same values in ms access database,
Like "Select count (*) from table group by groups" to count how many times a value appeared in database.. thanks for help in advance

[hielo]

Select groups, count (*) as total from table group by groups

[nyler01]

Please write the script.. I do not have any idea on how to write this in php with the script of retrieving the data.. please i have this code.. i think something is wrong with it because it can group records but it is not able to count how many record is existing.. thanks for help

$query="SELECT SearchString, count (*) as total FROM SearchString GROUP BY SearchString";


$result = odbc_exec($conn,$query);

// Print out result


while($fetch=odbc_fetch_array($result)){

    echo "There are total of ". $fetch[count($fetch)] ." Searches for ". $fetch['SearchString'] ."<br>";
    echo "<br />";

}

[hielo]

when you execute SELECT SearchString, count (*) as total you should get a result set with TWO columns. One of the columns is named 'SearchString', the OTHER column is named 'total'. So intead of $fetch[count($fetch)] you need to use $fetch['total']

[nyler01]

hi hielo.. you're really a genius! it helps me a lot.. thanks again

[nyler01]

by the way, can i sort the query result from highest value to lowest value or if possible, can i limit the result to top 10? thanks in advance my master.

[hielo]

Try:

SELECT TOP 10 SearchString, count (*) as total FROM SearchString 
ORDER BY count (*) DESC
GROUP BY SearchString

PS: please open a new thread for other questions. Even though the follow-up questions are related to your project, they are really not related to each other. Besides, it is to your advantage if you create a new thread for them since more people are likely to see your questions, especially if I am offline.

[nyler01]

ok.. but i am hoping that you will always see my threads because you are the only who is answering all the questions i have... my web development project has just started and i have more questions for sure in the future.. Please assist me as i grow as a good web developer.. thanks for your help.