For my next question, it's a lot simpler.

Is it possible to configure a column to accept either an integer
input, an empty field, or NULL?

I'm donig data entry from an HTML page, and I wanted to make some
fields optional. When the $_PUSH array element is included directly,
MySQL complains that an empty field is an incorrect integer value.

I've tried a default field value of NULL, and it doesn't seem to
address the error message that an empty field is an incorrect integer
value.

I can test the ($_PUSH['value']) in an IF statement that bypasses an
update for the field (causing no input), but it gets to be a lengthy
piece of coding just to avoid an error message.

In article <03086980-9f83-493e-9e2e-b81c834b1db3 (AT) y8g2000prd (DOT) googlegroups.com>, Charles <cchamb2 (AT) gmail (DOT) com> wrote:
What do you conceive to be the difference between "an empty field" and NULL?
We're not mind readers here -- we can't see the error message you got unless
you post it. We also can't see your table definition, or the INSERT or UPDATE
statements you're using.
See previous comment.
Need more information before a meaningful answer is possible.

A varchar field would accept those values.

Is there a reason why an empty field (e.g. INSERT INTO words VALUES
(''); ) is supposed to mean something different from NULL? (e.g.
INSERT INTO words VALUES (null); ) ? What are those meanings?

If not, it seems common to translate an empty field in a form input
to NULL in SQL, and to translate NULL in SQL to an empty table cell
on output. That leaves you with the field holding an integer or
NULL, which an integer column will accept if you don't specify NOT
NULL. You could do this the same time you check user input.
You *DO* check user input to prevent SQL injection attacks, don't you?

Is this PHP? PHP doesn't have a built-in $_PUSH array global,
although it's possible you use that variable name on your own. Or
did you mean $_POST (I hope not)?

On Aug 22, 4:50*pm, gordonb.a7... (AT) burditt (DOT) org (Gordon Burditt) wrote:
3.3.4.6 of the Community Server manual says that a NULL field is a
"missing unknown value", whereas an empty field is a missing value.

The code:

<INPUT TYPE="TEXT" NAME="Make" SIZE="15"
MAXLENGTH="30"><BR>Make<BR><BR>

Goes to the processing script and ($_POST['Ford']) generates a string
of "Ford" if that's what is input, and it works fine.

However, the code of:

<INPUT TYPE="TEXT" NAME="cargo_weight" SIZE="6"
MAXLENGTH="6"><br>Cargo Wt<BR><BR>

Goes to the processing script and ($_POST['cargo_weight']) generates
'1000' if that's what is input, and it generates '' <-two single
quotes with no data-> if nothing is input. MySQL for the
corressponding field generates "Incorrect integer value in field "xyz"
at row #whatever.

Yes, with the code of:

function check_input($value)
{
if (get_magic_quotes_gpc())
{
$value = stripslashes($value);
}
if (!is_numeric($value))
{
$value = mysql_real_escape_string($value);
}
return $value;
}

I think I have tried to add:

If (empty($value)) {
$value=NULL;
}

Into the function, but I wasn't sure if it was NULL, 'NULL', OR
"NULL".

Sorry, I got into a hurry this morning. I *do* mean $_POST instead of
$_PUSH.

El 22/08/2011 21:58, Charles escribió/wrote:
You should copy here the exact error message with the numeric error code
plus the exact code that's triggering it. Your vague interpretation of
those is just not as good

I guess you are attempting to insert an empty *string* in your *integer*
column and MySQL server won't let you because it's configured in strict
mode:

CREATE TABLE foo (
bar INT(10) NULL DEFAULT NULL
)
ENGINE=InnoDB;

INSERT INTO foo (bar) VALUES ('');

SQL Error (1366): Incorrect integer value: '' for column 'bar' at row 1

The error is self-explanatory but might be asking why you _can_ insert
non-empty strings as in:

INSERT INTO foo (bar) VALUES ('123');

The explanation is that MySQL casts your data to the target type. With
'' you would normally get 0 and your insertion would complete
successfully... with wrong data! That's why they implemented a new
setting that would prevent this kind of data corruption:

http://dev.mysql.com/doc/refman/5.1/...alid-data.html


Back to your question, you must be aware that you are dealing with three
different languages: HTML, the server-side language (you don't say which
one it is but it resembles PHP) and MySQL. That means that you sometimes
need to convert your data and empty values is one of the situations. In
PHP and MySQL you have NULL:

$foo = NULL;
INSERT INTO foo (bar) VALUES (NULL);

.... but not in HTML (HTML forms only support strings). You can do
something like this (assuming PHP+PDO):

if( isset($_POST['bar']) && is_numeric($_POST['bar']) ){
$bar = (int)$_POST['bar'];
}else{
$bar = NULL;
}

$res = $conn->prepare('INSERT INTO foo (bar) VALUES (:bar)');
$res->execute(array('bar' => $bar));


The most likely reason is that your code is not correct.


Certainly, shorter code that allows to insert bogus data would be a
bonus ;-P


--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

On 8/22/2011 9:46 PM, Charles wrote:
An empty field is a field with a blank value. An NULL field is a field
with no value or an unknown value (NULL is not a value).

An example might be a middle name. Some people do not have middle
names, in which case the field would contain an empty string ('').
However, some people may not answer the question - in which case you
don't know if they have a middle name or not. In such a case you would
use NULL.

Also, as you've been told before, you need to validate your data based
on the column type and valid contents, not the input data.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

In article <34930982-40ad-4245-bda6-e004fa1ce152 (AT) b9g2000prd (DOT) googlegroups.com>,
Charles <cchamb2 (AT) gmail (DOT) com> wrote:
You might want to look at the NULLIF() function in MySQL:

$sql = sprintf("INSERT INTO mytable (..., myfield, ...) VALUES (..., NULLIF('%s',''), ...)",
mysql_real_escape_string($myVariable,$db));

Then if $myVariable is empty, the NULLIF will convert it to a NULL.
This will work even if myfield is a numeric type, as the NULLIF will weed
out the empty string, and non-empty numeric strings will be converted.
Depending on the MySQL settings, you may still get an error if myfield
is numeric and $myVariable contains non-numeric characters.

By the way, you should use mysql_real_escape_string() at the point where
you are giving the value to a query, as shown above, not at the point where
you fetch it, as per one of our earlier posts.

So to get the actual value into $myVariable, with any auto-escaping
stripped, use a technique like this (PHP code follows):

function get_string($s) {
if (!isset($s))
return NULL;
if (get_magic_quotes_gpc())
return(stripslashes($s));
return $s;
}

Then to fetch the variable, de-slashing if necessary, you just do:

$myVariable = get_string($_POST['myFormField']);

and then you can use $myVariable for processing without worrying about slashes,
etc. You then use mysql_real_escape_string() when actually building a query, as
mentioned above.

Hope this helps!

Tony

--
Tony Mountifield
Work: tony (AT) softins (DOT) co.uk - http://www.softins.co.uk
Play: tony (AT) mountifield (DOT) org - http://tony.mountifield.org

On Aug 23, 12:35*am, "Álvaro G. Vicario"
<alvaro.NOSPAMTH... (AT) demogracia (DOT) com.invalid> wrote:
It works out to be 7 additional paragraphs instead of possibly bit-
fiddling a data field. Tedious, but now done.

On 8/24/2011 7:20 AM, The Natural Philosopher wrote:
So, what did you screw up when you first installed it? Did you go back
and delete the directory?

It's funny that it works fine for such a huge number of users but you
can't seem to even get a basic install right. And on a Mac yet - one of
the hardest platforms to screw anything up.

Yes, that's what I could call "hacking".

And BTW - YOUR code maybe goes to stage 4. Around here, "Professional
code" is properly documented.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

El 24/08/2011 12:57, Charles escribió/wrote:
That's hardly an issue when you can use functions to encapsulate tedious
repetitive tasks. In PHP, I often do this:

$foo = post('foo');

.... where:

/**
* Returns the value of given POST parameter if it exists, it's an
scalar and is not blank;
* returns $default_value otherwise
*/
function post($param_name, $default_value=NULL){
if( isset($_POST[$param_name]) && is_string($_POST[$param_name]) &&
trim($_POST[$param_name])!='' ){
return $_POST[$param_name];
}else{
return $default_value;
}
}



--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--