In this lesson you are going to learn the below stuffs:
1. How to Create and connect to a database in SQLite using PDO.
2. How Create a table in SQLite
3. How to insert values into the SQLite table
4. How to read all records from SQLite table
5. How to search a record in SQLite table
All the above operation using PHP PDO driver not the old mysql driver. The beauty of using PDO driver is, you can use the same PHP script for SQLite database as well as MySQL database, all you have do is change the PDO connection strings respectively.
Let’s write the connection first (connection.php):
<?php
/* Create / Connection to sqlite using PDO and set error mode */
$db = new PDO('sqlite:mydb.sqlite3');
/* end */
/*** if you want to use mysql database then uncomment the below lines and comment the above lines ***/
/*** but you should create the db manually in the mysql server ***/
/* $dsn = 'mysql:dbname=mydb;host=localhost';
$user = 'root';
$password = '';
$db = new PDO($dsn, $user, $password);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);*/
?>
Like I said you can simple switch over the database easily if you use PDO driver, In the above code I have given connection for both SQLite and MySQL, In case if SQLite is not working or not installed in your server you can always use MySQL, just uncomment the MySQL part and comment the SQLite connection. But keep in mind when you open a connection for SQLite, the database will be created automatically but in MySQL you have to create it manually.
1. Okay, now create a table first (create-pdo-sqlite.php):
<?php
try{
include('connection.php');
/* Create a prepared statement */
$stmt = $db -> prepare("CREATE TABLE IF NOT EXISTS mytable (id INTEGER PRIMARY KEY, name TEXT);");
/* execute the query */
if ( $stmt -> execute() )
{
echo "Table is created. Let's Insert some values in that <a href='insert-pdo-sqlite.php'>INSERT</a>";
}
/* close connection */
$db = null;
}
catch (PDOExecption $e){
echo $e->getMessage();
}
?>
2. Insert Records (insert-pdo-sqlite.php)
<?php
if($_POST){
$id = $_POST['id'];
$name = $_POST['name'];
try{
include('connection.php');
/* Create a prepared statement */
$stmt = $db -> prepare("INSERT INTO mytable (id, name) VALUES (:id, :name)");
/* bind params */
$stmt -> bindParam(':id', $id, PDO::PARAM_INT);
$stmt -> bindParam(':name', $name, PDO::PARAM_STR);
/* execute the query */
if( $stmt -> execute() ){
echo "Row Inserted - <a href='read-pdo-sqlite.php'>Read Here</a>";
}
/* close connection */
$db = null;
}
catch (PDOExecption $e){
echo $e->getMessage();
}
}
?>
<html>
<style>
label{width: 50px; float:left;}
</style>
<body>
<form action="" method="post">
<label>Id: </label><input type="text" name="id" /><br>
<label>Name: </label><input type="text" name="name" /><br>
<input type="submit" />
</form>
</body>
3. Read all records from SQLite database (read-pdo-sqlite.php):
<?php
try{
include('connection.php');
/* Create a prepared statement */
$stmt = $db -> prepare("SELECT * from mytable");
/* execute the query */
$stmt -> execute();
/* fetch all results */
$res = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach($res as $row){
extract($row);
echo $id." | ".$name."<br>";
}
/* close connection */
$db = null;
}
catch (PDOExecption $e){
echo $e->getMessage();
}
echo "<br> Search particular row here: <a href='search-pdo-sqlite.php'>SEARCH</a>";
?>
4. Read a particular record, basically a search option (search-pdo-sqlite.php):
<html>
<body>
<form action="" method="post">
<label>Seach by Id: </label><input type="text" name="id" />
<input type="submit" />
</form>
</body>
<?php
if( $_POST ){
$id = $_POST[ 'id' ];
try{
include('connection.php');
/* Create a prepared statement */
$stmt = $db -> prepare("SELECT * from mytable where id = :id");
/* bind param */
$stmt -> bindParam(':id', $id, PDO::PARAM_INT);
/* execute the query */
$stmt -> execute();
/* fetch all results */
$res = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo "Result: <br>";
foreach($res as $row){
extract($row);
echo $id." | ".$name."<br>";
}
/* close connection */
$db = null;
}
catch (PDOExecption $e){
echo $e->getMessage();
}
echo "<br> More tutorials at: <a target='_blank' href='https://theonlytutorials.com'>Theonlytutorials.com</a>";
}
?>
I hope this will give you a clear idea about handling SQLite or MySQL database using PDO driver.
For you convenience, here is the demo and download:
Hi
Thank you. Very Nice and clear
How to update a record in SQLite table in this example.
Regards
Hiii
How to inner join in 3 table in PDO.
very helpful, thank you