forked from DuajDiaz/Travel-Website
-
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathuse-sqlite.html
239 lines (221 loc) · 9.17 KB
/
use-sqlite.html
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
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<!-- Always force latest IE rendering engine or request Chrome Frame -->
<meta content="IE=edge,chrome=1" http-equiv="X-UA-Compatible">
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<!-- Use title if it's in the page YAML frontmatter -->
<title>Use SQLite and PHP</title>
<link href="/dashboard/stylesheets/normalize.css" rel="stylesheet" type="text/css" /><link href="/dashboard/stylesheets/all.css" rel="stylesheet" type="text/css" />
<link href="//cdnjs.cloudflare.com/ajax/libs/font-awesome/3.1.0/css/font-awesome.min.css" rel="stylesheet" type="text/css" />
<script src="/dashboard/javascripts/modernizr.js" type="text/javascript"></script>
<link href="/dashboard/images/favicon.png" rel="icon" type="image/png" />
</head>
<body class="docs docs_use-sqlite">
<div id="fb-root"></div>
<script>(function(d, s, id) {
var js, fjs = d.getElementsByTagName(s)[0];
if (d.getElementById(id)) return;
js = d.createElement(s); js.id = id;
js.src = "//connect.facebook.net/en_US/all.js#xfbml=1&appId=277385395761685";
fjs.parentNode.insertBefore(js, fjs);
}(document, 'script', 'facebook-jssdk'));</script>
<div class="contain-to-grid">
<nav class="top-bar" data-topbar>
<ul class="title-area">
<li class="name">
<h1><a href="/dashboard/index.html">Apache Friends</a></h1>
</li>
<li class="toggle-topbar menu-icon">
<a href="#">
<span>Menu</span>
</a>
</li>
</ul>
<section class="top-bar-section">
<!-- Right Nav Section -->
<ul class="right">
<li class=""><a href="/applications.html">Applications</a></li>
<li class=""><a href="/dashboard/faq.html">FAQs</a></li>
<li class="active"><a href="/dashboard/howto.html">HOW-TO Guides</a></li>
<li class=""><a target="_blank" href="/dashboard/phpinfo.php">PHPInfo</a></li>
<li class=""><a href="/phpmyadmin/">phpMyAdmin</a></li>
</ul>
</section>
</nav>
</div>
<div id="wrapper">
<div class="hero">
<div class="row">
<div class="large-12 columns">
<h1>Documentation</h1>
</div>
</div>
</div>
<div class="row">
<div class="large-12 columns">
<ul class="sub-nav">
<li>
<a class="pdf" target="_blank" href="/dashboard/docs/use-sqlite.pdf"> Download PDF
<span>use-sqlite.pdf</span>
</a> </li>
</ul>
<article class="asciidoctor">
<h1>Use SQLite and PHP</h1>
<div class="paragraph">
<p>XAMPP comes with built-in support for SQLite, making it easy to get started building database-powered applications with PHP. This guide will walk you through the process of creating and populating a new SQLite database using XAMPP, then accessing the data within it using PHP.</p>
</div>
<div class="paragraph">
<p>Begin by creating and populating a new database using the SQLite command-line client, as below:</p>
</div>
<div class="olist arabic">
<ol class="arabic">
<li>
<p>Open your Windows command prompt by clicking the “Shell” button in the XAMPP control panel.</p>
</li>
<li>
<p>Change to the <em>htdocs\</em> subdirectory of your XAMPP installation directory (typically <em>C:\xampp</em>) and create a new SQLite database file named <em>mydb.sq3</em> with the SQLite command-line client:</p>
<div class="literalblock">
<div class="content">
<pre>cd C:\xampp\htdocs
sqlite3 mydb.sq3</pre>
</div>
</div>
<div class="paragraph">
<p>You should now see an SQLite prompt, as below:</p>
</div>
<div class="imageblock">
<div class="content">
<img src="./images/use-sqlite/image1.png" alt="image1">
</div>
</div>
</li>
<li>
<p>At the <em>sqlite></em> prompt, create a new table to hold your data. In this example, the table is named items and it contains 3 columns, for item ID, item name and item cost. You can use standard CREATE TABLE syntax to create the table, and you can <a href="http://www.sqlite.org/datatype3.html">learn more about SQLite’s built-in datatypes here</a>.</p>
<div class="literalblock">
<div class="content">
<pre>CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT, price REAL);</pre>
</div>
</div>
</li>
<li>
<p>Add some data to the new inventory table using INSERT commands, as shown below:</p>
<div class="literalblock">
<div class="content">
<pre>INSERT INTO items VALUES ('1001', 'Salt', 3.15);
INSERT INTO items VALUES ('1002', 'Pepper', 2.75);
INSERT INTO items VALUES ('1003', 'Eggs', 2.00);
INSERT INTO items VALUES ('1004', 'Bacon', 7.25);
INSERT INTO items VALUES ('1005', 'Milk', 1.15);
INSERT INTO items VALUES ('1006', 'Strawberries', 8.73);
INSERT INTO items VALUES ('1007', 'Cereal', 2.65);</pre>
</div>
</div>
</li>
<li>
<p>You can now also run a SELECT query on the data. For example, the query below returns all items that cost less than $3:</p>
<div class="literalblock">
<div class="content">
<pre>SELECT * FROM items WHERE price < 3.00;</pre>
</div>
</div>
<div class="imageblock">
<div class="content">
<img src="./images/use-sqlite/image2.png" alt="image2">
</div>
</div>
</li>
<li>
<p>Once you’re done using the database, exit it by typing <em>.quit</em> at the <em>sqlite></em> prompt.</p>
</li>
</ol>
</div>
<div class="admonitionblock important">
<table>
<tr>
<td class="icon">
<i class="fa icon-important" title="Important"></i>
</td>
<td class="content">
The database file (in this example, <em>mydb.sq3</em>) contains all your tables and data, so you should remember to back it up regularly.
</td>
</tr>
</table>
</div>
<div class="paragraph">
<p>The previous steps discussed how to create and use an SQLite database using the command-line client. However, more often than not, you’ll be using an SQLite database in combination with a PHP-powered Web application. XAMPP includes the PHP SQLite extension, so doing this is not very difficult at all.</p>
</div>
<div class="paragraph">
<p>To connect to your SQLite database and execute queries on it with PHP, use your text editor to create an example script named <em>sqlite.php</em> in the <em>htdocs</em> subdirectory of your XAMPP installation directory and fill it with the following code:</p>
</div>
<div class="literalblock">
<div class="content">
<pre><?php
$db = new SQLite3('mydb.sq3');
$sql = "SELECT * FROM items WHERE price < 3.00";
$result = $db->query($sql);
while ($row = $result->fetchArray(SQLITE3_ASSOC)){
echo $row['name'] . ': $' . $row['price'] . '<br/>';
}
unset($db);</pre>
</div>
</div>
<div class="paragraph">
<p>The first line of code creates a new SQLite3 object, using the <em>mydb.sq3</em> database file you created earlier. Then, the object’s <em>query()</em> method is used to execute a SELECT query on the database, and the result object’s <em>fetchArray()</em> method is used to iterate over the result set. Adding the SQLITE3_ASSOC parameter to the <em>fetchArray()</em> method tells PHP to return the results as an associative array, making it easy to access individual fields of the result set and display them on a Web page.</p>
</div>
<div class="paragraph">
<p>Once done, save your changes and ensure that your Apache server is running. Then, browse to <a href="http://localhost/sqlite.php" class="bare">http://localhost/sqlite.php</a> to execute the script. You should see something like this:</p>
</div>
<div class="imageblock">
<div class="content">
<img src="./images/use-sqlite/image3.png" alt="image3">
</div>
</div>
<div class="admonitionblock tip">
<table>
<tr>
<td class="icon">
<i class="fa icon-tip" title="Tip"></i>
</td>
<td class="content">
To find out more about SQLite’s powerful features, <a href="http://sqlite.org/docs/">read the SQLite documentation</a>.
</td>
</tr>
</table>
</div>
</article>
</div>
</div>
</div>
<footer>
<div class="row">
<div class="large-12 columns">
<div class="row">
<div class="large-8 columns">
<ul class="social">
<li class="twitter"><a href="https://twitter.com/apachefriends">Follow us on Twitter</a></li>
<li class="facebook"><a href="https://www.facebook.com/we.are.xampp">Like us on Facebook</a></li>
<li class="google"><a href="https://plus.google.com/+xampp/posts">Add us to your G+ Circles</a></li>
</ul>
<ul class="inline-list">
<li><a href="https://www.apachefriends.org/blog.html">Blog</a></li>
<li><a href="https://www.apachefriends.org/privacy_policy.html">Privacy Policy</a></li>
<li>
<a target="_blank" href="http://www.fastly.com/"> CDN provided by
<img width="48" data-2x="/dashboard/images/[email protected]" src="/dashboard/images/fastly-logo.png" />
</a> </li>
</ul>
</div>
<div class="large-4 columns">
<p class="text-right">Copyright (c) 2018, Apache Friends</p>
</div>
</div>
</div>
</div>
</footer>
<!-- JS Libraries -->
<script src="//code.jquery.com/jquery-1.10.2.min.js"></script>
<script src="/dashboard/javascripts/all.js" type="text/javascript"></script>
</body>
</html>