phpmyadmin Question posted in
The official documentation can be found here.

No rows being returned from MySQL query using PHP while manually running query returns rows – Phpmyadmin

I am using jquery ajax to call up a PHP page simple API that returns JSON.
here is the code for that 

<?php 


$response['is_error'] = 'no';
$user = $_POST['user'];
$date = $_POST['date'];

$sql = "select * from locations where user_id='$user' and DATE(timestamp) = '$date'";
$locations = $conn->query($sql) or $response['is_error'] = 'yes';
$response['num_rows'] = $locations->num_rows;

$locations = $locations->fetch_assoc(); 

$response['locations'] = $locations;
$response['date'] = $date;

if($response['is_error'] == 'yes'){
    $response['status'] = "failed";
    $response['error'] = $conn->error;
}else{
    $response['status'] = "ok";
}

$response['sql']=$sql;

echo json_encode($response);

When executed this script returns the following JSON

{"is_error":"no","num_rows":0,"locations":null,"date":"2019-07-30","status":"ok","sql":"select * from locations where user_id='0123456789' and DATE(timestamp) = '2019-07-30'"}

Here is the parsed version for the sake of viewing

date: "2019-07-30"
is_error: "no"
locations: null
num_rows: 0
sql: "select * from locations where user_id='0123456789' and DATE(timestamp) = '2019-07-30'"
status: "ok"

however, if I copy-paste the SQL echoed in JSON in the PHPMyAdmin page SQL it returns a row which was indeed intended it’s just weird that it does not return anything while executing the same query using PHP in with the script.

here is an attached screenshot of the PHPMyAdmin query output

enter image description here

Running print_r($conn) echos following output

mysqli Object
(
    [affected_rows] => 1
    [client_info] => 5.6.30
    [client_version] => 50630
    [connect_errno] => 0
    [connect_error] => 
    [errno] => 0
    [error] => 
    [error_list] => Array
        (
        )

    [field_count] => 1
    [host_info] => Localhost via UNIX socket
    [info] => 
    [insert_id] => 0
    [server_info] => 5.6.44-cll-lve
    [server_version] => 50644
    [stat] => Uptime: 94095  Threads: 23  Questions: 77085244  Slow queries: 625  Opens: 814448  Flush tables: 1  Open tables: 5000  Queries per second avg: 819.227
    [sqlstate] => 00000
    [protocol_version] => 10
    [thread_id] => 1035427
    [warning_count] => 0
)
Tags:

2

Answers


  1. 0

    I think the user_id which is starting with 0 is creating a problem. Can you change the data type of the user_id column and make it as varchar and then try or else remove 0 from your query and also the table row and then check. It should work.

    Login or Signup to reply.
  2. 0 
    if (isset($_POST["adds"])) {
    $servername = "localhost";
    $username = "root";
    $password = "root";
    $db = "loc";
    $conn = new mysqli($servername, $username, $password, $db);
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    $response['is_error'] = 'no';
    $user = $_POST['user'];
    $date = $_POST['date'];
    $sql = "select * from location where user_id='$user' and DATE(timestamp) = '$date'";
    $locations = $conn->query($sql) or $response['is_error'] = 'yes';
    $response['num_rows'] = $locations->num_rows;

    $locations = $locations->fetch_assoc();
    $response['locations'] = $locations;
    $response['date'] = $date;
    if ($response['is_error'] == 'yes') {
        $response['status'] = "failed";
        $response['error'] = $conn->error;
    } else {
        $response['status'] = "ok";
    }
    $response['sql'] = $sql;
    echo json_encode($response);
}
`<html>
<head>
</head>
<body>
    <form method="post">
        <input type="text" name="user"><br>
        <input type="text" name="date"> <br>
        <input type="submit" name="adds">
    </form>
</body>
</html>
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search