JSON WITH DATABASE

Summary

1. Creating Database and table with data

2. Create connection with database

3. Install & configure wamp/xamp server to to run php script

4. Showing api

Note : Install wamp/xampp to run mysql database and localhost( to run php pages and place all php file in 'c:\wamp\www\' (in case of wamp) , c:\xampp\htdocs ( in case of xampp).

STEP - 1 : CREATE DATABASE IN MYSQL - Run the wamp/xampp.

Now, Open your browser and place this line - 'http://localhost/phpmyadmin/' (default password 'root').

Go to New --> click on New -->   In ' create database' enter your database name' --> click on create.

Now Click on SQL tab in mysql database and place below code.

DATABASE NAME : country_list

*************************************************************

CREATE TABLE `country_list` (

  `id` int(11) NOT NULL,

  `countrys` varchar(50) NOT NULL,

  `capitals` varchar(50) NOT NULL,

  `languages` varchar(50) NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `country_list` (`id`, `countrys`, `capitals`, `languages`) VALUES

(1, 'india', 'delhi', 'hindi'),

(2, 'america', 'new york', 'english'),

(3, 'russia', 'moscow', 'russian');

ALTER TABLE `country_list`

  ADD PRIMARY KEY (`id`);

*************************************************************

Now, click GO button to complete operation.

Step - 2 : Database Connection - To perform INSERT, DELETE, UPDATE operation, you need to create a connection.

Open notepad and place below code

Connection.php

*************************************************************

 <?php

$con=mysqli_connect("localhost","root","","php topic");

if($con->connect_error)

{

    echo"connection fail".$con->connect_error;

}

?>

*************************************************************

and save it in proper folder ( discussed above).

Step - 3 : Now, create another file in same folder where you place connection.php file, named it ' simple_json_with_db' and place below code-

***************************************************************

 <?php

include('include/connection.php');

  

    $sql="select * from country_list";

    $res=mysqli_query($con,$sql);

  if($num=mysqli_num_rows($res)>0)

  {

      while($row=mysqli_fetch_assoc($res))

      {

          $json[]=$row;

      }

      echo json_encode(array('record'=>$json));

  }

   else

   {

       $json[]=array('msg'=>"no records found..!",'status'=>0);

       echo json_encode(array('message'=>$json));

   }

?>

***************************************************************

Step - 4 : Get Json - Open your browser and place this is addressbar 'localhost/country_list.php'.

OUTPUT :

 {"record":[{"id":"1","countrys":"india","capitals":"delhi","languages":"hindi"},{"id":"2","countrys":"america","capitals":"new york","languages":"english"},{"id":"3","countrys":"russia","capitals":"moscow","languages":"russian"}]}