Posted by "Ben Litton" on 10/18/05 21:37

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/

[Back to original message]