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
