group by - phponwebsites.com

Showing posts with label group by. Show all posts

29 Jul 2014

Generate Yearly Statistics dynamically using PHP and MySQL

All of you know to create daily statistics and also monthly statistics using php and mysql. You can also create yearly statistics using php and mysql. The php and mysql allows user to create charts on daily, monthly and yearly charts between two years. Due to this yearly stats, you can find how much your website got viewed and also find comparison of number of visits.

Yearly stats using php and mysql:

You need to follow the below steps to create yearly stats using php.

1.Create table in mysql database:

First you have to create table for update values.

Create table stats(view int(10),date date)

Now the table is created with fields view and date.

2.Counter code in php:

Then you add the number of views to mysql table using php. Add the following code to header of each page in your website. Because header is in all pages. So you can easily calculate the number of views of your website. Due to this code, you can find total number views of your website per day.

<?php

mysql_connect('localhost','root','') or die(mysql_error());

mysql_select_db('new') or die(mysql_error());

$q=mysql_query('select * from stats where date IN (CURDATE())') or die(mysql_error());

$n=mysql_num_rows($q);

if($n==0)

{

mysql_query("insert into stats values(1,CURDATE())");

}

else

{

mysql_query("update stats set view=view+1 where date IN (CURDATE())");

}

where,
- select * from stats where date IN (CURDATE()) is select the views if present in current date.
- mysql_num_rows() return the number of rows selected. If it is 0, then value inserted into mysql table. Otherwise values are updated.

3. Stats(chart) using php and mysql:

Then you need to create statistics using php and mysql through google charts. There are many types of charts in google. You can choose anything as you want. The php code for create yearly stats:

yearly_stats.php

<html>
<body>
<?php
mysql_connect('localhost','root','');
mysql_select_db('new');
?>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
google.load("visualization", "1", {packages:["corechart"]});
google.setOnLoadCallback(drawChart);
function drawChart() {var data = google.visualization.arrayToDataTable([
<?php
$str=" ['Year', 'Year'] ";
$query="select SUM(view) as vi, DATE_FORMAT( date, '%Y' ) as dat from stats group by DATE_FORMAT(date, '%Y') order by DATE_FORMAT(date, '%Y') ";
$result=mysql_query($query);
while($rows=mysql_fetch_array($result,MYSQL_BOTH)){
$str =$str . ",['". $rows['dat'] ."'," .$rows['vi'] ."]" ;
}
echo $str;
?>
]);

var options = {
//title: 'Company Performance',
hAxis: {title: 'Year', titleTextStyle: {color: 'red'}},
vAxis: {title: 'Total views', titleTextStyle: {color: '#FF0000'}, maxValue:'5', minValue:'1'},
};

var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));
chart.draw(data, options);
}
</script>
<p style="font-size:20px;">Monthly Stats</p>
<div id="chart_div" style="width: 400px; height: 200px;"></div>
</body>
</html>

where,
The following MySQL query is used to find total number of views of your websites per yearly.
- select SUM(view) as vi, DATE_FORMAT( date, '%Y' ) as dat from stats group by DATE_FORMAT(date, '%Y') order by DATE_FORMAT(date, '%Y') ASC
is select the number of views monthly.

Consider the following example:
Suppose your table look like this:

Create yearly stats dynamically using PHP and MySQL

Then you'll get output like below:

Now you can calculate the number of views of your website yearlly.

Related Post:
Create daily statistics using PHP and MySQL
Calculate monthly statistics dynamically using PHP and MySQL

28 Jul 2014

Monthly statistics using php and mysql

All of you know to create daily stats using php and mysql. Otherwise visit How to calculate daily statistics using PHP and MySQL. You can also create monthly statistics using php and mysql. The php and mysql allows user to create charts on daily, monthly and charts between two dates.

Monthly stats using php and mysql:

You need to follow the below steps to create monthly stats using php.

1.Create table in mysql database:

First you have to create table for update values.

Create table stats(view int(10),date date)

Now the table is created with fields view and date.

2.Counter code in php:

<?php

mysql_connect('localhost','root','') or die(mysql_error());

mysql_select_db('new') or die(mysql_error());

$q=mysql_query('select * from stats where date IN (CURDATE())') or die(mysql_error());

$n=mysql_num_rows($q);

if($n==0)

{

mysql_query("insert into stats values(1,CURDATE())");

}

else

{

mysql_query("update stats set view=view+1 where date IN (CURDATE())");

}

where,

- select * from stats where date IN (CURDATE()) is select the views if present in current date.
- mysql_num_rows() return the number of rows selected. If it is 0, then value inserted into mysql table. Otherwise values are updated.

3. Stats(chart) using php and mysql:

Then you need to create statistics using php and mysql through google charts. There are many types of charts in google. You can choose anything as you want. The php code for create monthly stats:

monthly_stats.php

<html>
<body>
<?php
mysql_connect('localhost','root','');
mysql_select_db('new');
?>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
google.load("visualization", "1", {packages:["corechart"]});
google.setOnLoadCallback(drawChart);
function drawChart() {var data = google.visualization.arrayToDataTable([
<?php
$str=" ['Month', 'Month'] ";
$query="select SUM(view) as vi, DATE_FORMAT( date, '%M' ) as dat from stats group by DATE_FORMAT(date, '%Y-%M') order by DATE_FORMAT(date, '%Y-%M') ASC";
$result=mysql_query($query);
while($rows=mysql_fetch_array($result,MYSQL_BOTH)){
$str =$str . ",['". $rows['dat'] ."'," .$rows['vi'] ."]" ;
}
echo $str;
?>
]);

var options = {
//title: 'Company Performance',
hAxis: {title: 'Month', titleTextStyle: {color: 'red'}},
vAxis: {title: 'Total views', titleTextStyle: {color: '#FF0000'}, maxValue:'5', minValue:'1'},
};

var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));
chart.draw(data, options);
}
</script>
<p style="font-size:20px;">Monthly Stats</p>
<div id="chart_div" style="width: 400px; height: 200px;"></div>
</body>
</html>

where,
The following MySQL query is used to find total number of views of your websites per monthly
- select SUM(view) as vi, DATE_FORMAT( date, '%M' ) as dat from stats group by DATE_FORMAT(date, '%Y-%M') order by DATE_FORMAT(date, '%Y-%M') ASC
is select the number of views monthly.

Consider the following example:
Suppose your table look like this:

Then you'll get output like below:

Now you can calculate the number of views of your website monthly.

Related Post:
Create daily statistics using PHP and MySQL
Create yearly statistics dynamically using PHP and MySQL

3 Feb 2014

Select distinct values in mysql

If your column in mysql table contains duplicate values, then you need to display distinct values by mysql command 'DISTINCT' and 'GROUP BY'.

Select unique values in Mysql by distinct:

The mysql command 'DISTINCT' is used to select distinct values. The mysql query for select distinct values as follows as:

SELECT DISTINCT column_name FROM table_name

Consider the following table. table1 is name of this table.

Now we are going to get unique values in column 'id' by 'DISTINCT' mysql command like this:

SELECT DISTINCT id FROM table1

Now you'll get output like this:

select unique values using distinct in mysql

You can see the table 'table1' which have unique values.

Select unique values in Mysql by 'Group by':

You can also display unique values using 'GROUP BY' mysql command. If you want to select distinct values from one column and also other values from table, then you can use mysql command 'GROUP BY'. The mysql query is:

SELECT * FROM table_name GROUP BY column_name

Now we are going to get unique values in column 'id' by 'GROUP BY' mysql command like this:

SELECT * FROM table1 GROUP BY id

Now you'll get output like this:

display unique values using group by in mysql

You can see the column 'id' values in mysql table 'table1' are unique.

Related Post:

Change column name in mysql
Change column size in mysql table
Delete column in mysql table
Add column after specific field in mysql table
Move columns in mysql table

change column datatype in mysql
Change table name in mysql
Add primary key to existing column in mysql
how to add auto increment to existing column in mysql
How to use order by before group by in mysql

How to remove auto increment from column in mysql
How to reset auto increment initial value in mysql

31 Jan 2014

Use order by before group by in mysql

Normally you can use order by before group by in mysql. Now the question is rise, can you use order by before group b in mysql? Yes you can use order by before group by in mysql. It is possible. You've to use subquery for this. ie,

SELECT * FROM (
SELECT * FROM `table` ORDER BY column_name DESC
) t
GROUP BY column_name
Where,
t is alias because you use subquery. If you don't add it, then you'll get error message "Every derived table must have its own alias".

Group by in Mysql:

Consider the following table. table1 is name of this table.

Now we are going to get unique values in column 'id' by 'GROUP BY' mysql command like this:

SELECT * FROM table1 GROUP BY id

Now you'll get output like this:

You can see the column 'id' values in mysql table 'table1' are unique. The id value 1 are present 1st, 2nd and 5th row. But it takes only the first row.

Use order by before group by:

On above example, you want to select highest count of each id and id should be unique. Then you have to use subquery to select highest count values. The mysql query as follows as:

SELECT * FROM (
SELECT * FROM `table1` ORDER BY count DESC
) t
GROUP BY id

where,
SELECT * FROM `table1` ORDER BY count DESC run first. It ordered the data in descending order by count.
SELECT * FROM ( ... ) t GROUP BY id it takes unique values.
Now you'll get output like this:

Now you'll get id with highest count values.

Related Post:

Change column name in mysql
Change column size in mysql table
Delete column in mysql table
Add column after specific field in mysql table
Move columns in mysql table

7 Jan 2014

Use order by together with group by in mysql

You can use both mysql command 'ORDER BY' and 'GROUP BY' separately. The mysql command 'ORDER BY' is used to sort the column values and 'GROUP BY' is used to eliminate the duplicate values in column.
Can you use both 'OREDER BY' and 'GROUP BY' together in a mysql query?
Yes you can use both mysql command in a query.
Why you need to use both mysql command in a query?
If you want to eliminate duplicate values in column and also sort the column in a mysql table, then you've to use these two command in single mysql query. ie,

SELECT * FROM table GROUP BY column_name ORDER BY column_name

You must use 'GROUP BY' before 'ORDER BY' in mysql. Because 'GROUP BY' get higher preference than 'ORDER BY' in mysql.

Select all values with one value is distinct in mysql table:

You can use 'DISTINCT' mysql command for display unique values in only one column. But the situation, you need to select all columns but one column value is distinct in mysql. Then you've to use both 'ORDER BY' and 'GROUP BY' mysql command in a query. The 'ORDER BY' command should be placed after 'GROUP BY' in mysql query.

Consider the following table. table1 is name of this table.

group by and order by in same mysql query

Now we are going to get total count of unique values in column 'id' by 'GROUP BY' mysql command and also order by count in ascending order by 'ORDER BY' mysql command. The mysql query as follows as:

SELECT id, SUM(count) as count, date FROM table5
GROUP BY id ORDER BY count DESC

Now you'll get output like this:

using order by and group by together in mysql

You can see the output of the above mysql query which displays total count in descending order.

Related Post:

distinct in mysql
Order by in mysql
Group by in mysql
Difference between delete truncate drop in mysql
how to use order by before group by in mysql

5 Jan 2014

Group by in mysql table

If your column in mysql table contains similar values, then you will use 'GROUP BY' mysql command to display unique values. It is used to eliminate the duplicate values in mysql table. It takes only first row value from top to bottom in mysql table. The mysql query as follows as:

SELECT * FROM table_name GROUP BY field_name

Group by in Mysql:

Consider the following table. table1 is name of this table.

Now we are going to get unique values in column 'id' by 'GROUP BY' mysql command like this:

SELECT * FROM table1 GROUP BY id

Now you'll get output like this:

You can see the column 'id' values in mysql table 'table1' are unique. The id value 1 are present 1st, 2nd and 5th row. But it takes only the first row.

Group by multiple columns in mysql:

You can group the multiple columns in single mysql query. The mysql query as follows as:

SELECT * FROM table1 GROUP BY id , count

Where,
- The column 'id' got the first preference to group.
- Then the column 'count' is grouped.
Your output like this:

You can see the mysql table 'table1' which is grouped by both id and count. But you can't the get unique values in both mysql columns.

Related Post:

distinct in mysql
Order by in mysql
How to use order by together with group by in mysql
Difference between delete truncate drop in mysql
how to use order by before group by in mysql
Primary key in mysql

Pages

29 Jul 2014

Generate Yearly Statistics dynamically using PHP and MySQL

Yearly stats using php and mysql:

1.Create table in mysql database:

2.Counter code in php:

3. Stats(chart) using php and mysql:

28 Jul 2014

Monthly statistics using php and mysql

Monthly stats using php and mysql:

1.Create table in mysql database:

2.Counter code in php:

3. Stats(chart) using php and mysql:

3 Feb 2014

Select distinct values in mysql

Select unique values in Mysql by distinct:

Select unique values in Mysql by 'Group by':

31 Jan 2014

Use order by before group by in mysql

Group by in Mysql:

Use order by before group by:

7 Jan 2014

Use order by together with group by in mysql

Select all values with one value is distinct in mysql table:

5 Jan 2014

Group by in mysql table

Group by in Mysql:

Group by multiple columns in mysql: