mysql_num_rows - phponwebsites.com

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

17 Apr 2014

Pagination using php and mysql

Pagination in php is very simple concepts. You can learn easily. Just follow the below steps.

1. Find the Start values based on page number:

$limit=10;
$page=$_GET['p'];
if($page=='')
{
$page=1;
$start=0;
}
else
{
$start=$limit*($page-1);
}

Where,
$limit is the number rows per page.
$page is page number.
$start is starting point of limit in mysql query.
If the page number is 1, then the start is 0 which is find by $start=$limit*($page-1).
$start=10*(1-1)
$start=10*(0)
$start=0

If the page number is 2, then the start is 10.
Similarly, if the page number is 3, then the start is 20.

2. Find total number of records in mysql table:

Then we need to calculate the total number of data in table. Then find the maximum pages using php script like below:

$tot=mysql_query("SELECT * FROM table1") or die(mysql_error());
$total=mysql_num_rows($tot);
$num_page=ceil($total/$limit);

Where,
mysql_num_rows() returns the numbers results in numeric.
ceil() returns the whole digit number. ie, ceil(2.3) => 3.
$maxpage returns the number of pages.

3. Function for pagination:

The php script for pagination function is:

function pagination($page,$num_page)
{
echo'<ul style="list-style-type:none;">';
for($i=1;$i<=$num_page;$i++)
{
if($i==$page)
{
echo'<li style="float:left;padding:5px;">'.$i.'</li>';
}
else
{
echo'<li style="float:left;padding:5px;"><a href="pagination.php?p='.$i.'">'.$i.'</a></li>';
}
}
echo'</ul>';
}

Where,
You need to use for loop for display number of pages in mysql table.

4.The whole php code for pagination as follows as:

<?php
error_reporting('E_ALL ^ E_NOTICE');
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new') or die(mysql_error());
$page=$_REQUEST['p'];
$limit=10;
if($page=='')
{
$page=1;
$start=0;
}
else
{
$start=$limit*($page-1);
}
$query=mysql_query("select * from table1 limit $start, $limit") or die(mysql_error());
$tot=mysql_query("select * from table1") or die(mysql_error());
$total=mysql_num_rows($tot);
$num_page=ceil($total/$limit);
echo'<table><th>Reg.Id</th><th>Name</th><th>Category</th>';
while($res=mysql_fetch_array($query))
{
echo'<tr><td>'.$res['game_ID'].'</td><td>'.$res['game_Title'].'</td><td>'.$res['category_Name'].'</td></tr>';
}
echo'</table>';
function pagination($page,$num_page)
{
echo'<ul style="list-style-type:none;">';
for($i=1;$i<=$num_page;$i++)
{
if($i==$page)
{
echo'<li style="float:left;padding:5px;">'.$i.'</li>';
}
else
{
echo'<li style="float:left;padding:5px;"><a href="pagination.php?p='.$i.'">'.$i.'</a></li>';
}
}
echo'</ul>';
}
if($num_page>1)
{
pagination($page,$num_page);
}
?>

Now you'll get output like this:

Now you can paginated to next page.

Related Post:

Sorting column by clicking column header using php and mysql
Sorting column with pagination by clicking column header using php and mysql

19 Mar 2014

Avoid duplicate values storing in table using php and mysql

We can avoid duplicate values storing in table when we insert data to table. Because the duplicate entry is one of the main problem, while insert data into mysql table . We removed duplicate values using 'DISTINCT' key in mysql query, if it is present in table. We can check whether the value is present in table or not using PHP and Mysql before add data to table.

We can also avoid duplicate values storing in mysql table while inserting by primary key.

Consider following example. You've a database named with 'new' and table named with 'user'. You try to add names in user table. Now you've to check the values is present or not while you inserting. The table look like this:

Avoid duplicat values sotring in table using PHP at phponwebsites

Now you try to add name 'guru' again. You can check whether the name 'guru' is present in table or not using below PHP script.

<?php
if(isset($_POST['submit']))
{
mysql_connect('localhost','root','');
mysql_select_db('new');
$name=$_POST['name'];
$query=mysql_query("select * from user where name='".$name."' ") or die(mysql_error());
$duplicate=mysql_num_rows($query);
if($duplicate==0)
{
$query1=mysql_query("insert into user values('".$name."')") or die(mysql_error());
}
else
{
echo'The name '.$name.' is already present in the user table';
}
}
?>
<html>
<body>
<form method='post' action='#'>
name: <input type='text' name='name'>
<input type='submit' name='submit' value='Submit'>
</form>
</body>
</html>

Where,
mysql_query() - execute the query and select if any similar values are present in table. mysql_num_rows() - returns numeric value of selected rows. If its value is 0, then there is no similar values present in table. Otherwise the value is already present in table.
Where,
you can also use mysql_fetch_row() instead of mysql_num_rows().
If the value is present in table, it could not be stored in table. Now you can avoid duplicate entries in table using PHP and Mysql.

Related Post:

How to store values into mysql table using php

Pages