Results 1 to 7 of 7

Thread: SQLite Databse help for newbie

  1. #1
    Join Date
    Dec 2010
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default SQLite Databse help for newbie

    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

  2. #2
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,164
    Thanks
    265
    Thanked 690 Times in 678 Posts

    Default

    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.
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

  3. #3
    Join Date
    Dec 2010
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    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

  4. #4
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,164
    Thanks
    265
    Thanked 690 Times in 678 Posts

    Default

    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.
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

  5. #5
    Join Date
    Dec 2010
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    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!)

  6. #6
    Join Date
    Dec 2010
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    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

  7. #7
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    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.)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •