order by - phponwebsites.com

Showing posts with label order 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

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:

display unique values using group by in mysql

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

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 select distinct value in mysql

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

3 Jan 2014

Order by in mysql table

If your column values in mysql table contains more rows, then you can't get the highest and lowest values. On that time, you can sort the column values ascending and descending by 'ORDER BY' mysql command with 'ASC' and 'DESC'. The mysql query for sort column values in mysql table as follwos as:
SELECT * FROM table_name ORDER BY field_name ASC( for ascending)
SELECT * FROM table_name ORDER BY field_name DESC( for descending)

Order by in Mysql:

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

Now we are going to sort column 'id' in ascending order. The mysql query for this:

SELECT * FROM table1 ORDER BY id ASC

Now you'll get output like this:

sorting column values using OREDER BY in mysql

You can see the column 'id' values are sorting in ascending order.

Order by multiple columns in mysql:

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

SELECT * FROM table1 ORDER BY id ASC, date DESC

Where,
- The column 'id' got the first preference to sort
- Then the column 'date' is sort
Your output like this:

You can see the mysql table 'table1' which is sorted by id in ascending order and date in descending order.

Related Post:

distinct in mysql
Group 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:

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:

3 Jan 2014

Order by in mysql table

Order by in Mysql:

Order by multiple columns in mysql: