<html>
<head>
<title>Sqlitei.class.php Example File</title>
<meta name="author" content="Andrea Giammarchi" />
<meta name ="copyright" content="Andrea Giammarchi" />
<meta name ="generator" content="ConTEXT V0.97.5" />
<meta name="code_Language" content="PHP5" />
<meta name ="robots" content="ALL" />
<link rel="shortcut icon" href="/favicon.ico" title="L'icona di identificazione" />
</head>
<body>
<pre><span style="color: #000000">
<span style="color: #0000BB"><?php </span><span style="color: #898989">// 5 <br />// EXAMPLE FILE for Sqlitei.class.php<br />// requires the class file<br /></span><span style="color: #007700">require( </span><span style="color: #DD0000">'Sqlitei.class.php' </span><span style="color: #007700">);<br /><br /></span><span style="color: #898989">// try all these things ...<br /></span><span style="color: #007700">try {<br /> </span><span style="color: #898989">// new Sqlitei instance<br /> </span><span style="color: #0000BB">$connect </span><span style="color: #007700">= &new </span><span style="color: #0000BB">Sqlitei</span><span style="color: #007700">( </span><span style="color: #DD0000">'test.sqlitei' </span><span style="color: #007700">);<br /> <br /> </span><span style="color: #898989">// Uncomment if this is at least second time you're testing this file<br /> /*<br /> // Dopping table ...<br /> $stmt = $connect->prepare("DROP TABLE test");<br /> // execute drop<br /> $stmt->execute();<br /> // close this prepared query<br /> $stmt->close();<br /> // */<br /> <br /> // table creation, prepare the query<br /> </span><span style="color: #0000BB">$stmt </span><span style="color: #007700">= </span><span style="color: #0000BB">$connect</span><span style="color: #007700">-></span><span style="color: #0000BB">prepare</span><span style="color: #007700">(</span><span style="color: #DD0000">"CREATE TABLE test ( id INTEGER PRIMARY KEY, something VARCHAR(255) )"</span><span style="color: #007700">);<br /> </span><span style="color: #898989">// simple error management with try / catch sintax<br /> </span><span style="color: #007700">try {<br /> </span><span style="color: #898989">// execute the prepared query<br /> </span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">execute</span><span style="color: #007700">();<br /> </span><span style="color: #898989">// close this prepared query<br /> </span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">close</span><span style="color: #007700">();<br /> }<br /> catch( </span><span style="color: #0000BB">Exception $e </span><span style="color: #007700">) {<br /> </span><span style="color: #898989">// if somethong on query was wrong, die with a message<br /> </span><span style="color: #007700">die( </span><span style="color: #0000BB">$e</span><span style="color: #007700">-></span><span style="color: #0000BB">getMessage</span><span style="color: #007700">() );<br /> }<br /> <br /> </span><span style="color: #898989">// new prepared query, this time with 2 variables<br /> </span><span style="color: #0000BB">$stmt </span><span style="color: #007700">= </span><span style="color: #0000BB">$connect</span><span style="color: #007700">-></span><span style="color: #0000BB">prepare</span><span style="color: #007700">(</span><span style="color: #DD0000">"INSERT INTO test VALUES( ?, ? )"</span><span style="color: #007700">);<br /> </span><span style="color: #898989">// try to do queries ...<br /> </span><span style="color: #007700">try {<br /> </span><span style="color: #898989">// first table id, start from 1<br /> </span><span style="color: #0000BB">$id </span><span style="color: #007700">= </span><span style="color: #0000BB">1</span><span style="color: #007700">;<br /> </span><span style="color: #898989">// multiple queries generation<br /> </span><span style="color: #007700">for( </span><span style="color: #0000BB">$a </span><span style="color: #007700">= </span><span style="color: #0000BB">0</span><span style="color: #007700">; </span><span style="color: #0000BB">$a </span><span style="color: #007700">< </span><span style="color: #0000BB">100</span><span style="color: #007700">; </span><span style="color: #0000BB">$a</span><span style="color: #007700">++ ) {<br /> </span><span style="color: #898989">// foreach $a insert 2 rows to test slashes on sqlite ...<br /> </span><span style="color: #0000BB">$something </span><span style="color: #007700">= </span><span style="color: #DD0000">'test number '</span><span style="color: #007700">.( </span><span style="color: #0000BB">$a </span><span style="color: #007700">+ </span><span style="color: #0000BB">1 </span><span style="color: #007700">).</span><span style="color: #DD0000">'a with "this" and \'that\' !'</span><span style="color: #007700">;<br /> </span><span style="color: #898989">// send parameters to use for this query<br /> // i = integer value<br /> // s = not int, not double, not bool but everything else ( a string for example )<br /> </span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">bind_param</span><span style="color: #007700">( </span><span style="color: #DD0000">'is'</span><span style="color: #007700">, </span><span style="color: #0000BB">$id</span><span style="color: #007700">++, </span><span style="color: #0000BB">$something </span><span style="color: #007700">);<br /> <br /> </span><span style="color: #898989">// and again, with differents slashes<br /> </span><span style="color: #0000BB">$something </span><span style="color: #007700">= </span><span style="color: #DD0000">"test number "</span><span style="color: #007700">.( </span><span style="color: #0000BB">$a </span><span style="color: #007700">+ </span><span style="color: #0000BB">1 </span><span style="color: #007700">).</span><span style="color: #DD0000">"b with \"this\" and 'that' !"</span><span style="color: #007700">;<br /> </span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">bind_param</span><span style="color: #007700">( </span><span style="color: #DD0000">'is'</span><span style="color: #007700">, </span><span style="color: #0000BB">$id</span><span style="color: #007700">++, </span><span style="color: #0000BB">$something </span><span style="color: #007700">);<br /> }<br /> </span><span style="color: #898989">// execute all prepared queries<br /> // NOTE: with multiple queries this class automatically uses a TRANSACTION<br /> // then if there is an error, its ROLLBACKs queries<br /> </span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">execute</span><span style="color: #007700">();<br /> </span><span style="color: #898989">// close all these queries ...<br /> </span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">close</span><span style="color: #007700">();<br /> }<br /> </span><span style="color: #898989">// ... or give me the error<br /> </span><span style="color: #007700">catch( </span><span style="color: #0000BB">Exception $e </span><span style="color: #007700">) {<br /> die( </span><span style="color: #0000BB">$e</span><span style="color: #007700">-></span><span style="color: #0000BB">getMessage</span><span style="color: #007700">() );<br /> }<br /> <br /> </span><span style="color: #898989">// prepare another query, this time with a SELECT statement<br /> </span><span style="color: #0000BB">$stmt </span><span style="color: #007700">= </span><span style="color: #0000BB">$connect</span><span style="color: #007700">-></span><span style="color: #0000BB">prepare</span><span style="color: #007700">(</span><span style="color: #DD0000">"SELECT * FROM test LIMIT 0, ?"</span><span style="color: #007700">);<br /> </span><span style="color: #898989">// try my operations ...<br /> </span><span style="color: #007700">try {<br /> </span><span style="color: #898989">// limit 0, how many ??? ... <br /> </span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">bind_param</span><span style="color: #007700">( </span><span style="color: #DD0000">'i'</span><span style="color: #007700">, </span><span style="color: #0000BB">10 </span><span style="color: #007700">);<br /> <br /> </span><span style="color: #898989">// really impossible for me to clone the MySQLI bind_result method<br /> // ... it's something like that but you can't send directly undefined variables ...<br /> // then send reult names, it's simple too<br /> </span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">bind_result</span><span style="color: #007700">( </span><span style="color: #DD0000">'id'</span><span style="color: #007700">, </span><span style="color: #DD0000">'text' </span><span style="color: #007700">);<br /> </span><span style="color: #898989">// execute this query<br /> </span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">execute</span><span style="color: #007700">();<br /> </span><span style="color: #898989">// as MySQLI does, you can use fetch() method too ...<br /> </span><span style="color: #007700">while( </span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">fetch</span><span style="color: #007700">() ) {<br /> </span><span style="color: #898989">// then to use binded results, just call this object with those names !<br /> </span><span style="color: #007700">echo </span><span style="color: #DD0000">'ID: '</span><span style="color: #007700">.</span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">id</span><span style="color: #007700">.</span><span style="color: #DD0000">'<br />'</span><span style="color: #007700">; </span><span style="color: #898989">// the id<br /> </span><span style="color: #007700">echo </span><span style="color: #DD0000">"TEXT: {$stmt->text}<br />"</span><span style="color: #007700">; </span><span style="color: #898989">// and the text value !<br /> </span><span style="color: #007700">echo </span><span style="color: #DD0000">'<hr />'</span><span style="color: #007700">;<br /> }<br /> </span><span style="color: #898989">// and close this query<br /> </span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">close</span><span style="color: #007700">();<br /> }<br /> </span><span style="color: #898989">// ... or tell me the error!<br /> </span><span style="color: #007700">catch( </span><span style="color: #0000BB">Exception $e </span><span style="color: #007700">) {<br /> die( </span><span style="color: #0000BB">$e</span><span style="color: #007700">-></span><span style="color: #0000BB">getMessage</span><span style="color: #007700">() );<br /> }<br /> <br /> </span><span style="color: #898989">// however, you can use all default SQLiteDatabase class methods, isn't cool ?<br /> // just query !<br /> </span><span style="color: #0000BB">$query </span><span style="color: #007700">= </span><span style="color: #0000BB">$connect</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">( </span><span style="color: #DD0000">'SELECT * FROM test' </span><span style="color: #007700">);<br /> </span><span style="color: #898989">// for example, the number of rows ...<br /> </span><span style="color: #007700">echo </span><span style="color: #DD0000">'Total Rows = '</span><span style="color: #007700">.</span><span style="color: #0000BB">$query</span><span style="color: #007700">-></span><span style="color: #0000BB">numRows</span><span style="color: #007700">();<br /> <br /> </span><span style="color: #898989">// close database connection, resets all and unset internal SQLiteDatabase class<br /> </span><span style="color: #0000BB">$connect</span><span style="color: #007700">-></span><span style="color: #0000BB">close</span><span style="color: #007700">();<br />}<br /></span><span style="color: #898989">// or tell me what's wrong<br /></span><span style="color: #007700">catch( </span><span style="color: #0000BB">Exception $e </span><span style="color: #007700">) {<br /> die( </span><span style="color: #0000BB">$e</span><span style="color: #007700">-></span><span style="color: #0000BB">getMessage</span><span style="color: #007700">() );<br />}<br /></span><span style="color: #0000BB">?></span></span>
</pre>
</body>
</html>