1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Generate php-array with mySQL output for JSON!

Discussion in 'PHP' started by iago111, May 11, 2020.

  1. #1
    Hello,
    I have a question about generating a php array from db-records for a JSON file.

    The desired JSON output should look like that (however it could be ANY similar output, I would like to understand the translation process!)

    
    [
     {
     "label": "CSP",
     "items": [
     {
     "label": "P dispute commission English",
     "text": "Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. "
     },
     {
     "label": "dispute commission / German",
     "text": "Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. "
     }
     ]
     },
     {
     "label": "MISCONDUCT",
     "items": [
     {
     "label": "ADMIN Complaint - Guest misconduct template sent to the G (P claims €50)New File",
     "text": "Lorem ipsum dolor sit amet."
     }
     ]
     },
     {
     "label": "COVID-19",
     "items": [
     {
     "label": "ADMIN cxl foc due to FC is possible",
     "text": "Lorem ipsum dolor sit amet."
     }
     ]
     },
    .....]
     
    Code (markup):
    I have 2 tables in the DB: (sections : cpicks) (1 : n)

    Now, I have the following db-query (I will replace mysqli later on by PDO, this is not my question here)

    
    $query = "SELECT
    `s`.`label` AS `s_label`, 
    `c`.`label` AS `c_label`,
    `c`.`text` AS `c_text`,
    `c`.`section_id` AS `section_id`,
    `c`.`cp_id` AS `CP_Id`
    
    FROM `sections` `s`
    LEFT JOIN `cpicks` `c` ON (`s`.`id` = `c`.`section_id`)
    ORDER BY `s`.`id` DESC";
    
    $result = mysqli_query($connection,$query) or die ('Unable to execute query. '.mysqli_error($connection));
    
    $records = mysqli_fetch_assoc($result);
    
    Code (markup):
    Now the exciting part starts, generating the array. I've come so far right now:

    
    
    $jsonArray = array();
    
    foreach ($records as $CP_output){
    
    $jsonArray[]['label'] = $CP_output;
    }
    
    Code (markup):
    json_encode returns: [{"label":"CSP"},{"label":"dispute commission \/ German"}...]
    SEMrush
    How can I break down $CP_output for the desired JSON structure?

    (I do not want find a better solution for json encode and this has nothing to do with AJAX!)
    (Please do not respond with: "Why are you not using PDO?")

    Thanks!
     
    Solved! View solution.
    iago111, May 11, 2020 IP
    SEMrush
  2. iago111

    iago111 Peon

    Messages:
    26
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    3
    #2
    I've improved $jsonArray, however I cannot connect the result of the 'cpicks' table (right side of the JOIN), correctly.
    Now it's items:{...} ... items{...}
    but it should be items: [{...},{...}] ...
    (in JSON syntax)

    
    while($records = mysqli_fetch_assoc($result)) {
    
    $jsonArray[] = ['label' => $records['s_label'],'items' => ['label' => $records['c_label'],'text' => $records['c_text']]];
    }
    
    
    Code (markup):
     
    iago111, May 12, 2020 IP
  3. deathshadow

    deathshadow Acclaimed Member

    Messages:
    9,302
    Likes Received:
    1,802
    Best Answers:
    244
    Trophy Points:
    515
    #3
    Honestly your join is likely resulting in a larger data set than you need, wasting memory despite any alleged savings in processing time. This actually looks like a job for POEM (prepare once, execute mostly) with multiple queries. Whilst the "conventional wisdom" is to use JOIN to reduce the number of queries, in practice that's often broken, bloated, and slower than brute forcing a bunch of tiny ones.

    
    $result = [];
    
    // use prepare so we can bind_result, ends up easier to work with
    $sectionStmt = $connection->prepare('
    	SELECT id, label
    	FROM sections
    ');
    $sectionStmt->execute();
    $sectionStmt->bind_result($sectionId, $sectionLabel);
    
    $itemStmt = $connection->prepare('
    	SELECT label, text
    	FROM cpicks
    	WHERE section_id = ?
    ');
    $itemStmt->bind_param('i', $sectionId);
    
    while ($sectionStmt->fetch()) {
    
    	$section = [
    		'label' => $sectionLabel,
    		'items' => []
    	];
    	
    	$itemStmt->execute();
    	$itemStmt->bind_result($itemLabel, $itemText);
    	
    	while ($itemStmt->fetch()) {
    		$section['items'][] = [
    			'label' => $itemLabel,
    			'text' => $itemText
    		];
    	} // while $itemStmt->fetch
    	
    	$result[] = $section;
    	
    } // while $sectionStmt->fetch
    	
    
    Code (markup):
    Normally I rail against the use of mysqli, but in this case bind_result works greatly in our favor over fetching an associative array. Yeah, it's gonna run a ton more separate queries, but the logic is easier to follow and it may run faster because less information is being shoved around... especially as we can leverage prepare/execute/bind_param/bind_result.

    Sucks though you can't bind_result until after an execute.

    Side note, untested code, may have typo's... but should be sufficient to explain what I mean.
     
    deathshadow, May 12, 2020 IP
    JEET likes this.
  4. JEET

    JEET Notable Member

    Messages:
    3,298
    Likes Received:
    351
    Best Answers:
    15
    Trophy Points:
    235
    #4
    Try this:
    $records = mysqli_fetch_assoc($result);
    $jsonArray = array();
    $labelsMain=array();

    foreach ($records as $cp){

    $label= $cp['s_label'];
    $items=array(
    'text'=>$cp['text'],
    'label'=>$cp['c_label']
    );

    $labelsMain[$label][]=$items;
    }//while ends here

    foreach( $labelsMain as $label => $item ){
    $jsonArray[]=array(
    'label'=>$label,
    'items'=>$item
    );
    }//foreach ends

    $json= json_encode( $jsonArray );
     
    JEET, May 12, 2020 IP
  5. iago111

    iago111 Peon

    Messages:
    26
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    3
    #5
    Ok, thanks guys for the answers!!

    I'm not so familiar with OOP, PDO, prepared statements in php, this is why I tried to avoid these topics but ok, let's go on:

    So, concerning this line (deathshadow):

    $itemStmt->bind_param('i', $sectionId);
    PHP:
    I receive the following error message:
    Call to a member function bind_param() on bool

    So $sectionId must be boolean, why is this??

    Concerning JEET 's version: Where does while start, when it ends where it ends?

    The output without any while-loop looks like this:

    [
      {
        "label":"C",
        "items":[
          {
            "text":"C",
            "label":"C"
          }
        ]
      },
      {
        "label":"d",
        "items":[
          {
            "text":"d",
            "label":"d"
          }
        ]
      },
      {
        "label":"L",
        "items":[
          {
            "text":"L",
            "label":"L"
          }
        ]
      },
      {
    Code (markup):

    "C" ist the first letter of section.lable, "d" is the first letter of cpicks.label ...
    Why is the result not at least the whole text??

    By the way, how does the program know, that every entry of the left side of the JOIN should always occur once?
     
    iago111, May 13, 2020 IP
  6. JEET

    JEET Notable Member

    Messages:
    3,298
    Likes Received:
    351
    Best Answers:
    15
    Trophy Points:
    235
    #6
    My mistake with the "while"
    Try this one:
    <?php
    $jsonArray = array();
    $labelsMain=array();
    while($row=$result->fetch_array(MYSQLI_ASSOC)){
    $label= $row['s_label'];
    $items=array(
    'text'=>$row['text'],
    'label'=>$row['c_label']
    );

    $labelsMain[$label][]=$items;
    }//while ends here

    foreach( $labelsMain as $label => $item ){
    $jsonArray[]=array(
    'label'=>$label,
    'items'=>$item
    );
    }//foreach ends

    $json= json_encode( $jsonArray );
    ?>

    What you need is an array like this:

    $jsonArray=array(
    '0'=>array(
    'label'=>'label',
    'items'=>array(
    '0'=>array( 'text'=>'something', 'label'=>'label' ),
    '1'=>array( 'text'=>'something', 'label'=>'something' )
    ),
    '1'=>same thing repeats with different label etc
    );
     
    JEET, May 13, 2020 IP
  7. JEET

    JEET Notable Member

    Messages:
    3,298
    Likes Received:
    351
    Best Answers:
    15
    Trophy Points:
    235
    #7
    About program knowing that left side columns must occur only once,
    I think your query is returning the output like this:
    "s_label" is your left side column, right?

    array(
    '0'=>array(
    s_label => label1,
    text => something1
    c_label => something1
    ),
    '1'=>array(
    s_label => label1,
    text => something2
    c_label => something2
    ),
    '2'=>array(
    s_label => label_new
    text => something_new
    c_label => something_new
    ),
    and so on...
    );


    This one needs to be formatted to look like the other one I posted above.
    So we separated all entries for "label1" and stored them in one array.
    we did the same for other entries "label2, label_new, etc"
    Then we rebuild the array to the new form.
     
    JEET, May 13, 2020 IP
  8. #8
    Actually no, that means SOMEHOW -- typically an invalid query -- $itemStmt is boolean, likely false. A prepare returns false if the query is invalid or incorrect.

    Might need to add back-ticks around `text` in the query. I dislike using reserved words as field names, so usually my queries don't have the issues that require backticks in the first place.

    Try swapping to this:

    
    $itemStmt = $connection->prepare('
    	SELECT `label`, `text`
    	FROM cpicks
    	WHERE section_id = ?
    ');
    
    if ($itemStmt === false) die($connection->error);
    Code (markup):
    To see if
    1) "text" is confusing it since that's a reserved word you shouldn't be using for field names.

    2) if that's not it, have it spit out what the actual error is.

    <yoda>and that is why you fail.</yoda> :D

    Really the garbage procedural wrappers of mysqli should NEVER have existed in the first place; they were meant as a crutch for people who can't grasp objects, but in practice they're more of a hobble skirt.

    As to prepared statements, they are one of the entire reasons that the old mysql_ function were killed off, and if you're not using them it's likely your codebase is an insecure mess. Again part of why honestly I think mysqli should go the way of the dodo in favor of PDO, whilst moving a few things (like binding of results) over as an option.

    If you're able to put the big boy pants on to deal with joins, you should be able to handle the prepare/execute model... and point in fact if you aren't using prepare/execute for any queries that you're putting variables into, you've utterly failed to understand what was so wrong with mysql_ and why it no longer exists in PHP.
     
    deathshadow, May 13, 2020 IP
    JEET likes this.
  9. iago111

    iago111 Peon

    Messages:
    26
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    3
    #9
    ...and with that we avoide these doublets, brilliant thanks JEET!!!

    I think this line:
    if ($itemStmt === false) die($connection->error);
    
    Code (markup):
    returns this error:
    Commands out of sync; you can't run this command now
     
    iago111, May 14, 2020 IP
    JEET likes this.
  10. deathshadow

    deathshadow Acclaimed Member

    Messages:
    9,302
    Likes Received:
    1,802
    Best Answers:
    244
    Trophy Points:
    515
    #10
    Oops, egg on face, that's a "my bad". I don't really use mysqli so some of it's derpier stuff -- that's part of why I don't use it -- I often forget. By default mysqli doesn't buffer results, meaning you can't run two queries at the same time; which is really stupid, but whaddayagonnadoabbadit?

    Add:

    $sectionStmt->store_result();

    After $sectionStmt-execute and before the result is bound, and it should work. You may want to explicitly $sectionStmt->free_result(); at the end of the code.
     
    deathshadow, May 14, 2020 IP
  11. iago111

    iago111 Peon

    Messages:
    26
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    3
    #11
    Ok, that works thanks!
    But now only half the job is done, right!! ;)

    So I have this db-connection now:

    $dsn = "mysql:host=localhost;dbname=cherrypics-3133335250;charset=utf8mb4";
    $options = [
      PDO::ATTR_EMULATE_PREPARES   => false, // turn off emulation mode for "real" prepared statements
      PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, //turn on errors in the form of exceptions
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, //make the default fetch be an associative array
    ];
    try {
      $pdo = new PDO($dsn, "root", "", $options);
    } catch (Exception $e) {
      error_log($e->getMessage());
      exit('Something weird happened');
    }
    PHP:
    Then I have the following code for the query. I think what is missing here is s.th. like:
    $sectionStmt->bind_result($sectionId, $sectionLabel); because for now the second query has no 'idea' what $id (sections.id) is:

    $result = [];
    
    $sectionStmt = $pdo->prepare('
        SELECT `id`, `label`
        FROM sections ORDER BY id DESC
    ');
    
    $sectionStmt->execute();
    
    $itemStmt = $pdo->prepare('
        SELECT `label`, `text`
        FROM cpicks
        WHERE section_id = ?
    ');
    
    
    //if ($itemStmt === false) die($connection->error);
    
    while ($sectionStmt->fetch()) {
    
        $section = [
            'label' => $sectionLabel,
            'items' => []
        ];
       
        $itemStmt->execute([$id]);
    
    while ($itemStmt->fetch()) {
            $section['items'][] = [
                'label' => $itemLabel,
                'text' => $itemText
            ];
        }  // while $itemStmt->fetch
    
    $result[] = $section;
    
    }  // while $sectionStmt->fetch
    PHP:
     
    iago111, May 15, 2020 IP
  12. deathshadow

    deathshadow Acclaimed Member

    Messages:
    9,302
    Likes Received:
    1,802
    Best Answers:
    244
    Trophy Points:
    515
    #12
    You've got a PDO connect with a mysqli codebase. PDO doens't have bind_result so you have to store it when you fetch like the old mysql_ commands. It's why at the start I said "I usually rail against the use of mysqli but in this case we can leverage bind_result"

    Wait, are you unaware that mysqli has a object model too or something?

    Also I'd suggest axing all the variables for nothing as a waste of RAM, processing time, and possible security exploits.

    try {
      $db = new PDO(
    		'mysql:host=localhost;dbname=cherrypics-3133335250;charset=utf8mb4',
    		'root', // username
    		'', // password
    		[
    			PDO::ATTR_EMULATE_PREPARES => false,
    			PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    		]
    	);
    } catch (PDOException $e) {
      error_log($e->getMessage());
      die('Something weird happened');
    }
    Code (markup):
    Remember, you should never store secure information in anything but local variables IF AT ALL.

    Then for the rest:

    $sectionStmt = $db->query('
    	SELECT id, `label`
    	FROM sections
    	ORDER BY id DESC
    ');
    
    if ($result = $sectionStmt->fetchAll()) {
    	$itemStmt = $db->prepare('
    		SELECT `label`, `text`
    		FROM cpicks
    		WHERE section_id = ?
    	');
    	foreach ($result as &$section) {
    		$itemStmt->execute([$section['id']]);
    		$sections['items'] = $itemStmt->fetchAll();
    	}
    }
    Code (markup):
    PDO is quite different in how it works from mysqli when it comes to prepare/execute -- especially at handling results. NORMALLY I'd also say don't use fetchAll, but in this case it makes perfect sense since you want the whole result set in memory at once to change into JSON. You'd normally not want that with data sets because of the memory it wastes. Being able to leverage pass-by-reference greatly simplifies everything. Part of why I favor PDO over mysqli.

    Though... hmm... if the only thing this was doing is outputting a JSON response, I'd be tempted to just echo the results instead of building the massive array. It would lower the memory footprint dramatically.
     
    deathshadow, May 15, 2020 IP
  13. iago111

    iago111 Peon

    Messages:
    26
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    3
    #13
    No, that's comprehensible!

    foreach ($result as &$section) {
            $itemStmt->execute([$section['id']]);
            $section['items'] = $itemStmt->fetchAll();
        }
    PHP:
    Sorry for being the pain in the a.. here, again. Why does the array have to return a reference here? That we can manipulate the values??
    Aren't we doing that the whole time?

    And then another thing. Now I have no restrictions concerning the output for the first query. $result should not
    give out $section['id'] but it does here.
     
    iago111, May 16, 2020 IP
  14. qwikad.com

    qwikad.com Illustrious Member Affiliate Manager

    Messages:
    6,383
    Likes Received:
    1,352
    Best Answers:
    24
    Trophy Points:
    400
    #14
    That &$section could be typo in ($result as &$section)
     
    qwikad.com, May 16, 2020 IP
  15. iago111

    iago111 Peon

    Messages:
    26
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    3
    #15
    Ok, I try to figure these things out. Thanks deathshadow!
     
    iago111, May 17, 2020 IP
  16. deathshadow

    deathshadow Acclaimed Member

    Messages:
    9,302
    Likes Received:
    1,802
    Best Answers:
    244
    Trophy Points:
    515
    #16
    Nope, it's not. We need to be able to write to it. If you remove the & the $section['items'] assignment will fail.

    @iago111, if you need to remove the id, remove it inside the loop. I'd still include it in the result set since it's needed to create the associations.

    Just add:

    unset($section['id']);
    Code (markup):
    after the $section['item'] assignment. Just be sure to do it last since we need it to exist for the query.
     
    deathshadow, Jun 26, 2020 IP
  17. iago111

    iago111 Peon

    Messages:
    26
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    3
    #17
    Yes, I was looking hours and hours at that code like a completely crazy person and I came to that solution, too!! Thanks!!
     
    iago111, Jul 7, 2020 at 1:47 AM IP