Sql query not producing output in php

Question

I'm creating an android project that receives data from PHP side. But the PHP code gives no result.

I wrote this php code and checked it goes to else loop and shoes no people found . But my SQL query gives output in XAMPP SQL manually.

if($_SERVER['REQUEST_METHOD']=='POST'){
    include_once "Dbconnect.php"; 
    $phone= $_POST['phn'];

    $sql="Select 
            peoples.name as pname,
            peoples.address as addr,
            peoples.phone as phone,
            peoples.nomineephone as nphone,
            peoples.idproofnumber as idproof,
            camp.campid as cid,
            camp.Managername as mgrname,
            camp.managerno as mgrphone,
            camp.name as cname,
            camp.Location as loc 
        from 
            peoples 
        inner join camp 
            on peoples.campid=camp.campid 
        where 
            peoples.phone='$phone'";

    $result = $conn->query($sql);

    if ($result->num_rows >0) {
        while($row[] = $result->fetch_assoc()) {
            $tem = $row;
            $json = json_encode($tem);
         }
    } else {
       echo 'No people data found';
    }

    echo $json;
}

i tried to echo the phone number in PHP side i got the phone number from android and the data is available in DB with that phone number. But this code not giving the data. I expect the output of the query as json in android

Have you tried to debug your code at all (maybe a `var_dump($result)`? Have you checked your error logs? You also need to escape your inputs better. — ficuscr, Apr 26 '19 at 14:51
**Warning:** You are _wide open_ for SQL injections. Use parameterized prepared statements instead of injecting completely unescaped user data directly into the query like that. — M. Eriksson, Apr 26 '19 at 14:54
1.First check its coming inside `if($_SERVER['REQUEST_METHOD']=='POST'){` condition? 2.Make sure `phone` has the value 3.echo `$sql` and try to execute it in phpmyadmin and make sure the `phone` number existing in DB — Rakesh Jakhar, Apr 26 '19 at 14:55
On @MagnusEriksson's note, search for [php pdo](https://www.ecosia.org/search?q=php+pdo&addon=opensearch). I find it far better that `mysqli`. I've liked [this tutorial](https://phpdelusions.net/pdo) when checking it out in the past. — Reed, Apr 26 '19 at 14:57
change $json = json_encode($tem); to $json = json_encode($row);break; and see — Rakesh Jakhar, Apr 26 '19 at 15:00
Have you tried running a simple query with PHP? Like `SELECT * FROM peoples`? Just to make sure your db connection is working. — Reed, Apr 26 '19 at 15:10
Or `SELECT * FROM peoples WHERE peoples.phone='+1-555-867-5309'`? With a hard coded number. Just as to verify stuff is working and then build upon it one step at a time? Oh. If the phone numbers are strings, then it would be `.phone LIKE 'phone-string'`. — Reed, Apr 26 '19 at 15:12
@Reed when i use hard coded no also it goes into else loop only — jasvik, Apr 26 '19 at 16:05
I @VidhyaSampath, I imagine there's something wrong with your connection. You've echo'd the query and run it through `phpAdmin`, I'm guessing, from the other comments. Maybe the the database password is wrong. Or maybe the dabase-user does not have the necessary permissions to select from those tables. Through your cpanel, probably, you might want to check the DB user and make sure the user has the correct priveleges. — Reed, Apr 26 '19 at 19:09
Additionally, try putting everything in one script, for the sake of simplifying troubleshooting, from opening the database to running the query etc. try `var_dump` on all your variables, making sure `$conn` is the correct class, etc. Other than the risk for sql injection, I don't see anything wrong with your query. — Reed, Apr 26 '19 at 19:12
And turn on errors. `error_reporting(E_ALL); ini_set('display_errors',1);` [src](https://stackoverflow.com/questions/5438060/showing-all-errors-and-warnings). You would do this first, before running any other code and see if any errors output. — Reed, Apr 26 '19 at 19:14

score 1 · Answer 1 · answered Apr 26 '19 at 15:08

The code at the end to fetch the data and output it is probably the cause. When you get the result from fetch_assoc(), the last iteration will return false, but as you assign it to $row[] it will add false to the data.

In this code, the loop just builds up a list of rows and then json_encodes() them on the output. It also creates the empty array first to ensure it's initialised.

It also returns the data of nothing found in the JSON rather than just echoing it out.

$rows = [];
if ($result->num_rows >0) {
    while($row = $result->fetch_assoc()) {
        $rows[] = $row;
     }
} else {
   $rows = [ 'error' => 'No data found' ];
}

echo json_encode($rows);

If you are just expecting 1 row to be returned, you could use

if ($result->num_rows >0) {
    $row = $result->fetch_assoc();
} else {
   $row = [ 'error' => 'No data found' ];
}

echo json_encode($row);

Can you try changing the `inner join` to `left join` in case there isn't a match in that record. — Nigel Ren, Apr 26 '19 at 17:28

score 0 · Answer 2 · answered Apr 26 '19 at 15:11

Maybe this will help. Basically just rewrote the last lines of what you shared. Think it is self explanatory. My gripe is you should be able to better troubleshoot the issue. Work on your debugging. Ask something specific or maybe answer your own question.

$result = $conn->query($sql);
if ($result === false) {
  die('query error');
}

$row = $result->fetch_array(MYSQLI_ASSOC);
if (empty($row)) {
  die('no results');
}

//your code suggests you expect one result. So unless phone number is a unique index in database you should handle that better here.

$jsonOut = json_encode($row);

header('Content-Type: application/json');
echo $jsonOut;

i'm sure the data is there in the db because i login with the same phone number — jasvik, Apr 26 '19 at 16:00

Sql query not producing output in php

2 Answers2