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

13 Apr 2014

Display results in html table using php and mysql

Normally, you displayed data results without order in php. If you want to display single data, then you can easily display it using ' <?php echo $name; ?> '. But if you have to display large amount of data from mysql table, then you should be display it properly. So only, you can understand the displayed results. So the best option for display data is table. You can display results orderly in table using php. The following mysql query is used to select the values from mysql table.

SELECT * FROM table_name

Display results in table using php and mysql:

You can retrieve data from mysql table using php. The php script for display data in table as follows as:

<html>
<body>
<style type="text/css">
th,td{
border-width:0px 1px 1px 0px;
}
</style>
<?php
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new') or die(mysql_error());
$query=mysql_query("select * from table1 limit 0,10") or die(mysql_error());
echo'<table border="1" ><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>';
?>
</body>
</html>

Now you'll get output like this:

Now you can get your results in table.

9 Apr 2014

Get database list in mysql using php

You can retrieve databases in your mysql server using php. On mysql server, you can use mysql query in order to get databases in mysql.

Get database list using mysql query:

The following mysql query is used to display database list.

SHOW DATABASES

It displays database list in mysql as like below:

Get database list in mysql using php:

You can display database list in mysql using php in two ways.

1. Display database list using php and mysql query

2. Display database list using php and mysql_list_tables()

Display database list using php and mysql:

Now you can find the database list in mysql server using php scripts:

<?php
$link=mysql_connect('localhost','root','') or die(mysql_error());
$result = mysql_query("SHOW DATABASES") or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
echo $row[0]."<br>";
}
?>

Display databases in mysql using php and mysql_list_dbs():

The php code for display database list:

<?php
$link=mysql_connect('localhost','root','') or die(mysql_error());
$db_list = mysql_list_dbs($link);
while ($row = mysql_fetch_object($db_list))
{
echo $row->Database . "<br>";
}

?>

Now you'll get output like below:

display databases available in mysql using php

How to get table list in mysql database using php

7 Apr 2014

Display tables list in mysql database using php

You can display the tables in a mysql database two ways using mysql query.
1. First you select your mysql database. Then use below mysql query:

SHOW TABLES

2.You can get your tables in mysql database without select database using following mysql query.

SHOW TABLES FROM db_name

ex: SHOW TABLES FROM new

Both two ways, you will get output like this:

show tables using mysql_list_tables mysql query

Display table list in database using php:

You can display list of tables in database using php in two ways.

1. Display table list in database using php and mysql

2. Display tables in database using php and mysql_list_tables()

Display table list in database using php and mysql:

You can get list of tables in database using mysql query and php. The following php code is used for display list of tables.

<?php
$link=mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new') or die(mysql_error());
$result=mysql_query("show tables ") or die(mysql_error());
while ($row = mysql_fetch_row($result)) {
echo " {$row[0]}<br>";
}
?>

Display tables in mysql database using php and mysql_list_tables():

You can also get list of tables in database using mysql_list_tables function in php. The following php code is used for display list of tables.

<?php
error_reporting('E_ALL ^ E_NOTICE');
mysql_connect('localhost','root','') or die(mysql_error());
$rs=mysql_list_tables('new') ;
while($res=mysql_fetch_row($rs))
{
echo $res[0].'<br>';
}
?>

Now you'll get output like this:

How to get database list in mysql server using php

2 Apr 2014

Retrieve data from mysql table using php

We are going to see how can fetch the result from mysql table using php.
1. First you need to connect your Mysql database using php script like below:

mysql_connect('server_name','username','password');
mysql_select_db('db_name');

where,
- server_name means localhost,
- username is username of your database
- password is password of your database
- db_name is name of your database

2. Then select the values which is present is table using mysql query.
" SELECT * FROM table_name "
Use above query, if you select all values in table. Otherwise you need to use 'WHERE' clause to retrieve desired data from table.

3. Then you have to use MYSQL function to retrieve data from table such as mysql_fetch_array(), mysql_fetch_row(), and mysql_assoc.
where,
mysql_fetch_array() returns row as numeric array, associative array or both.
mysql_fetch_row() returns numeric array corresponds to fetched row.
mysql_fetch_assoc() returns row as an associative array.

Consider following example:

Fetch result from mysql table using php at phponwebsites

Fetch results from mysql table using mysql_fetch_array() in php:

<html>
<body>
<table>
<th>Game_ID</th><th>Category</th><th>Title</th>
<?php
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new') or die(mysql_error());
$query=mysql_query('select * from table1') or die(mysql_error());
while($res=mysql_fetch_array($query))
{
echo'<tr><td>'.$res['game_ID'].'</td><td>'.$res[1].'</td><td>'.$res['game_Title'].'</td></tr>';
}
echo'<table>';
?>
</body>
</html>

Now you'll get output like this:

Fetch result from mysql table using mysql_fetch_array in php

Fetch result from mysql table using mysql_fetch_row() in php:

<html>
<body>
<table>
<th>Game_ID</th><th>Category</th><th>Title</th>
<?php
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new') or die(mysql_error());
$query=mysql_query('select * from table1') or die(mysql_error());
while($res=mysql_fetch_row($query))
{
echo'<tr><td>'.$res[0].'</td><td>'.$res[1].'</td><td>'.$res[2].'</td></tr>';
}
echo'<table>';
?>
</body>
</html>

Now you'll get output like this:

Fetch result from mysql table using mysql_fetch_row in php

Suppose you'll use
echo'<tr><td>'.$res[0].'</td><td>'.$res[1].'</td><td>'.$res['game_Title'].'</td></tr>';

That means you use associative index instead numerical, then you will get undefined index error like as follows as:

retrieve data from mysql table using mysql_fetch_row in php

Fetch results from mysql table using mysql_fetch_assoc() in php:

<html>
<body>
<table>
<th>Game_ID</th><th>Category</th><th>Title</th>
<?php
mysql_connect('localhost','root','')  or die(mysql_error());
mysql_select_db('new')  or die(mysql_error());
$query=mysql_query('select * from table1')  or die(mysql_error());
while($res=mysql_fetch_assoc($query))
{
echo'<tr><td>'.$res['game_ID'].'</td><td>'.$res['category_Name'].'</td><td>'.$res['game_Title'].'</td></tr>';
}
echo'<table>';
?>
</body>
</html>

Now you'll get output like this:

Fetch result from mysql table using mysql_fetch_assoc in php

Suppose you'll use
echo'<tr><td>'.$res[0].'</td><td>'.$res['category_Name'].'</td><td>'.$res['game_Title'].'</td></tr>';

That means you use numeric index instead associative, then you will get undefined offset error like as follows as:

Retrieve data from mysql table using mysql_fetch_assoc in php

Finally you can retrieve data from mysql table using php.

Pages

17 Apr 2014

Pagination using php and mysql

13 Apr 2014

Display results in html table using php and mysql

Display results in table using php and mysql:

9 Apr 2014

Get database list in mysql using php

Get database list using mysql query:

Get database list in mysql using php:

You can display database list in mysql using php in two ways. 1. Display database list using php and mysql query 2. Display database list using php and mysql_list_tables()

Display database list using php and mysql:

Display databases in mysql using php and mysql_list_dbs():

7 Apr 2014

Display tables list in mysql database using php

Display table list in database using php:

Display table list in database using php and mysql:

Display tables in mysql database using php and mysql_list_tables():

2 Apr 2014

Retrieve data from mysql table using php

Fetch results from mysql table using mysql_fetch_array() in php:

Fetch result from mysql table using mysql_fetch_row() in php:

Fetch results from mysql table using mysql_fetch_assoc() in php:

You can display database list in mysql using php in two ways.

1. Display database list using php and mysql query

2. Display database list using php and mysql_list_tables()