SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member
    Join Date
    May 2012
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PHP Multidimensional json array

    Hi all, hope some one can help me on this??

    I need to create the following json data, my PHP code produces this no problems, but I cant seem to loop $back with respect to $images and $text to show all my records in the below json string, I now its due to the way my SQL statement setup, so I managed to get my sql to produce one record"-126555895" that seem to work fine, but try to remove the WHERE and the entire json structor collapses, and messy data comes up.

    I think I need to join my sql by weID, I think thats where the problem is, any help in this is much appreciated.

    Code:
    {
                    "weID": "-126555895",
                    "size_h": "700",
                    "size_w": "494",
                    "images": [
                            {
                                    "imgID": "1535689403",
                                    "imgName": "1321865407"
                            }
                    ],
                    "text": [
                            {
                                    "txtID": "-1892792786",
                                    "txtText": "4.99"
                            },
                            {
                                    "txtID": "1583276033",
                                    "txtText": "$"
                            }
                    ]
            },
    
    // I cant loop the above structure  to show more records from $back in the above json layout
    PHP Code:
    //my PHP code:
     
    $back mysql_query("SELECT weID, size_h, size_w  FROM wb WHERE weID=-126555895");
    $images mysql_query("SELECT imgID, imgName  FROM wi WHERE weID=-126555895");
    $textmysql_query("SELECT  txtID, txtText FROM wt WHERE weID=-126555895");
     
    //all my above SQL statements have weID in common, how can I link them together, to show all my results rather then one "-126555895"??
     
     
    $model = array();
     
            while(
    $e mysql_fetch_assoc($back)){
            
      
                    
    $model['weID'] = $e['weID'];
                    
    $model['size_h'] = $e['size_h'];
                    
    $model['size_w'] = $e['size_w'];
     
     
    while(
    $h mysql_fetch_assoc($images)) {
                    
    $model['images'][] = array(
     
                                                    
    'imgID' => $h['imgID'],
                                                    
    'imgName' => $h['imgName']
                    );
     
    while(
    $f mysql_fetch_assoc($text)){
       
    $model['text'][] = array(
     
                                                    
    'txtID' => $f['txtID'],
                                                    
    'txtText' => $f['txtText']
                    );
    };
            };
    };
     
    echo 
    json_encode ($model);
     
    mysql_close($con); 

  2. #2
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    $back = mysql_query("SELECT weID, size_h, size_w FROM wb WHERE weID=-126555895");
    $images = mysql_query("SELECT imgID, imgName FROM wi WHERE weID=-126555895"
    );
    $text= mysql_query("SELECT txtID, txtText FROM wt WHERE weID=-126555895"
    );

    how can I link them together, to show all my results rather then one "-126555895"??


    PHP Code:
    $results mysql_query("SELECT wb.weID, size_h, size_w, imgID, imgName, txtID, txtText
                                      FROM wb
                                        JOIN wi 
                                           ON wi.weID = wb.weID
                                        JOIN wt
                                           ON wt.weID = wb.weID
                                      WHERE wb.weID = '-126555895'
                                    "
    ); 
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  3. #3
    SitePoint Member
    Join Date
    May 2012
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thank you for the reply, I tried the new sql setup, but it does not seem to work, I get errors to do with mysql version:
    "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax"


    what MYSQL Library version do I need to use the above setup?? at the moment mine is version 7.0 and Client API version 5.0.51a

  4. #4
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Strange, I cant see anything wrong with the query ? Unless im being particulary blind this afternoon, it is friday (pub lunch).

    normally the error will point to where the syntax error is by continuing your message with 'near ..........'

    Try entering the query directly into PHPmyadmin see if that gives ay more clues as to what its not happy with.
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  5. #5
    SitePoint Member
    Join Date
    May 2012
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok that works in PHPmyadmin, so this is my latest code, but I am not getting much data exept""

    PHP Code:
    $resultsmysql_query("SELECT wb.weID, size_h, size_w, imgID, imgName, txtID, txtText
    FROM wb
    JOIN wi ON wi.weID = wb.weID
    JOIN wt ON wt.weID = wb.weID
    WHERE wb.weID =  '-126555895'"
    );

    $model = array();
        
        while(
    $e mysql_fetch_assoc($results)){
                    
                
            
    $model['weID'] = $e['weID'];
            
    $model['size_h'] = $e['size_h'];
            
    $model['size_w'] = $e['size_w'];
        
        };
            
        while(
    $h mysql_fetch_assoc($results)) {    
            
    $model['images'][] = array(
            
                            
    'weID'=> $h['weID'],
                            
    'imgID' => $h['imgID'],
                            
    'imgName' => $h['imgName']
            );
            
        };
        
        while(
    $f mysql_fetch_assoc($results)){
             
    $model['text'][] = array(
            
                            
    'weID'=> $f['weID'],
                            
    'txtID' => $f['txtID'],
                            
    'txtText' => $f['txtText']
            );
        
        };
        

    print 
    json_encode ($model); 
    Code:
    //the current output
    {"weID":"-126555895","size_h":"700","size_w":"494"}
    Like I said in my first post the json data must be in the same format but able to loop through.

  6. #6
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    No, you'll need to change your code that writes the data out, as you only have one query now you can only have one mysql_fetch_array statement.

    Sorry Im off now for the we, if your stuck Im sure someone else will step in and show you how to format your results.
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  7. #7
    SitePoint Member
    Join Date
    May 2012
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hope you had a good weekend, sorry to bother you once again, it does't seem like anyone else get what we are trying to do, anyways been battling on with this since friday, but with no luck, multi array seems an almost an imposible task for me, I would really appreciate any help with the php, to get this thing of the ground.

    thanks once again.

  8. #8
    SitePoint Member
    Join Date
    Nov 2012
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi all I think you need a multi array + array map

    For the sql try something like this

    sql = " SELECT
    A.weID, A.size_h, A.size_w
    B.imgID, B.imgName
    C.txtID, C.txtText
    FROM wb AS A
    INNER JOIN wi AS B ON B.imgID = A.weID
    INNER JOIN wt AS C
    WHERE A.weID = -126555895"

    and:

    $result = mysql_query($sql);
    $newArrayMap = array( 0 => 'A', 1 => 'B', 2 => 'C', 3 => 'D', 4 => 'E', 5 => 'F');
    while($row = mysql_fetch_assoc($result)) {
    $data[$row['A']]['B'][]['C'][] = $row['A'];
    }
    foreach($data as $key => $images) {
    $newArray = array();
    foreach( $images as $i => $image ) {
    $newArray[$newArrayMap[$i]] = $image;
    }
    $data[$key]['B'] = $newArray;
    }
    echo json_encode(array("listing" => $data));


    Or something like this
    PS. I do not test the code but you can give a try

  9. #9
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,214
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by codename:$jan View Post
    hope you had a good weekend, sorry to bother you once again, it does't seem like anyone else get what we are trying to do, anyways been battling on with this since friday, but with no luck, multi array seems an almost an imposible task for me, I would really appreciate any help with the php, to get this thing of the ground.

    thanks once again.
    Try this
    PHP Code:
        $model = array();
        
    $record = -1;
        
    $currentWeID = -1;    
        while(
    $e mysql_fetch_assoc($results)){ 
          if (
    $e['weID'] != $currentWeID)
          {
            
    $record++;
            
    $model[] = array();
            
    $model[$record]['weID'] = $e['weID']; 
            
    $model[$record]['size_h'] = $e['size_h']; 
            
    $model[$record]['size_w'] = $e['size_w']; 
            
    $model[$record]['images'][] = array( 
             
                            
    'weID'=> $e['weID'], 
                            
    'imgID' => $e['imgID'], 
                            
    'imgName' => $e['imgName'
            ); 
            
    $model[$record]['text'][] = array( 
             
                            
    'weID'=> $e['weID'], 
                            
    'txtID' => $e['txtID'], 
                            
    'txtText' => $e['txtText'
            ); 
          }
          else
          {
            if (!empty(
    $e['imgID']) && !empty($e['imgName']))
            {
              
    $model[$record]['images'][] = array( 
               
                              
    'weID'=> $e['weID'], 
                              
    'imgID' => $e['imgID'], 
                              
    'imgName' => $e['imgName']
              ); 
            }
            
            if (!empty(
    $e['txtID']) && !empty($e['txtText']))
            {
              
    $model[$record]['text'][] = array( 
               
                              
    'weID'=> $e['weID'], 
                              
    'txtID' => $e['txtID'], 
                              
    'txtText' => $e['txtText'
              ); 
            }
          }
        } 
         

    print 
    json_encode ($model); 


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •