CODEWITHSUNDEEP
javascriptphpmysqljson
Joum
Joum

Reputation: 3245

MySQL query with PHP not returning expected data

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:

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

Upvotes: 1

Views: 73

Answers (2)

Joum
Joum

Reputation: 3245

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.

Upvotes: 0

Niranjan N Raju
Niranjan N Raju

Reputation: 11987

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.

Upvotes: 2

Related Questions