CODEWITHSUNDEEP
phpmysqljson
san88
san88

Reputation: 1384

return Json array from PHP mySQL - Error

Here is my php code and i want to get JSON array relevant to to my $_GET("ProductGroup") variable. but when i log in to the page by submitting parameters via URL http://iilsfa.br0s.info/SFA/get_all_products.php?%27Laptops%27 it displays this error.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

i could not find where the error is. please help

<?php
 include_once './db_connect.php';

function getProducts(){
$db = new DB_CONNECT();
// array for json response
$response = array();
$response["products"] = array();

// Mysql select query

// check for post data
 if (isset($_GET["ProductGroup"])) {
$selected_group = $_GET['ProductGroup'];
}


$result = mysql_query("SELECT * FROM Product WHERE ProductType= $selected_group")or die(mysql_error());


while($row = mysql_fetch_array($result)){
    // temporary array to create single category

    $tmp = array();
    $tmp["id"] = $row["ProductID"];
    $tmp["name"] = $row["ProductName"];
    $tmp["type"] = $row["ProductType"];
    $tmp["image"] = $row["ProductImage"];
    $tmp["des"] = $row["ProductDescription"];


    // push category to final json array
    array_push($response["products"], $tmp);
}

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

// echoing json result
echo json_encode($response);
 }

 getProducts();
 ?>

PS- I have changed my sql query to this

$result = mysql_query("SELECT * FROM Product WHERE ProductType=   '".mysql_real_escape_string('$selected_group')."'")or die(mysql_error());

Now its not displaying the previous error.but showing an empty json string. i have checked SQL query with phpmyAdmin.it working correctly and produce results..

Upvotes: 1

Views: 1957

Answers (3)

KarelG
KarelG

Reputation: 5244

First, your code is well documented, even for a starter, not too much, just only on places where it's required.

Now, your error lies in the query itself.

$result = mysql_query("SELECT * FROM Product WHERE ProductType= $selected_group")or die(mysql_error());

If a field in the query is of type string, you must add single quotes around it, or the interpreter will think that you're looking for a column name. Here below is a solution.

$result = mysql_query("SELECT * FROM Product WHERE ProductType= '$selected_group'")or die(mysql_error());

But i would add that your query is not secure. It allows SQL injection, which can harm your database data. for further information about SQLi, please refer to this page

You can go for mysqli() functions, which is an improved version of the mysql()-functions. Although i recommend to use PDO instead.

Upvotes: 2

Zeeshan
Zeeshan

Reputation: 1675

try this below. Dont use simple query. See SQL injection. SQL Injection use mysql_real_escape_string to prevent from sql injection 

<?php
 include_once './db_connect.php';

function getProducts(){
$db = new DB_CONNECT();
// array for json response
$response = array();
$response["products"] = array();

// Mysql select query

// check for post data
 if (isset($_GET["ProductGroup"])) {
$selected_group = $_GET['ProductGroup'];
}


$result = mysql_query("SELECT * FROM Product WHERE ProductType= '".mysql_real_escape_string($selected_group)."'")or die(mysql_error());


while($row = mysql_fetch_array($result)){
    // temporary array to create single category

    $tmp = array();
    $tmp["id"] = $row["ProductID"];
    $tmp["name"] = $row["ProductName"];
    $tmp["type"] = $row["ProductType"];
    $tmp["image"] = $row["ProductImage"];
    $tmp["des"] = $row["ProductDescription"];


    // push category to final json array
    array_push($response["products"], $tmp);
}

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

// echoing json result
echo json_encode($response);
 }

 getProducts();
 ?>

Upvotes: 3

Ashwini Agarwal
Ashwini Agarwal

Reputation: 4858

Quote the parameters...

$result = mysql_query("SELECT * FROM Product WHERE ProductType= '$selected_group'")or die(mysql_error());

You must not use mysql_*.
Also look at mysqli_real_escape_string

Upvotes: 2

Related Questions