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@xxxxxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxx>
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/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
