JSON output from PHP

[yas13]

New here and wishing I'd found this forum a week ago. Have a feeling the answer to my problem is really obvious, but I'm hoping this is the kind of forum where "there are no stupid questions" is the cultural norm. Here's my problem:

 

I'm trying to select all rows that match a queried id, put the results into a php array, and output the content of that array to json. I think I've almost got it, but when I get the json (http://codesocial.net/events/get.php?method=get&name=46&format=json) it only shows 1 row.

 

Here's my code (adapted from a tutorial on API creation - http://blog.andreaolivato.net/programming/create-your-first-api-set-in-php.html) :

 

<?php

	/*
	 * Mysql Configuration
	 */

	$sql_host = 'localhost';
	$sql_name = '##';
	$sql_user = '##';
	$sql_pass = '##';

	/*
	 * Trying to connect to mysql server.
	 * Output Temporary error if unable to.
	 */
	if (!@mysql_connect($sql_host,$sql_user,$sql_pass)) {
		$results = Array(
			'head' => Array(
				'status' 		=> '0',
				'error_number'	=> '500', 
				'error_message' => 'Temporary Error.'.
					'Our server might be down, please try again later.'
			),
			'body' => Array ()
		);
		$errors=1;
	}

	/*
	 * Trying to enter the database.
	 * Output Temporary error if unable to.
	 */
	if (!@mysql_select_db($sql_name)) {
		$results = Array(
			'head' => Array(
				'status' 		=> '0',
				'error_number'	=> '500', 
				'error_message' => 'Temporary Error.'.
					'Our server might be down, please try again later.'
			),
			'body' => Array ()
		);
		$errors=1;
	}

	/*
	 * If no errors were found during connection
	 * let's proceed with out queries
	 */
	if (!$errors) 
		switch ($_GET['method']) {				
			case 'get' :
					$query = '
						SELECT `eventId`,`description`,`eventStartDate`,`eventEndDate`
						FROM `Event`
						WHERE `accountId_fk` 
						LIKE "%'.mysql_real_escape_string($_GET['name']).'%"
					';
					if (!$go = @mysql_query($query)) {
						$results = Array(
							'head' => Array(
								'status' 		=> '0',
								'error_number'	=> '604', 
								'error_message' => 'Select Failed. '.
									'Probably wrong name supplied.'
							),
							'body' => Array ()
						);
					} else {
						$fetch = mysql_fetch_row($go);
						$return = Array($fetch[0],$fetch[1],$fetch[2],$fetch[3]);  
						$results = Array(  
							'body' => Array (    
								'eventId' 			=> $return[0],
								'description' 		=> $return[1],
								'eventStartDate' 	=> $return[2],
								'eventEndDate' 		=> $return[3]									  
							)  
						); 
					}
				break;
		}

	switch ($_GET['format']) {
		case 'xml' :
				@header ("content-type: text/xml charset=utf-8");
				$xml = new XmlWriter();
				$xml->openMemory();
				$xml->startDocument('1.0', 'UTF-8');
				$xml->startElement('callback');
				$xml->writeAttribute('xmlns:xsi','http://www.w3.org/2001/XMLSchema-instance');
				$xml->writeAttribute('xsi:noNamespaceSchemaLocation','schema.xsd');
				function write(XMLWriter $xml, $data){
						foreach($data as $key => $value){
								if(is_array($value)){
										$xml->startElement($key);
										write($xml, $value);
										$xml->endElement();
										continue;
								}
								$xml->writeElement($key, $value);
						}
				}
				write($xml, $results);

				$xml->endElement();
				echo $xml->outputMemory(true);
			break;
		case 'json' :
				@header ("content-type: text/json charset=utf-8");
				echo json_encode($results);
			break;
		case 'php' :
				header ("content-type: text/php charset=utf-8");  
				echo serialize($results);  
			break;
	}

?>

 

And some sample sql:

 

CREATE TABLE `Event` (
  `eventId` int(11) NOT NULL AUTO_INCREMENT,
  `accountId_fk` int(11) NOT NULL,
  `description` varchar(250) NOT NULL,
  `eventStartDate` datetime NOT NULL,
  `eventEndDate` datetime NOT NULL,
  `allDay` bit(1) NOT NULL,
  `recurringEndDate` date NOT NULL,
  `Type` enum('Special','Event') NOT NULL,
  `recurringPeriod` enum('None','Daily','Weekly','Monthly','Yearly') NOT NULL,
  `lastSentDate` datetime DEFAULT NULL,
  PRIMARY KEY (`eventId`),
  KEY `EventAccount` (`accountId_fk`),
  CONSTRAINT `EventAccount` FOREIGN KEY (`accountId_fk`) REFERENCES `account` (`accountId`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=614 DEFAULT CHARSET=latin1

INSERT INTO `Event` (`eventId`,`accountId_fk`,`description`,`eventStartDate`,`eventEndDate`,`allDay`,`recurringEndDate`,`Type`,`recurringPeriod`,`lastSentDate`)
VALUES
(137, 46, 'generic event 1', '2010-01-18 15:00:00', '2010-01-18 19:00:00', 0, '2050-01-01', 'Event', 'Weekly', '2010-02-08 11:00:08'),
(458, 46, 'generic event 2', '2010-02-09 00:00:00', '2010-02-09 04:00:00', 0, '2050-01-01', 'Event', 'Weekly', '2010-02-09 11:00:13'),
(459, 46, 'generic event 3', '2010-02-10 00:00:00', '2010-02-10 04:00:00', 0, '2050-01-01', 'Event', 'Weekly', '2010-02-10 15:00:05'),
(460, 46, 'generic event 4', '2010-02-11 00:00:00', '2010-02-11 04:00:00', 0, '2050-01-01', 'Event', 'Weekly', '2010-02-11 10:58:03'),
(461, 46, 'generic event 5', '2010-02-12 00:00:00', '2010-02-12 00:00:00', 1, '2050-01-01', 'Event', 'None', NULL),
(569, 46, 'generic event 6', '2010-02-08 00:00:00', '2010-02-08 04:00:00', 0, '2050-01-01', 'Event', 'Weekly', NULL),
(570, 46, 'generic event 7', '2010-02-08 00:00:00', '2010-02-08 00:00:00', 1, '2050-01-01', 'Event', 'Weekly', NULL),
(571, 46, 'generic event 8', '2010-02-08 00:00:00', '2010-02-08 00:00:00', 1, '2050-01-01', 'Event', 'Weekly', NULL),
(573, 46, 'generic event 9', '2010-02-09 00:00:00', '2010-02-09 00:00:00', 1, '2050-01-01', 'Event', 'Weekly', '2010-02-09 16:00:04'),
(575, 46, 'generic event 10', '2010-02-09 14:00:00', '2010-02-09 19:00:00', 0, '2050-01-01', 'Event', 'Weekly', '2010-02-09 16:00:04');

 

Thanks for any help!

[trq]

I'm not sure what editor your using but your code breaks when copy / pasted. Anyway, the part in your code where you put your results into an array, you'll need to loop through your results, placing each row into a new array. mysql_fetch_* function return one roweach time thay are called, not all rows in one go.

 

Anyway, that piece of code could be replaced with.

 

$results = array();
while ($row = mysql_fetch_assoc($g)) {
    $results[] = array(
        'body' => array($row);
    );
}