SQLite referance

Document Sample
SQLite referance Powered By Docstoc
					                     Creating and Using a Table With SQLite

We have seen how to install and check the software, by creating a database. The next step to use this
database requires we create an SQL table. We have to define the content of the records and build the
structure with an SQL command.


Creating a table

The table that we create will be based on the "posts" table of Wordpress, a simplified version but with
same columns.


  $dbname='base.s3db';
  $mytable ="tablename";

  $base= new SQLiteDatabase($dbname, 0666, $err);
  if ($err) exit($err);

  $query = "CREATE TABLE $mytable(
         ID bigint(20) NOT NULL PRIMARY KEY,
         post_author bigint(20) NOT NULL,
         post_date datetime,
         post_content longtext,
         post_title text,
         guid VARCHAR(255)
         )";

  $results = $base->queryexec($query);


The SQL command CREATE TABLE defines the columns. It is sent to the SQLite manager by the PHP
method queryExec() which returns true or false, depending on whether the operation is successful or
not.

See the code of the script sqlite-create-table.php.


Deleting a table

The deletion of a table is made by the DROP TABLE command of SQL.


  $query = "DROP TABLE $mytable";
  $results = $base->queryexec($query);


See the code sqlite-delete-table.php.


Adding posts

The table of posts that we just created will be filled, like in Wordpress, with the posts we write, each
post corresponding to a row of the table.

The SQL command: INSERT INTO allows to store the data.

                                                Pg 1 of 21
  $number = 1;
  $title="My first post";
  $content="The content of my post...";
  $date = strftime( "%b %d %Y %H:%M", time());
  $author=1;
  $url = "http://www.scriptol.com/sql/sqlite-tutorial.php";

  $query = "INSERT INTO $mytable(ID, post_title, post_content, post_author,
  post_date, guid)
             VALUES ('$number', '$title', '$content', '$author', '$date', '$url')";
  $results = $base->queryexec($query);


For the purposes of the tutorial, we place the contents of the post directly into variables. In practice
these variables will be assigned from an t online or local ext editor, as shown in the CMS tutorial.

The author is represented by the number 1 because Wordpress does not put the names in the posts table
but in a separate table instead.

The guid column contains the URL of the post that also serves as an ID unique.

The INSERT command jas for first parameter the table name and in parentheses the list of columns to
fill, then the parameter VALUE provides a list of values corresponding to the columns in the same order.
Thus, post_title, which contains the titles, will has for value $title, the variable that was assigned the
title of the post.

The same queryExec method is used to send the request.

Source code of the script sqlite-write.php.


Reading a post

You can access the contents of the database with the SELECT command.


  $query = "SELECT post_title, post_content, post_author, post_date, guid FROM
  $mytable";
  $results = $base->arrayQuery($query, SQLITE_ASSOC);
  $arr = $results[0];

  if($results)
  {
     $title = $arr['post_title'];
     $content = $arr['post_content'];
     $user = $arr['post_author'];
     $date = $arr['post_date'];
     $url = $arr['guid'];
  }


To the SELECT command is given the list of columns that you want get the content and lines will be
assigned to $results array. Indeed, the arrayQuery PHP method returns an array of arrays, each
representing a line of the table.
In practice we will use instead other commands that we will see later to limit the resources usage.

                                                 Pg 2 of 21
Data are retrieved in the associative array $arr, where the column names are keys and their content the
values.


                                            SQL Tables

The tables are the transposition into rows and columns of data from a file. It is conceptually easier to
put in rows records of each article and in column information relating to each. You can add items by
adding rows, but adding columns constitutes a change in the structure of the table.

If our file contains a list of apartments, we will have a row for each apartment and columns correspond
to the information on each, such as the city where it is located, the acquisition date, the name of the
owner...


Creating a Table

The CREATE TABLE command allows to insert a new table in the database.

Format:


  CREATE TABLE name (column, column...)


For each column definition a name is followed by the type of data in the column.

Examples:


  CHAR (10)
  NUMBER (8.2)
  DATE
  VARCHAR (32000)


We shall see in detail the list of data types. We have to know that fixed-size strings have the CHAR type,
while the VARCHAR type is used for texts stored with a variable size, such as posts of blogs.


Parameters of columns

A column could be empty or not. It not, NOT NULL must be added to its definition:


  DATE NOT NULL


Exemple

We will create a database of a real estate agency whose database contains a number of houses and
apartments.
Name of the base: houses.
The database contains a list of houses and apartments. For each item in the list:
- A unique identification code.
- Town.
- Surface.
                                                Pg 3 of 21
- Date of acquisition.
- Name of owner

The table could look as this:

Code City                       Surface Date            Owner
01      Miami                   45     01/01/2007       Del Icio Us
02      Mountain View           70     02/02/2008       Google

In the header was placed names of columns.

We create a table with the column names and the definition of the type of data:


     CREATE TABLE houses(
        code NUMBER(4),
        city CHAR(30),
        surface NUMBER(4),
        date DATE,
        owner CHAR(25)
        )


We have now to fill the table with data from the agency, this will be the topic of the next chapter.


            SQLite Tutorial: Common Commands and Triggers

By Mike Chirico

This article explores common methods in SQLite such as running commands from the shell prompt
and creating triggers for populating time stamped fields with either UTC or local time values. In
addition, delete, update, and insert trigger examples will be explored in detail.

All examples can be found in sqlite_examples.tar.gz (local copy), and I would encourage you to
download and run these examples as you read through this document.

The home page for sqlite3 is www.sqlite.org and the source for sqlite3 can be downloaded at
www.sqlite.org/download.htm. This tutorial was done with the source version 3.0.8

Getting Started - Common Commands

To create a database file, run the command "sqlite3" followed by the database name. For example, to
create the database "test.db" run the sqlite3 command as follows:

        $ sqlite3 test.db
        SQLite version 3.0.8
        Enter ".help" for instructions
        sqlite> .quit
        $

                                                Pg 4 of 21
The database file test.db will be created, if it does not already exist. Running this command will leave
you in the sqlite3 environment. There are 3 ways to safely exit this environment (.q, .quit, or .exit).

You do not have to enter the sqlite3 interactive environment. Instead, you could perform all commands
at the shell prompt, which is ideal when running bash scripts and commands in an ssh string. Below is
an example of how you would create a simple table from the command prompt.

       $ sqlite3 test.db "create table t1 (t1key INTEGER
                 PRIMARY KEY,data TEXT,num double,timeEnter DATE);"

After table t1 has been created, data can be inserted as follows:

     $ sqlite3 test.db "insert into t1 (data,num) values ('This is sample
  data',3);"
     $ sqlite3 test.db "insert into t1 (data,num) values ('More sample
  data',6);"
     $ sqlite3 test.db "insert into t1 (data,num) values ('And a little
  more',9);"

As expected, doing a select returns the data in the table. Note, the primary key "t1key" auto
increments; however, there are no default values for timeEnter. To populate the timeEnter field with
the time, an update trigger is needed. An important note on the PRIMARY KEY: do not use the
abbreviated "INT" when working with the PRIMARY KEY. You must use "INTEGER", for the
primary key to update.

       $ sqlite3 test.db "select * from t1 limit 2";
       1|This is sample data|3|
       2|More sample data|6|

In the statement above, the limit clause is used and only 2 rows are displayed. For a quick reference of
SQL syntax statements available with SQLite, see the link syntax. There is an offset option to the limit
clause. For instance, the third row is equal to the following: "limit 1 offset 2".

       $ sqlite3 test.db "select * from t1 order by t1key limit 1 offset 2";
       3|And a little more|9|

The ".table" command shows the table names. For a more comprehensive list of tables, triggers, and
indexes created in the database, query the master table "sqlite_master" as shown below.

       $ sqlite3 test.db ".table"
       t1

       $ sqlite3 test.db "select * from sqlite_master"
       table|t1|t1|2|CREATE TABLE t1 (t1key INTEGER
                 PRIMARY KEY,data TEXT,num double,timeEnter DATE)
                                                Pg 5 of 21
All SQL information and data inserted into a database can be extracted with the ".dump" command.

       $ sqlite3 test.db ".dump"
       BEGIN TRANSACTION;
       CREATE TABLE t1 (t1key INTEGER
                 PRIMARY KEY,data TEXT,num double,timeEnter DATE);
       INSERT INTO "t1" VALUES(1, 'This is sample data', 3, NULL);
       INSERT INTO "t1" VALUES(2, 'More sample data', 6, NULL);
       INSERT INTO "t1" VALUES(3, 'And a little more', 9, NULL);
       COMMIT;

The contents of the ".dump" can be filtered and piped to another database. Below table t1 is changed to
t2 with the sed command, and it is piped into the test2.db database.

        $ sqlite3 test.db ".dump"|sed -e s/t1/t2/|sqlite3 test2.db

Triggers

An insert trigger is created below in the file "trigger1". The Coordinated Universal Time (UTC) will be
entered into the field "timeEnter", and this trigger will fire after a row has been inserted into the table
t1. Again, this trigger will fire after the row has been inserted.

     --
  **********************************************************
  **********
     -- Creating a trigger for timeEnter
     --   Run as follows:
     --       $ sqlite3 test.db < trigger1
     --
  **********************************************************
  **********
     CREATE TRIGGER insert_t1_timeEnter AFTER INSERT ON t1
     BEGIN
      UPDATE t1 SET timeEnter = DATETIME('NOW') WHERE rowid =
  new.rowid;
     END;
     --
  **********************************************************
  **********

The AFTER specification in ..."insert_t1_timeEnter AFTER..." is necessary. Without the AFTER
keyword, the rowid would not have been generated. This is a common source of errors with triggers,
since AFTER is NOT the default, so it must be specified. In summary, if your trigger depends on
                                                Pg 6 of 21
newly created data, in any of the fields from the created row, which was the case for us in this example
since we need the rowid, then, the AFTER specification is needed. Otherwise, the trigger is a BEFORE
trigger, and will fire before rowid or other pertinent data is entered into the field.

Comments are preceded by "--". If this script was created in the file "trigger1", you could easily
execute this script as follows.

       $ sqlite3 test.db < trigger1

Now try entering a new record as before, and you should see the time in the field timeEnter.

     sqlite3 test.db "insert into t1 (data,num) values ('First entry with
  timeEnter',19);"

Doing a select reveals the following data:

       $ sqlite3 test.db "select * from t1";
       1|This is sample data|3|
       2|More sample data|6|
       3|And a little more|9|
       4|First entry with timeEnter|19|2004-10-02 15:12:19

If you look at the statement above, the last value has the timeEnter filled in automatically with
Coordinated Universal Time - or (UTC). If you want local time, then, use select
datetime('now','localtime'). See the note at the end of this section regarding UTC and local time.

For examples that follow the table "exam" and the database "examScript" will be used. The table and
trigger are defined below. Just like the trigger above, UTC time will be used.

     --
  **********************************************************
  *********
     -- examScript: Script for creating exam table
     -- Usage:
     --    $ sqlite3 examdatabase < examScript
     --
     -- Note: The trigger insert_exam_timeEnter
     --      updates timeEnter in exam
     --
  **********************************************************
  *********
     --
  **********************************************************
  *********
     CREATE TABLE exam (ekey         INTEGER PRIMARY KEY,
                  fn     VARCHAR(15),
                                                Pg 7 of 21
                         ln     VARCHAR(30),
                         exam     INTEGER,
                         score   DOUBLE,
                         timeEnter DATE);

       CREATE TRIGGER insert_exam_timeEnter AFTER INSERT ON exam
       BEGIN

     UPDATE exam SET timeEnter = DATETIME('NOW')
          WHERE rowid = new.rowid;
     END;
     --
  **********************************************************
  *********
     --
  **********************************************************
  *********

After the script file, it can be executed, by redirecting the contents of the script file into the sqlite3
command, followed by the database name. See the example below:

       $ sqlite3 examdatabase < examScript
       $ sqlite3 examdatabase "insert into exam (ln,fn,exam,score)
            values ('Anderson','Bob',1,75)"

       $ sqlite3 examdatabase "select * from exam"

       1|Bob|Anderson|1|75|2004-10-02 15:25:00

And, as a check, the PRIMARY KEY and current UTC time have been updated correctly, as seen from
the above example.

Logging All Inserts, Updates, and Deletes

The script below creates the table examlog and three triggers update_examlog, insert_examlog, and
delete_examlog to record update, inserts, and deletes made to the exam table. In other words, anytime
a change is made to the exam table, the changes will be recorded in the examlog table, including the
old value and the new value. By the way if you are familiar with MySQL, the functionality of this log
table is similar to MySQL's binlog. See (TIP 2, TIP 24 and TIP 25) if you would like more information
on MySQL's log file.

     --
  **********************************************************
  *********
     -- examLog: Script for creating log table and related triggers
     -- Usage:
                                                  Pg 8 of 21
   --   $ sqlite3 examdatabase < examLOG
   --
   --
   --
**********************************************************
*********
   --
**********************************************************
*********
   CREATE TABLE examlog (lkey INTEGER PRIMARY KEY,
               ekey INTEGER,
               ekeyOLD INTEGER,
               fnNEW VARCHAR(15),
               fnOLD VARCHAR(15),
               lnNEW VARCHAR(30),
               lnOLD VARCHAR(30),
               examNEW INTEGER,
               examOLD INTEGER,
               scoreNEW DOUBLE,
               scoreOLD DOUBLE,
               sqlAction VARCHAR(15),
               examtimeEnter DATE,
               examtimeUpdate DATE,
               timeEnter     DATE);

  -- Create an update trigger
  CREATE TRIGGER update_examlog AFTER UPDATE ON exam
  BEGIN

   INSERT INTO examlog (ekey,ekeyOLD,fnOLD,fnNEW,lnOLD,
                lnNEW,examOLD,examNEW,scoreOLD,
                scoreNEW,sqlAction,examtimeEnter,
                examtimeUpdate,timeEnter)

        values (new.ekey,old.ekey,old.fn,new.fn,old.ln,
             new.ln,old.exam, new.exam,old.score,
             new.score, 'UPDATE',old.timeEnter,
             DATETIME('NOW'),DATETIME('NOW') );

  END;
  --
  -- Also create an insert trigger
  -- NOTE AFTER keyword ------v
  CREATE TRIGGER insert_examlog AFTER INSERT ON exam
  BEGIN
  INSERT INTO examlog (ekey,fnNEW,lnNEW,examNEW,scoreNEW,
                 sqlAction,examtimeEnter,timeEnter)

                                Pg 9 of 21
               values (new.ekey,new.fn,new.ln,new.exam,new.score,
                    'INSERT',new.timeEnter,DATETIME('NOW') );

      END;

      -- Also create a DELETE trigger
      CREATE TRIGGER delete_examlog DELETE ON exam
      BEGIN

      INSERT INTO examlog (ekey,fnOLD,lnNEW,examOLD,scoreOLD,
                   sqlAction,timeEnter)

               values (old.ekey,old.fn,old.ln,old.exam,old.score,
                    'DELETE',DATETIME('NOW') );

     END;
     --
  **********************************************************
  *********
     --
  **********************************************************
  *********

Since the script above has been created in the file examLOG, you can execute the commands in sqlite3
as shown below. Also shown below is a record insert, and an update to test these newly created
triggers.

      $ sqlite3 examdatabase < examLOG

      $ sqlite3 examdatabase "insert into exam
                       (ln,fn,exam,score)
                      values
                       ('Anderson','Bob',2,80)"

      $ sqlite3 examdatabase "update exam set score=82
                        where
                      ln='Anderson' and fn='Bob' and exam=2"



Now, by doing the select statement below, you will see that examlog contains an entry for the insert
statement, plus two updates. Although we only did one update on the command line, the trigger
"insert_exam_timeEnter" performed an update for the field timeEnter -- this was the trigger defined in
"examScript". On the second update we can see that the score has been changed. The trigger is
working. Any change made to the table, whether by user interaction or another trigger is recorded in
the examlog.


                                              Pg 10 of 21
    $ sqlite3 examdatabase "select * from examlog"

   1|2||Bob||Anderson||2||80||INSERT|||2004-10-02 15:33:16
   2|2|2|Bob|Bob|Anderson|Anderson|2|2|80|80|UPDATE||2004-10-02
  15:33:16|2004-10-02 15:33:16
   3|2|2|Bob|Bob|Anderson|Anderson|2|2|82|80|UPDATE|2004-10-02
  15:33:16|2004-10-02 15:33:26|2004-10-02 15:33:26

Again, pay particular attention to the AFTER keyword. Remember by default triggers are BEFORE, so
you must specify AFTER to insure that all new values will be available, if your trigger needs to work
with any new values.

UTC and Local time

Note, select DATETIME('NOW') returns UTC or Coordinated Universal Time. But select
datetime('now','localtime') returns the current time.

       sqlite> select datetime('now');
       2004-10-18 23:32:34

       sqlite> select datetime('now','localtime');
       2004-10-18 19:32:46

There is an advantage to inserting UTC time like we did with the triggers above, since UTC can easily
be converted to local time after UTC has been entered in the table. See the command below. By
inserting UTC, you avoid problems when working with multiple databases that may not share the same
time zone and or daylight savings time settings. By starting with UTC, you can always obtain the local
time.
(Reference: Working with Time)

       CONVERTING TO LOCAL TIME:

       sqlite> select datetime(timeEnter,'localtime') from exam;


Other Date and Time Commands
If you look in the sqlite3 source file "./src/date.c", you will see that datetime takes other options. For
example, to get the local time, plus 3.5 seconds, plus 10 minutes, you would execute the following
command:

     sqlite> select datetime('now','localtime','+3.5 seconds','+10
  minutes');
     2004-11-07 15:42:26

It is also possible to get the weekday where 0 = Sunday, 1 = Monday, 2 = Tuesday ... 6 = Saturday.
                                                 Pg 11 of 21
         sqlite> select datetime('now','localtime','+3.5 seconds','weekday
  2');
         2004-11-09 15:36:51

The complete list of options, or modifiers as they are called in this file, are as follows:

         NNN days
         NNN hours
         NNN minutes
         NNN.NNNN seconds
         NNN months
         NNN years
         start of month
         start of year
         start of week
         start of day
         weekday N
         unixepoch
         localtime
         utc

In addition, there is the "strftime" function, which will take a time string, and convert it to the specified
format, with the modifications. Here is the format for this function:

       ** strftime( FORMAT, TIMESTRING, MOD, MOD, ...)
       **
       ** Return a string described by FORMAT. Conversions as follows:
       **
       ** %d day of month
       ** %f ** fractional seconds SS.SSS
       ** %H hour 00-24
       ** %j day of year 000-366
       ** %J ** Julian day number
       ** %m month 01-12
       ** %M minute 00-59
       ** %s seconds since 1970-01-01
       ** %S seconds 00-59
       ** %w day of week 0-6 sunday==0
       ** %W week of year 00-53
       ** %Y year 0000-9999

Below is an example.

    sqlite> select strftime("%m-%d-%Y %H:%M:%S %s %w
  %W",'now','localtime');
                                                 Pg 12 of 21
       11-07-2004 16:23:15 1099844595 0 44


Simple Everyday Application
Keeping Notes in a Database

This simple bash script (part of the sqlite_examples tarball) allows you to take notes. The notes consist
of a line of text followed by an optional category without the additional typing.

   "sqlite3 <database> <sql statement>",

Instead, it is a simple one letter command.

       $ n 'Take a look at sqlite3 transactions -
            http://www.sqlite.org/lang.html#transaction' 'sqlite3'

The above statement enters the text into a notes table under the category 'sqlite3'. Anytime a second
field appears, it is considered the category. To extract records for the day, I enter "n -l", which is
similar to "ls -l", to "note list".

With just "n" help is listed on all the commands.

       $n
       This command is used to list notes in
       a database.

       n <option>
        -l list all notes
        -t list notes for today
        -c list categories
        -f <search string> search for text
        -e <cmd> execute command and add to notes
        -d delete last entry


                                            Starting out


Here is where you should start off, however, you will need basic PHP knowledge to start off. If you don't
have any or very little PHP knowledge I suggest you go through this tutorial and come back once you
understand.

Now, you will also need to sign up for a 110MB hosting account, which is free if you have not already.

Okay, I think we are set and ready to go.

                                                 Pg 13 of 21
First we need to actually connect to the SQLite database, you don't actually have to make a .db file, it does
it automatically.


$con = sqlite_open("database.db");


That will set the variable $con as a handle, which will allow us to manipulate the database later with the
sqlite_query function.

Before we move on to actually querying into the database, I should show you a few more things about
sqlite_open, that can help debug if need be.


$con = sqlite_open("database.db", $mode, $error_msg);


Obviously database.db is the path to the database file, $mode is the mode of the file 0666 is the default,
this is like Linux File Permissions, 0666 wouldn't/shouldn't allow you to write to the file, however, it is
currently ignored by the PHP Function, so you can just leave the variable $mode as is. As for debugging
information, $error_msg is where the error message is stored, I recommend doing something like this to
echo the error message if one is set:


if(!empty($error_msg))
  echo $error_msg;


If you do not fully understand how this works after this, you can check out the PHP page for sqlite_open


                                           Data Types


What is a Data Type? I don't know how to be more explanatory then this, a data type is, well a type of data,
such as an integer (a whole number), a text string, a boolean (true or false).



This is also where MySQL and SQLite are very similar, while SQLite does not have all the MySQL Data types,
it has more then enough to get by.



VARCHAR(NUMBER) - VARCHAR stands for various character(s), simply replace NUMBER with how long
you want to allow the string to be, such as, if you replace NUMBER with 3, you can only have a string length
of up to 3, but it can be shorter.

NVARCHAR(NUMBER) - This is much like the VARCHAR Data type, however it is used to store Unicode
Data, other then that, treat it like VARCHAR

TEXT - TEXT is quite self explanatory, you use this to store text strings, such as posts, page contents, and
such.



INT(NUMBER) - INT is a Integer, a whole number, like VARCHAR, NUMBER is length of the number string.

FLOAT(m.n) - This is how you can store numbers that are not whole, such as 1.1, 100.003, you may need
to use the round function so the numbers won't be displayed as 1.10000000...


                                                 Pg 14 of 21
BOOLEAN - True or false, 0 is treated as false, and 1 or higher is true

CLOB - Coming Soon!

BLOB – Stored exactly as entered, used for images, etc.

TIMESTAMP - Every time the row gets updated, so does this.

NUMERIC(NUM, NUM2 [,NUM3[,...]]) - Separate all numbers you want to allow in this row with commas.

VARYING CHARACTER (NUMBER) - From what I understand, this is like VARCHAR, so refer to the
VARCHAR Data type above.




                                 SQLite CREATE TABLE


This is where we will first learn the sqlite_query function.




This is of course one of the most important things in SQL, a table, which has a form, that you store data into
rows, which have columns, of different types of data, known as data types.

How do you use the sqlite_query function?


$result = sqlite_query($con, $query, $result_type, $query_error);


$con is the database handle that we set with sqlite_open in the previous page, $query is the SQLite Query
that will be ran through the database, such as creating a table, inserting, updating, and removing data.
$result_type is the type of result you will receive, I will bring more on this at a later time as I do not
understand it yet, you can leave this part blank, or leave it as $result_type, and of course, the debug
information, $query_error which if an error has occurred, can be echoed to get the error information.

Lets get to the point of this page now shall we? Here is an example SQLite CREATE TABLE Query


CREATE TABLE tbl_name (
   user_id INT default '0',
   username VARCHAR(25),
   password TEXT
);


You can copy this, and run it through sqlite_query, but I will show you that in a few, let me explain this...

CREATE TABLE is how you tell it, hey, I am going to create a table in you!   now, in between the () are the
columns, user_id is defined as an INT, with a default of 0, username is a VARCHAR that can be up to 25
characters in length, and password is a text string.

Here is a warning, transitioning from MySQL or even MSSQL to SQLite can be tough, I need to be sure to let
you know, You CANNOT do `table` or `column` or anything with a ` in your SQLite Queries, except of
course in the data you may storing into it, if you do use ` around columns, or tables you will get an error!

Here is what the full code would look like, including the sqlite_open function.


                                                   Pg 15 of 21
/* Connect to the SQLite Database */
$con = sqlite_open("database.db", $mode, $error_msg);

// Check if there is an error...
if(!empty($error_msg))
  die($error_msg);

/* Okay, now, the query */
sqlite_query($con, "CREATE TABLE tbl_name (
  user_id INT default '0',
  username VARCHAR(25),
  password TEXT
)", $result_type, $query_error);

// Hmmm, was it successful?
if(!empty($query_error))
  die($query_error);

echo 'Created the table successfully!';


And you have successfully pulled off your first SQLite Query if all you get is Created the table successfully!,
now you can move on to SQLite INSERT, but if you are having troubles, you can get help in our forum




                                          SQLite INSERT


Now that you have learned how to create an SQLite table, you now would like to learn how to put things in
wouldn't you?




Before we move on, you might want to make a quick detour to the SQL Injection page, as you might have a
Little Bobby Tables come along one day... So, take a look and come back here...

If you know MySQL/MSSQL, you will probably already know how this works, but to those that don't know, I
don't think it is that hard still.

Okay, lets say we have a SQLite Table made with this SQLite Query: [Refer to CREATE TABLE]


CREATE TABLE info (
   variable TEXT,
   value TEXT,
   time INT(10) default '0'
);


Now here is what an SQLite INSERT might look like for the table above.


/* $con is obviously the SQLite Handle */
$time = time();
sqlite_query($con, "INSERT INTO info (variable, value, time) VALUES('site_name','NoSQL','$time')", $result_
type, $query_error);
if(!empty($query_error))
                                                  Pg 16 of 21
 echo $query_error;
else
 echo "Query Successful!";


Once you get Query Successful! That means the row with the variable of site_name has a value of NoSQL,
and time is set to the current 10 INT time stamp (Refer to time)

Break it down
INSERT INTO info - Should I have to explain INSERT INTO? It means it is Inserting into the table info, or
table you choose.

(variable, value, time) - This defines what rows you will be setting, you can exclude the variable column, the
value column, or the time column, or as many as you want, in fact, this this is optional, at least on this exact
query, I will talk more about that later though.

VALUES('site_name','NoSQL','$time') - VALUES is the values you are going to set for that row, it must be in
the order of what we set before (The (variable, value, time) declaration)

Simple? If you are having troubles, you can always get help in our forum

As I was saying before, the (variable, value, time) declaration is not needed, as long as you are going to set
all columns of the table, you can do this:


/* $con is obviously the SQLite Handle */
$time = time();
sqlite_query($con, "INSERT INTO info VALUES('site_name','NoSQL','$time')", $result_type, $query_error);
if(!empty($query_error))
  echo $query_error;
else
  echo "Query Successful!";


However, I should say, I do not recommend doing this, because if you alter the table such as changing or
adding a column, you may get issues, though of course you can easily fix it, just less error prone.




                                       SQLite UPDATE


Now it is time to learn SQLite UPDATE, which is of course quite useful for when you need to change
something in the row.

Here is an example:

/* $con is the SQLite Handle */
sqlite_query($con, "UPDATE table SET title = 'New Title', content = 'New Content or w/e here!'
WHERE id = 1", $result_type, $query_error);
if(!empty($query_error))
  echo $query_error;
else
  echo 'Update Successful!';


Break it down
UPDATE table SET - UPDATE, its the command to update, replace table with the table you want to update,
and SET is another reserved word.


                                                  Pg 17 of 21
title = 'New Title', content = 'New Content or w/e here!' - This is where you do like "column_to_update =
'What you want the new value to be'", you can update more then 1 column by separating them by commas.

WHERE id = 1 - This is where you choose what rows you want to be updated... If you want to update 1
specific row, and it has a similar row as another, you will need to find something that is unique to that row.
You can also do WHERE id = 1 AND another_col = 'something else', you can also do OR as well.


                                        SQLite SELECT


SELECT is something you really need to know, because without it, whats the point of using SQL if you can't
get it out?

Unlike other SQL Commands, this requires a few extras to get the data out.

Here is an example:

/* $con is the SQLite Handle */
$result = sqlite_query($con, "SELECT * FROM table", $result_type, $query_error);
/* But thats not all */
while($row = sqlite_fetch_array($result)) {
  /*
   The variable $row is accessible for each individual row
   If you have a column id, you can do echo $row['id'];
   and it will echo that rows ID, you can do it for any column
  */
}


Break it down
SELECT * FROM table - This is the SELECT Statement, this will select all columns (Thats what the * means)
from all rows in the tables. You can refine this by doing SELECT * FROM table WHERE id = 1, of course not
always WHERE id = 1, but you can indeed use the WHERE statement in that query to pull out specifics.

sqlite_fetch_array($result) - This function fetches the row from $result (Which was set to the sqlite_query),
once it has gone through them all, it returns false, which stops it.

Here is a more specific tutorial on the * in SELECT * FROM, lets say you had this SQLite Table:




  CREATE TABLE pages (
    page_id INT,
    title TEXT,
    content TEXT
  );


And all you wanted to get from the table was title and content, it would look like this...


/* $con is the SQLite Handle */
$result = sqlite_query($con, "SELECT title, content FROM table WHERE page_id = 1");
while($row = sqlite_fetch_array($result)) {
  echo $row['title'];
  echo $row['content'];
}

                                                  Pg 18 of 21
Thats it! And if you were to do echo row['page_id']; you would get nothing, as you did not select that
column.




                                              sqlite_exec


SQLite is above to actually execute more then 1 query in a sqlite_query, however, I think sqlite_exec is a
better option to parse multiple queries into the database.

sqlite_exec won't return anything (as in, its useless with SELECT) except a bool (true or false), the only
thing is, if there is one error, it stops, and will not continue!

Here is how you use sqlite_exec:

bool = sqlite_exec($con, $query [, $error_msg]);


bool - You can but a variable here, if it returns true, then it was successful, if false, it failed

$con - The SQLite Database Connection Handle (Refer to Starting Out)

$query - The SQLite Query (or multiple) you want it to ran against the database

$error_msg - This will return the SQLite Query error if one did occur, this is optional

Now an example of how you might use it


/* $con is the SQLite Handle */
$query = "
CREATE TABLE pages (
  page_id INT,
  title TEXT,
  content TEXT
);
INSERT INTO pages (page_id, title, content) VALUES(1, 'Hello','Hello! Its a page!');";
$success = sqlite_exec($con, $query, $error);
if($success)
  echo "Query Successful!";
else
  echo "Error: ", $error;


Of course if you get Query Successful! then it was all done right. If you may be wondering, why would I
need this? Well, it is great for importing an SQLite export file, such as from phpLiterAdmin although you can
also import easily through phpLiterAdmin.


                          sqlite_fetch_array Advanced


In MySQL, there is another array fetching function for when you SELECT something from the database,
called mysql_fetch_assoc(); although SQLite does not have a function sqlite_fetch_assoc() it is indeed
possible with SQLite.

                                                     Pg 19 of 21
Whats the difference?
The difference between fetch_array and fetch_assoc is, well, fetch_array also returns a numbered variable
for the column, lets see if I can explain better.

Say you have the columns title and content, and you did sqlite_fetch_array($result) you could echo the
contents not only by $row['title'] and $row['content'], but you can also do $row[0] and $row[1], $row[0]
would be the title column, and $row[1] would be the content column. However, with fetch_assoc, it does not
allow you to get it numerically.

How to use it
As said, SQLite does not have a pre made function sqlite_fetch_assoc, but you can make sqlite_fetch_array
do just that.

Here is how:

/* $result = sqlite_query($con, "SELECT Query"); */
while($row = sqlite_fetch_array($result, SQLITE_ASSOC)) {
  // Get Data ;)
}


So all you are doing is adding another parameter SQLITE_ASSOC.


                                      sqlite_num_rows

                                         SQL Injection


SQL Injection is something that should be taken seriously, as if you don't know what you are doing, you are
very likely to get hacked, or maybe all your data deleted.

WikiPedia defines SQL Injection as:


Quote
SQL injection is a technique that exploits a security vulnerability occurring in the database layer
of an application. The vulnerability is present when user input is either incorrectly filtered for
string literal escape characters embedded in SQL statements or user input is not strongly typed
and thereby unexpectedly executed. It is in fact an instance of a more general class of
vulnerabilities that can occur whenever one programming or scripting language is embedded
inside another.


We don't want a Little Bobby Tables on our hands, which might DROP or DELETE everything in your tables,
but how can you keep yourself safe?

You can keep yourself safe with sqlite_escape_string, which will escape anything needed, specially designed
for SQLite. This function is super easy to use


$str = "Hello! Don't try to hack me!";
$str = sqlite_escape_string($str);
echo $str;


                                                Pg 20 of 21
That would output: "Hello! Don''t try to hack me!", however, there are other ways of sanitizing your inputs
(Not always as secure though), I always use this function that I have created, much like htmlspecialchars,
although I like mine better


function clean($str) {
  $replace = array(
    '&' => '&amp;',
    '"' => '&quot;',
    "'" => '&#39;',
    '<' => '&lt;',
    '>' => '&gt;'
  );
  $str = str_replace(array_keys($replace), array_values($replace), $str);
  return $str;
}


If you used this function, it would out this for our string "Hello! Don't try to hack me!", "Hello! Don&#39;t
try to hack me!", however, you would only see the &#39; if you view the source, otherwise, it would look
just like '




                                                  Pg 21 of 21

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:32
posted:12/2/2011
language:English
pages:21