[sqlite] max id mystery
Hello dear comrades!
I hope together we will be able to shed some light on a rather mysterious
occurrences which are plaguing me for the past few days;
I have an sqlite3 db that stores user-accounts (each user has an id); When a
new user wants to add his account to db, he sends
all his data fields as he wants them, but id is left as zero;
then a backend handles the request like this:
if ($php_data-id == 0) {
$query = SELECT MAX(id) AS largestID FROM profiles;
$result_array = $database-arrayQuery($query, SQLITE_ASSOC);
foreach ($result_array[0] as $key = $value)
$max_id = $value;
$new_max_id = $max_id+1;
$query =
'INSERT INTO profiles (birthDate, company, country, email, facebook,
firstName, gender, id, lastName, skype, summary, twitter) ' .
'VALUES (' . $php_data-dateOfBirth . '' .
',' . $php_data-company . '' .
',' . $php_data-country . '' .
',em...@gmail.com' .
',' . $php_data-facebook . '' .
',' . $php_data-firstName . '' .
',' . $php_data-gender . '' .
',' . $new_max_id . '' .
',' . $php_data-lastName . '' .
',' . $php_data-skype . '' .
',' . $php_data-summary . '' .
',' . $php_data-twitter . ');';
}
So, if there are 10 accounts in DB, a new user will have id=11;
The problem is as follows; The first id is 80; when the number of users
reaches 20 ( last id =100), the next id is never incremented! And new users
keep getting id=100,
although the DB primary key is incremented correctly;
Any ideas?
Thanks in advance!!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] max id mystery
Hello
EN I have an sqlite3 db that stores user-accounts (each user has an id);
You say you have a sqlite3 database, but it looks like you are using PHP's
sqlite 2 interface. Unless you have special requirements or limilations, I
would consider using the PDO interface for sqlite3.
Why not let the database handle the generation of user IDs? You seem to be
emulating an auto increment primary key. It is possible to get the last
generated ID back with sqlite_last_insert_rowid (or something similar in
the other interfaces) - it isn't something you need to know in advance.
EN $query = SELECT MAX(id) AS largestID FROM profiles;
EN $result_array = $database-arrayQuery($query, SQLITE_ASSOC);
EN foreach ($result_array[0] as $key = $value)
EN $max_id = $value;
EN $new_max_id = $max_id+1;
It may just be a matter of style, but I wouldn't use a foreach loop to get
one value out of an array. You could do the same with:
$query = SELECT MAX(id) AS largestID FROM profiles;
$result_array = $database-arrayQuery($query, SQLITE_ASSOC);
$new_max_id = $result_array[0][largestID] + 1;
EN $query =
EN 'INSERT INTO profiles (birthDate, company, country, email, facebook,
EN firstName, gender, id, lastName, skype, summary, twitter) ' .
EN 'VALUES (' . $php_data-dateOfBirth . '' .
EN ',' . $php_data-company . '' .
EN ',' . $php_data-country . '' .
EN ',em...@gmail.com' .
EN ',' . $php_data-facebook . '' .
EN ',' . $php_data-firstName . '' .
EN ',' . $php_data-gender . '' .
EN ',' . $new_max_id . '' .
EN ',' . $php_data-lastName . '' .
EN ',' . $php_data-skype . '' .
EN ',' . $php_data-summary . '' .
EN ',' . $php_data-twitter . ');';
EN }
It would be better to bind your pdp_data variables, so that you don't
leave yourself open to SQL injection attacks. The sqlite3 and PDO
interfaces let you do this.
EN The problem is as follows; The first id is 80; when the number of users
EN reaches 20 ( last id =100), the next id is never incremented! And new users
EN keep getting id=100,
EN although the DB primary key is incremented correctly;
Is there a reason for starting at 80?
If you put some error checking in your code, then that should let you know
where the problem is.
Swithun.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] max id mystery
Hello, thanks for many corrections and suggestions;
I am new to php, so i was under the impression it had sqlite3 (now i
see why i cant edit my db from sqlite3 shell); i understand that my
approach is not right ( is shouldn't emulate primary key again)
the problem is (at least i think it is) in the fact that my id was
TEXT and a string 99 was bigger then 100 (sacii-wise?); i was
mislead by the fact that client app treats id as text, so i made it
text in db
2011/10/13, Swithun Crowe swit...@swithun.servebeer.com:
Hello
EN I have an sqlite3 db that stores user-accounts (each user has an id);
You say you have a sqlite3 database, but it looks like you are using PHP's
sqlite 2 interface. Unless you have special requirements or limilations, I
would consider using the PDO interface for sqlite3.
Why not let the database handle the generation of user IDs? You seem to be
emulating an auto increment primary key. It is possible to get the last
generated ID back with sqlite_last_insert_rowid (or something similar in
the other interfaces) - it isn't something you need to know in advance.
EN $query = SELECT MAX(id) AS largestID FROM profiles;
EN $result_array = $database-arrayQuery($query, SQLITE_ASSOC);
EN foreach ($result_array[0] as $key = $value)
EN $max_id = $value;
EN $new_max_id = $max_id+1;
It may just be a matter of style, but I wouldn't use a foreach loop to get
one value out of an array. You could do the same with:
$query = SELECT MAX(id) AS largestID FROM profiles;
$result_array = $database-arrayQuery($query, SQLITE_ASSOC);
$new_max_id = $result_array[0][largestID] + 1;
EN $query =
EN 'INSERT INTO profiles (birthDate, company, country, email,
facebook,
EN firstName, gender, id, lastName, skype, summary, twitter) ' .
EN 'VALUES (' . $php_data-dateOfBirth . '' .
EN ',' . $php_data-company . '' .
EN ',' . $php_data-country . '' .
EN ',em...@gmail.com' .
EN ',' . $php_data-facebook . '' .
EN ',' . $php_data-firstName . '' .
EN ',' . $php_data-gender . '' .
EN ',' . $new_max_id . '' .
EN ',' . $php_data-lastName . '' .
EN ',' . $php_data-skype . '' .
EN ',' . $php_data-summary . '' .
EN ',' . $php_data-twitter . ');';
EN }
It would be better to bind your pdp_data variables, so that you don't
leave yourself open to SQL injection attacks. The sqlite3 and PDO
interfaces let you do this.
EN The problem is as follows; The first id is 80; when the number of users
EN reaches 20 ( last id =100), the next id is never incremented! And new
users
EN keep getting id=100,
EN although the DB primary key is incremented correctly;
Is there a reason for starting at 80?
If you put some error checking in your code, then that should let you know
where the problem is.
Swithun.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
