bool type (between MySQL and PHP)

Hello,
I'm having some issues with bool's relating to MySQL.

I am using:

$tvar = true;
$tvar = false;

in PHP, but only the TRUE value gets
accepted by the MySQL insert or update's.

It appears MySQL sees the TRUE value as 1..
because that is what shows up.. and that MySQL
throws an error when false because the query:

UPDATE tablename SET vbool=$tbool

parses to:

UPDATE tablename SET vbool=

which throws a syntax error.

What is the best PHP practice when using bools with
MySQL? Save 1's and 0's instead of true/false?

TIA!,
Donovan



--
D Brooke

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: bool type (between MySQL and PHP)

On Tue, Jan 25, 2011 at 17:05, Donovan Brooke wrote:
>
> What is the best PHP practice when using bools with
> MySQL? Save 1's and 0's instead of true/false?

You could either do an INT(1) DEFAULT 0 or an ENUM('Y','N') to
make it easier on yourself.

--

Network Infrastructure Manager
Documentation, Webmaster Teams
http://www.php.net/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: bool type (between MySQL and PHP)

Daniel Brown wrote:
> On Tue, Jan 25, 2011 at 17:05, Donovan Brooke wrote:
>>
>> What is the best PHP practice when using bools with
>> MySQL? Save 1's and 0's instead of true/false?
>
> You could either do an INT(1) DEFAULT 0 or an ENUM('Y','N') to
> make it easier on yourself.


Thanks Dan,

I think the error would still persist using DEFAULT.. because as
mentioned, I don't think MySQL likes:

UPDATE tablename SET vbool=

...which is not great news for me since I'd like to avoid having to redo
all my bool's in PHP (input checking, updates, add's etc.. ;-) ENUM
would require that route as well I guess.

I suppose I could instead place a string value of "0" in the query
statement, if bool false, perhaps?

I'll try that.

Donovan






--
D Brooke

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: bool type (between MySQL and PHP)

On Tue, Jan 25, 2011 at 17:40, Donovan Brooke wrote:
>
> Thanks Dan,
>
> I think the error would still persist using DEFAULT.. because as mentioned,
> I don't think MySQL likes:
>
> UPDATE tablename SET vbool=
>
> ..which is not great news for me since I'd like to avoid having to redo
> all my bool's in PHP (input checking, updates, add's etc.. ;-) ENUM would
> require that route as well I guess.

Oh, sorry, I must've skimmed that part of the message. If you're
not changing the value from the default, just omit it from the query.
You can't send an empty value. If, for whatever reason, you MUST send
the `vbool` column data in your UPDATE query, then define it: 0 =
false, 1 = true (of course). Two easy workarounds here:


/**
* Method #1 - note the triple-equals. If you set $tvar to anything
* other than boolean true/false (such as 0/1), drop it to
double-equals.
*/
if ($tvar === true) {
mysql_query("UPDATE tablename SET vbool='1'");
}

/**
* Method #2 - do it on the fly with a ternary operator.
*/
$sql = "UPDATE tablename SET vbool='".isset($tvar) && $tvar ?
'1' : '0'."'";

?>

--

Network Infrastructure Manager
Documentation, Webmaster Teams
http://www.php.net/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: bool type (between MySQL and PHP)

Daniel Brown wrote:
> On Tue, Jan 25, 2011 at 17:40, Donovan Brooke wrote:
>>
>> Thanks Dan,
>>
>> I think the error would still persist using DEFAULT.. because as mentioned,
>> I don't think MySQL likes:
>>
>> UPDATE tablename SET vbool=
>>
>> ..which is not great news for me since I'd like to avoid having to redo
>> all my bool's in PHP (input checking, updates, add's etc.. ;-) ENUM would
>> require that route as well I guess.
>
> Oh, sorry, I must've skimmed that part of the message. If you're
> not changing the value from the default, just omit it from the query.
> You can't send an empty value. If, for whatever reason, you MUST send
> the `vbool` column data in your UPDATE query, then define it: 0 =
> false, 1 = true (of course). Two easy workarounds here:
>
> >
> /**
> * Method #1 - note the triple-equals. If you set $tvar to anything
> * other than boolean true/false (such as 0/1), drop it to
> double-equals.
> */
> if ($tvar === true) {
> mysql_query("UPDATE tablename SET vbool='1'");
> }
>
> /**
> * Method #2 - do it on the fly with a ternary operator.
> */
> $sql = "UPDATE tablename SET vbool='".isset($tvar)&& $tvar ?
> '1' : '0'."'";
>
> ?>



Thanks Dan, I'm sure that would work, but it appears I figured out where
I went wrong...

Read below at your own risk of becoming confused. ;-)

As of now, "false" causes MySQL to input the correct value, which is "0".

I was originally trying to initialize/set the var inside the checker:

if (filter_input(INPUT_POST,'f_bool',FILTER_VALIDATE_BOOLEAN)) {
$vbool = $_POST['f_bool'];
} else {

}

The very strange thing is that, though the "else" statement was never
thrown, anything inside the if (true) statement was not enacting... thus
MySQL was getting an empty value, because the variable was never
getting set.

Strange, anyway, I bypassed the filter and now MySQL accepts false
boolean's.

I'll now have to figure out how to correctly validate a boolean!...
(tomorrow) ;-)

Thanks for the assist.

Donovan



--
D Brooke

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php