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