DigitalOmaha — Sun, 23 Oct 2005 20:00:22 +0000

Your syntax choosing available interchanging synonymous keywords in the ANSI SQL92 equate to the same ultimate database script. What you have to be carful of is nested selects (mySQL 4.1+ only).

I hope this helps.

JP Stones — Sat, 10 Sep 2005 11:10:40 +0000

hiya

thanlks for your help

im a bit confused tho

i could not get it to work with the IS NOT NULL it just displayed all the records

got it working this way tho

$query = mysql_query("SELECT mobile FROM subscriber WHERE mobile != 'NULL'");
$mobile = mysql_num_rows($query);

is this not as efficient then?

thanks!

Greg K — Fri, 09 Sep 2005 19:52:29 +0000

Assuming you have it set up something like:

|<strong>ID</strong>|<strong>user</strong>|<strong>email</strong>          |<strong>mobile</strong>      |
   +--+----+---------------+------------+
   | 1|greg|greg@domain.com|<em>NULL</em>        |
  | 2|john|john@domain.com|555-555-2222|
  | 3|mark|<em>NULL</em>           |555-555-3333|
  | 4|rich|rich@domain.com|<em>NULL</em>        |
  | 5|jill|<em>NULL</em>           |555-555-5555|
  | 6|jane|jane@domain.com|555-555-6666|
   +--+----+---------------+------------+

if you are jsut wanting the total number, you can execute:

SELECT count(id) FROM tablename;

to get a single row result, with the only value being a count of # of total rows

SELECT COUNT(email) FROM tablename WHERE email IS NOT NULL

to get a single row result, with the only value being a count of # of rows whose email field is NOT NULL (note, this will not work if the field is just set to an empty string). You can do the same with phone.

Please note, my experience is with mySQL, but something close to these should work in other SQL environments. again these are also if you are just wanting a number, and not needing the data that goes with each one. If you need that, please let me know what language, and if PHP, I could probably write you something.

FYI: the reason that these are better if you want to get purely a count of records is that when you do this, the SQL server only returns 1 piece of data. Where if you use a function for number of row returned, ALL those rows have to be sent to the language. Not such a big deal when it is just a few records, but as your data increases that is more infor that has to be passed.

-Greg

PS. what the crap is up with the editor for here? When i entered this message, and when i go back to try to edit it, there are NO spaces before the |'s in my example above, they are all lined up! But when the message finally posts, it displayed messed up as above!