How to encode mysqli results with JSON in PHP

Question

I am having trouble encoding a mysqli result using json_encode

<?php

$emp_id = $_GET["emp_id"];

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "overtimedtr";
$jsonData;

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$sql = "SELECT * FROM employees where id ='".$emp_id."'";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    // output data of each row
    if($row = mysqli_fetch_assoc($result)) {

        $empName = $row['name'];
        $empPosition = $row['position'];

    }

            $jsonData = array('empName' => "'".$empName."'",'empPosition' => "'".$empPosition."'");
} else {
    //$result["empName"] = '0 results.';
    //$result["empPosition"] = '0 results.';
}

$myjson = json_encode($jsonData,JSON_FORCE_OBJECT);
mysqli_close($conn);
echo $myjson;
?>

Error code:

Fatal Error: Uncaught Error: Cannot use object of type mysqli_result as array in line 24.
Stack trace: #0{main} thrown in line 24.

Code starting from line 24:

        $empName = $row['name'];
        $empPosition = $row['position'];

    }
            $jsonData = array('empName' => "'".$empName."'",'empPosition' => "'".$empPosition."'");
} else {
    //$result["empName"] = '0 results.';
    //$result["empPosition"] = '0 results.';
}

$myjson = json_encode($jsonData,JSON_FORCE_OBJECT);
mysqli_close($conn);
echo $myjson;
?>

I wonder if I am missing something obvious here. I am confused by the error because I am thinking that the code below already converts my mysqli_result object type to an associative array?

if($row = mysqli_fetch_assoc($result)) {

        $empName = $row['name'];
        $empPosition = $row['position'];

    }
$jsonData = array('empName' => "'".$empName."'",'empPosition' => "'".$empPosition."'");

I have read similar questions, and could not find any answer that can solve my problem, I am still confused. I am really new to web development, any insights would be so much appreciated. Thank you in advance!

That has nothing to do with JSON, it's the `else` part where you treat the mysqli result as array. — Dormilich, Jun 13 '18 at 08:16
http://bobby-tables.com Read about SQL injections and how to prevent them. Right now, your code is not safe at all and your whole database could be deleted by anyone without any need of deeper knowledge of your system. NEVER put in user inputs directly into queries, always use prepared statements, which are explained pretty well on bobby-tables. Take the two minutes to learn and adapt, its quite easy. — Twinfriends, Jun 13 '18 at 08:16
@Dormilich Which part? Do you mean the `$jsonData = array()` part? — GenesisTepait, Jun 13 '18 at 08:23
It turns out my code was working all along. I've had the error because of a really silly mistake, I got the wrong file opened in my code editor, so all the edits and corrections i have made were useless. Thank you! — GenesisTepait, Jun 13 '18 at 09:06

Abdullah Al Shakib · Answer 1 · 2018-06-13T08:27:04.797

0

You may change your code this way and can then test again

if (mysqli_num_rows($result) > 0) {
    $row = mysqli_fetch_assoc($result);
    $jsonData = ['empName' => $row['name'], 'empPosition' => $row['position'];

} else {
    $jsonData = ['empName' => '0 results.', 'empPosition' => '0 results.'];
}

$myjson = json_encode($jsonData);

edited Jun 13 '18 at 08:27

answered Jun 13 '18 at 08:20

Abdullah Al Shakib

1,954
2
13
15

I still get the same error, unfortunately. – GenesisTepait Jun 13 '18 at 08:44

How to encode mysqli results with JSON in PHP

1 Answers1