PHP & MySQL to JSON

General PHP Help
#1

Hello, I need help figuring out how to generate a loop using foreach with queries from the database to get an output result like below.

{ "emails": [ { "group": "Today April 23", "list": [{ "id": 1, "subject": "Pages - Multi-Purpose Admin Template Revolution Begins here!", "to": ["David Nester", "Jane Smith"], "body": "<p>First email body</p> ", "time": "5 Mins ago", "datetime": "Today at 1:33pm", "from": "David Nester", "dp": "assets/img/profiles/avatar.jpg", "dpRetina": "assets/img/profiles/avatar2x.jpg" }, { "id": 2, "subject": "Your site has some very imaginative animation /movement! ", "to": ["Anne Simons"], "body": "<p>Second email body</p> ", "time": "45 mins ago", "datetime": "Today at 1:33pm", "from": "Anne Simons", "dp": "assets/img/profiles/5.jpg", "dpRetina": "assets/img/profiles/5x.jpg" }] }, { "group": "Yesterday April 22", "list": [{ "id": 3, "subject": "Good design is obvious. Great design is transparent", "to": ["John Doe", "Anne Simons"], "body": "<p>Third email body</p> ", "time": "1:33pm", "datetime": "Today at 1:33pm", "from": "David Nester", "dp": "assets/img/profiles/b.jpg", "dpRetina": "assets/img/profiles/b2x.jpg" }] } } ]

Currently I am getting this output. Which ha repeated items in the list array inside the email array.

[code]{

“emails”: [
{
“group”: “Today, Jun 05”,
“list”: [
{
“id”: “7”,
“subject”: “Subject Text”,
“to”: [
“admin”
],
“body”: "This is sample text!
",
“time”: “15:02:46”,
“datetime”: “2016-06-05”,
“from”: “admin”,
“dp”: “…/assets/img/profiles/3.jpg”,
“dpRetina”: “…/assets/img/profiles/3x.jpg”
},
{
“id”: “7”,
“subject”: “Subject Text”,
“to”: [
“edem”
],
“body”: "This is sample text!
",
“time”: “15:02:46”,
“datetime”: “2016-06-05”,
“from”: “edem”,
“dp”: “…/assets/img/profiles/3.jpg”,
“dpRetina”: “…/assets/img/profiles/3x.jpg”
},
{
“id”: “8”,
“subject”: “Subject Text 2”,
“to”: [
“admin”
],
“body”: “This is sample text!  2
”,
“time”: “15:02:55”,
“datetime”: “2016-06-05”,
“from”: “admin”,
“dp”: “…/assets/img/profiles/3.jpg”,
“dpRetina”: “…/assets/img/profiles/3x.jpg”
},
{
“id”: “8”,
“subject”: “Subject Text 2”,
“to”: [
“edem”
],
“body”: “This is sample text!  2
”,
“time”: “15:02:55”,
“datetime”: “2016-06-05”,
“from”: “edem”,
“dp”: “…/assets/img/profiles/3.jpg”,
“dpRetina”: “…/assets/img/profiles/3x.jpg”
},
{
“id”: “9”,
“subject”: “Subject Text 3”,
“to”: [
“admin”
],
“body”: “This is sample text!  3
”,
“time”: “15:03:01”,
“datetime”: “2016-06-05”,
“from”: “admin”,
“dp”: “…/assets/img/profiles/3.jpg”,
“dpRetina”: “…/assets/img/profiles/3x.jpg”
},
{
“id”: “9”,
“subject”: “Subject Text 3”,
“to”: [
“edem”
],
“body”: “This is sample text!  3
”,
“time”: “15:03:01”,
“datetime”: “2016-06-05”,
“from”: “edem”,
“dp”: “…/assets/img/profiles/3.jpg”,
“dpRetina”: “…/assets/img/profiles/3x.jpg”
}
]
}
]

}
[/code]

This is my php code generating the wrong putput above.

[code]<?php
header(‘Content-type: application/json’);

    // Start session and get session values.
    session_start();

    // Connect to database.
    require('../../config/database.php');

   $sql   = "SELECT DISTINCT(`datetime`) FROM `memo`";
   $dates = mysqli_query($conn, $sql);

   foreach ($dates as $key => $date) {
             $sql     = "SELECT `memo`.*, `users`.`username` FROM `memo` JOIN `users` ON `memo`.`to` = '" . $_SESSION['user_id'] . "' WHERE `datetime` = '" . $date['datetime'] . "'";
             $results = mysqli_query($conn, $sql);

             // Clean array for each new loop.
             unset($lists);

             // Loop and create lists.
             foreach ($results as $key => $result) {
                       $lists[] = array(
                                 'id' => $result['id'],
                                 'subject' => $result['subject'],
                                 'to' => array(
                                           $result['username']
                                 ),
                                 'body' => $result['body'],
                                 'time' => $result['time'],
                                 'datetime' => $result['datetime'],
                                 'from' => $result['username'],
                                 'dp' => "../assets/img/profiles/3.jpg",
                                 'dpRetina' => "../assets/img/profiles/3x.jpg"
                       );
             }

             // Set group variable to date.                  
             $group = date("M d", strtotime($date['datetime']));

             // Current date.
             $current_date = date("M d");

             // User interface recent date into simple text like "Today, April 24".  
             if ($group == $current_date) {
                       $group = "Today, " . date("M d");
             }

             // Add group and list to email array.                 
             $json[] = array(
                       'group' => $group,
                       'list' => $lists
             );
   }

   $memos = array(
             'emails' => $json
   );
   echo json_encode($memos);

   // Close connection.
   mysqli_close($conn);
  ?>

[/code]

Thanks for the opportunity.
I really need help with this one.

#2

I would test removing newline characters. JSON is perfectly valid when on a single line.

#3

The JSON output I am getting is valid. But I’m having trouble with the arrays.
I keep getting repetitive items in the list array.

Thanks for replying.

#4

Gotcha. A bit busy now, but I’ll take a look in a bit.

#5

Gotcha. I’m a bit busy now but I will take a look shortly.

#6

Not sure, but, shouldn’t this part of the second query:
WHERE datetime = '" . $date[‘datetime’]

be something like:
WHERE datetime = '" . $dates[‘datetime’]

Aren’t you looking for the datetime from the first query that you named “$dates” ???

#7

You want to identify how you want the results of the query grabbed, fetch_assoc(). Defaults are to grab both, which causes duplicate rows displayed, even when the result set is not actually a duplicate.

You also need to use prepared statements. Doesn’t matter if you are using the session value or another query.

#8

I used a foreach loop to select each date in the dates. Hence $date[‘datetime’] not $dates[‘datetime’]

#9

Aaron, yes, after I reread your post, I saw I was mistaken. And, also, you should look at your join. I do agree with
Astonecipher that is most likely your problem. I am not very good with joins. Just never needed them so far. But, it
appears that you are selecting two groups of data. I think it is because you are joining two tables, not just data.

Change the JOIN to LEFT OUTER JOIN and see if it fixes the issue. If not let us know…

#10
I am not very good with joins. Just never needed them so far.

For real? If you are using properly normalized databases how are you getting your data together without joins?

#11

Well, Kev, I just do not do much with databases. Although, I am starting a new project which will need a complicated one.
Perhaps I will be asking you a lot of questions on this at some point… LOL By the way, was my answer incorrect? If so,
I would like to know and Aaron can use your help if I am wrong!

#12

Ya know, it wasn’t so long ago that joins were new and confusing to me, along with table shortcuts. Like most everything else I have learned, it was because I needed to learn it for a very complex project which taught me many new things. One being the Party Model DB Schema which really bumped my skill level and understanding of scalable enterprise level DB applications.

I will be happy to help you with your upcoming project. Database applications is pretty much all I do.

As far as OP, I haven’t even read his code so I don’t know about answers that have been given.

#13

Good Job! [member=43746]ErnieAlex[/member] that was the problem. It was duplicating the rows because of how I joined it. I cant believe I missed that after all the advanced queries Ive used in this project. Thats what happens when you overclock your brain! Thanks again… :stuck_out_tongue: 8)

#14

You are welcome! Glad it was a simple fix. See you in the “Bitstream” ! ! !

Sponsor our Newsletter | Privacy Policy | Terms of Service