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

Create RSS Feed dynamically using PHP and MySQL

                                    You can create RSS feed dynamically using PHP like how you create sitemap dynamically using PHP and MySQL. Before you are going to know create RSS Feed, you should know what is RSS Feed? and why you need to add RSS Feed to your websites?

What is RSS Feed?

                                   RSS expands Really Simple Syndication. It is developed in XML like Sitemap. It is like content delivery vehicle. That means, you created a product, Ii distributes it to all places. Due to this feed, the viewers of your websites can easily find the latest pages of your websites. So there is no need to spend more time to search their desired topics on your web contents. This is the short description about RSS Feed. 

For more details, visit
    RSS Feed in W3schools
    RSS Feed at rss.softwaregarden
    what is RSS Feed
    Definition of RSS Feed at press-feed

Structure of RSS Feed

             
                                   The structure of RSS Feed is described below.

<?xml version='1.0' encoding='UTF-8'?>
<rss version='2.0'>
<channel>
    <title>Phponwebsites</title>
    <link>http://www.phponwebsites.com</link>
    <description>Phponwebsites about php related topics like .htaccess, robots.txt, mysql, jquery, ajax, js and also php for php developers</description>
    <language>en-us</language>
    <item>
      <title>PHP</title>
      <link>http://www.phponwebsites.com/p/php.html</link>
      <description>PHP doubts clarified at Phponwebsites</description>
   </item>
  <item>
      <title>MySQL</title>
      <link>http://www.phponwebsites.com/p/mysql.html</link>
      <description>MySQL doubts clarified at Phponwebsites</description>
   </item>
  <item>
      <title>htaccess</title>
      <link>http://www.phponwebsites.com/p/htaccess.html</link>
      <description>Htaccess doubts clarified at Phponwebsites</description>
   </item>
</channel>
</rss>

Generate RSS Feed dynamically using PHP and MySQL

                 Let see how to create RSS Feed dynamically using PHP and MySQL. Follow the below steps to create feed.

1. Create database and table

                  First you create database for store your link details on it. The below MySQL query is used for create database.

                Create database new

 where new is database name

                Use new

The above query is used to use database "new".
                  The MySQL query for create table is,

                Create table links (id int not null auto_increment primary key, name varchar(200), link varchar(200), description varchar(250))

                 Now the table "links" is created. Then store values into table using below query

                insert into table links values('','PHP','http://www.phponwebsites.com/p/php.html','PHP doubts are clarified at Phponwebsites')

                After insert values into table, your table look likes below

2. Add header type RSS Feed

                            First you need to add content type of RSS feed. It is represented below.

header("Content-Type: application/rss+xml; charset=ISO-8859-1");

3. Add content to RSS Feed

                         
                            First of all you need to store all links in your database. Then on;y you can retrieve it using PHP. The below example we Retrieve data from MySQL database using mysql_fetch_array in PHP.
After added header, you have to add some name spaces for RSS Feed. Finally add contents to RSS Feed.

<?php
header("Content-Type: application/rss+xml; charset=ISO-8859-1");
echo '<?xml version="1.0" encoding="UTF-8" ?>
<?xml-stylesheet type="text/xsl" media="screen" href="/~d/styles/rss2full.xsl"?>
<?xml-stylesheet type="text/css" media="screen" href="http://feeds.feedburner.com/~d/styles/itemcontent.css"?>
<rss
xmlns:content="http://purl.org/rss/1.0/modules/content/"
xmlns:wfw="http://wellformedweb.org/CommentAPI/"
xmlns:dc="http://purl.org/dc/elements/1.1/"
xmlns:atom="http://www.w3.org/2005/Atom"
xmlns:sy="http://purl.org/rss/1.0/modules/syndication/"
xmlns:slash="http://purl.org/rss/1.0/modules/slash/"
    xmlns:feedburner="http://rssnamespace.org/feedburner/ext/1.0"
version="2.0">
<channel>
<title>Phponwebsites</title>
<link>http://www.phponwebsites.com</link>
<description>Phponwebsites about php related topics like .htaccess, robots.txt, mysql, jquery, ajax, js and also php for php developers</description>';
       $connection = @mysql_connect('localhost', 'root', '') or die(mysql_error());
       mysql_select_db('new')  or die (mysql_error());
       $query = "SELECT * FROM links order by id desc";
       $rss = mysql_query($query) or die (mysql_error());
       $ImgPath='https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiwiEMDL1tvD_oaHIph6KkufjJK5hc5m7kwdJoVgkjFJuf4oqWWbSQXNDkuq1-ukdOhgudTe6gou7Al4oAbvJ-oC-ejuZEhre-ue99v02aajGkKGacaDkZPfk09v7DvUzZlJbIoaxNajoMu/s1600/logo5.png';
       while($row=mysql_fetch_array($rss)){
       echo '
<item>
<title>'.$row['name'].'</title>
<link>'.$row['link'].'</link>
<content:encoded>
      <![CDATA[<p align="left">
          <a href="'.$row['link'].'"><img style="background-image: none; " border="0" src="'.$ImgPath.'" />    </a></p>]]>
       </content:encoded>
       <description>'.$row['description'].'</description></item>';
}
      echo '</channel>
</rss>';
?>

               When you run this file on firfox, you will get output like below

4. Htaccess

                         Then you have to add below lines to your htaccess file.

                    RewriteRule ^rss.xml$ rss.php [L]

When you type rss.xml in your browser address bar, it redirects to rss.php.

             Finally you got dynamic RSS Feed.

Related Post:
Read data from RSS Feed using PHP
Create json file using PHP and MySQL
Create XML 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 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:

             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

                   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

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

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

                       All of you know how to sorting column while clicking column header like mysql table in database through my previous post. Don't you know? Please visit this sorting column by clicking column header using php .

                       Now we are going to see about sorting column with pagination like table in mysql database. You sorting column by clicking column header in mysql table and you paginated to next page. You will get result followed by next value.
                       ie, you sort column by id. First 30 values displayed in first page in mysql table. When you paginate to next page, you will get output from 31st to 60 in your mysql table. Can you make your table like mysql table with sorting and pagination? Yes, you can also done pagination using php.
                      Already you know the sorting concepts. So now we will see sorting with pagination concepts.

                      1. Start and limit 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.

                    2. Total number of data in mysql table:
Then we need to calculate the total number of data in table. Then find the maximum pages like below:

$total_values=mysql_query("SELECT game_ID, category_Name, game_Title FROM table1");
$total=mysql_num_rows($total_values);
$maxpage=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:

function pagination($maxpage,$page,$url,$field,$sort)
{  
  //After you sorting your table by clicking particular column, the following trick is used to display 
   //values with similar sorted values.
  if($sort=='ASC')
  {
    $sort='DESC';
  }
  else
  {
    $sort='ASC';
  }
  echo'<ul style="list-style-type:none;">';
   for($i=1; $i<=$maxpage; $i++)
   {
    if($i==$page)
{
 echo'<li style="float:left;padding:5px;">'.$i.'</li>';
}
else
{
 echo'<li style="float:left;padding:5px;"><a href="sort.php?p='.$i.'&sorting='.$sort.'&field='.$field.' ">'.$i.'</a></li>';
}
   }
  echo'</ul>';
}
pagination($maxpage,$page,$url,$field,$sort);

          Where, you have to use small tricks. You have to pass the sorting and field values in url using function.
ie, Already we set if the url get 'ASC' value, then it will take as 'DESC'. This is for sorting. Similarly, you have to do same thing for pagination.

Sorting column with pagination by clicking column header:

The total php script as follows for both sorting and pagination  by clicking column header:

<?php
error_reporting(E_ALL ^ E_NOTICE);
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new');
$field='game_ID';
$sort='ASC';
if(isset($_GET['sorting']))
{
  if($_GET['sorting']=='ASC')
  {
  $sort='DESC';
  }
  else { $sort='ASC'; }
}
if($_GET['sorting'])
{
if($_GET['field']=='game_ID')
{  $field = "game_ID";  }
elseif($_GET['field']=='category_Name')
{ $field = "category_Name"; }
elseif($_GET['field']=='game_Title')
{ $field="game_Title"; }
}
//pagination
 $url='sort.php';
 $limit=10;
 $page=$_GET['p'];
 if($page=='')
 {
  $page=1;
  $start=0;
 }
 else
 {
  $start=$limit*($page-1);
 }

$sql = "SELECT game_ID, category_Name, game_Title FROM table1 ORDER BY $field $sort limit $start, $limit";
$total_values=mysql_query("SELECT game_ID, category_Name, game_Title FROM table1");
$total=mysql_num_rows($total_values);
$maxpage=ceil($total/$limit);
$result = mysql_query($sql) or die(mysql_error());
echo'<table border="0">';
echo'<th><a href="sort.php?sorting='.$sort.'&field=game_ID">Game Id</a></th>
     <th><a href="sort.php?sorting='.$sort.'&field=category_Name">Category Name</a></th>
<th><a href="sort.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>';
function pagination($maxpage,$page,$url,$field,$sort)
{
  //After you sorting your table by clicking particular column, the following trick is used to display
   //values with similar sorted values.
  if($sort=='ASC')
  {
    $sort='DESC';
  }
  else
  {
    $sort='ASC';
  }
  echo'<ul style="list-style-type:none;">';
   for($i=1; $i<=$maxpage; $i++)
   {
    if($i==$page)
{
echo'<li style="float:left;padding:5px;">'.$i.'</li>';
}
else
{
echo'<li style="float:left;padding:5px;"><a href="sort.php?p='.$i.'&sorting='.$sort.'&field='.$field.' ">'.$i.'</a></li>';
}
   }
  echo'</ul>';
}
pagination($maxpage,$page,$url,$field,$sort);
?>

               Now you will get output  like this:

               Now you can sorting column with pagination by clicking column header.          

Related Post:

Simple pagination concepts using php and mysql
Pagination using php, mysql and ajax

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