Sorting column by clicking column header with php and mysql in table

                       All of you use the tables in mysql database. When you click the column header at first time, it displays values in ascending order. Likewise it displays values in descending order, while clicking field name second time. Normally the table look like this:

When you click the category_Name field, it display value by ascending order as follow as:

                      We can make our table like tables in mysql database. We can sorting values in column by clicking filed name. It can be done by PHP.
Follow the below steps.
          1. Connect file with mysql database as follows as:

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, you need to sorting tables based on column filed name. So you have to pass the field name in url. Similarly, you sort table values both ascending and descending. So you need to also pass order types in url. By default, table values displays in ascending. You can fixed order types and also field name.

$field='game_ID';
$sort='ASC';

where, the mysql table display values by ascending order based on game_ID

Now your headings should be like this.

<th><a href="table1.php?sorting='.$sort.'&field=game_ID">Game Id</a></th>
<th><a href="table1.php?sorting='.$sort.'&field=category_Name">Category Name</a></th>
<th><a href="table1.php?sorting='.$sort.'&field=game_Title">Game Name</a></th>

where,
           - table1.php is the name of file.
           - $sort means order type either ascending or descending
           - game_ID, category_Name, game_Title are field names in mysql table.

        3. Now you need get the values from url.

if(isset($_GET['sorting']))
{
  if($_GET['sorting']=='ASC')
  {
  $sort='DESC';
  }
  else { $sort='ASC'; }
}
if($_GET['field']=='game_ID')
{ 
    $field = "game_ID";  
}
elseif($_GET['field']=='category_Name')
{
   $field = "category_Name"; 
}
elseif($_GET['field']=='game_Title')
{ 
   $field="game_Title"; 
}

where, you handle your statergies. ie, If sorting value is ascending, then you will sort table by descending. Likewise you'll sort table by ascending, if sort value is descending. Then you need to get column field values.
           
       4. Then you write a query in which both order types and field name should be present. The mysql query should be like this.

SELECT game_ID, category_Name, game_Title FROM yobash_game ORDER BY $field $sort

where,
            - $filed is a default field name ie, gameId
            - $sort is a default order type already we fixed. ie. ASC

Sorting column by clicking column header

       5. Finally combine all the PHP codes as follows as:

table1.php

<?php
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('db_name') or die(mysql_error());
$field='game_ID';
$sort='ASC';
if(isset($_GET['sorting']))
{
  if($_GET['sorting']=='ASC')
  {
  $sort='DESC';
  }
  else
  {
    $sort='ASC';
  }
}
if($_GET['field']=='game_ID')
{
   $field = "game_ID";
}
elseif($_GET['field']=='category_Name')
{
   $field = "category_Name";
}
elseif($_GET['field']=='game_Title')
{
   $field="game_Title";
}
$sql = "SELECT game_ID, category_Name, game_Title FROM yobash_game ORDER BY $field $sort";
$result = mysql_query($sql) or die(mysql_error());
echo'<table border="1">';
echo'<th><a href="table1.php?sorting='.$sort.'&field=game_ID">Game Id</a></th>
     <th><a href="table1.php?sorting='.$sort.'&field=category_Name">Category Name</a></th>
<th><a href="table1.php?sorting='.$sort.'&field=game_Title">Game Name</a></th>';
while($row = mysql_fetch_array($result))
{
echo'<tr><td>'.$row['game_Id'].'</td><td>'.$row['category_Name'].'</td><td>'.$row['game_Title'].'</td></tr>';
}
echo'</table>';
?>

          Finally you'll get PHP scripts for sorting table when you click the column filed name. Now you can sort tables as like as tables in mysql database.

Related Post:
Simple pagination concepts using php and mysql
Pagination using php, mysql and ajax

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

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.

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:

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>";
}
?>

                                        OR

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:

Related Post:

How to get database list in mysql server using php

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 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_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:

       
      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:

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:

 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:

          Finally you can retrieve data from mysql table using php.

mysql_fetch_row in php and mysql

                      Mysql_fetch_row()  return row as numeric array. We can't use associative indices to retrieve data from mysql table using php.

Consider following example:

                     

Retrieve data from table using mysql_fetch_row()  in php:

                       You can retrieve data from mysql table using  mysql_fetch_assoc() in php. The following php script is used for retrieve data.

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

Where,
          the result of array should be like this:
             Array(
                         [0] => 1
                         [1] => Adventures
                         [2] => Achilles
                       )      

 Suppose you'll use following php script,
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:

 Where,
              'game_Title' is the field name of column. You can't use $res['game_title'] here. Because you retrieved data from table using mysql_fetch_row().

Related Post:

Retrieve data from mysql database using mysql_fetch_assoc in php
Retrieve data from mysql database using mysql_fetch_array() in php

mysql_fetch_assoc in php and mysql

                       Mysql_fetch_assoc() return row as an associative array. Here, we can use numeric indices to get values from mysql data using php.

Consider following example:

                     

Retrieve data from table using mysql_fetch_assoc() in php:

                       You can retrieve data from mysql table using mysql_fetch_assoc() in php. The following php script is used for retrieve data.

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

Where,
            the result of array should be like this:
     Array(
                  [game_ID] => 1
                  [category_Name] => Adventures
                  [game_Title] => Achilles
               )

 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:

Where,
              $res[0] represents the first column of mysql table. But you can't use this to retrieve data from table. Because you used mysql_fetch_assoc() mysql function to retrieve data.

Related Post:

Retrieve data from mysql database using mysql_fetch_row in php
Retrieve data from mysql database using mysql_fetch_array() in php

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:

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

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.

 <html>
 <body>
 <?php
      if(isset($_POST['submit']))
       {
            mysql_connect('localhost','root','') or die(mysql_error());
            mysql_select_db('new') or die(mysql_error());
            $id=$_POST['id'];
            $name=$_POST['name'];
            $img=$_POST['img'];
            if($id!='' && $name!='' && $img!='' )
            {
                $query=mysql_query("insert into table4 values('".$id."','".$name."', '".$img."')") or die(mysql_error());
                if($query)
                {
                   echo 'Vaules are inserted into table';
                } 
               else
               {
                   echo'Vaules are not inserted';
               }
            }
           else
           {
                echo'Insert all values';
           }
         }
?>
<form action="#" method='post'>
<table align='center' cellspacing='5'>
<tr><td>ID</td><td><input type='text' name='id'></td></tr>
<tr><td>Name</td><td><input type='text' name='name'></td></tr>
<tr><td>img</td><td><input type='text' name='img'></td></tr>
<tr><td></td><td><input type='submit' name='submit' value='submit'></td></tr>
</table>
 </form>
</body>
</html>

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:

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

Create mysql database using php

                       All of you know, create mysql database in phpmyadmin. Now we are going to know how to create database using php.

The mysql query is:
            CREATE DATABASE db_name

 Then you use the particular database. The mysql query is:
                      USE db_name

The php coding as follows as:

<?php
mysql_connect('localhost','root','');
$query=mysql_query('create database new') or (mysql_error());
$qu=mysql_query('use new')  or (mysql_error());
if($query)
 {
   echo'Database is created';
 }
  else
 {
   echo'Database 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_query() is used for execute the mysql query.
           - 'new' is the database new.
           - mysql_error() give error message while the mysql query is execute.
           - die() terminate the excution.

       Now you'll get output as " Database is created ". Then you can see the new database in your phpmyadmin.

      The database 'new' is created and ready for use.

Related Post:

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