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