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! :D 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.


/* 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