Re: Re: Inserting NULL Integer Values

Good explanation but I think he wanted to avoid quoting the integers. I may be wrong, but I think not quoting integers is a decent practice because it makes it easier to port your SQL over to a different database if you later decide you must do so. Of course he could just add a single quote to both sides of the string, whether it is empty or not, but if he wants to go without any single quotes, he'll have to use NULL or a numberic value for every column. Everything you said is of course correct and a bit less lazy than my explanation. I would just say that if he didn't want any single quotes at all he could just replace your elses of $value = "'" . $value2 . "'"; with $value = (int) $value1; OR $value1 = intval($value1). If he decies not going to use quotes, it's probably a good idea to make sure it's really an number or it'll break the query. He could also do an is_numeric($value1) to make sure it really is one, but if you don't mind converting an errant string to a zero, casting works fine. As for the double quotes inside the parser thing, I do that too out of laziness on occasion, but try not to. If the OP wants to stick to the gospel and employ quotes he could make your else $value1 = '\'.$value1.\''; _Ben On Tue, 18 Oct 2005 13:42:19 -0400, <tg-php@gryffyndevelopment.com> wrote:

What Ben said is correct, but I'd like to elaborate so you know why it's correct. The INSERT statement you're trying to end up with is: INSERT INTO MYTABLE (column1, column2) VALUES ('somevalue1', 'somevalue2') I'm not sure why it wouldn't work if you ended up with: INSERT INTO MYTABLE (column1, column2) VALUES ('', '') That should work. You can set it so you can't have NULL, but dont know of anything that tells the database not to accept '' as a value (barring triggers or other things that check on insert). Anyway, assuming that the first example is what youre going for, then it sounds like this is what you want if the first value is empty: INSERT INTO MYTABLE (column1, column2) VALUES (NULL, 'somevalue2') So I might try something like this: $value1 = ""; $value2 = "somevalue"; if (is_empty($value1)) { $value1 = "NULL"; } else { $value1 = "'" . $value1 . "'"; } if (is_empty($value2)) { $value2 = "NULL"; } else { $value2 = "'" . $value2 . "'"; } $qid = mysql_query("INSERT INTO MYTABLE (column1, column2) VALUES ($value1, $value2)"); That way, if it's empty, you'll get NULL, otherwise you'll get 'somevalue'. I use double quotes (") PHP variable values (yeah, I know.. some people have issues because it makes everything inside interpret..blah blah..) and use single quotes (') for SQL stuff. Looks like you do the opposite. Whatever works for you. Good luck! -TG = = = Original message = = = Either cast your empty ints (which should make it zero) or do an if (!isset($variable)) $variable = 'NULL'; Ben On Tue, 18 Oct 2005 12:15:41 -0400, "Shaun" <shaunthornburgh@hotmail.com> wrote:

Hi, Up to this point in time I used to construct my insert statements like this $qid = mysql_query('INSERT INTO MYTABLE (

                                column1,
                                column2,
                               ) VALUES (
                                "'.$value1.'",
                                "'.$value2.'"
                               )');

However I understand it is better to remove the quote marks around an insert if the column type is an integer. This is easy to do, however if the $value is empty it causes a mysql error. Has anyone encountered this and found a solution? Thanks for your advice

___________________________________________________________ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com.

-- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/