[oclug]MySQL question

[Rod Giffin]

On Sun, 2003-01-19 at 17:29, B McKee wrote:
> Thanks guys for the good info - When I hit a head scratcher like that I start 
> questioning everything.  If it's generating a new number for each each row, 
> then I'm not likely to get the results I want.  Sorting and then picking one 
> sounds like a lot of processing for what should be a simple task.  Since the 
> query is to be made by a perl script (that's the next step) I think I'll have 
> perl generate the random number before I pass the query to the database.   It 
> will take a second query to determine the number of rows first, but come to 
> think of it, I don't think I could have done that with a single query anyway.
> Next step? Now I have to figure out why DBI doesn't want to install on my 
> machine....
> Brian
> PS  Your tip re round(x) vs round(x,y) was interesting - the documentation I 
> had stated that round with no number of digits argument rounded off the last 
> decimal place.  You are right though - it instead rounds off all decimal 
> places on my machine.  The link below suggested truncate as a more 
> predicatable result.

You're on the right track Brian.  But I've got something else for you to
consider - especially if you're using this in a program.  I don't want
this to get too complicated so I won't go into the details of what can
occur unless you want me to, or if you run into trouble with it.

In the example you gave, you had a sponsor name with a value of NULL. 
That does not necessarily mean what you expected - and could result in
some logic errors in the translation between SQL and your program.  You
are going to have to trap the value coming from the sponsor name field,
and test it for the content of "NULL" (record 0 was selected), or a
value of NULL (an error occurred... nothing was returned.)

See, in a program (depending on the language), you can initialize a
variable to null.  The variable exists, but doesn't contain anything. 
But in SQL, that is represented by "", by the character '', by the
integer 0, or the float 0.00. (depending on what the field type is).  In
SQL, null means NOTHING like that exists.  If a result set is null, it
means that no records that match the query were returned.  If a field
returns a null, it does not mean the name is empty - it means it doesn't
exist.  The difference is subtle, but important to grasp.

If I just confused you, for a more concise explanation of how it works
see

http://www.mysql.com/documentation/mysql/bychapter/manual_Problems.html#Problems_with_NULL


Rod.