$sth = mysqli_query($conn, "SELECT ...");
$rows = array();
while($r = mysqli_fetch_assoc($sth)) {
    $rows[] = $r;
}
print json_encode($rows);

The function json_encode needs PHP >= 5.2 and the php-json package - as mentioned here

NOTE: mysql is deprecated as of PHP 5.5.0, use mysqli extension instead http://php.net/manual/en/migration55.deprecated.php.

Try this, this will create your object properly

 $result = mysql_query("SELECT ...");
 $rows = array();
   while($r = mysql_fetch_assoc($result)) {
     $rows['object_name'][] = $r;
   }

 print json_encode($rows);

http://www.php.net/mysql_query says "mysql_query() returns a resource".

http://www.php.net/json_encode says it can encode any value "except a resource".

You need to iterate through and collect the database results in an array, then json_encode the array.

When using PDO

Use fetchAll() to fetch all rows as an associative array.

$stmt = $pdo->query('SELECT * FROM article');
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo json_encode($rows);

When your SQL has parameters:

$stmt = $pdo->prepare('SELECT * FROM article WHERE id=?');
$stmt->execute([1]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo json_encode($rows);

When you need to rekey the table you can use foreach loop and build the array manually.

$stmt = $pdo->prepare('SELECT * FROM article WHERE id=?');
$stmt->execute([1]);

$rows = [];
foreach ($stmt as $row) {
    $rows[] = [
        'newID' => $row['id'],
        'Description' => $row['text'],
    ];
}

echo json_encode($rows);

When using mysqli

Use fetch_all() to fetch all rows as an associative array.

$res = $mysqli->query('SELECT * FROM article');
$rows = $res->fetch_all(MYSQLI_ASSOC);
echo json_encode($rows);

When your SQL has parameters you need to perform prepare/bind/execute/get_result.

$id = 1;
$stmt = $mysqli->prepare('SELECT * FROM article WHERE id=?');
$stmt->bind_param('s', $id); // binding by reference. Only use variables, not literals
$stmt->execute();
$res = $stmt->get_result(); // returns mysqli_result same as mysqli::query()
$rows = $res->fetch_all(MYSQLI_ASSOC);
echo json_encode($rows);

When you need to rekey the table you can use foreach loop and build the array manually.

$stmt = $mysqli->prepare('SELECT * FROM article WHERE id=?');
$stmt->bind_param('s', $id);
$stmt->execute();
$res = $stmt->get_result();

$rows = [];
foreach ($res as $row) {
    $rows[] = [
        'newID' => $row['id'],
        'Description' => $row['text'],
    ];
}

echo json_encode($rows);

When using mysql_* API

Please, upgrade as soon as possible to a supported PHP version! Please take it seriously. If you need a solution using the old API, this is how it could be done:

$res = mysql_query("SELECT * FROM article");

$rows = [];
while ($row = mysql_fetch_assoc($res)) {
    $rows[] = $row;
}

echo json_encode($rows);

We shouldn't see any use of mysql_ functions in modern applications, so either use mysqli_ or pdo functions.

Explicitly calling header("Content-type:application/json"); before outputting your data payload is considered to be best practice by some devs. This is usually not a requirement, but clarifies the format of the payload to whatever might be receiving it.

Assuming this is the only data being printed, it is safe to print the json string using exit() which will terminate the execution of the script as well. This, again, is not essential because echo will work just as well, but some devs consider it a good practice to explicitly terminate the script.

MySQLi single-row result set from query result set object:

exit(json_encode($result->fetch_assoc()));  // 1-dimensional / flat

MySQLi multi-row result set from query result set object:

Prior to PHP 8.1.0, available only with mysqlnd.

exit(json_encode($result->fetch_all(MYSQLI_ASSOC)));  // 2-dimensional / array of rows

MySQLi single-row result set from prepared statement:

$result = $stmt->get_result();
exit(json_encode($result->fetch_assoc()));  // 1-dimensional / flat

MySQLi multi-row result set from prepared statement:

$result = $stmt->get_result();
exit(json_encode($result->fetch_all(MYSQLI_ASSOC)));  // 2-dimensional / array of rows

PDO single-row result set from query result set object:

exit(json_encode($result->fetch(PDO::FETCH_ASSOC)));  // 1-dimensional / flat

PDO multi-row result set from query result set object:

exit(json_encode($result->fetchAll(PDO::FETCH_ASSOC)));  // 2-dimensional / array of rows

PDO single-row result set from prepared statement:

exit(json_encode($stmt->fetch(PDO::FETCH_ASSOC)));  // 1-dimensional / flat

PDO multi-row result set from prepared statement:

exit(json_encode($stmt->fetchAll(PDO::FETCH_ASSOC)));  // 2-dimensional / array of rows

Obey these rules to prevent the possibility of generating invalid json.:

1. you should only call json_encode() after you are completely finished manipulating your result array and
2. you should always use json_encode() to encode the payload (avoid the urge to manually craft a json string using other string functions or concatenation).

If you need to iterate your result set data to run php functions or provide functionality that your database language doesn't offer, then you can immediately iterate the result set object with foreach() and access values using array syntax -- e.g.

$response = [];
foreach ($result as $row) {
    $row['col1'] = someFunction($row['id']);
    $response[] = $row;
}
exit(json_encode($response));

If you are calling json_encode() on your data payload, then it won't make any difference to whether the payload is an array of arrays or an array of objects. The json string that is created will have identical syntax.

You do not need to explicitly close the database connection after you are finished with the connection. When your script terminates, the connection will be closed for you automatically.

Considering there's not really any NESTED json objects in mysql in general etc., it's fairly easy to make your own encoding function

First, the function to retrieve the mysqli results in an array:

function noom($rz) {
    $ar = array();
    if(mysqli_num_rows($rz) > 0) {
        while($k = mysqli_fetch_assoc($rz)) {
            foreach($k as $ki=>$v) {
                $ar[$ki] = $v;
            }
        }
    }
    return $ar;
}

Now, function to encode array as json:

function json($ar) {
    $str = "";
    $str .= "{";
    $id = 0;
    foreach($ar as $a=>$b) {
        $id++;
        $str .= "\"".$a."\":";
        if(!is_numeric($b)) {
            $str .= "\"".$b."\"";
        } else {
            $str .= $b;
        }
        
        if($id < count($ar)) {
            $str .= ",";
        }
    }
    $str .= "}";
    return $str;
}

Then to use it:

<?php
$o = new mysqli(
    "localhost",
    "root",""
);
if($o->connect_error) {
    echo "DUDE what are you/!";
} else {
    $rz = mysqli_query($o,
        "SELECT * FROM mydatabase.mytable"
    );
    $ar = noom($rz);
    echo json($ar);
}
?>