MySQL query with PHP not returning expected data

Question

I'm trying to access a MySQL DB with PHP in order to populate a Javascript array, but the array always comes up empty. Before, I had a 'www-data@localhost' access error, but couldn't figure it out and have since rolled back the code. Can anyone share any insight?

JS:

var arrayPOIs = [];

function getPoints(){
    $.get('getdata.php', function(data){
        arrayPOIs = data;   
    });
}

getdata.php:

$mysqli = new mysqli("localhost", "user", "pass", "test");

if (mysqli_connect_errno())
{
    echo("Connect failed: ") . mysqli_connect_error();
    exit();
}

$query = "SELECT * FROM points";

$result = mysqli_query($query) or die(mysqli_error());

$potential = mysqli_fetch_array($result);

echo json_encode($potential);

I was trying to output the contents of the table to an array. In my browser's console it's always showing up empty. Such as:

arrayPOIs
""

I think I might have some issue with the DB connection query process. Although, I'm quite new to this and I'm lost. Any ideas?

Additionally, how and where can I check what PHP is doing realtime, ie, the error messages?

EDIT:

Following up on the comments:

after adding "json" as a third parameter, the arrayPOIs var became (in FF's console) Array [ ]
my getdata.php page comes out empty (blank) when accessed directly

EDIT2:

After Niranjan N Raju's help (+1), it's partially fixed. Bad php syntax on my query call. Now, my object is ill formed. I'm getting:

{"0":"1","id":"1","1":"poi_teste1","name":"poi_teste1","2":"41.1953","latitude":"41.1953","3":"-8.60134","longitude":"-8.60134"}

But I need something like (JSON):

name: poi_teste1,
latitude: 3464357247,
longitude: 247245672

for each of the rows in the table, which is something like this:

id | name    | latitude | longitude
#1 | string  | float    | float
#2 | string  | float    | float
#3 | string  | float    | float
#4 | string  | float    | float

Hi, add an array count before json_encode to make sure that your array is not empty — CodeIsLife
– CodeIsLife, Commented Oct 19, 2015 at 16:36
Does it work when adding json as a third parameter to your $.get function? — Jacques Marais
– Jacques Marais, Commented Oct 19, 2015 at 16:39
Also, visit the getdata.php page from your browser, and tell us what it returns. — Jacques Marais
– Jacques Marais, Commented Oct 19, 2015 at 16:40
I think you have to change the fetch type now. Can't test it, try mysqli_fetch_assoc. — FirstOne
– FirstOne, Commented Oct 19, 2015 at 17:02

FirstOne · Accepted Answer · 2015-10-19 17:23:11Z

2

you have missed connection object in mysqli_query()

change like this

$result = mysqli_query($mysqli,$query) or die(mysqli_error());
                       ^      ^

Edit

If you are getting more that 1 rows,like

array(
    0 => id,name,lat and long,
    1 => id,name,lat and long,
    2 => id,name,lat and long,
);

For above kind of result, output is correct. To access the values in ajax, use for() loop

Also, since you don't want the index, remember to change from

$potential = mysqli_fetch_array($result);

to

$potential = mysqli_fetch_assoc($result);

More at fetch_assoc.

edited Oct 19, 2015 at 17:23

FirstOne

6,2857 gold badges28 silver badges48 bronze badges

answered Oct 19, 2015 at 16:46

Niranjan N Raju

12k4 gold badges24 silver badges41 bronze badges

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

10 Comments

Joum Over a year ago

this is it...! but unfortunately, I think I have some other problem while encoding the object. I'll add detail in the question above. Can you help out?

Niranjan N Raju Over a year ago

ya sure add it. but the above answer is working. i checked it.

FirstOne Over a year ago

@Joum did you see my comment on the question?

FirstOne Over a year ago

@NiranjanNRaju If i may edit the answer to fit my comment, is it ok to you?

Niranjan N Raju Over a year ago

@FirstOne ya sure. do it.

|

Joum · Accepted Answer · 2015-10-19 17:38:21Z

0

Just for future reference, if it might help someone out: after being vastly helped by @Niranjan N Raju and @FirstOne this worked as I intended:

JS:

function getPoints(){
    $.get('getdata.php', function(data){
    //gotta do something with that data! :)
    arrayPOIs = data;   
    }, "json");
}

getdata.php:

$mysqli = new mysqli("localhost", "user", "pass", "test");

/* check connection */
if (mysqli_connect_errno())
{
    echo("Connect failed: ") . mysqli_connect_error();
    exit();
}

$query = "SELECT * FROM points";

$resultArray = array();
$index = 0;

if ($result = $mysqli->query($query)) {

    /* fetch associative array */
    while ($row = $result->fetch_assoc()) {
        $resultArray[$index] = $row;
        $index++;
    }

    /* free result set */
    $result->free();
}

echo json_encode($resultArray);

Small note (I'll figure it out alone :):

In the MySQL DB, latitude and longitude are float-type columns. This solution doesn't account for that as they are coming out as strings. I recon that either in PHP or JS I can parseFloat them.

edited Oct 19, 2015 at 17:38

answered Oct 19, 2015 at 17:26

Joum

3,2553 gold badges39 silver badges65 bronze badges

1 Comment

FirstOne Over a year ago

Can you mysqli_fetch_all then? Just change the resulttype so it doesn't return the index, just the associative array. It would probably look something like this: $potential = mysqli_fetch_all($result, MYSQLI_ASSOC);

Collectives™ on Stack Overflow

MySQL query with PHP not returning expected data

2 Answers 2

10 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related