How to get data form MySQL db and output as JSON through PHP, then cross domain ajax request for the data in JQuery

Question

I'm using a digital signage player that allows me to use HTML and javascript to display things , in my case I'm building a menu board with a list of products and prices. I've already created a php system to create entries for products and prices and those values are stored in a MySQL database. What I would like to do is have a PHP script output the mysql data in JSON format, then have a cross domain ajax request for the products and prices to display on the menu board. Unfortunately the menu board cannot support PHP so I have to improvise and get the data through JSON. I've been pulling my hair the past couple of hours trying to figure this out but I can't seem to get it. Any help would be appreciated.

EDIT 1

I have a php script and html file but can't seem to have the data show. Here are the files below

showjson.html

<script src="http://code.jquery.com/jquery-latest.min.js"></script>
<script>
$.ajax({
 url:"http://localhost/blakewilson/api.php",
 dataType: 'jsonp', // Notice! JSONP <-- P (lowercase)
 success:function(json){
     // do stuff with json (in this case an array)
     $("#userdata tbody").html("");
    $.getJSON(url,function(data){
     $.each(data.members, function(i,user){
    var tblRow =
    "<tr>"
    +"<td>"+user.postID+"</td>"
    +"<td>"+user.postProduct+"</td>"
    +"<td>"+user.postPrice+"</td>"
    +"</tr>" ;
    $(tblRow).appendTo("#userdata tbody");
},
 error:function(){
     alert("Error");
}      
});
</script>

api.php

<?php
$link = mysql_pconnect("localhost", "root", "") or die("Could not connect");
mysql_select_db("dn_name") or die("Could not select database");
$arr = array();
$rs = mysql_query("SELECT * FROM products");
while($obj = mysql_fetch_object($rs)) {
$arr[] = $obj;
}
echo $_GET['callback']."(".json_encode($arr).");";  // 09/01/12 corrected the statement
?>

EDIT 2

This code give me an alert with success so I know it works.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>jQuery PHP Json Response</title>
<style type="text/css">
</style>
</head>
<body>
<div id="msg"></div>
<script src="http://code.jquery.com/jquery-latest.min.js"></script>
<script>
$.ajax({
     url:"http://localhost/blakewilson/api.php",
     dataType: 'jsonp', 
     success:function(json){
        alert("Success");
    },
     error:function(){
         alert("Error");
    }      
});
</script>
</body>
</html>

I'm trying to get a few values like postID, postProduct, and postPrice from JSON, but I can't seem to figure it out. I'm very new to jQuery/AJAX etc.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>jQuery PHP Json Response</title>
<style type="text/css">
</style>
</head>
<body>
<div id="msg"></div>
<script src="http://code.jquery.com/jquery-latest.min.js"></script>
<script>
$.ajax({
     url:"http://localhost/blakewilson/api.php",
     dataType: 'jsonp', 
     success:function(json){
        // loop through the productg here
        $.each(json.members,function(i,dat){
        $("#msg").append(
        '<div class="members">'+
        '<h1>'+dat.postID+'</h1>'+
        '<p>Firstname : <em>'+dat.postProduct+'</em>'+
        '<p>SurName : <em>'+dat.postPrice+'</em></p>'+
        '<hr>'+
        '</div>'
        );
        });
    },
     error:function(){
         alert("Error");
    }      
});
</script>
</body>
</html>

EDIT 3

What I'm basically trying to do is take this project: http://tournasdimitrios1.wordpress.com/2011/11/04/how-to-generate-json-with-php-from-mysql-and-parse-it-with-jquery/ and make it work cross domain. That is all I need.

EDIT 4

This is the output of the api.php file. It's throwing an error "Notice: Undefined Index"

Notice: Undefined index: callback in E:\xampp\htdocs\blakewilson\api.php on line 13
([{"postID":"8","postProduct":"Synthetic Oil Change","postDollar":"29","postCents":"95","postDate":"2014-08-12 12:11:00"},{"postID":"9","postProduct":"Tire Rotation","postDollar":"16","postCents":"95","postDate":"2014-08-12 12:11:10"},{"postID":"10","postProduct":"Rotate and Balance","postDollar":"39","postCents":"95","postDate":"2014-08-12 12:11:21"},{"postID":"11","postProduct":"4-Wheel Alignment","postDollar":"79","postCents":"95","postDate":"2014-08-12 12:11:35"},{"postID":"12","postProduct":"Cooling System Service","postDollar":"129","postCents":"95","postDate":"2014-08-12 12:11:51"},{"postID":"13","postProduct":"Transmission Flush","postDollar":"189","postCents":"95","postDate":"2014-08-12 12:12:07"},{"postID":"14","postProduct":"AC Performance Service","postDollar":"69","postCents":"95","postDate":"2014-08-12 12:12:19"}]);

Javascript can't do cross domain ajax requests. The target url has to be the same domain (maybe someone knows if this is also true for requests to subdomain). — Charlotte Dunois
– Charlotte Dunois, Commented Aug 13, 2014 at 18:26
I didn't mean it like that, my player can render ajax and jquery so that's no issue. It just needs cross domain because the html page that will display the prices and products will be on the player @charlotte-dunois — Blake
– Blake, Commented Aug 13, 2014 at 18:46
you do know that $.getJSON do is an AJAX call so you are technicaly doing an AJAX call within an AJAX success callback? — Sebastien
– Sebastien, Commented Aug 13, 2014 at 19:36
Yes i realized that, I accidentally posted that in. @Sebastien — Blake
– Blake, Commented Aug 13, 2014 at 19:37
have you tried to console.log(json); to see if the success callback it triggered somewhere along the execution and to see if you even receive data in your json parameter. — Sebastien
– Sebastien, Commented Aug 13, 2014 at 19:41

Chintan Patel · Accepted Answer · 2014-12-21 10:54:37Z

1

the Below link works well for me, you can try

http://jquery-howto.blogspot.in/2013/09/jquery-cross-domain-ajax-request.html

answered Dec 21, 2014 at 10:54

Chintan Patel

1441 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

slfan Over a year ago

please provide a full answer to the question, not just a link

Chris N · Accepted Answer · 2014-08-13 19:33:06Z

0

Not sure whats in the

echo $_GET['callback']."(".json_encode($arr).");";

But you might try removing it all down to

echo json_encode($arr);

If you need to send 2 pieces of data back, just build that into another array. Such as:

echo json_encode(array('message' => $_GET['callback'], 'data' => $arr));

answered Aug 13, 2014 at 19:33

Chris N

243 bronze badges

1 Comment

Blake Over a year ago

Thanks @chris-n. The reason I have the callback in there is that im basically just going off this post. stackoverflow.com/questions/3506208/jquery-ajax-cross-domain . This works great for me, I just need to know how to enter those values in a table

Collectives™ on Stack Overflow

How to get data form MySQL db and output as JSON through PHP, then cross domain ajax request for the data in JQuery

EDIT 1

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

EDIT 1

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related