adsmgt

[davy_yg]

adsmgt.php

$result = mysql_query("SELECT COUNT(idads) AS countidads FROM adsmgt") or die(mysql_error()); 

while($row = mysql_fetch_assoc($result))
{
echo "Number of ad clicks: ".$row['countidads'];

}

$result2 = mysql_query("SELECT waktu, date, time FROM adsmgt") or die(mysql_error());

echo "<br>";
echo "<br>"."Tanggal"."   "."       Waktu"."       Jumlah clicks per hari";

while($row2 = mysql_fetch_array($result2))
{
echo "<br>".$row2['date']."   ".$row2['time'];

}

as of now, all I have is:

Tanggal Waktu Jumlah clicks per hari
2012-11-29 08:51:04
2012-11-29 08:52:00
2012-11-29 08:52:27

I would like to see the number of clicks each day printed out. How to do so? I only see the time and date when it is clicked.

@davy_yg

I would like to see the number of clicks each day printed out. How to do so? I only see the time and date when it is clicked.

Can you tell me what option did you try before you post this question? I don't see you post any option what you done. Have you try JOIN or 2 SELECT in one query?

[davy_yg]

I add this code:

$resultday = mysql_query("SELECT COUNT(idads) AS countidadsday GROUP BY date FROM adsmgt") or die(mysql_error());

while($rowday= mysql_fetch_array($resultday))
{
echo $rowday['countidadsday'];
}

Basically to see how many ads count each day.

Still get this error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY date FROM adsmgt' at line 1

@davy_yg

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY date FROM adsmgt' at line 1

What is your table? Post your table so we can see what you are doing.

[davy_yg]

freescript (database)

adsmgt (table) - idads, waktu, number, date, time

@davy_yg

I'm not sure it's gonna work or not. You just want the count right? which is idads?

Try this:

$result = mysql_query("SELECT idads, date, time FROM adsmgt") or die(mysql_error()); 
echo "<br>";
echo "<br>"."Tanggal"." "."  Waktu"." Jumlah clicks per hari";
while($row = mysql_fetch_array($result))
{
echo "<br>".$row['date']."  ".$row['time']"  ".$row['idads'];
}

[davy_yg]

I mean the count right for each day.

@davy_yg

I mean the count right for each day.

$result = mysql_query("SELECT COUNT(idads) As countidads, date, time FROM adsmgt") or die(mysql_error());

-

Not sure you can try this:

$result = mysql_query("SELECT COUNT(idads) As countidads, waktu, date, time FROM adsmgt") or die(mysql_error());
echo "<br>";
echo "<br>"."Tanggal"." "." Waktu"." Jumlah clicks per hari";
while($row = mysql_fetch_array($result))
{
echo "<br>".$row['date']." ".$row['time']" ".$row['countidads'];
}

[davy_yg]

Jumlah clicks per hari = number of count for each date

@davy_yg

Jumlah clicks per hari = number of count for each date

Does this code that you post from above works?

$result = mysql_query("SELECT COUNT(idads) AS countidads FROM adsmgt") or die(mysql_error());
while($row = mysql_fetch_assoc($result))
{
echo "Number of ad clicks: ".$row['countidads'];
}

[davy_yg]

$row['countidads'];

gives me the total ads clicks for all date. Is it possible to print out the countidads for each different date.

[code739]

i found this on your code
$resultday = mysql_query("SELECT COUNT(idads) AS countidadsday GROUP BY date FROM adsmgt") or die(mysql_error());

basically this displays error because you declare group by before the table name so this should be the proper one

$resultday = mysql_query("SELECT COUNT(idads) AS countidadsday,date, time FROM adsmgt GROUP BY date") or die(mysql_error());

that might solve you problem on displaying the countidaday each different date.

gives me the total ads clicks for all date. Is it possible to print out the countidads for each different date.

I think code739 rearrange the query that you post previously and it looks right but I'm not sure if that's what you are asking (Does still print out all the dates?):

$result = mysql_query("SELECT COUNT(idads) AS countidadsday,date, time FROM adsmgt GROUP BY date") or die(mysql_error());
echo "<br>";
echo "<br>"."Tanggal"." "." Waktu"." Jumlah clicks per hari";
while($row = mysql_fetch_array($result))
{
echo "<br>".$row['date']." ".$row['time']" ".$row['countidadsday'];
}

[code739]

and it depends upon the structure of your table. if your table could store the same date then you could use the group by as what the above post stated.

[davy_yg]

date time Jumlah clicks per hari (Number of clicks per day) turns into:

2013-01-21 07:16:10 4012114112352619

$resultday = mysql_query("SELECT COUNT(idads) AS countidadsday FROM adsmgt GROUP BY date") or die(mysql_error());

while($row = mysql_fetch_assoc($result))
{
echo "Number of ad clicks: ".$row['countidads'];

}

$result2 = mysql_query("SELECT waktu, date, time FROM adsmgt") or die(mysql_error());

echo "<br>";
echo "<br>"."Tanggal"."&nbsp&nbsp&nbsp"."&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspWaktu"."&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspJumlah clicks per hari";

while($row2 = mysql_fetch_array($result2))
{
echo "<br>".$row2['date']."&nbsp&nbsp&nbsp".$row2['time']."&nbsp&nbsp&nbsp".$row['countidadsday'];

[code739]

whats the problem?

[Biiim]

What a confusing thread to follow!

Your first query is a group by - which is what you're after and you get the counts without any other data

the second query has no group by on it - its just pulling all rows and concatting a var from the last loop which is either not set or is the last value in the last loop.

just add the fields you want to the group by - don't pull time as time will be false(it will be a random time entry from one of the rows in the group)

$resultday = mysql_query("SELECT waktu, date, COUNT(idads) AS countidadsday FROM adsmgt GROUP BY date") or die(mysql_error());
    echo "waktu,date,count<br/>\r\n";
    while($row = mysql_fetch_assoc($result)){
        echo "{$row['waktu']},{$row['date']},{$row['countidadsday']}<br/>\r\n";
    }

If you want the data into an array for random use you can output it into an array with the date as the key - i find it useful for when you are dynamically pulling data and not sure which data will be shown as the data shown is also dynamic.

$resultday = mysql_query("SELECT waktu, date, COUNT(idads) AS countidadsday FROM adsmgt GROUP BY date") or die(mysql_error());
$clicksByDay = array();
while($row = mysql_fetch_assoc($result)){
    $clicksByDay[$row['date']] = $row;
}

echo $clicksByDay['2013-01-17'];

Edit:
Oh and you can use date() to turn into a date or substr() to turn a datetime field into a year,month,day,hour or minute

select date(`date`) FROM...
SELECT SUBSTR(`date`,1,10) as `date`,SUBSTR(`date`,1,7) as `month`,SUBSTR(`date`,1,13) as `hour` FROM...

Remember using a function is much slower than using an indexed field, you'll want to make a separate field and index it on large tables

[davy_yg]

4012114112352619 - wonder how I get this number ?

It suppose to be simpler number (the number of clicks of each specified date).