InfinityQuest - Programming Code Tutorials and Examples with Python, C++, Java, PHP, C#, JavaScript, Swift and more

Menu
  • Home
  • Sitemap

Python Programming Language Best Tutorials and Code Examples

Learn Python Right Now!
Home
PHP
Using an SQLite Database in PHP
PHP

Using an SQLite Database in PHP

InfinityCoder November 29, 2016

You want to use a relational database that doesn’t involve a separate server process.

Use SQLite. This robust, powerful database program is easy to use and doesn’t require running a separate server.

An SQLite database is just a file. Example 10-7 creates an SQLite database, populates it with a table if it doesn’t already exist, and then puts some data into the table.
Example 10-7. Creating an SQLite database

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
<programlisting>$db = new PDO('sqlite:/tmp/zodiac');
 
// Create the table and insert the data atomically
$db-&gt;beginTransaction();
// Try to find a table named 'zodiac'
$q = $db-&gt;query("SELECT name FROM sqlite_master WHERE type = 'table'" .
                 " AND name = 'zodiac'");
// If the query didn't return a row, then create the table
// and insert the data
if ($q-&gt;fetch() === false) {
    $db-&gt;exec(&lt;&lt;&lt;_SQL_
CREATE TABLE zodiac (
  id INT UNSIGNED NOT NULL,
  sign CHAR(11),
  symbol CHAR(13),
  planet CHAR(7),
  element CHAR(5),
  start_month TINYINT,
  start_day TINYINT,
  end_month TINYINT,
  end_day TINYINT,
  PRIMARY KEY(id)
)
_SQL_
);
 
   // The individual SQL statements
   $sql=&lt;&lt;&lt;_SQL_
INSERT INTO zodiac VALUES (1,'Aries','Ram','Mars','fire',3,21,4,19);
INSERT INTO zodiac VALUES (2,'Taurus','Bull','Venus','earth',4,20,5,20);
INSERT INTO zodiac VALUES (3,'Gemini','Twins','Mercury','air',5,21,6,21);
INSERT INTO zodiac VALUES (4,'Cancer','Crab','Moon','water',6,22,7,22);
INSERT INTO zodiac VALUES (5,'Leo','Lion','Sun','fire',7,23,8,22);
INSERT INTO zodiac VALUES (6,'Virgo','Virgin','Mercury','earth',8,23,9,22);
INSERT INTO zodiac VALUES (7,'Libra','Scales','Venus','air',9,23,10,23);
INSERT INTO zodiac VALUES (8,'Scorpio','Scorpion','Mars','water',10,24,11,21);
INSERT INTO zodiac VALUES (9,'Sagittarius','Archer','Jupiter','fire',11,22,12,<?pdf-cr?>21);
INSERT INTO zodiac VALUES (10,'Capricorn','Goat','Saturn','earth',12,22,1,19);
INSERT INTO zodiac VALUES (11,'Aquarius','Water Carrier','Uranus','air',1,20,2,<?pdf-cr?>18);
INSERT INTO zodiac VALUES (12,'Pisces','Fishes','Neptune','water',2,19,3,20);
_SQL_;
 
  // Chop up each line of SQL and execute it
  foreach (explode("\n",trim($sql)) as $q) {
     $db-&gt;exec(trim($q));
  }
  $db-&gt;commit();
} else {
  // Nothing happened, so end the transaction
  $db-&gt;rollback();
}</programlisting>

Because SQLite databases are just regular files, all the precautions and gotchas that apply to file access in PHP apply to SQLite databases.

The user that your PHP process is running as must have permission to read from and write to the location where the SQLite database is.

It is an extremely good idea to make this location somewhere outside your web server’s document root.

If the database file can be read directly by the web server, then a user who guesses its location can retrieve the entire thing, bypassing any restrictions you’ve built into the queries in your PHP programs.
In PHP, the sqlite extension provides regular SQLite access as well as a PDO driver for SQLite version 2. The pdo_sqlite extension provides a PDO driver for SQLite version 3.

If you’re starting from scratch, use the PDO driver for SQLite 3, because it’s faster and has more features. If you already have an SQLite 2 database, consider using the PDO drivers to migrate to SQLite 3.
The sqlite_master table referenced in Example 10-7 is special system table that holds information about other tables—so it’s useful in determining whether a particular table exists yet.

Other databases have their own ways of providing this sort of system metadata.

Share
Tweet
Email
Prev Article
Next Article

Related Articles

Preventing Cross-Site Scripting in PHP
You want to securely display user-entered data on an HTML …

Preventing Cross-Site Scripting in PHP

Getting and Putting Files with FTP in PHP
You want to transfer files using FTP. Use PHP’s built-in …

Getting and Putting Files with FTP in PHP

About The Author

InfinityCoder
InfinityCoder

Leave a Reply

Cancel reply

Recent Tutorials InfinityQuest

  • Adding New Features to bash Using Loadable Built-ins in bash
    Adding New Features to bash Using Loadable …
    June 27, 2017 0
  • Getting to the Bottom of Things in bash
    Getting to the Bottom of Things in …
    June 27, 2017 0

Recent Comments

  • fer on Turning a Dictionary into XML in Python
  • mahesh on Turning a Dictionary into XML in Python

Categories

  • Bash
  • PHP
  • Python
  • Uncategorized

InfinityQuest - Programming Code Tutorials and Examples with Python, C++, Java, PHP, C#, JavaScript, Swift and more

About Us

Start learning your desired programming language with InfinityQuest.com.

On our website you can access any tutorial that you want with video and code examples.

We are very happy and honored that InfinityQuest.com has been listed as a recommended learning website for students.

Popular Tags

binary data python CIDR convert string into datetime python create xml from dict python dictionary into xml python how to create xml with dict in Python how to write binary data in Python IP Address read binary data python tutorial string as date object python string to datetime python

Archives

  • June 2017
  • April 2017
  • February 2017
  • January 2017
  • December 2016
  • November 2016
Copyright © 2021 InfinityQuest - Programming Code Tutorials and Examples with Python, C++, Java, PHP, C#, JavaScript, Swift and more
Programming Tutorials | Sitemap