CODEWITHSUNDEEP
phpjson
Satch3000
Satch3000

Reputation: 49432

PHP / JSON parameters on url

I have created a php file which connects to the database, gets the data from table and shows it in json format.

The file in called index.php.

To view the json I just go to the file in the browser:

http://127.0.0.1/json/index.php and it displays:

{"title":[{"id":"1","title":"Title1","desc":"Description1"},{"id":"2","title":"Title2","desc":"Description2"}]}

What I need to do is to be able to filter this by adding parameters like:

For example: http://127.0.0.1/json/index.php?id=1 to just show the data with an id of 1 but it still shows all the data.

Here is the php code:

<?php

$username = "root";
$password = "";
$hostname = "localhost"; 

//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password)
  or die("Unable to connect to MySQL");

$selected = mysql_select_db("mydb",$dbhandle)
  or die("Could not select mydb");

$result = mysql_query("SELECT * FROM contacts");
 $rows = array();
   while($r = mysql_fetch_assoc($result)) {
     $rows['title'][] = $r;
   }

 print json_encode($rows);

?>

What I'm I doing wrong here or missing?

Upvotes: 0

Views: 3027

Answers (4)

DerekIsBusy
DerekIsBusy

Reputation: 61

For one you have to add WHERE to your SQL statement....

SELECT * FROM `contacts` WHERE `id` = $id

Where you see id this should be the name of the id column in your table whatever that may be. But your also going to have to sanitize the input first...

if(!is_numeric($_GET['id']))
    exit; // if not a number then exit

$id = mysql_real_escape_string($_GET['id']); // escape the input

Of course this is the most basic error checking. You could expound upon it. So your code would look more like this...

<?php

$username = "root";
$password = "";
$hostname = "localhost"; 

//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password)
  or die("Unable to connect to MySQL");

$selected = mysql_select_db("mydb",$dbhandle)
  or die("Could not select mydb");

if(!is_numeric($_GET['id']) || !$_GET['id'])
    exit; // if not an integer or id not set then exit

$id = mysql_real_escape_string($_GET['id']); // escape the input

$result = mysql_query("SELECT * FROM contacts WHERE id = $id");
 $rows = array();
   while($r = mysql_fetch_assoc($result)) {
     $rows['title'][] = $r;
   }

 print json_encode($rows);

?>

And you really shouldn't use root to connect to the database in your web app. And also Mihai is right, you should use PDO instead, but it's not really necessary for such a simple app.

Edit But the above code will require an id input. If you want to still be able to get the entire list if no id is provided it would look like this...

<?php

$username = "root";
$password = "";
$hostname = "localhost"; 

//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password)
  or die("Unable to connect to MySQL");

$selected = mysql_select_db("mydb",$dbhandle)
  or die("Could not select mydb");

$sql = "SELECT * FROM `contacts`";

if(isset($_GET['id']) && $_GET['id'] > 0) {
    // if id is set then add the WHERE statement
    if(!is_numeric($_GET['id']))
        die('id must be an integer');  // if id is not an integer then exit
    $id = mysql_real_escape_string((int)$_GET['id']); // escape the input
    $sql .= " WHERE `id` = $id"; // append the WHERE statement to the sql
}


$result = mysql_query($sql);
 $rows = array();
   while($r = mysql_fetch_assoc($result)) {
     $rows['title'][] = $r;
   }

 print json_encode($rows);

?>

Upvotes: 1

Mihai Iorga
Mihai Iorga

Reputation: 39724

<?php
    $username = "root";
    $password = "";
    $hostname = "localhost"; 

    //connection to the database
    $dbhandle = mysql_connect($hostname, $username, $password)
    or die("Unable to connect to MySQL");

    $selected = mysql_select_db("mydb",$dbhandle)
    or die("Could not select mydb");

    $id = 0;
    if(isset($_GET['id'])){ $id = (int)$_GET['id']; }

    if(!$id){
        $query = "SELECT * FROM `contacts`";
    } else {
        $query = "SELECT * FROM `contacts` WHERE `id`='".$id."'";
    }
    $result = mysql_query($query);
    $rows = array();
    while($r = mysql_fetch_assoc($result)) {
        $rows['title'][] = $r;
    }
    print json_encode($rows);
?>

Upvotes: 1

Amar Gharat
Amar Gharat

Reputation: 154

Change following

$result = mysql_query("SELECT * FROM contacts");

to 

$id = $_REQUEST['id'];

$query = 'SELECT * FROM contacts';

if(is_numeric($id))
    $query .= ' WHERE id = ' . $id;

$result = mysql_query($query);

Upvotes: 1

xdazz
xdazz

Reputation: 160973

You need to add where condition to your query.

$id = (int) $_GET['id'];
$result = mysql_query("SELECT * FROM contacts WHERE id = $id");

Upvotes: 0

Related Questions