Create Search API like itunes Search API using PHP and MySQL

                      The Apple itunes provided a search API for retrieve app information from it. The Apple search API display result as JSON format. Do you want to know more information about Apple itunes search API, then visit iTunes Search API.

                      You can also create search API like Apple itunes search API if you know how to create JSON file using PHP. Due to this, you can provide search API for your websites. If you didn't know create JSON file using PHP and MySQL, then visit How to get output as JSON format using PHP and Mysql.

Generate Search API using PHP and MySQL

                       Lets see how to create search API using PHP.  Follow below steps to generate search API for your websites using PHP.

Step1: Connect MySQL database using PHP

                     Connect your MySQL database to get stored values from it using PHP. The PHP code for connect MySQL database is below:

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

Step2: Get value from url using PHP

                     While user search something on your search API, you need to get those values for search from url. So only you can provide particular results from your MySQL database. This is done by below PHP code.

 $field=$_REQUEST['field'];
 $limit=$_REQUEST['limit'];

Step3: Make MySQL select query

                     After got values from url, you've to make MySQL query to select corresponding results from database. You can get zero or more than one values from urls for search. You should make MySQL query like below using PHP.

 if($field!='' && $limit!='')
 {
   $cond="where name='".$field."' order by id desc limit 0, $limit";
 }
 if($field=='' && $limit!='')
 {
   $cond="order by id desc limit 0, $limit";
 }
 if($field!='' && $limit=='')
 {
   $cond="where name='".$field."' order by id desc limit 0, 50";
 }
 if($field=='' && $limit=='')
 {
   $cond="order by id desc limit 0, 50";
 }
 $q=mysql_query("select * from f_img $cond") or die(mysql_error());

Step4: Add header for get output as JSON format

              You must add header type ato your PHP script for get output as JSON format. This is like below

 header('Content-Type: Application/json');

Step5: Add content to JSON file using PHP

               You need to create 2 array for create output as multidimensional array. Retrieve values from query result using mysql_fetch_array. Then you push values to array using array_push() in PHP. Finally encode the results to display output as JSON format using json_encode().

               The full PHP code for create search API is represented below,

<?php
 error_reporting('E_ALL ^ E_NOTICE');
 mysql_connect('localhost','root','') or die(mysql_error());
 mysql_select_db('fitness') or die(mysql_error());
 $field=$_REQUEST['field'];
 $limit=$_REQUEST['limit'];
 if($field!='' && $limit!='')
 {
   $cond="where name='".$field."' order by id desc limit 0, $limit";
 }
 if($field=='' && $limit!='')
 {
   $cond="order by id desc limit 0, $limit";
 }
 if($field!='' && $limit=='')
 {
   $cond="where name='".$field."' order by id desc limit 0, 50";
 }
 if($field=='' && $limit=='')
 {
   $cond="order by id desc limit 0, 50";
 }
 $q=mysql_query("select * from user $cond") or die(mysql_error());
 header('Content-Type: Application/json');
 $details=array();
 $user=array();
 while($res=mysql_fetch_array($q))
 {
  $user['name']=$res['name'];
  $user['state']=$res['state'];
  $user['country']=$res['country'];
  array_push($details,$user);
 }
 //print_R($details);
 echo json_encode($details);

?>

           Your JSON output should be like below,

[
 {"name":"Clark","state":"California","country":"USA"},
 {"name":"Anderson","state":"Los Angeles","country":"USA"},
 {"name":"Smith","state":"London","country":"United Kingdom"},
 {"name":"Jones","state":"Cambridge","country":"United Kingdom"},
 {"name":"Nathan","state":"Franche Comte","country":"France"},
 {"name":"Marie","state":"Paris","country":"France"},
 {"name":"Nastya","state":"St Petersburg ","country":"Russia"},
 {"name":"Dima","state":"Moscow","country":"Russia"},
 {"name":"Linh","state":"Hanoi","country":"vietnam"},
 {"name":"Hoang","state":"Hai Phong","country":"vietnam"},
]

Step6 : .htaccess

                   Add following lines to your .htaccess file

RewriteRule ^search search.php [L]

                  Now you entered search on address bar, the .htaccess redirects you to search.php file. You can get search API using below url types.

                http://www.phponwebsites.com/search?field=nathan&limit=10
                http://www.phponwebsites.com/search?field=nathan
                http://www.phponwebsites.com/search?limit=10
                http://www.phponwebsites.com/search

Related Posts:

Get APP or Game details from Apple iTunes using PHP

Retrieve images from mysql database using php

                       Already you know  how to upload images to mysql database using php. Otherwise, visit upload images to mysql database using php.

Retrieve images from database:

                        Normally you can retrieve data from mysql table using php. Then you print the result. Similarly you retrieve image name from mysql database and print image name with located folder in '<img>' tag.

Consider following mysql table.

 The php script for retrieve images from mysql database is:

<?php
        mysql_connect('localhost','root','') or die(mysql_error());
        mysql_select_db('new')  or die(mysql_error());
        $q=mysql_query("select * from image where no=1 ")  or die(mysql_error());
        $res=mysql_fetch_array($q);
?>
    <img src="<?php echo 'http://localhost/upload/image/'.$res['img_name'];?>">

   
      where,
                 mysql_query() select the row which is no '1' from mysql database.
                 mysql_fetch_array() return the result an associated array. You want to know about  mysql fetch array().
                 In your img src print image name with location of image. Now you''ll get output like this:

Related Post:

Upload files using PHP
Upload multiple files or images using PHP
How to Upload images using Ajax with jQuery in PHP

Create XML file using PHP and MySQL

                                   You can get output as XML format using PHP. You can import values to XML file from Mysql database using PHP.

                                   XML expands Extensible Markup Language. It is used to share your data across various platforms. You can also retrieve values from XML file using PHP.

Get output as XML format using PHP

                                    If you want to convert your Mysql table values into XML format using PHP, then follow below steps.

Step1: Add header to XML file in PHP

                                    First you need to add content type of XML file in PHP in order to get output as XML format. It need to add below PHP codes in your PHP file.

header ("content-type: text/xml");

Step2: Connect mysql database

                                    After added content type of XML, you need to connect Mysql database using PHP to retrieve values from it. You add below codes after content type in PHP file.

mysql_connect('localhost','root','');
mysql_select_db('fitness');

Step3: Add content to XML file

                                  Then select required values from Mysql database using mysql_query(). Retrieve values from query result using mysql_fetch_array. Finally your PHP file look likes below one.

<?php
header ("content-type: text/xml");
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('fitness') or die(mysql_error());
$xml='<?xml version="1.0" encoding="UTF-8"?>';
$qr=mysql_query("SELECT * FROM `f_img` order by id desc") or die(mysql_error());
$xml.='<userlist>';
while($res=mysql_fetch_array($qr))
{
 $xml.='<user><name>'.$res['name'].'</name><state>'.$res['state'].'</state><country>'.$res['country'].'</country></user>';
}
$xml.='</userlist>';
echo $xml;
?>

                                 You will get output as XML format like below,

<userlist>
    <user>
       <name>Clark</name>
       <state>California</state>
       <country>USA</country>
   </user>
   <user>
      <name>Smith</name>
      <state>London</state>
      <country>United Kingdom</country>
   </user>
   <user>
      <name>Nathan</name>
      <state>Franche Comte</state>
      <country>France</country>
   </user>
   <user>
      <name>Nastya</name>
      <state>Moscow</state>
      <country>Russia</country>
   </user>
</userlist>

                                     Now you got output as XML content. Similarly, you can get XML element's values using PHP.

Related Post:
Create Excel file using PHP and MySQL
Create XML Sitemap dynamically using PHP and MySQL
Create RSS Feed Dynamically using PHP and MySQL
PHP : Parse data from RSS Feed using SimpleXML
PHP : Parse data from XML Sitemap using simplexml_load_file and file_get_elements
Read data from JSON file using PHP
Parse data from XML file using PHP

Create json file using PHP and MySQL

                        You can create json file using PHP and Mysql. You can get output as json format using PHP and Mysql. Before you want to know that, first you should know what is json? and what is the us of json?

JSON

      
                       JSON expands JavaScript Object Notation. It is one of the data interchange format. You can easily read and write data through JSON file.

                       It is language independent. So you can retrieve data from it using any familiar languages like PHP. It should be array format, object format and both array and object fotmat.

     The array format of Json file should be like this,

       [    
           {
              'tutorial':'PHP',
              'link':'http://www.phponwebsites.com/p/php.html'
           },
           {
              'tutorial':'Mysql',
              'link':'http://www.phponwebsites.com/p/mysql.html'
           },
           {
            'tutorial':'htaccess',
            'link':'http://www.phponwebsites.com/p/htaccess.html'
           }    
      ]

The object of JSON should be like this,

    {  
           'tutorial':'PHP',
            'link':'http://www.phponwebsites.com/p/php.html'  
    }

Get output as json format using PHP and Mysql

                           Now you know something about JSON files. So we are going to know how to get output as JSON format using php and mysql.

Add header to create json file using PHP and Mysql

       
                          If you want to create json file, then you have to add header for json file. It is like below

header('Content-Type: application/json'); 

          Where json indicate this is json file

Add content to json file using PHP

             Then you need to add contents to your json file as you want as. Retrieve values from query result using mysql_fetch_array. Suppose you have more values in a variable. Then you need to store all values in array. Consider the following PHP code.

<?php
header('Content-Type: application/json');
$linklist=array();
$link=array();
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('quicksai_quick') or die(mysql_error());
$qr=mysql_query("SELECT * FROM `links` ") or die(mysql_error());
while($res=mysql_fetch_array($qr))
{
 $link['tutorial']=$res['tutorial'];
 $link['url']=$res['url'];
 array_push($linklist,$link);
}
//print_R($linklist);
echo json_encode($linklist);
?>

      The above example explain how to create json file on multidimensional array using PHP and Mysql.
     where,
               array() - create array variable.
               array_push() - insert values into array
               json_encode() - encode the values and returns JSON representation of values
             

The above example will give output as json file. It should be like this:

      

      [

      {'tutorial':'PHP', 'link':'http://www.phponwebsites.com/p/php.html'},

      {'tutorial':'Mysql', 'link':'http://www.phponwebsites.com/p/mysql.html'},

      {'tutorial':'htaccess','link':'http://www.phponwebsites.com/p/htaccess.html'}

      ]

  

                   Now you can get output as json file. Then you should know how to retrieve values from json file using PHP.

Related Post:
Create Excel file using PHP and MySQL
PHP : Create XML file
Create XML Sitemap dynamically using PHP and MySQL
Create RSS Feed Dynamically using PHP and MySQL
PHP : Parse data from RSS Feed using SimpleXML
Read data from JSON file using PHP
Parse data from XML file using PHP

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:

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

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:

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

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 

Create excel spreadsheet using PHP and Mysql

                       You can generate excel spreadsheets easily using PHP. You need to follow below steps to create excel file.

Add content type:

                    First you need to add excel sheet content type to create excel file using PHP.

header("Content-Type: application/vnd.ms-excel");

Add name and download excel file:

                    You can provide name for excel spreadsheet and make your sheet as downloadable file on coding. The Content-Disposition is used to force browser to save a file. For that, your file should be attached following line:

header("Content-disposition: attachment; filename=spreadsheet.xls");

Add content to excel spreadsheet:

                   Finally you can add contents to your excel file. Consider the following PHP script for generate excel file:

<?php
header("Content-Type: application/vnd.ms-excel");
header("Content-disposition: attachment; filename=spreadsheet.xls");
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new') or die(mysql_error());
$q=mysql_query("select * from table1")  or die(mysql_error());
if(mysql_num_rows($q)>0)
{
echo"<table><tr><th>Name</th><th>Address</th><th>Phone</th></tr>";
 while($res=mysql_fetch_array($q))
 {
  echo"<tr><td>".$res['name']."</td><td>".$res['address']."</td><td>".$res['phone']."</td></tr>";
 }
echo"</table>";
}
?>

     Where,
                 mysql_fetch_array() return values as both an associative and numeric array.
                      while executing file, it required you to save excel file. After save file, you can view it. Now you got excel spreadsheet with named spreadsheet.xls which is created by PHP.

Related Post:
How to fetch result from mysql table using mysql_fetch_array in PHP
Create json file using PHP and MySQL
PHP : Parse data from XML Sitemap using simplexml_load_file and file_get_elements
Read data from JSON file using PHP
Parse data from XML file using PHP

Create sitemap dynamically using PHP and MySQL

                         Do you know why you need to add sitemap into you websites? what is the use of sitemap? Let see here to solve all of your doubts about sitemap.

Sitemap:

                         Sitemap is very important for all websites. All of you know, the search engines like google, bing are crawling pages on your websites to know about websites. Using this sitemap, webmasters to inform search engines about pages for crawling. Sitemap is a XML file which contains all url of your websites upto to date. If you add new page to your site, then you'll add url of new page to sitemap for crawling.

                          Sitemap is also provide url structure of websites. Suppose a new user comes to your websites. The sitemap leads new user to all pages on your site.

Structure of sitemap: 

                    The structure of sitemap is:

<?xml version="1.0" encoding="UTF-8">
 <urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://www.sitemaps.org/schemas/sitemap/0.9
 http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd">
  <url>
    <loc>http://www.phponwebsites.com</loc>
<lastmod>today date<lastmod>
<changefreq>daily</changefreq>
<priority>1</priority>
  </url>
  <url>
    <loc>http://www.phponwebsites.com/p/htaccess.html</loc>
<lastmod>today date<lastmod>
<changefreq>daily</changefreq>
<priority>1</priority>
  </url>
 </urlset>

  where,
      urlset - encapsulates the file ie, start sitemap
      url - parent tag for each entry ie, each page
      loc - location of url
      lastmod - date for last modification of file
      changefreq -  how frequently the pag is changed. It should be always, hourly, daily, monthly, weekly, yearly, never
      priority - priority of url. It should be between 0.0 to 1.0

Create sitemap dynamically using PHP and MySQL:

                          First you need to add content type for xml to generate sitemap in PHP. ie,

 header ("content-type: text/xml");

                        Then add contents to XML file. Then save this file as sitemap.php .

<?php
 mysql_connect('localhost','root','') or die(mysql_error());
 mysql_select_db('new') or die(mysql_error());
 $dat=date('Y-m-d');
 header ("content-type: text/xml");
 echo '<?xml version="1.0" encoding="UTF-8"?>
 <urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://www.sitemaps.org/schemas/sitemap/0.9
 http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd">';
 $urls=mysql_query("select * from url")or die(mysql_error());
 while($row_Tags=mysql_fetch_array($urls))
 {
   echo "<url><loc>".$row_Tags['url']."</loc><lastmod>".$dat."</lastmod><changefreq>daily</changefreq><priority>1</priority></url>";
 }
  echo "</urlset>";
 ?>

.htaccess:

               

               In your .htaccess, you add following codes.

RewriteRule ^sitemap.xml sitemap.php [L]

                   When you run sitemap.xml on your website each time,  it display upto date contents.

Related Post:

Create RSS Feed dynamically using PHP and MySQL
Read data from RSS Feed using PHP
Create XML file using PHP and MySQL

Pagination using php and mysql with jquery and ajax

                      You can display values with pagination using php. But you have to pass the page number and also relevant values through url. So your url look like this:

                                   pagination.php?p=2

                      The good php website should be contain good url strucutre. Thats why you alter the urls using .htaccess. You don't need to show passing values in url. It can be hidden by .htaccess. If you don't know, just visit Redirect urls using htaccess. 
                      So you need ajax and jquery to pass the values. Already you know the basic pagination concept. Otherwise, please visit Pagination using php and mysql. 

 Pagination using juery and ajax:

                       You need small function which is called by onclick event to display values with pagination in php. The jquery function as follows as:

<script type="text/javascript">
   function page(e) {
         $.ajax({
                       url:'pagination.php',
                       data:{p:e},
                       dataType:'html',
                       Type:'post',
                       success:function(e) {
                          $('#page').html(e);
                           }
                      });
                  }
</script>

   
         Where,
                     - function page(e){} is called from click event.
        ie, <a onclick="return page('.$i.')">'.$i.'</a>            
                    
                     - e is a page number passed from click event.
                     - pagination.php is name of this php file.
                      You define the url, data, dataType and type in ajax post to pass the values.

The overall php script for pagination: 

<html>
<body>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
<div id='page'>
<?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:3px;">'.$i.'</li>';
      }
        else
     {
       echo'<li style="float:left;margin-                 left:4px;background:rgb(170,177,35);padding:4px;cursor:pointer;border:1px solid darkgreen ;"><a  onclick="return page('.$i.')">'.$i.'</a></li>';
      }
   }
  echo'</ul>';
}
if($num_page>1)
{
 pagination($page,$num_page);
}
?>
<script type="text/javascript">
function page(e) {
$.ajax({
url:'pagination.php',
data:{p:e},
dataType:'html',
Type:'post',
success:function(e){
   $('#page').html(e);
  }
});
}
</script>
</div>
</body>

</html>

You'll get output like like this:

                     Now you can paginated to next page. The url structure of your website should be fine.

Note:
             For pagination, you need any jquery reference like:
" <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script> "

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.

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

                                                                 OR

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:

Related Post:

How to get table list in mysql database 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_array in php and mysql

                        Mysql_fetch_array() returns row as an associative, numeric array or both based on parameters passed in it. Three types of parameters can be passed in mysql_fetch_array function.
                 
  1. MYSQL_BOTH - return both associative and numeric array
  2. MYSQL_ASSOC - return associative array
  3. MYSQL_NUM - return numeric array

Retrieve data from table using mysql_fetch_array() in php:

Consider following example:

                     

Mysql_fetch_array() with MYSQL_BOTH in php:

                       It returns  result as both associative and numeric array. You can retrieve data using both associative and numeric indices 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, MYSQL_BOTH))
{
  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:

Where,
          the array should be like this:

     Array(
                  [game_ID] => 1
                  [category_Name] => Adventures
                  [game_Title] => Achilles
               )

                        

                         OR

     Array(
                  [0] => 1
                  [1] => Adventures
                  [2] => Achilles
               )

mysql_fetch_array() with MYSQL_NUM in php:

                              It returns result as numeric array. ie, indices are numeric like 0,1,2...

<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, MYSQL_NUM))
{
  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:

In this type, the array of result should be like below:

     Array(
                  [0] => 1
                  [1] => Adventures
                  [2] => Achilles
               )

          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:

mysql_fetch_array() with MYSQL_ASSOC in php:

                             It returns result as an associative array.

<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,MYSQL_ASSOC))
 {
   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 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:

Note:

          mysql_fetch_array() without array_type means return row as an associative, numeric array or both

Related Post:

Retrieve data from mysql database using mysql_fetch_row in php
Retrieve data from mysql table using mysql_fetch_assoc in php