SQLite Databse help for newbie

[whitestar7]

Hi Guys,

I have created a SQLite databse using SQLite browser, I needed to amend the database so that in the "date" field, the user can only input 6 numerical digits. so they would have to input 12-09-10 for 12th September 2010

I have searched online for documentation, but being a newbie alot of it has gone over my head or I have missed something.

Please help.

WS7

[djr33]

Databases should be simple and always use consistent data. Dealing with user input is not something a database is designed for (or sometimes even capable of). Your server side language (PHP? CGI? ASP? etc) will take the user input, then you can format it, then enter it in a consistent format in the database. So this means that whatever is stored in the database is just pure information, and the readability is not relevant. For input and output, use your server side script (and a bit of math) to convert it.

[whitestar7]

Hi Daniel,

thankyou for your reply, and helping me to understand better how it works. The user inputs the data directly into the database fields using SQLite Browser, and then any data entered into those fields get used in a small CMS I created in Adobe Director. Is there a way in SQlite Browser to set the field to only accept certain data, I tried to change the field type to NUMERIC but it still let me input text into it

[djr33]

I see what you mean now. A "user" is typically the normal type of web visitor-- they rarely even know what a database is. What you're describing sounds more like an administrator (I know that sometimes the two jobs tend to overlap).
That changes things. You have three options:

1) Create a front end to the database using PHP (or another similar language). In other words, make a user interface because what you have isn't good enough. Best solution, but also the hardest.

2) Train your 'users' to know what they are doing. Anyone who is directly editing a database especially if it's important information should have some idea what they're doing, and asking them to use a specific format for the date isn't too much. If it needs to be more 'user friendly' than that, consider option (1).

3) Change the format of the date stored in the database. You can just use a text field (which may happen to contain date info) to store any format you'd like. But of course if you're doing to use this later, especially computationally, they will need to at least be consistent in the format and the best format will be one like you already have that is easy to parse (rather than naming the month, for example).


I'm not sure if SQLite can block certain data since you're not using an official format (as far as I know). In MySQL there's a "timestamp" format which I don't like but does require a certain type of data. (It's a formatted string like you've described, not the unix timestamp.)


The easiest answer to this is to train the people entering the data and explain why it's important to them. If not, you may need to create a user interface. SQLite isn't designed to 'require' certain kinds of data really. It's just designed to store it efficiently. You might be able to get standard data types (number vs. string for example) but if you aren't using a date format it is familiar with that won't work for you.

I hope that helps.

[whitestar7]

Thanks loads for this, it helps me understand the options perfectly, I will see if I can get someone to create a user interface using PHP (not even sure where to start with that!)

[whitestar7]

do you know of any GUI or interfaces I can use which will allow me to do this? I am prepared to purchase software for it

[traq]

Some ideas:

http://www.razorsql.com/ <-- commercial; I've never used it but it has good user reviews

https://addons.mozilla.org/en-US/firefox/addon/5817/ <-- free, I've used it once or twice, but only to create a DB (not to administer it; I used PHP for that)

http://sqliteadmin.orbmu2k.de/ <-- free (haven't even looked at it)

Other than that, if you want to build something, PHP has SQLite extensions:
The SQLite class is compatible with version 2

If you're using version 3 you'll need to use PDO-SQLite. It's an object-oriented class, so it's a little more involved, but it's more reliable. (I actually had to switch to using PDO instead of the SQLite class with a version 2 project.)