Using SUM in Query and Printing Results

artnow

Hello all. I know I am doing something wrong but I am not sure what the problem is. I am logging clicks on links on my site and now I want to print the totals clicks per day based on the link_id. However, the total of SUM is not getting printed.

<?
$getrows = mysql_query('SELECT day,link_id,SUM(raw) FROM click_count WHERE 1 AND day = CURDATE() AND link_id=$data->id GROUP by day');
if(!$SUM = 0) {
echo "$data->id";
} else {
echo "$SUM";
}

abnfire

so you want the current day vistors clicks right or wronge?
well first your query is wronge and second give me more information, like what are you trying to do.

artnow

When I am done, I want to be able to review which links received clicks today, yesterday, this week, this month, last month. The clicks are tracked in a table with the following fields: link_id, day, ip, time and raw and unique clicks. The URLs are in a second table using the unique identifier id which is the same as the link_id. Eventually I want to be able to remove duplicate clicks on the same link from the same id.

artnow

I have worked out how to get my results for today and yesterday but I just can't seem to do so between two dates:

<?
$getrows = mysql_query("SELECT SUM(raw) AS sum_raw3 FROM click_count WHERE 1 AND day BETWEEN 'CURDATE()' AND 'CURDATE(), - INTERVAL 7 DAY' AND link_id=$data->id GROUP by day");
$row = mysql_fetch_array($getrows);
$quan3 = $row['sum_raw3'];

echo "$quan3";

Any thoughts?

artnow

Well, I figured out the week but now I am stuck on the month stats. Here I am comparing the current date's month to that of the click month. So far, it is not returing any values. Any thoughts would help.

<?
$this_month = "MONTH(CURDATE())";
$getrows = mysql_query("SELECT MONTH(`day`) AS M, SUM(`raw`) AS sum_raw4 FROM  $counter WHERE day = '$this_month' AND link_id=$data->id GROUP by day");
$row = mysql_fetch_array($getrows);
$quan4 = $row['sum_raw4'];
$current = "MONTH(CURDATE())";
$current_month = $row['M'];

if(!$current_month == $current) {

if($quan4){
echo "$quan4"; 
} else {
echo "-";
}
} else {
echo "n/a";
}

?>

artnow

<?
$this_month = "MONTH(CURDATE())";
$getrows = mysql_query("SELECT MONTH(`day`) AS M, SUM(`raw`) AS sum_raw4 FROM  $counter WHERE day = '$this_month' AND link_id=$data->id GROUP by day");
$row = mysql_fetch_array($getrows);
$quan4 = $row['sum_raw4'];
$current = "MONTH(CURDATE())";
$current_month = $row['M'];

if(!$current_month == $current) {

if($quan4){
echo "$quan4"; 
} else {
echo "-";
}
} else {
echo "n/a";
}

?>

xblue

I suspect there may be flaws in the query itself still, but most obvious is:

$current holds the string 'MONTH(CURDATE())' - it is just a string, not more.
$current_month is supposed to hold a value from the database.

even if the query worked, these two will never be equal...