Dealing with strings and integers 2

[mufka]

I have a web form to update a date in MySQL. Sometimes the entry will be NULL. When it is NULL, I have to format the update as an integer. When it is not NULL, I have to format the update as a string.

For example:

//The date is NULL
UPDATE table SET date=NULL

//The date is a date
UPDATE table SET date='2009-01-28'

The issue isn't with MySQL, it is with my ability to manipulate the variables in PHP.

I'm using something like the following:

//If there were just one data type, I could just use
$query = UPDATE table SET date=$_POST[date];

//But since there are two types, I'm trying
	if (is_null($_POST['date'])) {

//That doesn't work because it isn't picking up the NULL
//So I try
$date=$_POST[date];
if ($date == 'NULL') {
    $date=$date;
	} else {
	        $date=$_POST['date'];
}
$query = UPDATE table SET date=$date;
//This works if it is NULL, but if a date is entered like 1945-01-01 it tries to update with the integer 1943.

 

[feherke]

Hi

I do not really understand that. What exactly is sent to PHP when there is no date ?

Anyway, I would do this :

$date=$_POST[date];
$query='UPDATE table SET date='.($date?"'".[url=http://php.net/mysql_real_escape_string/]mysql_real_escape_string[/url]($date)."'":'null');

Feherke.

http://rootshell.be/~feherke/

 

[vacunita]

There's a difference between a variable being Null, and a variable not existing all together.


In your case, when the form is submitted, but whatever element the date comes from is empty, the variable you are looking for is not set, that is it does not exist.

$_POST['date'] does not exist and as such cannot be NULL.

Doing this:

if (is_null($_POST['date'])) {

Should technically result in a Notice.


I would check for the existence of a variable rather than its contents.

 if (isset(($_POST['date'])) {
$date=$_POST['date'];
}
else{
$date='NULL';

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[vacunita]

Just a small correction, if the element that $_POST['date'] is coming from is a textbox, then it will also never be null, though it will be set, which is why there is no error. It will be an empty variable, so rather than checking if its null since it is not null but just empty, use empty() to check whether it has something or is just en empty string.



----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[mufka]

I don't think my problem lies in figuring out what is in the variable. It is in stuffing whatever is in the variable into a SQL statement formatted correctly.

It looks like feherke's suggestion will work, but I have two questions about it.
1. How does '.($date?"'".mysql_real_escape_string($date)."'":'null') work?

2. I'm having trouble using this while updating other fields. I'm getting something wrong with the ' and ".

//this works ok but how?  There's no trailing '
$query='UPDATE table SET date='.($date?"'".mysql_real_escape_string($date)."'":'null');
//this doesn't work
$query='UPDATE table SET date='.($date?"'".mysql_real_escape_string($date)."'":'null'), name='Joe';

 

[mufka]

I figured out part of my problem. I needed to do:

$query='UPDATE table SET date='.($date?"'".mysql_real_escape_string($date)."'":'null').', name="Joe"';
//instead of 
$query='UPDATE table SET date='.($date?"'".mysql_real_escape_string($date)."'":'null'), name='Joe';

I'd still like some help in understanding ($date?"'".mysql_real_escape_string($date)."'":'null') though.

 

[vacunita]

Feherke's code is using the ternary operator. Its a form of an if statement.

$query='UPDATE table SET date='.($date?"'".mysql_real_escape_string($date)."'":'null').', name="Joe"';

Basically its read as: If $date is true(exists and has some content), then run $date through the mysql_real_escape_string() function and output the value, otherwise output NULL.

It works because unlike your code , he's not comparing the variable contents to NULL as you where doing. The variable is never going to be NULL, so your comparison will never work.

Additionally his method fails to detect if there is a real date in the variable. if I where to just add an empty space it would still register as existing, however a space would not be a valid date.







----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[feherke]

Hi

Additionally his method fails to detect if there is a real date in the variable

Correct. I just ignored that problem because I still not know what PHP should expect.

Of course, another way is to just do its best :

$date=$_POST[date];
if ($date=[url=http://php.net/strtotime]strtotime[/url]($date)) $date=[url=http://php.net/date/]date[/url]("Y-m-d",$date);
$query='UPDATE table SET date='.($date?"'$date'":'null').', name="Joe"';

But this is just a more robust alternative, not a definite solution.

Feherke.

http://rootshell.be/~feherke/