Reading whole emails is a skill I haven't fully mastered ;)
Simon pointed out that the records should never contain NULLs. I
previously used a .CSV flat text file to store the data and am
migrating over to sqlite2. It might not be very good database
design, but the reason I do is so the users can add their data, I will
check it and if it is good, then I'll assign them a userID number. So
if that field is blank or NULL, it tells me that I have not manually
validated that record yet.
Not sure if this is a good practice or not, but it has worked good
with the text files and am hoping to be able to do the same thing with
sqlite.
My preference would be for userID as INTEGER PRIMARY KEY AUTOINCREMENT
and a separate column for validation (1=validated, 0 = not validated)
CREATE TABLE mailist (
userID INTEGER PRIMARY KEY AUTOINCREMENT,
validated INTEGER DEFAULT 0,
name TEXT,
email TEXT
);
Mircea
On 09/05/2012 11:38 AM, Shorty wrote:
I now realize that my data entry form was making the Steve entry a
blank string, and that is why the <> '' worked.
Original database
recordID userID name email
1 1 John j...@somewhere.com
2 2 Mike m...@example.com
3 3 Bill b...@example.com
4 '' Steve st...@example.com
SELECT max(userID) AS highest_userID FROM maillist WHERE userID <> ''
SELECT max(userID) AS highest_userID FROM maillist WHERE userID <> ''
AND userID IS NOT NULL
SELECT max(userID) AS highest_userID FROM maillist WHERE (userID <>
'') AND (userID IS NOT NULL)
SELECT max(userID) AS highest_userID FROM maillist WHERE userID NOT IN
('', NULL)
all of these return 3, which is what I am trying to get.
----- HERE IS THE THING I AM AFTER
-----------------------------------------------
I am still interested in finding the max value of userID if that
column does end up with a NULL in there. I added a new record with
just the recordID so the rest of the columns should be NULL. I tried
my original query plus the querys suggested by Simon and they all
failed to produce the desired max value that I am looking for. I'm
running php 5.2 that has sqlite 2.8.17
New Database:
recordID userID name email
1 1 John j...@somewhere.com
2 2 Mike m...@example.com
3 3 Bill b...@example.com
4 '' Steve st...@example.com
5 null null null
SELECT max(userID) AS highest_userID FROM maillist WHERE userID <> ''
SELECT max(userID) AS highest_userID FROM maillist WHERE userID <> ''
AND userID IS NOT NULL
SELECT max(userID) AS highest_userID FROM maillist WHERE (userID <>
'') AND (userID IS NOT NULL)
SELECT max(userID) AS highest_userID FROM maillist WHERE userID NOT IN
('', NULL)
all of the above querys return NULL with the new database, I am trying
to get it to return a 3.
Hmmmm..... any ideas what to try next? Sorry for the noob questions,
already this thread has significantly helped my understanding of
sqlite and I really apreciate the help!
----- WHY I SETUP THE DATABASE LIKE IT IS ----------------
Simon pointed out that the records should never contain NULLs. I
previously used a .CSV flat text file to store the data and am
migrating over to sqlite2. It might not be very good database
design, but the reason I do is so the users can add their data, I will
check it and if it is good, then I'll assign them a userID number. So
if that field is blank or NULL, it tells me that I have not manually
validated that record yet.
Not sure if this is a good practice or not, but it has worked good
with the text files and am hoping to be able to do the same thing with
sqlite.
Thanks
Shorty
> 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?
NULL compares smaller than any other value. So, unless all the values
are NULL, max() won't pick it.
_______________________________________________
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
