CODEWITHSUNDEEP
phpmysqljsonmysqlimariadb
Noeriel Hilal
Noeriel Hilal

Reputation: 33

Create nested JSON from MariaDB datas using PHP

I need help.
I have 3 tables.
1. post_table with columns : id_post, post_text.
2. attach_picture with column : id_picture, picture_name, id_post.
3. comment_table with column : id_comment, comment_text, id_post.

<?php 
$con = mysqli_connect('localhost', 'root', '', 'postingan');

// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
  else {
    echo "success";
  }

$postinganlist_array  = array();
$postingan_array = array();
$image_array = array();
$comment_array = array();
$fetch_postingan = mysqli_query($mysqli, 
    "SELECT id_post, post_text FROM post_table")
 or die(mysqli_error($mysqli));
while ($row_postingan = mysqli_fetch_assoc($fetch_postingan)){
$postingan_array['id'] = $row_postingan['id_post'];
$postingan_array['text'] = $row_postingan['post_text'];
$postingan_array['image']=array();
$postingan_array['comment']=array();

$fetch_images = mysqli_query($mysqli, "SELECT id_picture, picture_name FROM attach_picture WHERE id_post = ".$row_postingan['id_post']."") or die(mysqli_error($mysqli));
while ($row_image = mysqli_fetch_assoc($fetch_images)) {
    $image_array['id']=$fetch_images['id_picture'];
    $image_array['pict']=$fetch_images['picture_name'];
    array_push($postingan_array['image'], $image_array);
}
$fetch_comments = mysqli_query($mysqli, "SELECT id_comment, comment_text FROM comment_table WHERE id_post = ".$row_postingan['id_post']."") or die(mysqli_error($mysqli));
while ($row_comment = mysqli_fetch_assoc($fetch_comments)) {
    $comment_array['id']=$fetch_comments['id_comment'];
    $comment_array['comment']=$fetch_comments['comment_text'];
    array_push($postingan_array['comment'], $comment_array);
}
array_push($postinganlist_array, $postingan_array);}
$jsonData = json_encode(($postinganlist_array));
echo $jsonData;
?>

The output is like

success
Warning: mysqli_query(): Couldn't fetch mysqli in C:\xampp\htdocs\post\get.php on line 36 

Warning: mysqli_error(): Couldn't fetch mysqli in C:\xampp\htdocs\post\get.php on line 37

line 36 : ("SELECT id_post, post_text FROM post_table")
line 37 : (or die(mysqli_error($mysqli));

My expected result is like

[{
    "id": "1",
    "text": "Good morning all",
    "image": 
    {
        "id": "1",
        "pict": "morning.jpg"
    }
    "comment": [
    {
        "id": "1",
        "comment": "Nice picture"
    }
    {
        "id": "2",
        "comment": "Thank you"
    }
    ]

    "id": "2",
    "text": "This is my new project",
    "image": [
        {
            "id": "2",
            "pict": "splassreen.jpg"
        }
        {
            "id": "3",
            "pict": "main.jpg"
        }
    ]
    "comment": [
        {
            "id": "3",
            "comment": "Great app"
        }
        {
            "id": "4",
            "comment": "Amazing"
        }
    ]
}
]

Help me to solve my problem

Upvotes: 0

Views: 80

Answers (1)

Sachin PATIL
Sachin PATIL

Reputation: 745

In your code you have used 

$con = mysqli_connect('localhost', 'root', '', 'postingan');

$fetch_postingan = mysqli_query($mysqli, "SELECT id_post, post_text FROM post_table") or die(mysqli_error($mysqli));

$fetch_images = mysqli_query($mysqli, "SELECT id_picture, picture_name FROM attach_picture WHERE id_post = ".$row_postingan['id_post']."") or die(mysqli_error($mysqli));

$fetch_comments = mysqli_query($mysqli, "SELECT id_comment, comment_text FROM comment_table WHERE id_post = ".$row_postingan['id_post']."") or die(mysqli_error($mysqli));

mysqli_query uses connection string. You have entered $mysqli which should have been $con. Your query must be as below:

$fetch_postingan = mysqli_query($con, "SELECT id_post, post_text FROM post_table") or die(mysqli_error($con));

$fetch_images = mysqli_query($con, "SELECT id_picture, picture_name FROM attach_picture WHERE id_post = ".$row_postingan['id_post']."") or die(mysqli_error($con));

$fetch_comments = mysqli_query($con, "SELECT id_comment, comment_text FROM comment_table WHERE id_post = ".$row_postingan['id_post']."") or die(mysqli_error($con));

Upvotes: 1

Related Questions