CODEWITHSUNDEEP
phpmysqlarraysjson
Abhishek Singh
Abhishek Singh

Reputation: 425

sql data to json array using PHP gives HTTP ERROR 500

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?

Upvotes: 1

Views: 920

Answers (1)

Milan
Milan

Reputation: 759

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);
}

Upvotes: 1

Related Questions