insert table - phponwebsites.com

Showing posts with label insert table. 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

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:

<?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())");

}

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

22 Jul 2014

Create Registration / Sign up form using PHP and Mysql

If you want to add login form to your websites, then you need to add sign up or registration form in order to get values from user and store it into Mysql database. Your registration form should be made in HTML and the values are stored in Mysql using PHP. Lets see create the registration form in PHP , Mysql, HTML.

1. Create Database

First you need to create database to store user values. You need to use below Mysql query to create database in your phpmyadmin.

Create database db_name

ie,
Create database new

where new is my database name

Then you've to use below Mysql query for use created database.

Use db_name

ie,

Use new

2. Create table in Mysql database

After created database, you need to create table for store users values. You can create table using below Mysql query.

create table login (name varchar(30),password varchar(30),mail varchar(50))

Now you can see created table is presented in your database.

3. Create registration form in HTML

After finished database and table creation, you need to create front end design which can be viewed by users. ie, client side script. Using below HTML code to create registration form in HTML.

<form action="#" method="post">
<table id="tbl" align="center">
<tr><td>Name:</td><td><input type="text" name="name" id="name"></td></tr>
<tr><td>Password:</td><td><input type="text" name="password" id="password"></td></tr>
<tr><td>Email:</td><td><input type="text" name="mail" id="mail"></td></tr>
<tr><td></td><td><input type="submit" name="submit" id="submit" value="Submit"></td></tr>
</table>

</form>

4. Add styles to registration form

After created form in HTML, it should not be present user attractable one. So you need to add CSS styles to HTML form in order to make form colorful. Just add below CSS codes to your html form.

body {
color:white;
font-size:14px;
}
.contact {
text-align:center;
background: none repeat scroll 0% 0% #8FBF73;
padding: 20px 10px;
box-shadow: 1px 2px 1px #8FBF73;
border-radius: 10px;
width:520px;
}
#name, #password, #mail {
width: 250px;
margin-bottom: 15px;
background: none repeat scroll 0% 0% #AFCF9C;
border: 1px solid #91B57C;
height: 30px;
color: #808080;
border-radius: 8px;
box-shadow: 1px 2px 3px;
}
#submit
{
background:none repeat scroll 0% 0% #8FCB73;
display: inline-block;
padding: 5px 10px;
line-height: 1.05em;
box-shadow: 1px 2px 3px #8FCB73;
border-radius: 8px;
border: 1px solid #8FCB73;
text-decoration: none;
opacity: 0.9;
cursor: pointer;
color:white;
}
#er {
color: #F00;
text-align: center;
margin: 10px 0px;
font-size: 17px;

}

5. Form validation in javascript

After add CSS to your form, you've to validate your form using javascript or jquery. You can validate empty textbox, validate valid email id and valid phone number using below javascript. Add below javascript validation to your HTML form.

$(document).ready(function() {
$('#submit').click(function() {
var name=document.getElementById('name').value;
var password=document.getElementById('password').value;
var mail=document.getElementById('mail').value;
var chk = /^([\w-\.]+@([\w-]+\.)+[\w-]{2,4})?$/;
if(name=='')
{
$('#er').html('Enter your name');
return false;
}
if(password=='')
{
$('#er').html('Enter your password');
return false;
}
if(mail=='')
{
$('#er').html('Enter your mail');
return false;
}
if(!chk.test(mail))
{
$('#er').html('Enter valid email');
return false;
}
});

});

6. Connect Mysql database using PHP

Then you need to write server side script to store values into database. First you make connection to Mysql database to store user values using PHP. Visit How to connect Mysql database using PHP. Add below PHP codes

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

7. Add PHP code for store values into database

Here is the PHP code for insert user data into Mysql database. This is the code for avoid duplicate entries in table. visit How to avoid duplicate values storing in Mysql table while inserting using PHP.

$name=$_POST['name'];
$password=$_POST['password'];
$mail=$_POST['mail'];
$q=mysql_query("select * from login where name='".$name."' or mail='".$mail."' ") or die(mysql_error());
$n=mysql_fetch_row($q);
if($n>0)
{
$er='The username name '.$name.' or mail '.$mail.' is already present in our database';
}
else
{
$insert=mysql_query("insert into login values('".$name."','".$password."','".$mail."')") or die(mysql_error());
if($insert)
{
$er='Values are registered successfully';
}
else
{
$er='Values are not registered';
}

}

8. PHP script for register values into Mysql database

Now you combine all above codes to single PHP file. Then you'll get code like below one. visit How to insert values into Mysql database using PHP.

<html>
<head>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
<style type="text/css">
body {
color:white;
font-size:14px;
}
.contact {
text-align:center;
background: none repeat scroll 0% 0% #8FBF73;
padding: 20px 10px;
box-shadow: 1px 2px 1px #8FBF73;
border-radius: 10px;
width:520px;
}
#name, #password, #mail {
width: 250px;
margin-bottom: 15px;
background: none repeat scroll 0% 0% #AFCF9C;
border: 1px solid #91B57C;
height: 30px;
color: #808080;
border-radius: 8px;
box-shadow: 1px 2px 3px;
}
#submit
{
background:none repeat scroll 0% 0% #8FCB73;
display: inline-block;
padding: 5px 10px;
line-height: 1.05em;
box-shadow: 1px 2px 3px #8FCB73;
border-radius: 8px;
border: 1px solid #8FCB73;
text-decoration: none;
opacity: 0.9;
cursor: pointer;
color:white;
}
#er {
color: #F00;
text-align: center;
margin: 10px 0px;
font-size: 17px;
}
</style>
</head>
<body>
<?php
error_reporting('E_ALL ^ E_NOTICE');
if(isset($_POST['submit']))
{
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new') or die(mysql_error());
$name=$_POST['name'];
$password=$_POST['password'];
$mail=$_POST['mail'];
$q=mysql_query("select * from login where name='".$name."' or mail='".$mail."' ") or die(mysql_error());
$n=mysql_fetch_row($q);
if($n>0)
{
$er='The username name '.$name.' or mail '.$mail.' is already present in our database';
}
else
{
$insert=mysql_query("insert into login values('".$name."','".$password."','".$mail."')") or die(mysql_error());
if($insert)
{
$er='Values are registered successfully';
}
else
{
$er='Values are not registered';
}
}
}
?>
<div class="contact">
<h1>Registration Form</h1>
<div id="er"><?php echo $er; ?></div>
<form action="#" method="post">
<table id="tbl" align="center">
<tr><td>Name:</td><td><input type="text" name="name" id="name"></td></tr>
<tr><td>Password:</td><td><input type="text" name="password" id="password"></td></tr>
<tr><td>Email:</td><td><input type="text" name="mail" id="mail"></td></tr>
<tr><td></td><td><input type="submit" name="submit" id="submit" value="Submit"></td></tr>
</table>
</form>
</div>

<script type="text/javascript">
$(document).ready(function() {
$('#submit').click(function() {
var name=document.getElementById('name').value;
var password=document.getElementById('password').value;
var mail=document.getElementById('mail').value;
var chk = /^([\w-\.]+@([\w-]+\.)+[\w-]{2,4})?$/;
if(name=='')
{
$('#er').html('Enter your name');
return false;
}
if(password=='')
{
$('#er').html('Enter your password');
return false;
}
if(mail=='')
{
$('#er').html('Enter your mail');
return false;
}
if(!chk.test(mail))
{
$('#er').html('Enter valid email');
return false;
}
});
});
</script>
</body>

</html>

When you run this file, your screen should be like this:

For example, you'll try to store values guru, guru, guruparthiban19@gmail.com, then your screen like below:

Insert values into Mysql database using PHP

After enter submit button, the values are registered in Mysql database. Now you can see the values are present in yout table.

You will get error message when you tried to insert same values again. This is below

Duplicate entry error message while inserting using PHP and Mysql

Now your table contains user details. So you can add login form to validation user name and password of users.

Related Post:
Login form validation using PHP and Mysql
Login form validation with remeber me function using PHP and Mysql
Send forgot password to mailin login form using PHP and Mysql

13 Mar 2014

Insert data into mysql table using php

You can insert values to mysql table using php. The following mysql query is used for insert data into table.
INSERT INTO table_name VALUES(values datatype(length))

Store values into mysql table using php:

The following php script is used to store values into mysql table.

<?php
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new') or die(mysql_error());
$query=mysql_query("insert into table1 values(1,'sample1', 'sample1.jpg')") or die(mysql_error());
if($query)
{
echo 'Vaules are inserted into table';
}
else
{
echo'Vaules are not inserted';
}
?>

Note:
If you declared field name as varchar, then you must insert values between single quotation marks ( 'sample1').

Now the values are inserted into table. You can see it in your mysql table at phpmyadmin. Your mysql table look like this:

You inserted values to your table using php.

Get values from user and insert into mysql table using php:

Suppose you want to get values from user. First you have to know how to get values from text boxes.

How to get values from textbox using php:

$name=$_POST['name']; if(method is post)
or
$name=$_GET['name']; if(method is get)
or
$name=S_REQUEST['name']; if(method is either post or get)

where,
S_POST[] is used to get values from textbox, If you use POST method.
S_GET[] is used to get values from textbox, If you use GET method.
S_REQUEST[] is used to get values from textbox, If you use either GET or POST method.

Then the following php code is used to insert values into table.

Where,
the values will get from users using post method like as below:
$_POST['textbox_name']

Now you run this file. Your display look like this:

Then you can get values, if user type anything in textbox. Now i added values as like this:

Get values from user and stored into mysql table using php

Now you'll get output like this:

Your mysql table have user inserted values.

Related Post:

How to avoid duplicate values storing in mysql table using php and 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:

27 Jul 2014

Daily statistics using php and mysql

Daily stats using php and mysql:

1.Create table in mysql database:

2.Counter code in php:

3. Stats(chart) using php and mysql:

22 Jul 2014

Create Registration / Sign up form using PHP and Mysql

1. Create Database

2. Create table in Mysql database

3. Create registration form in HTML

4. Add styles to registration form

5. Form validation in javascript

6. Connect Mysql database using PHP

7. Add PHP code for store values into database

8. PHP script for register values into Mysql database

13 Mar 2014

Insert data into mysql table using php

Store values into mysql table using php:

Get values from user and insert into mysql table using php:

How to get values from textbox using php: