Why is mysqli query not completed or cut when using ajax / php response

Question

I am using an ajax script to send parameters and get a response to ajax.php. This is working fine as I can send parameters and get a response. My problem is that when I echo the query in the ajax.php script, I check on my browsers network. I see that the query is not complete and thus returns in an unexpected result. But when I execute the query within phpMyAdmin then I get the correct result. Please help.

This is the actual query below

$lat = $_REQUEST['lat'];
$lon = $_REQUEST['lng'];

$db = new MysqliDb ('uveehae_road');
$results= $db->rawQuery('SELECT id, address, ( 6371 * acos( cos( radians('.$lat.') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('.$lon.') ) + sin( radians('.$lat.') ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 100 ORDER BY distance LIMIT 0,1');

This is what I see when I print the query and check on browser network

SELECT id, address, ( 6371 * acos( cos( radians(-26.0749518) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(28.1167666) ) + sin( radians(-26.0749518) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance

As you can see the query is not complete but I don't know why.

@YourCommonSense because the query is incomplete it brings back the entire database records instead of only bring the records where the distance is < 100 — user7498826, Oct 20 '17 at 12:46
that's strange. I would rather expect a query error. but anyway MysqliDb is unusable, so there is no solution — Your Common Sense, Oct 20 '17 at 12:48
@YourCommonSense well i have been using it for long its definitely works. Maybe not working in this case. Let me try the normal way and see what i get — user7498826, Oct 20 '17 at 12:49
I strongly advise you to learn about parameter binding. Using PDO is also highly recommended — Mawg says reinstate Monica, Oct 23 '17 at 07:03

score 2 · Answer 1 · answered Oct 20 '17 at 12:13

As far as I remember, the author of that MysqliDb you are using has absolutely no knowledge in SQL, and made their class fail in situations like this. Let alone it's fatally prone to SQL injection.

You better quit using it and start with PDO prepared statements.

score 0 · Answer 2 · answered Oct 23 '17 at 06:59

So the most basic worked for me..

$lat = $_REQUEST['lat'];
    $lon = $_REQUEST['lng'];

    $servername = "localhost";
    $username = "user";
    $password = "password";
    $dbname = "dbname";

    $conn = new mysqli($servername, $username, $password,$dbname);

    $sql = "SELECT id,address, ( 6371 * acos( cos( radians($lat) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians($lon) ) + sin( radians($lat) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 100 ORDER BY distance LIMIT 0,1";

    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
        while($row = $result->fetch_assoc()) {
           echo json_encode($row);
        }
    }

and SQL injection still. some on, do you really want to let anyone free access into your database? — Your Common Sense, Oct 24 '17 at 07:47

Why is mysqli query not completed or cut when using ajax / php response

2 Answers2