Outputting MySQL as JSON

PHP
1

I wonder if somebody can help me please. I’m pretty new to PHP and am trying to get the results of a MySQL query to output as JSON but have no idea how to do this.
This is the query that I’m using to get the results:

<?php
$sql_select = "SELECT * "; 
$sql_from = "from feeds,shipping,merchant,product_categories_map where feeds.enabled=1 and feeds.stock=1 and feeds.deleted=0 and feeds.brand_name=shipping.merchant and feeds.best_seller=1 and feeds.brand_name=merchant.name and product_id=product_feed_id and product_feed_id!=1 and ( ";
$sql_orderby = " ORDER BY merchant.pos ASC";
$sql_group = " GROUP BY feeds.product_name";
$sql_limit = " LIMIT 25";

	$query=$sql_select . $sql_from . $sql_group . $sql_orderby . $sql_limit;
    $product_results=dbselect( $query,"dbLinkInt" );

	print_r($product_results)
?>

This is returning the results like this:

Array ( [0] => Array ( [id] => 6177 [salesID] => 5055534302033 [product_name] => name [product_brand] => Brand [brand_name] => Brand name [promo_text] => [delivery_cost] => 1.99 [weight] => 2.5 [price] => 38.99 [serving] => 83 [image_url] => http://www.domain/images/10529260-1395068394-711188.jpg [large_image] => http://www.domain/images/10529260-1395068394-711188.jpg [product_url] => http://www.domain.com/10529261.html?switchcurrency=GBP&shippingcountry=GB [deep_link] => http://www.link.com/pclick.php?p=1234567890&a=123456&m=7890 [source] => N/A [protein] => 91g [carbs] => 0.2g [fat] => 1.1g [calories] => 375 [stock] => 1 [merchant] => seller [name] => Seller name [category_id] => 1 [product_id] => 6177 ) [1] => Array ( [id] => 6177 [salesID] => 5055534302033 [product_name] => name [product_brand] => Brand [brand_name] => Brand name [promo_text] => [delivery_cost] => 1.99 [weight] => 2.5 [price] => 38.99 [serving] => 83 [image_url] => http://www.domain/images/10529260-1395068394-711188.jpg [large_image] => http://www.domain/images/10529260-1395068394-711188.jpg [product_url] => http://www.domain.com/10529261.html?switchcurrency=GBP&shippingcountry=GB [deep_link] => http://www.link.com/pclick.php?p=1234567890&a=123456&m=7890 [source] => N/A [protein] => 91g [carbs] => 0.2g [fat] => 1.1g [calories] => 375 [stock] => 1 [merchant] => seller [name] => Seller name [category_id] => 1 [product_id] => 6177 ))

But this is how I want them to be returned although I’m not worried about the name as I can change that:

{"totalResults":"182","displayNumber":"25","page":[{"product":"item one","brand": "brand x","weight":"1g","serving":"6","source":"coffee","protein":"0","fat":"3","calories":"15","carbs":"0","price":"2.99","link":"http://www.somewhere.com","image":"http://www.somewhere.com/images","status":"0"},{"product":"item two","brand": "brand z","weight":".51g","serving":"2","source":"tea","protein":"0","fat":"0","calories":"5","carbs":"0","price":"12.99","link":"http://www.somewhere.com","image":"http://www.somewhere.com/images","status":"0"},{"product":"item three","brand": "brand x","weight":"1g","serving":"6","source":"coffee","protein":"0","fat":"3","calories":"15","carbs":"0","price":"2.99","link":"http://www.somewhere.com","image":"http://www.somewhere.com/images","status":"0"}]}

Thanks in advance for any help.

2

try with $json_data = json_encode($product_results); this will return all this data like print_r
and you fetch data where you want as $json_data

or you can output only data you want as

$data = array();

$sql_select = "SELECT * "; 
$sql_from = "from feeds,shipping,merchant,product_categories_map where feeds.enabled=1 and feeds.stock=1 and feeds.deleted=0 and feeds.brand_name=shipping.merchant and feeds.best_seller=1 and feeds.brand_name=merchant.name and product_id=product_feed_id and product_feed_id!=1 and ( ";
$sql_orderby = " ORDER BY merchant.pos ASC";
$sql_group = " GROUP BY feeds.product_name";
$sql_limit = " LIMIT 25";

$query=$sql_select . $sql_from . $sql_group . $sql_orderby . $sql_limit;
$product_results = dbselect( $query,"dbLinkInt" );

foreach($product_results as $row) {
    // data you want to send to json
	$data[] = array(
		'label' => $row['salesID'],
		'value' => $row['product_name']
	);

}

$json_data = json_encode($data);
3

Thank you for that but I can’t seem to get it to work. I tried print_r to make sure results where showing but as soon as I changed that to $json_data = son_encode it didn’t show anything. I checked and it said that JSON support is enabled and the version is 1.2.1

This is what I’ve now got:

$sql_select = "SELECT * "; 
$sql_from = "from feeds,shipping,merchant,product_categories_map where feeds.enabled=1 and feeds.stock=1 and feeds.deleted=0 and feeds.brand_name=shipping.merchant and feeds.best_seller=1 and feeds.brand_name=merchant.name and product_id=product_feed_id and product_feed_id!=1 ";
$sql_orderby = " ORDER BY merchant.pos ASC";
$sql_group = " GROUP BY feeds.product_name";
$sql_limit = " LIMIT 25";

	$query=$sql_select . $sql_from . $sql_group . $sql_orderby . $sql_limit;
    $product_results=dbselect( $query,"dbLinkInt" );

$json_data = json_encode($product_results);

Thanks for helping with this.

4

What do you see if you var_dump($json_data) ?

5

Okay now I feel really silly because that shows it exactly how I want it to be. So if I’m using ajax to call that how do I get it to give that result to the ajax call? Return($json_data) ?

6

I thought if you were calling it from Ajax you’d use echo to output the result, and the output is passed back to the calling js function. return would only work if you were calling this PHP code as a function from some other PHP code, as far as I know. Maybe an Ajax expert will know better.

7

Thank you, I’ll try echo then and see if that works.

8

If you’re troubleshooting JavaScript code I think you might have a better go at it by using console.log(somevar) and looking in the browsers dev tools console.

9

Thanks I’ll do that

10

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.