sql data to json array using PHP gives HTTP ERROR 500

Question

I am trying to get data from two database tables using the token I get from the first table I am trying to get the team name in the second table. This then is added to an array to finally print a json array. Have a look at my code below:

$dbh = new PDO("mysql:host=localhost;dbname=dbnameabc", $username, $password);

$sql = "SELECT * FROM chatbox order by id desc";

$result = $dbh->query($sql)->fetchAll(PDO::FETCH_ASSOC);
$return = [];

foreach ($result as $row) {

    $postid  = $row['id'];
    $token   = $row['token'];
    $gpslat  = $row['gpslat'];
    $gps     = $row['gps'];
    $postmsg = $row['msg'];

    //echo $postid + " is id " + $token + " is token " + $postmsg + " is postmsg " + $gps + " is gps and " + $gpslat;

    $team;

    $sqlx    = "SELECT * FROM logintable WHERE token='$token'";
    $resultx = $dbh->query($sqlx)->fetchAll(PDO::FETCH_ASSOC);


    foreach ($resultx as $rowx) {   
        $team = $rowx['name']; 

    } 

    $return[] = [   
        'postmsg' => $postmsg, 
        'token'   => $token, 
        'gps'     => $gps, 
        'gpslat'  => $gpslat 
    ];

}
$dbh = null;

header('Content-type: application/json');
echo json_encode($return);

Doing the above however throws me an error HTTP ERROR 500. The next thing I should mention is that echo statement I commented above gives me a random number like 10.1421321. So what is the issue? A similar code works fine on another table I had tested but don't know why this doesn't work.

Update : There is something wrong with the connection in the second query ($resultx = $dbh->query($sqlx)->fetchAll(PDO::FETCH_ASSOC);) because the first one providing fields other than $team are working fine and I can print json if I don't use the second query plus the team name but how do I get the second to work?

An internal server error means an error log is generated. Have you checked the server's error log for more information? — dimlucas, Jul 17 '16 at 20:11
Show errors: http://stackoverflow.com/questions/1053424/how-do-i-get-php-errors-to-display — rjdown, Jul 17 '16 at 20:11
Additionnaly, it will be nice to provide us with what `var_dump($return)` after the loop gives. — Adib Aroui, Jul 17 '16 at 20:12
Yeah I checked the error log and it says : `Call to a member function fetchAll() on a non-object` in the line `$resultx = $dbh->query($sqlx)->fetchAll(PDO::FETCH_ASSOC);` @dimlucas @rjdown — Abhishek Singh, Jul 17 '16 at 20:16
`$dbh->query()` returns `null` that's what the non-object is. Is everything ok with the connection? Try enabling `ERRMODE_EXCEPTION` and catch any exceptions thrown — dimlucas, Jul 17 '16 at 20:18
Does `$token` happen to have a single quote in it? Also what is the output of `print_r($dbh->errorInfo());` right after the query? — Dave Chen, Jul 17 '16 at 20:25
I should tell you that first query works fine. The error is only in `$resultx = $dbh->query($sqlx)->fetchAll(PDO::FETCH_ASSOC);` without the second query the json prints fine. I am trying `print_r($dbh->errorInfo());` but get same error. — Abhishek Singh, Jul 17 '16 at 20:35
I tried solution given in http://stackoverflow.com/a/23132773/5054460 but I get same error there — Abhishek Singh, Jul 17 '16 at 20:36
@AbhishekSingh Does it work if you change `$token = $dbh->quote($row['token']);` **and also by changing the query to** `$sqlx = "SELECT * FROM logintable WHERE token=$token";` instead of `$token = $row['token'];`? — Alon Eitan, Jul 17 '16 at 20:51
@AbhishekSingh Are you sure you have a column named `'name'` in your table? Maybe the column name is actually `'name '` (With space)? Make sure that the index key is **exactly** the same as the column name in the table. You can try commenting out the `foreach ($resultx as $rowx) { ... }` to see if the query will execute without errors — Alon Eitan, Jul 17 '16 at 21:02
what about concatenation? `$sqlx = "SELECT * FROM logintable WHERE token=".$token;` — Adib Aroui, Jul 18 '16 at 00:08

Milan · Accepted Answer · 2016-07-18T02:28:58.593

There is nothing wrong with json_encode function. It works fine.

The issue is this line: $resultx = $dbh->query($sqlx)->fetchAll(PDO::FETCH_ASSOC);

You are calling fetch all on the result of a query, which fails and returns false. Try to run the SQL query in a console/GUI and see what the output is.

If you want to see the specific error returned by PDO use the following function: http://php.net/manual/en/pdo.errorinfo.php

Also change your code into 2 lines, to check if query is valid:

$stmt = $dbh->query($sqlx);
if($stmt!=false){
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
}

sql data to json array using PHP gives HTTP ERROR 500

1 Answers1