AUTOINCREMENT issue in PHP

Question

I was creating AUTOINCREMENT function for my SQLite database but whenever I put AUTOINCREMENT function I get error 'SYNTAX' error, am I doing something wrong?

<?php
$db = sqlite_open("user_indormation.db");

@sqlite_query($db, "DROP TABLE user_info");

sqlite_query($db,
"CREATE TABLE user_info (
 ID                     INTEGER(100) AUTOINCREMENT,
 FirstName              VARCHAR2(10) NOT NULL,
 SurName                VARCHAR2(10) NOT NULL,
 UserName               VARCHAR2(10) NOT NULL,
 CONSTRAINT USER_PRIMARY_KEY PRIMARY KEY (FirstName));
INSERT INTO 'user_info' VALUES('kim','lim','jim');
INSERT INTO 'user_info' VALUES('tom','tim','zim')"
,$sqliteerror);

$result=sqlite_query($db,"SELECT * from user_info");
echo "<table border=1>";

while($row=sqlite_fetch_array($result,SQLITE_NUM ))
{
    echo "<tr>";
    for ($i = 0; $i < sizeof($row)  ; $i++) {
     echo "<td>" . $row[$i] . "</td>"; 
   }
   echo "</tr>";
}
echo "</table>";

sqlite_close($db);
?>

see http://stackoverflow.com/questions/7905859/is-there-an-auto-increment-in-sqlite — , Apr 13 '14 at 10:48
Also, you know that everytime your script gets called, the table is **recreated**? So selecting from it will give you empty results each time.. — , Apr 13 '14 at 10:50
but when ever i remove AUTOINCREMENT syntax everything works — WESTKINz, Apr 13 '14 at 10:59

WESTKINz · Accepted Answer · 2014-04-13T12:21:57.707

2

I mange to figure it out, just change:

ID                     INTEGER(100) AUTOINCREMENT,

to

ID                     INTEGER PRIMARY KEY,

and to insert a value put null for the primary key like this:

INSERT INTO 'user_info' VALUES(NULL,'tom','bob','zim');

or

INSERT INTO user_info (ID,FirstName,Surname,UserName) VALUES(NULL,'tom','bob','zim');

for more info https://sqlite.org/faq.html#q1

edited Apr 13 '14 at 12:21

answered Apr 13 '14 at 11:08

WESTKINz

257
1
6
18

You should always specify the column names in your `INSERT` statement, so it will still work if you change your table structure. In that situation, you can just miss out the PK column, rather than explicitly using `NULL` for it. – halfer Apr 13 '14 at 11:32

score 0 · Answer 2 · answered Apr 13 '14 at 11:14

0

You can use AUTOINCREMENT only with INTEGER PRIMARY KEY. Change the column spec to

ID INTEGER PRIMARY KEY AUTOINCREMENT

answered Apr 13 '14 at 11:14

laalto

150,114
66
286
303

1

i still get "AUTOINCREMENT": syntax error but dont worry i mange to figure it out :) – WESTKINz Apr 13 '14 at 11:20

AUTOINCREMENT issue in PHP

2 Answers2