create table - phponwebsites.com

Showing posts with label create table. Show all posts

27 Jul 2014

Daily statistics using php and mysql

All website owners wants to know how many times the site is viewed ?. So they create this details in admin panel of their website. You can create daily statistics using php and mysql. The php and mysql allows user to create charts on daily, monthly and charts between two dates.

Daily stats using php and mysql:

You need to follow the below steps to create daily 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.

<?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 stats:

daily_stats.php

<html>
<body>
<?php
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new') or die(mysql_error());
?>
<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=" ['Day', 'Day'] ";
$query="select view as vi, DATE_FORMAT( date, '%b-%d' ) as dat from stats order by date ASC";
$result=mysql_query($query) or die(mysql_error());
while($rows=mysql_fetch_array($result,MYSQL_BOTH)){
$str =$str . ",['". $rows['dat'] ."'," .$rows['vi'] ."]" ;
}
echo $str;
?>
]);

var options = {
//title: 'Company Performance',
hAxis: {title: 'Day', 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;">Dialy 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 daily
- select view as vi, DATE_FORMAT( date, '%b-%d' ) as dat from stats order by date ASC
is select the number of views daily.

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.

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

6 Mar 2014

Create mysql table using php

You can create mysql table using php. The following mysql query is used to create mysql table.
CREATE TABLE table_name(field_name datatype(length))

The php script as follows as:

<?php
mysql_connect('localhost','root','');
mysql_select_db('new');
$query=mysql_query("create table table1(id int(5),name varchar(40),img varchar(30))") or die(mysql_error());
if($query)
{
echo 'Table is created';
}
else
{
echo'Table is not created';
}
?>

Where,
- mysql_connect() is used to connect the database.
- 'localhost' is server name.
- 'root' is user name of database.
- '' represent the password of database.
- mysql_select_db() is used to select the database for use.
- 'new' is the database new.
- mysql_query() is used for execute the mysql query.
- mysql_error() gives error message when the mysql query is not running successfully.

Now the mysql table 'table1' is created. You can see it in your mysql database at phpmyadmin.
Your mysql database should be like this:

Create mysql table using CREATE TABLE mysql command in php

Now you created your mysql table.

Related Post:

How to connect mysql database using php
How to create mysql database using php

12 Feb 2014

Primary key in mysql

The primary key in mysql is used to identify the unique values in table. It don't allow duplicate entries in mysql table. The primary key column should be NOT NULL. It don't allow null values in mysql table. The mysql table can have only one primary key.

How to create primary key in mysql:

The mysql query for create primary key is:

Create table table_name (column_name datatype(length), primary key(column_name))

For example,
create table table1(name varchar(30), address varchar(100), primary key(name))

Where the column 'name' is primary key.

How to add primary key to existing table in mysql:

You created table without primary like this:

create table table1(name varchar(30),address varchar(100))

But you need to add primary key in order to remove duplicate entries in mysql. At the time you can add primary key to existing table in mysql. Following mysq query is used for add primary key.

Alter table table_name add primary key(column_name)

For example,

Alter table table1 add primary key(num)

How to remove primary key in mysql:

You created table with primary. But you need not primary key for particular table. Then how can remove primary key from table in mysql. The following mysql query is used to drop the primary key from table in mysql.

alter table table_name drop primary key