在一个JSON中有两个查询？

Question

I am trying to Select everything from two tables and display them through JSON. Here is my shot at trying that:

 <?php
// Create connection
$conn = new mysqli("localhost", "root", "****", "user");

if ($conn->connect_error) {

 die("Connection failed: " . $conn->connect_error);
} 
 // Getting the received JSON into $json variable.
 $json = file_get_contents('php://input');

 // decoding the received JSON and store into $obj variable.
 $obj = json_decode($json,true);

// Populate Username from JSON $obj array and store into $usnername.
$username = $_GET['username'];

$sql = "SELECT * FROM users  WHERE username = '$username'";
$usql = "SELECT * FROM user_images  WHERE username = '$username'";

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

if ($result->num_rows >0) {


 while($row[] = $result->fetch_assoc()) {

 $tem = $row;

 $json = json_encode($tem);


 }

} else {
 echo  "No Results Found.";
}
 echo $json;
$conn->close();
?>

I am not too sure if this is the correct way to achieve my task, but I looked at other questions and none are in the same format as mine. Also, I know this is vulnerable to SQL injections, this is just for example purposes.

users Table:

-id -username -profilepic

1 Bill image.png

2 Sally cats.png

user_images table:

-id -username -posts

1 Bill Food

2 Bill Sports

3 Sally Coffee

Answer 1

I know you mentioned it in your question, but it bears repeating- this is vulnerable to SQL injections because you are referencing user input from the $_GET array directly in your SQL query without first sanitizing it or, better, using prepared statements.

$result = $conn->query($sql, $usql);
The mysqli::query function takes a single query, and an optional MYSQLI_STORE_RESULT parameter which you are incorrectly specifying by passing the second SQL statement, $usql- so this will not work.

Instead, you should perform a JOIN on the two tables in the same query. So, combining your queries would look something like this:

$sql = "SELECT * FROM users 
LEFT JOIN user_images ON user_images.username = user.username 
WHERE username = '$username'";

Or, as a prepared statement:

$prepared_statement = $conn->prepare("SELECT * FROM users LEFT JOIN user_images ON user_images.username = user.username WHERE username = ?");
$prepared_statement->bind_param("s", $username);
$result = $prepared_statement->execute();

This will combine the information contained in the two tables so that you can pull the relevant information out in a single loop. Additionally you might consider gathering only the relevant information for your response, so you are not sending back an entire user object via JSON.

Edit After considering new information provided, it may be best to do the following:

$sql = "SELECT * FROM users  WHERE username = '$username'";
$usql = "SELECT * FROM user_images  WHERE username = '$username'";

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

if ($users_result->num_rows > 0) {
    while($user = $users_result->fetch_assoc()) {
        $posts_array = array();
        $posts_result = $conn->query($usql);
        if ($posts_result->num_rows > 0) {
            while($post = $posts_result->fetch_assoc()) {
                $posts_array[] = array(
                    "id" => $post['id'],
                    "post" => $post['post']
                );
            }
        }
        $response = array(
            "id" => $user['id'],
            "username" => $user['username'],
            "profilepic" => $user['profilepic'],
            "posts" => $posts_array
        );
        $json = json_encode($response);
        echo $json;
    }
} else {
    echo  "No Results Found.";
}
$conn->close();