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