return Json array from PHP mySQL - Error

Question

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..

See the single quotes ? ProductType= '$selected_group'

Shankar Narayana Damodaran
– Shankar Narayana Damodaran

2014-04-08 06:27:19 +00:00
Commented Apr 8, 2014 at 6:27 — Shankar Narayana Damodaran
– Shankar Narayana Damodaran, Commented Apr 8, 2014 at 6:27

Zeeshan · Accepted Answer · 2014-04-08 06:33:54Z

3

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();
 ?>

answered Apr 8, 2014 at 6:33

Zeeshan

1,68314 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

san88 Over a year ago

i have changed my code in to this $result = mysql_query("SELECT * FROM Product WHERE ProductType= '".mysql_real_escape_string('$selected_group')."'")or die(mysql_error()); but now the JSON array is empty..can you help..you can see the results by above url..

Zeeshan Over a year ago

@san88 Please check record in database relevant to selected ProductType found or not?

san88 Over a year ago

yes..database contains 5 records for Laptops keyword and when i execute the sql command in phpMyAdmin, its working correctly..

Zeeshan Over a year ago

just echo ("SELECT * FROM Product WHERE ProductType= '".mysql_real_escape_string($selected_group)."'"); and see the query and then run this query in db?

san88 Over a year ago

i have add these lines echo "this is a test"; echo ("SELECT * FROM Product WHERE ProductType= '".mysql_real_escape_string($selected_group)."'"); but still not working... you can see the results by using this url iilsfa.br0s.info/SFA/get_all_products.php?%22Laptops%22 please help

|

Community · Accepted Answer · 2023-11-17 19:53:52Z

2

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

edited Nov 17, 2023 at 19:53

CommunityBot

11 silver badge

answered Apr 8, 2014 at 6:28

Ashwini Agarwal

4,8582 gold badges44 silver badges59 bronze badges

Comments

Community · Accepted Answer · 2023-11-14 22:27:54Z

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.

Collectives™ on Stack Overflow

return Json array from PHP mySQL - Error

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related