1,105,427 Community Members

adsmgt

Member Avatar
davy_yg
Veteran Poster
1,118 posts since May 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 1 [?]
 
0
 

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
Deleted Member
 
0
 

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

Member Avatar
davy_yg
Veteran Poster
1,118 posts since May 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 1 [?]
 
0
 

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
Deleted Member
 
0
 

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

Member Avatar
davy_yg
Veteran Poster
1,118 posts since May 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 1 [?]
 
0
 

freescript (database)

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

LastMitch
Deleted Member
 
0
 

@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'];
}
Member Avatar
davy_yg
Veteran Poster
1,118 posts since May 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 1 [?]
 
0
 

I mean the count right for each day.

LastMitch
Deleted Member
 
0
 

@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'];
}
Member Avatar
davy_yg
Veteran Poster
1,118 posts since May 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 1 [?]
 
0
 

Jumlah clicks per hari = number of count for each date

LastMitch
Deleted Member
 
0
 

@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'];
}
Member Avatar
davy_yg
Veteran Poster
1,118 posts since May 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 1 [?]
 
0
 

$row['countidads'];

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

Member Avatar
code739
Posting Whiz in Training
213 posts since May 2012
Reputation Points: 17 [?]
Q&As Helped to Solve: 30 [?]
Skill Endorsements: 6 [?]
 
1
 

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
Deleted Member
 
0
 

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'];
}
Member Avatar
code739
Posting Whiz in Training
213 posts since May 2012
Reputation Points: 17 [?]
Q&As Helped to Solve: 30 [?]
Skill Endorsements: 6 [?]
 
0
 

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.

Member Avatar
davy_yg
Veteran Poster
1,118 posts since May 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 1 [?]
 
0
 

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'];
Member Avatar
code739
Posting Whiz in Training
213 posts since May 2012
Reputation Points: 17 [?]
Q&As Helped to Solve: 30 [?]
Skill Endorsements: 6 [?]
 
0
 

whats the problem?

Member Avatar
Biiim
Posting Pro
504 posts since Oct 2011
Reputation Points: 85 [?]
Q&As Helped to Solve: 86 [?]
Skill Endorsements: 9 [?]
 
0
 

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

Member Avatar
davy_yg
Veteran Poster
1,118 posts since May 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 1 [?]
 
0
 

4012114112352619 - wonder how I get this number ?

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

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article