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.

[LastMitch]

@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

[LastMitch]

@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

[LastMitch]

@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.

[LastMitch]

@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

[LastMitch]

@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.

[LastMitch]

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).