-1

This is my database of mysql on the web

This is my database on the web

This is the PHP file to access the database to get a JSON file, first I select all the "category" cell in the table, then loop through the category, and then loop through the other columns of a category, and assign to an array of $data2, after that assign $data2 and category to br an array of $data, which is the json I want to display. 

<?php 
header("Access-Control-Allow-Origin: *");
$user = "u348833238_rest"; /* User */
$password = "a!23286029"; /* Password */
$dbname = "u348833238_rest"; /* Database name */
$host = "localhost";

$con = mysqli_connect($host, $user, $password, $dbname);
// Check connection
if (!$con) {
 die("Connection failed: " . mysqli_connect_error());
} 

// $sel = mysqli_query($con,"select * from restaurant");

// $data = array();

// while ($row = mysqli_fetch_array($sel)) {
//  $data[] = array("dishes" => ["name"=>$row['food'], "price"=>$row['price']] , "category"=>$row['category']);
// }
// echo json_encode($data);


$sel = mysqli_query($con,"select distinct category from restaurant");

$data = array();

while ($row = mysqli_fetch_array($sel)) {

    $c = $row['category'];

    $sel2 = mysqli_query($con,"select * from restaurant where category = $c ");

    $data2 = array();

    while ($row2 = mysqli_fetch_array($sel2)){
        $data2[] = array("name"=>$row2['food'], "price"=>$row2['price']);
    }

    // echo $data2;

    $data[] = array("category"=>$row['category'], "dishes"=>$data2);
}

// echo $data;
echo json_encode($data);

?>

How the JSON is not displayed properly as the array of property "dishes" is empty as below:

the array is empty

Cheng Chi Man
  • 17
  • 3
  • I advice you to read about preventing [SQL injections](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – Raymond Nijland Dec 05 '18 at 12:11
  • Don't do iterated queries. Use a JOIN. – mickmackusa Dec 05 '18 at 12:11
  • @RaymondNijland Where exactly do you think the vulnerability is in posted query? – mickmackusa Dec 05 '18 at 12:13
  • I don't see it. Be more specific. What makes you think that `$c` is a threat? Are you concerning yourself with potential 2nd-stage injection where user-supplied data is previously inserted into the `restaurant` table? This extends too far into tinfoil hat territory to be making an assumption. We have no reason to believe that the table is corrupted based on the posted details. – mickmackusa Dec 05 '18 at 12:14
  • 1
    using a PHP variable directly in the query @mickmackusa when using a database always use prepared statements **no exceptions** even if you think the input/data is "safe".. Indeed stored SQL injections can be possible if the user has access to the `restaurant` table. – Raymond Nijland Dec 05 '18 at 12:15
  • 1
    Possible duplicate of [When to use single quotes, double quotes, and back ticks in MySQL](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-back-ticks-in-mysql) ...and many, many others. – mickmackusa Dec 05 '18 at 12:19
  • 1
    Also closable with: [MySQL query in a loop vs using a SQL join](https://stackoverflow.com/q/18404913/2943403) – mickmackusa Dec 05 '18 at 12:21

1 Answers1

0

I’d recommend turning errors on so you can see where the problem is.

At a guess it’s here:

select * from restaurant where category = $c

Should be:

select * from restaurant where category = '$c'
Zoe Edwards
  • 12,999
  • 3
  • 24
  • 43
  • i wonder why you didn't even mention the SQL injection in your answer which is possible.. – Raymond Nijland Dec 05 '18 at 12:10
  • 1
    @RaymondNijland There are considerable problems yes, but I’m guessing this is not a serious project. They’ve also posted what looks like actual MySQL data in their example too, so there’s only so much one can do. – Zoe Edwards Dec 05 '18 at 12:14
  • "so there’s only so much one can do. " True well the problem of internet is that bad things/tutorials/answers keep around and can be found for decades. This is a pretty populair website for (starting) programmers at least warn the (starting) programmers for dangerous unsafe code more people are doing this.. – Raymond Nijland Dec 05 '18 at 12:22
  • 1
    @ThomasEdwards This low-hanging fruit question is a duplicate. – mickmackusa Dec 05 '18 at 12:23