Hi

Please take a look at the code section. Does anyone know how to:
a) Return JSON from the prepared procedure call instead of me using a while loop and a concatenated string?
b) Given the MySQL query returns these columns: ID, DepartmentName how do I access these in my client-side JavaScript, ie what will the JSON look like ?

Many thanks.

function fnGetDepartments() {

	require ('mysqli_connect.php'); // Connect to the Db.	
	$sql = "CALL get_departments(?)";
	$stmt = $dbc->prepare($sql);
	if ($dbc->errno) {die($dbc->errno.":: ".$dbc->error);}
	$stmt->bind_param("i", $prm);
	$stmt->execute( );
	if ($dbc->errno) {die($dbc->errno.": ".$dbc->error);}
	$stmt->bind_result($id, $dept_name);
	$buf = "";
	while ($stmt->fetch( )) {
		$buf .= $id . '^' . $dept_name . '|';
	}
	$buf = substr($buf, 0, strlen($buf)-1);
	echo $buf;	
	mysqli_close($dbc);
}

As cereal says you will need to use json_encode and you'll need to loop.

Looking at your code, Line 13 may make it difficult to parse in JSON.

$buf .= $id . '^' . $dept_name . '|';

I would use this instead:

$buf[] = array('id' => $id, 'dept_name' => $dept_name);

Then once I have all my results in the array I would use

echo json_encode($buf);

As cereal says you will need to use json_encode and you'll need to loop.

Looking at your code, Line 13 may make it difficult to parse in JSON.

$buf .= $id . '^' . $dept_name . '|';

I would use this instead:

$buf[] = array('id' => $id, 'dept_name' => $dept_name);

Then once I have all my results in the array I would use

echo json_encode($buf);

Excellent, thanks for the advice about using an array, but:
a) Is there a way to inspect the json before it gets sent?
b) Using your example and at the jQuery client-end, should I refer by id, that is: id and dept_name to pull out the values in the returned json?

Cheers.

As cereal says you will need to use json_encode and you'll need to loop.

Looking at your code, Line 13 may make it difficult to parse in JSON.

$buf .= $id . '^' . $dept_name . '|';

I would use this instead:

$buf[] = array('id' => $id, 'dept_name' => $dept_name);

Then once I have all my results in the array I would use

echo json_encode($buf);

Just cracked it! Thanks, the method to use an array was useful and I used:
print_r(json_encode($array));
to inspect the json.

Cheers !

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.