CODEWITHSUNDEEP
phpsqlite
Eriwas
Eriwas

Reputation: 21

Missing leading Zero's when inserting Strings


I actually get very mad about PHP and SQLite3 and the way some of my strings behave there.
I try to save opening hours but in strings instead of numeric to prevent problem with leading zeros (and still have it now haha... -.-).
Hours and minutes have their own column but when I insert '0x' the zero is gone and whatever x is, is left in the database. :/
Im sure im just missing some little damn part somewhere...
I already checked the INSERT-statement but found nothing at all.
Example for an insert string:
INSERT INTO opening INSERT INTO opening (start_day, end_day, start_hour, start_minute, end_hour, end_minute) VALUES('Montag', 'Freitag', '00', '00', '01', '00')
But the output is:

11|Montag|Freitag|0|0|1|0

Part of the Code:

class Database_Opening_Hours extends SQLite3{
    function __construct() {
        if(!file_exists("../../data/opening_hours/opening_hours.sqlite")){
            $this->open("../../data/opening_hours/opening_hours.sqlite");
            $this->exec('CREATE TABLE opening (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, start_day STRING, end_day STRING, start_hour STRING, start_minute STRING, end_hour STRING, end_minute STRING)');
        }
        else{
            $this->open("../../data/opening_hours/opening_hours.sqlite");
        }
    }
}
$db = new Database_Opening_Hours();
$insert = "INSERT INTO opening (start_day, end_day, start_hour, start_minute, end_hour, end_minute) VALUES('".htmlspecialchars($_GET["start_day"])."','".htmlspecialchars($_GET["end_day"])."','".$start_hour."','".$start_minute."','".$end_hour."','".$end_minute."')";
if($db->exec($insert)){
    $db->close();
    unset($db);
    echo "Insert erfolgreich";
}else{
    $db->close();
    unset($db);
    echo "Nicht wirklich...";
}

Upvotes: 1

Views: 2372

Answers (4)

oware
oware

Reputation: 706

use varchar instead of string, I had the same problem then I used varchar(length) and it worked fine

Upvotes: 0

Anton Andreev
Anton Andreev

Reputation: 2132

I had this problem with C/C++ because I did not quote the strings:

insert into test values('aa', 'bb');

Upvotes: 0

timclutton
timclutton

Reputation: 13004

This is caused by SQLite using dynamic typing. From the FAQ:

This is a feature, not a bug. SQLite uses dynamic typing. It does not enforce data type constraints. Data of any type can (usually) be inserted into any column. You can put arbitrary length strings into integer columns, floating point numbers in boolean columns, or dates in character columns. The datatype you assign to a column in the CREATE TABLE command does not restrict what data can be put into that column. Every column is able to hold an arbitrary length string.

And from the linked page (emphasis mine):

In order to maximize compatibility between SQLite and other database engines, SQLite supports the concept of "type affinity" on columns. The type affinity of a column is the recommended type for data stored in that column. The important idea here is that the type is recommended, not required. Any column can still store any type of data. It is just that some columns, given the choice, will prefer to use one storage class over another. The preferred storage class for a column is called its "affinity".

So SQLite is dynamically casting your values to integer.

I would suggest combining start_hour and start_minute into start_time (the same for the end_ fields) and storing the value in the format 00:00.

SQLite will store this 'as-is' but is smart enough to recognise a time value and allow you to perform date/time operations:

select time(start_time, '+1 hour') from opening

Upvotes: 3

Andrius
Andrius

Reputation: 5939

Fairly sure that the type of your columns is set to an integer (or any other number type) instead of TEXT.

Make sure to double check the column data type and actually dump the table for us to check if it's really set to TEXT.

Upvotes: 2

Related Questions