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.

{
                "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
 

//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");
$text= mysql_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);

$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”
[COLOR=#007700][FONT=Courier New]);

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

[/FONT][/COLOR]

$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'
                                ");

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

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.

ok that works in PHPmyadmin, so this is my latest code, but I am not getting much data exept""


$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'");

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

//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.

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.

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.

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

Try this

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