On 9 May 2012, at 6:11am, "Shorty" <sho...@shortypen.com> wrote:
> Oh that is interesting !! > > When I created the table I had the following column declarations: > recordID INTEGER PRIMARY KEY, > userID INTEGER UNIQUE, > name CHAR, > email CHAR A quick note that SQLite does not have a CHAR type and is interpreting those as TEXT: <http://sqlite.org/datatype3.html> Do not depend on CHAR behaviour that differs from TEXT behaviour. However, the difference is obscure and I don't think this difference is causing any of your problems. > I thought I read that sqlite 2.8 does recognize INTEGER and then stores those > as numeric values, and not strings. But am wondering if php will convert it > over to all strings anyway since php does funky auto typing on it's own. (or > rather I don't understand the rules that php uses for it's type juggling in > relation to sqlite) SQLite handles INTEGERs as their own thing, different from both REAL and TEXT. Which PHP API are you using to talk to SQLite ? PHP's sqlite3 API does not do autotyping. It works exactly as the documentation for SQLite works. PHP's PDO API does some whacky stuff since it has to talk to many different database systems and has to do something useful for all of them. > Next Question - if I use the statment: > SELECT max(userID) AS 'highest_userID' FROM maillist WHERE userID <> '' > > Would that produce the same results for both NULL numeric entries and empty > strings? First, the single quotes around 'highest_userID' don't do anything. SQLite uses single quotes for text strings, double quotes for items. But you don't need any quotes here. Secondly, NULL isn't a value, it means 'value missing' or 'value unknown' or something like that. It doesn't have a useful sort order because there's no value to sort. So a comparison like '<>' is not useful. If you want to detect nulls and empty strings do two tests: SELECT max(userID) AS highest_userID FROM maillist WHERE userID <> '' AND userID IS NOT NULL or SELECT max(userID) AS highest_userID FROM maillist WHERE (userID <> '') AND (userID IS NOT NULL) or SELECT max(userID) AS highest_userID FROM maillist WHERE userID IN ('', NULL) will all give identical results. However, do you really allow your userID values to be an empty string ? Seems a strange ID to give a user. If my program did that I'd fix the bug, and if I found a database that had some rows with NULL and others with an empty string I'd assume it was corrupt. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
