or use one of this line to create database at different locations.
// Create (connect to) SQLite database in file
//$my_conn = new PDO('sqlite:my_student.sqlite3');// same path as file execution
//$my_conn = new PDO('sqlite:D:\\sqlite-data\\my_student.db');// different path
$my_conn = new PDO('sqlite:'.dirname(__FILE__).'/test.db'); // same location
We will use $my_conn to execute our query.
Create table
$count=$my_conn->prepare("CREATE TABLE IF NOT EXISTS
student(id integer primary key,
name text,
class text,
mark integer,
gender text
)");
if($count->execute()){
echo " TABLE student created ";
}else{
echo " Not able to create student ";
}
Here we are added the name, class, mark and gender columns of the new record. After successful addition of record SQLite will assign the unique number to the ID column ( ROWID ). This unique number or ID is the next highest available ID of the primary key column. If the existing highest id is 35 then 36 is inserted as ID by SQLite for the new record.
$my_conn = new PDO('sqlite:'.dirname(__FILE__).'/test.db'); // same location
// Set errormode to exceptions
$my_conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
$sql=$my_conn->prepare("INSERT INTO student (name,class,mark,gender)
values('ABC','Four',80,'Female')");
if($sql->execute()){
echo " <br><br>Record added ID: ". $my_conn->lastInsertId();
}else{
print_r($sql->errorInfo());
}
$my_conn = null;
Using PDO with Parameter query
// Create (connect to) SQLite database in file
$my_conn = new PDO('sqlite:my_student.sqlite3');
// Set errormode to exceptions
$my_conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
////////////Collect data/////////////
$name='plus2net';
$mark=50;
$class='Four';
$gender='Female';
///////// End of data collection ///
$sql=$my_conn->prepare("INSERT INTO student (name,class,mark,gender)
values(:name,:class,:mark,:gender)");
$sql->bindParam(':name',$name,PDO::PARAM_STR, 25);
$sql->bindParam(':class',$class,PDO::PARAM_STR, 25);
$sql->bindParam(':mark',$mark,PDO::PARAM_INT, 15);
$sql->bindParam(':gender',$gender,PDO::PARAM_STR, 10);
if($sql->execute()){
echo "Successfully added record ";
echo "<br><br>ID of the new record is : ".$my_conn->lastInsertId();
}
else{
print_r($sql->errorInfo()); // if any error is there it will be posted
$msg=" Database problem, please contact site admin ";
}
Output is here
Successfully added record
ID of the new record is : 36
Adding multiple records
We can get number of records added by using rowCount()
Difference with AUTOINCREMENT & INTEGER PRIMARY KEY
If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a slightly different ROWID selection algorithm is used. The ROWID chosen for the new row is at least one larger than the largest ROWID that has ever before existed in that same table.
With AUTOINCREMENT, rows with automatically selected ROWIDs are guaranteed to have ROWIDs that have never been used before by the same table in the same database.
Example
The highest ROWID is 5. This record is deleted and one more record is added to the same table. The new ROWID wihtout AUTOINCRMENT is 5 but if we use INTEGER PRIMARY KEY AUTOINCREMENT for the id column then ROWID will be 6. ⇓ Download sample script for SQLite with instructions on how to use.