Basic PHP and SQLite Table Drop Create Insert and Select Commands

.

Basic PHP and SQLite Table Drop Create Insert and Select Commands

This tutorial assumes that you have basics in PHP Programming. Visit this site if you want to learn the basics instead, http://programming-steps.blogspot.my/2014/05/learn-basic-php-programming.html .

0) Starting Up

This tutorial continues from http://php-steps.blogspot.my/2016/02/how-to-quickly-setup-virtual-host.html 

1) Create PHP Script File

File name: initbuddies.php (outlines only)

<?php   try   {     //open the database     // Set errormode to exceptions     //option to drop the table if exists     //create the table if not exits     //insert dummy data if requested     //output the data to a simple html table     // close the database connection       }   catch(PDOException $e)   {     echo 'Exception : '.$e->getMessage();   } ?>

We use GET method to obtain two optional parameters in the URL; droptable  and insertdummydata.

File name: initbuddies.php (full codes)

<?php   try   {     //open the database     $db = new PDO('sqlite:buddies.sdb');     // Set errormode to exceptions     $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);     //option to drop the table if exists     if ((isset($_GET['droptable'])) && ($_GET['droptable']=='true')){         $db->exec("DROP TABLE IF EXISTS tblbuddies");     }     //create the table if not exits     $db->exec("CREATE TABLE IF NOT EXISTS tblbuddies (id INTEGER PRIMARY KEY, name TEXT, email TEXT, photo BLOB)");         echo "tblbuddies created"."<br/>";     echo "<br/>";     //insert dummy data if requested     if ((isset($_GET['insertdummydata'])) &&  ($_GET['insertdummydata']=='true')){     $db->exec("INSERT INTO tblbuddies (name, email, photo) VALUES ('James', 'james@gmail.com', 'data:image/gif;base64,R0lGODlhAQABAIAAAAUEBAAAACwAAAAAAQABAAACAkQBADs=');".                "INSERT INTO tblbuddies (name, email, photo) VALUES ('Kathie', 'kathie@gmail.com', 'data:image/gif;base64,R0lGODlhAQABAIAAAAUEBAAAACwAAAAAAQABAAACAkQBADs=');".                "INSERT INTO tblbuddies (name, email, photo) VALUES ('Larry', 'larry@gmail.com', 'data:image/gif;base64,R0lGODlhAQABAIAAAAUEBAAAACwAAAAAAQABAAACAkQBADs=');".                "INSERT INTO tblbuddies (name, email, photo) VALUES ('May', 'may@gmail.com', 'data:image/gif;base64,R0lGODlhAQABAIAAAAUEBAAAACwAAAAAAQABAAACAkQBADs=');".                "INSERT INTO tblbuddies (name, email, photo) VALUES ('Nick', 'nick@gmail.com', 'data:image/gif;base64,R0lGODlhAQABAIAAAAUEBAAAACwAAAAAAQABAAACAkQBADs=');".                "INSERT INTO tblbuddies (name, email, photo) VALUES ('Olaf', 'olaf@gmail.com', 'data:image/gif;base64,R0lGODlhAQABAIAAAAUEBAAAACwAAAAAAQABAAACAkQBADs=');".                "INSERT INTO tblbuddies (name, email, photo) VALUES ('Lenny', 'lenny@gmail.com', 'data:image/gif;base64,R0lGODlhAQABAIAAAAUEBAAAACwAAAAAAQABAAACAkQBADs=');");     //output the data to a simple html table     echo "<table border=1>";     echo "<tr><td>Id</td><td>Name</td><td>Email</td><td>Photo</td></tr>";     $result = $db->query('SELECT * FROM tblbuddies');     foreach($result as $row)     {       echo "<tr><td>".$row['id']."</td>";       echo "<td>".$row['name']."</td>";       echo "<td>".$row['email']."</td>";       echo "<td><img src='".$row['photo']."' width='50px'/></td></tr>";     }     echo "</table>";     }     // close the database connection     $db = NULL;   }   catch(PDOException $e)   {     echo 'Exception : '.$e->getMessage();   } ?>

OUTCOME.

The script automatically creates a sqlite database file in the containing folder.

.