jeudi 5 novembre 2015

PHP sqlite SELECT query returning nothing

I'm writing a simple application that needs to retrieve all the values from a table.

I'm using PHP, SQLite as database, and PDO for opening the database.

The database has been created with this structure

CREATE TABLE 'messages' (
'message_id' INTEGER PRIMARY KEY NOT NULL, 
'sender_id' INTEGER NOT NULL, 
'sender_name' TEXT NOT NULL, 
'message' TEXT NOT NULL, 
'time' DATETIME NOT NULL DEFAULT CURRENT_TIME)

At this moment, it contains

message_id  sender_id   sender_name message time
1              21        carletto     za    16:03:20
2              21        carletto    poli   16:07:05
3              21        carletto    pasta  16:08:07
4               1         admin       test  16:08:07

Running the query

SELCET * FROM messages WHERE message_id > 0;

from the phpmyadmin correctly returns all the tuples.

Running the query from my code, the result is that it never enters the while. INSERT queries from the same code works perfectly. NOTE: lastReceivedMessage correctly receives a value of 0.

<?php session_start();
// send headers to prevent caching
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT" ); 
header("Last-Modified: " . gmdate( "D, d M Y H:i:s" ) . "GMT" ); 
header("Cache-Control: no-cache, must-revalidate" ); 
header("Pragma: no-cache" );

// open database
$file_db = new PDO('sqlite:./chatdb') or die("cannot open database");


if ($file_db) {

    // check if a message was sent to the server
    if (isset($_POST['message']) ) {

        $message = $_POST["message"];
        $sender_id = $_POST["sender_id"];
        $sender_name = $_POST["sender_name"];

        $query = 'INSERT INTO messages (sender_id, sender_name, message) 
                    VALUES (' . $sender_id . ',"' . $sender_name . '","' . $message . '");';
        $file_db->query($query);
    }

    // retrieve all new messages from server
    else if (isset($_POST["lastReceivedMessage"])) {

        // select sql to retrieve all new messages from server
        $lastMessage = $_POST["lastReceivedMessage"];
        $query = 'SELECT * FROM messages WHERE message_id > ' . $lastMessage . ';';
        $result = $file_db->query($query);

        // start creating json
        $json = '{"messages": {';
        $json .= '"message":[ ';        

        // for every line, create a json element
        while($row = $result->fetchArray()) {
            $json .= '{"message_id": "' . $row['message_id'] . '",
                    "sender": "' . htmlspecialchars($row['sender_name']) . '",
                    "message": "' . htmlspecialchars($row['message']) . '",
                    "time": "' . $row['time'] . '"},';
        }

        // close json
        $json .= ']';
        $json .= '}}';      

    }
}

// close database
$file_db->close();


?>

Aucun commentaire:

Enregistrer un commentaire