Having two queries in one 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

First of all you can't pass two queries in a single call to `$conn->query()`. The second parameter is `$resultmode` and is used to pass certain options for the query execution. But to really help you, we need to know: What would you expect the output to look like? — Tobias Xy, Feb 11 '18 at 20:54
I want to display some user info such as a profile picture and display all of the user's post. And I can't really use `JOIN` because that will display the profile picture multiple times. @TobiasXy — Hannah Parks, Feb 11 '18 at 21:00

Jesse · Answer 1 · 2018-02-11T22:39:23.817

3

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

edited Feb 11 '18 at 22:39

answered Feb 11 '18 at 21:09

Jesse

305
2
7

Thank you, but the thing about `JOIN` is that it will display all the user posts and include say the profile picture depending on how ever many posts. I'm trying to achieve something like this: https://stackoverflow.com/questions/17911533/two-queries-mysql-in-one-object-json @Jesse – Hannah Parks Feb 11 '18 at 21:16
Basically, where it does display all user posts(`user_images`), but it will display a profile picture once. (`users`) – Hannah Parks Feb 11 '18 at 21:26
Unless you've got a strange DB schema, I'm not following on how tables `users` and `user_images` contain user posts- I'm assuming this is for something like a messaging application, forum software, or comments section? To me, `users` would contain a singular entry for each user in your database and `user_images` would contain a singular profile picture (or multiple pictures with one defined as the "primary"). If your goal is to get a list of posts with attached user information, start with whatever your `posts` table is and `JOIN` user info onto that. – Jesse Feb 11 '18 at 21:26
I updated my code. Maybe I can try using `JOIN`. Is there a way to limit a row, because the row of `users` will echo everytime a user have a post. So say if a user have 10 posts, they would see their information(username,email,password, etc.) 10 times. @Jesse – Hannah Parks Feb 11 '18 at 21:43
If that's the case then it may be better to keep your two queries as-is and execute them separately. First, gather a list of `users`. Then, loop over the results of that query and begin building an array based off of that. I've updated my answer to reflect this. – Jesse Feb 11 '18 at 22:03
I tried out your code, and all the results came out null. This seems like it should work – Hannah Parks Feb 11 '18 at 22:35
My bad, take the `[]` off the end of `$user[]` and `$post[]` in the `while` lines. – Jesse Feb 11 '18 at 22:39
@Jessie Ok the data displays on the browser, but it doesn't display on the React Native app. Could it be a JSON problem? – Hannah Parks Feb 11 '18 at 22:52
I don't have any experience with React so I can't help you there. – Jesse Feb 12 '18 at 00:49

score 0 · Answer 2 · answered Feb 11 '18 at 22:02

Try this

<?php

//$conn = ... connect to database

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

$queryTableOne = mysqli_query($conn, $sql);
$queryTableTwo = mysqli_query($conn, $usql);

$json = mysqli_fetch_array($queryTableOne, MYSQLI_ASSOC);

$json2 = array();
while ($row = mysqli_fetch_assoc($queryTableTwo)) {
    $json2[] = array(
        'your_field1' => $row["your_field1"],
        'your_field2' => $row["your_field2"],
        'your_field3' => $row["your_field3"],
        //...etc
    );
}

$json['user_images'] = $json2;
echo json_encode($json);

?>

Reference

But using JOIN is a better way.

Having two queries in one JSON?

2 Answers2