PHP can't populate array from selected table to json_encode and receive it on javascript

Question

I'm build an hybrid app based on JqueryMobile. And now I'm trying to get a simple array with the categorys that I select from my mysql table. I saw many questions related to this and tired a thousand solutions but nothing worked.

Here's what I have:

PHP Code:

$sql=mysql_query("SELECT Tipo FROM tfc_db.Category ");
$id=array();

while($row = mysql_fetch_assoc($sql)) {
    $id[] = $row;
    //$id[] = $row['Tipo']; doesn't work either
}
echo json_encode($id);

mysql_close($conn);

with this I get blank result.

The closest I got was with this:

$id=array();
$i=0;
while ($row = mysql_fetch_assoc($sql)) { 
    $id[] = array("data$i" => $row['Tipo']);
    echo json_encode($id[$i]);
    $i++;
}

with the result:

{"data0":"Restaurantes"}{"data2":"Shopping"}{"data3":"Eventos"}{"data4":"Hoteis"}{"data5":"Oficinas"}{"data6":"Combustiveis"}

but this is obviously wrong because it has multiple json_encode invocations.

Im trying to get it with Jquery's .$get

<script type="text/javascript">
 var inicio=function(argument){
    $("#submit").click(function(e){
        e.preventDefault();
        $.get('http://myurl/get_category.php',{ 
        },function(answer){
        alert(answer.length+" "+answer);         //trying to know whats happening with the data.
        for (var i = 0; i < answer.length; i++) {   //my final objective is to fill a listview.
            $('#list').append("<li> <h3>"+answer.tipo+"</h3></a></li>");
        }
        $('#list').listview('refresh');
        },"json"
        );
    });
}
$(document).on('ready',inicio);
</script>

As I said I searched into many questions and solutions but always got null values. I don't have too much experience with PhP and this is me studying and making experiences with JqueryMobile so.. something's wrong here that I can't see..

Thank you.

score 1 · Accepted Answer · edited May 23 '17 at 12:15

1

Found the solution here on this post Everything on the DB must be set to UTF-8 or utf8mb4 and now it's finally working. Apreciated everyone's help :)

edited May 23 '17 at 12:15

Community

1
1

answered Nov 23 '14 at 23:33

RubenC

496
1
5
15

user254948 · Answer 2 · 2014-11-22T11:08:01.837

0

You can use the following query:

$sql=mysql_query("SELECT Tipo FROM tfc_db.Category ");
$id=array();

while($row = mysql_fetch_assoc($sql)) {
    $id[] = $row['Tipo'];
}
echo json_encode($id);

mysql_close($conn);

It will return the categories as an array of strings in JSON. e.g. ["entry 1","entry 2"]. If you decide to use this then you would need to adjust your Javascript code. Your javascript code contains some problems on this line:

    $('#list').append("<li> <h3>"+answer.tipo+"</h3></a></li>");

In the case of you using

 $id[] = $row;

In PHP, then the column name would be named Tipo, not tipo (case sensitive). The second issue is that you are trying to access answer.tipo and not the element of the array answer[i].tipo.

So with your current php code you can make it working by changing the javascript. This line

 $('#list').append("<li> <h3>"+answer.tipo+"</h3></a></li>");

To

 $('#list').append("<li> <h3>"+answer[i].Tipo+"</h3></a></li>");

Edit: Since your apparently having issues here is the full code I used.

//query.php
<?php
    mysql_connect ('localhost','username','password');
    mysql_select_db('db_test');
    $sql=mysql_query("SELECT Tipo FROM db_test.Category ");
    $id=array();

    while($row = mysql_fetch_assoc($sql)) {
        $id[] = $row;
    }

    echo json_encode($id);
    mysql_close($conn);
?>

<html>
  <head>
    <title> JQuery db test </title>
    <script src="http://code.jquery.com/jquery-2.1.1.min.js"></script>
  </head>
  <body>
    <div id='list' class='list'>
    </div>
    <script>
        var debug;
        $.get('http://www.example.com/test/query.php',{ 
        },function(answer){
            debug=answer;
        //alert(answer.length+" "+answer);         //trying to know whats happening with the data.
        for (var i = 0; i < answer.length; i++) {   //my final objective is to fill a listview.
            $('#list').append("<li> <h3>"+answer[i].Tipo+"</h3></a></li>");
        }
       // $('#list').listview('refresh');
        },"json"
        );
    </script>
</html>

edited Nov 22 '14 at 11:08

answered Nov 22 '14 at 10:44

user254948

1,036
6
14

I tried already that query, that's why I had it commented but I didn't try it with the answer[i].Tipo, But yeah it doesn't work either.. Nothing happens on my page and the get_category.php page doesn't show any output aswell. – RubenC Nov 22 '14 at 11:00
I just tried it on my own webserver without any issues? I will update my post with the full code I used to test. Also check your error log to see if you perhaps get any errors? Perhaps you are missing mysql_connect? or mysql_select_db? – user254948 Nov 22 '14 at 11:02
No because I already got other data from my DB and on my post I showed the closest output I got where I cant take those values from the table so, conections are not the problem. Uncaught TypeError: Cannot read property 'Tipo' of undefined . I'm getting this now – RubenC Nov 22 '14 at 11:13
Could you load the page in a webbrowser and open the javascript/developer console and type in debug? what is in the debug variable. mysql_query will most likely return the name of the column in the database (if you query for Tipo, yet its called tipo in the table, it will be called tipo in the output, verify the capitalization of the output) – user254948 Nov 22 '14 at 11:15
debug gives me this ? oO : function debug(fn) { [Command Line API] } The column is Tipo yes. – RubenC Nov 22 '14 at 11:28
Ah guessing the variable debug was already in use. try renaming my variable debug in the javascript to something else and try getting the value of that :) (change 'var debug;' to another name and change debug=answer;) – user254948 Nov 22 '14 at 11:32
It says it's not defined.. that's weird – RubenC Nov 22 '14 at 11:46
Check the URL in the jquery get. make sure to change it to whatever domain you are using instead of mine example.com. Secondly verify the output of the php script. Thirdly make sure the domain allows jquery calls (x-allow-origin) – user254948 Nov 22 '14 at 12:03
domain is fine the output is blank, null, as I said before. The thing is I already used Jquery and this get method in other pages of my project. But I always got single variables. I also got a json_encoded one but I "wrote" the array manually because it was a static one. I can't understand because if I do the second method I have in my question I can get a result from json_encode, but only one row ! I also tried array_push and it was adding nothing to the array. I dunno ig it's json_encode problem or something but as I see it work for some things but not for others? :s – RubenC Nov 22 '14 at 12:29

Sampath Liyanage · Answer 3 · 2014-11-22T11:06:31.143

0

Try something like this...

If you want only to get the values of column 'Tipo',

$sql=mysql_query("SELECT Tipo FROM tfc_db.Category ");
$id=array();
$i=0;
while($row = mysql_fetch_assoc($sql)) {
    $id["data".$i] = $row['Tipo'];
    $i++;
}
echo json_encode($id);
mysql_close($conn);

If you awnt to get all the values,

$sql=mysql_query("SELECT Tipo FROM tfc_db.Category ");
$id=array();
$i=0;
while($row = mysql_fetch_assoc($sql)) {
    foreach($value as $key=>$value){
        $id["data".$i][$key] = $value;
    }
    $i++;
}
echo json_encode($id);
mysql_close($conn);

edited Nov 22 '14 at 11:06

answered Nov 22 '14 at 10:56

Sampath Liyanage

4,776
2
28
40

the outpu from the php page is "[]" so it wont work but what should I use on JS to get anything from this? – RubenC Nov 22 '14 at 11:19
Did you check if the $sql is populated? if you didn't check, add a var_dump($sql) and print_r($sql) just after executing mysql_query and check if $sql has values from the table. – Sampath Liyanage Nov 22 '14 at 11:32
resource(3) of type (mysql result) Resource id #3 gives me this, but yeah if I $rowz=mysql_fetch_assoc($sql); and echo $rowz['Tipo']; it gives me the first row of my column – RubenC Nov 22 '14 at 11:43

score 0 · Answer 4 · answered Nov 22 '14 at 11:26

0

[PHP]

$id = array();
while($row = mysql_fetch_assoc($sql))
    $id[] = $row['Tipo'];
echo json_encode($id);

[JS]

$(document).ready(function(){
    $('#submit').click(function(e){
        $.ajax({
            url: '/get_category.php',
            type: 'GET',
            dataType: 'json'
        }).done(function(res){
            $('#list').html('');
            for(var i=0;i<res.length;++i)
                $('#list').append('<li><h3>'+res[i]+'</h3></li>');
        });
});

answered Nov 22 '14 at 11:26

Dienes László

84
4

and not using other print data in script? – Dienes László Nov 22 '14 at 11:40
no, just this one. I was alerting everything I could with the var answer so I could get any result. The best I got was after JSON.parse it gave me an array with every character of the string – RubenC Nov 22 '14 at 11:47

PHP can't populate array from selected table to json_encode and receive it on javascript

4 Answers4

Linked