[RESOLVED] date formatting error

[jrough]

THe date I get when I submit the form is 0000-00-00. So I tried to format the date see line with asterisks.
I added the date string formatting to the Mysqli query.
date("Y-m-d H:i:s", .....)

This is the error I get:
<br />
<b>Notice</b>: A non well formed numeric value encountered in <b>/Library/WebServer/Documents/ajax/form_handler_copy.php</b> on line <b>23</b><br />
1 Row inserted.

I don't see why. It looks like it should work?

Thanks,

function set_it(){
require_once('global.inc.php');	
	$mysqli = new mysqli($dbhost,$dbuser, $dbpass, $dbname,$dbport);
   $task =$mysqli->real_escape_string($_GET['task_todo']);
	$name =$mysqli->real_escape_string($_GET['todo_name']);

$date = date("Y-m-d H:i:s",$mysqli->real_escape_string($_GET['due_date']));   *****


if ($mysqli->connect_error) {
    printf("Connect failed:%s\n",  mysqli_connect_error());
    exit();
}


if($mysqli->query("INSERT INTO taskos.task(todo,  name,date) VALUES('$task','$name','$date')")  ){
	 printf("%d Row inserted.\n", $mysqli->affected_rows);

 }
$mysqli->close();
}

[Weedpacket]

If that "*****" indicates line 23, it would suggest that what you're passing to [man]date[/man] isn't numeric. What is it? (And if you're passing it to date, why are you escaping it for passing into MySQL?)

[jrough]

Sorry I didn't get the email that someone responded for some reason.

I changed the input type for the due_date field to a date in HTML 5 form, but I don't think that was the problem.
There is a date picker function in jquery that should fire, but it isn't firing. I don't think that is the issue because you can still input manually.
test page: janisrough.dyndns.biz/todo17.html
jquery datepicker function:

		$(function() {
		     $( "#due_date" ).datepicker({
			   clickInput:true,
			   dateFormat: 'm/d/yy',
		       minDate:  +52,
		       maxDate: '+2y',
		        defaultDate: "+1d",
		        showOtherMonths: true,
		        selectOtherMonths: true,
		       changeMonth: true}) ;
	});


$.datepicker.setDefaults($.datepicker.regional['en']);	
$('input[type="submit"]').click(function() {
    if (this.checked) {

        $(this).remove();
    }

});

You can input a date into the form. It should still be a date format when it gets to mysql? This is the form:

        <form id="taskForm" >   

            <input  name="task_todo" type="text" value="What needs to be done?" /><br />
            <input  name="todo_name" type="text" value="By whom?" />
            <input name="due_date" type="date" value="By when?"  /><br />
            <button type="submit"  class="fg-button ui-state-default">Submit</button>
        </form>

I just input a date into the form online. This is the resulting GET string:GET http://janisrough.dyndns.biz/ajax/form_handler_c...%3F&todo_name=By+whom%3F&due_date=10%2F15%2F2012

I noticed when I checked phpmyadmin that what was input into the dbase was 0000-00-00. It is a date type field in mysql.

I thought you were supposed to escape all input? That is why I escaped it before input to the mysql database. Why do you ask why I escaped it? Isn't that what you are supposed to do? thankx

[bradgrafelman]

Read the manual for [man]date/man to learn what the optional second parameter it expects should be. The reason you're getting an invalid date in your DB is probably because you aren't using this second parameter correctly.

The answer to why you shouldn't be escaping the input is two-fold:

1. You aren't preparing the input for use in a SQL query string, so escaping it as if you were doesn't make any sense.

2. Even if we ignore #1 above... since the optional second parameter for [man]date/man is an integer, escaping it as if it were a string is inappropriate. Yes, all user-supplied data should be properly sanitized before being used directly in a SQL query, but no, not all types of data should be sanitized in the same way; the real_escape_string/b method should only be used on string data.

EDIT: And as for a resolution to your issue, you have numerous options, including:

1. Alter the JS code such that it supplies the data in the correct format (YYYY-MM-DD).

2. Same as #1, but use [man]explode/man to explode the date string into its separate components, allowing you to use something like [man]checkdate/man to ensure you've got a valid date. (In other words, this is just #1 plus some additional error checking before the original date string is used in a SQL query.)

3. Leave the date in whatever format you wish, but use [man]strtotime/man, [man]strptime/man, etc. to parse this date, and then re-construct it back into the YYYY-MM-DD format that the SQL server expects.

4. Use MySQL's STR_TO_DATE() function to achieve the same as #3 above (but letting the SQL server do the parsing/converting, rather than explicitly doing so in PHP).

[Weedpacket]

jrough wrote:

I changed the input type for the due_date field to a date in HTML 5 form, but I don't think that was the problem.
There is a date picker function in jquery that should fire, but it isn't firing. I don't think that is the issue because you can still input manually.

That doesn't really answer the question I asked ("What is it?") - I also said that it should be numeric, and gave a link to the necessary documentation for the details.

This bit is closer to answering what I asked:

dateFormat: 'm/d/yy',

But since that is clearly not numeric, I had already pointed out what the problem with it was.

Further to bradgrafelman's point 2, above: parameterised queries (which MySQLi supports) would avoid the need to manually escape parameters, since the appropriate escaping and quoting would be done by the database driver.

[jrough]

Maybe something else is wrong? I changed the date line to have the time input as the second parameter. The problem is now there is a syntax error on line 22? It is the line where $date =MySQLi($_GET['due_date']);
Parse error</b>: syntax error, unexpected '(', expecting T_STRING or T_VARIABLE or '{' or '$' in <b>/Library/WebServer/Documents/ajax/form_handler_copy.php</b> on line <b>22</b><br />

It isn't a problem with the format on line 23

It must be something with mySQL. I checked the table. What is getting input is 0000-00-00 instead of the date I input in the form.

function set_it(){
require_once('global.inc.php');	
	$mysqli = new mysqli($dbhost,$dbuser, $dbpass, $dbname,$dbport);
   $task =$mysqli->real_escape_string($_GET['task_todo']);
	$name =$mysqli->real_escape_string($_GET['todo_name']);
	 $date= $mysqli->($_GET['due_date']);
isDate($date) ? $date=date("yy-m-d",time($date)); :echo "due date is not a date";exit;);

if ($mysqli->connect_error) {
    printf("Connect failed:%s\n",  mysqli_connect_error());
    exit();
}


if($mysqli->query("INSERT INTO taskos.task(todo,  name,date) VALUES('$task','$name','$date')")  ){
	 printf("%d Row inserted.\n", $mysqli->affected_rows);

 }
$mysqli->close();
}

function set_it(){
require_once('global.inc.php');	
	$mysqli = new mysqli($dbhost,$dbuser, $dbpass, $dbname,$dbport);
   $task =$mysqli->real_escape_string($_GET['task_todo']);
	$name =$mysqli->real_escape_string($_GET['todo_name']);
	 $date= $mysqli->($_GET['due_date']);                                      ******
isDate($date) ? $date=date("d-m-yy",time($date)); :echo "due date is not a date";exit;);

if ($mysqli->connect_error) {
    printf("Connect failed:%s\n",  mysqli_connect_error());
    exit();
}


if($mysqli->query("INSERT INTO taskos.task(todo,  name,date) VALUES('$task','$name','$date')")  ){
	 printf("%d Row inserted.\n", $mysqli->affected_rows);

 }
$mysqli->close();
}

thankx,

[Weedpacket]

jrough wrote:

It isn't a problem with the format on line 23

Well, no: the error message is saying that PHP's parser is failing on line 22. It's not even looking at line 23.

$date= $mysqli->($_GET['due_date']);

You don't see what's wrong with this line (hint: the '(' is unexpected)?

isDate($date) ? $date=date("d-m-yy",time($date)); :echo "due date is not a date";exit;);

You'll also have a parse error on this line: the operands to the conditional operator are supposed to be expressions, not entire statements (just an if() statement).

[bradgrafelman]

On top of the issue Weedpacket points out above, there are also several problems with this line:

isDate($date) ? $date=date("d-m-yy",time($date)); :echo "due date is not a date";exit;);

1. What does this isDate() function do? I'll assume that it's perfectly correct and simply returns a 0/1 equivalent value...

2. [man]time/man doesn't expect (and thus, use) any parameters, so why are you giving it one?

3. Echo'ing out some cryptic message (saying that the "due date" is not a date; if that's true, then it's not a "due date" in the first place now is it? 🙂) in the middle of (what I'm presuming to be) a well-formed HTML document and then exit;'ing is, to me, akin to starting a perfectly normal conversation with the captain of a ship, screaming "OH NO SOMETHING IS WRONG," and then just jumping overboard. In other words, you probably didn't leave things in a very clean state for anyone involved (PHP or the end user).

4. You can't execute multiple statements inside of a ternary operator (not to mention that even executing one full statement looks ugly enough), so the "exit;" bit isn't going to parse.

5. You've got an unmatched ')' after the 'exit;' (compounding one parse error on top of another).

6. You've got two different copies of the same function definition - set_it() - inside the same code snippet above, and neither of them even attempt to use date() to generate a date in the correct format that MySQL expects, thus even if all of the above issues were ignored, you'd still end up with '0000-00-00' in the SQL DB since you're not generating the YYYY-MM-DD date format MySQL expects.

[jrough]

I have a question. Why do I get this error? I input 2012/7/7. It should be valid but the problem is it processes the $due_date var and leaves the $ instead of removing it? tnx,
<br />
<b>Notice</b>: Undefined property: mysqli::$2012/7/7 in <b>/Library/WebServer/Documents/ajax/form_handler_copy1.php</b> on line <b>31</b><br />
please input a valid date!

function set_it(){
require_once('global.inc.php');	
	$mysqli = new mysqli($dbhost,$dbuser, $dbpass, $dbname,$dbport);

if ($mysqli->connect_error) {
    printf("Connect failed:%s\n",  mysqli_connect_error());
    exit();
}
		$task =   $mysqli->real_escape_string($_GET['task_todo']);
		$name =$mysqli->real_escape_string($_GET['todo_name']);
			$date = $mysqli->$_GET['due_date'];
    if(valid_date($date)){if($mysqli->query("INSERT INTO taskos.task(todo,  name,date) 
			VALUES('$task','$name','$date')")  ){
				 printf("%d Row inserted.\n", $mysqli->affected_rows);						
			 }//end if

}else {echo "please input a valid date!";}	

$mysqli->close();

}
function valid_date($date) {
    return (preg_match("/^([0-9]{4})-([0-9]{2})-([0-9]{2})$/", $date));
}

[bradgrafelman]

jrough;11015595 wrote:

I input 2012/7/7. It should be valid

No it shouldn't; at least, not according to your valid_date() function.

jrough;11015595 wrote:

the problem is it processes the $due_date var and leaves the $ instead of removing it?

Er.. no, that's not it at all. The problem is that this:

$date = $mysqli->$_GET['due_date'];

makes no sense at all. Why are you attempting to access a property of the $mysqli object here? Shouldn't you just be using the value provided in the $_GET array?

[jrough]

bradgrafelman;11015605 wrote:

No it shouldn't; at least, not according to your valid_date() function.

Er.. no, that's not it at all. The problem is that this:

$date = $mysqli->$_GET['due_date'];

makes no sense at all. Why are you attempting to access a property of the $mysqli object here? Shouldn't you just be using the value provided in the $_GET array?

ok it works now except for the date validation.

[bradgrafelman]

Works just fine for me:

<?php
function valid_date($date) { 
    return (preg_match("/^([0-9]{4})-([0-9]{2})-([0-9]{2})$/", $date)); 
}

var_dump(valid_date("2012-1-1")); // int(0)
var_dump(valid_date("2012-01-01")); // int(1)

EDIT: Granted, it's not a very good date validator:

function valid_date($date) { 
    return (preg_match("/^([0-9]{4})-([0-9]{2})-([0-9]{2})$/", $date)); 
}

var_dump(valid_date("2012-02-31")); // int(1)
var_dump(valid_date("2012-10-33")); // int(1)

[Weedpacket]

bradgrafelman;11015651 wrote:

EDIT: Granted, it's not a very good date validator:

Oh, if only PHP had a function to check dates to see if they were valid..... 🙂