[RESOLVED] Inserting date into MySQL table

Square1

Hi all:

I have spent HOURS on this and have come up empty.

I have a form I need to insert into a MySQL table. There is a column for a date. Not today's date or Now(), just a date when an event will take place, an order date.

I finally realized after about 5 hours that the format to insert has to be 'YYYY-MM-DD' and not 'MM/DD/YYYY'.

What I am now trying to do is to be able to put the date in the field as 'MM/DD/YYYY' but at some point covert it to 'YYYY-MM-DD' prior to inserting it so it will be accepted by MySQL.

Any help appreciated or am I missing something????

Thanks!

dagon

very common issue, so there's a few hundred options, this is 1:

$mydate = $_POST["mydate"];
list($d, $m, $y) = preg_split('/\//', $mydate);
$mydate = sprintf('%4d%02d%02d', $y, $m, $d);
echo $mydate;

Square1

Holy &^{%%^E$(~!}

I can't believe you need to do this much to get something like this work! I am coming out our MSSQL so this is a shock! This round goes to MSSQL!

Thank you. I need to let this code soak in so I understand it.

bradgrafelman

Square1;10977006 wrote:
This round goes to MSSQL!

Not by a long shot. 🙂

As dagon points out, there are tons of ways to do this. A simple way would be to do it all right in the query using MySQL's STR_TO_DATE() function.

dagon

Square1;10977006 wrote:
Holy &^{%%^E$(~!}

I can't believe you need to do this much to get something like this work! I am coming out our MSSQL so this is a shock! This round goes to MSSQL!

Thank you. I need to let this code soak in so I understand it.

2 lines, oh the humanity :eek:

Square1

dagon;10977009 wrote:
2 lines, oh the humanity :eek:

LOL! OK... but shouldn't this be a built-in function in the PHP library?

I like bradgrafelman's function approach. I have an Insert with 68 fields being inserted. I am binding the parameters. I tried the both of the following without success:

INSERT INTO tsfl_clients(DateField) VALUES (?)

mysqli_stmt_bind_param($stmt, 's', STR_TO_DATE($DateInput, '%m/%d/%Y')

As well as

INSERT INTO tsfl_clients(DateField) VALUES (STR_TO_DATE(?, '%m/%d/%Y')

mysqli_stmt_bind_param($stmt, 's', $DateInput)

dagon

Square1;10977010 wrote:
LOL! OK... but shouldn't this be a built-in function in the PHP library?

please NO, there are far to many functions as it is.

bradgrafelman

Square1 wrote:
but shouldn't this be a built-in function in the PHP library?

Why? It's a rather specific need, and I don't expect (or even want) PHP to have many "built-in" functions that simply duplicate other more atomic functions already available. Plus, since this is a MySQL issue (and not a PHP issue - after all, PHP doesn't care how the date is formed... MySQL does), and since MySQL already has a "built-in" function to handle it as shown above, I can't see what more you could want.

As for the errors... we'd probably have to see the actual code you tried. Both of the code snippets you posted above have errors in the SQL query (e.g. the 1st is missing quotes around the string value $DateInput while the second is missing a closing ')').

If all else fails, you might just have to execute a regular query rather than a prepared statement (which is overkill anyway if you're just executing the INSERT query once).

Square1

Option 1:

	  	$stmt = mysqli_prepare($link, "INSERT INTO tsfl_clients(
		FName,
		LName,
		Email_H,
		Email_W,
		Address,
		CSZ,
		Phone_H,
		Phone_W,
		Phone_C,
		Text,

	CallTime,
	AllSoy,
	AllOther,
	Goal,
	Weight,
	Height,
	BMI,
	Exercise,
	Reason1,
	Reason2,

	Reason3,
	Meds1,
	Meds2,
	Meds3,
	Meds4,
	Meds5,
	Meds6,
	Dr,
	Difficult,
	Improved,

	Username,
	Password,
	ClientID,
	OrderN,
	OrderDate,
	DeliveryDate,
	StartDate,
	StartGuideCall,
	W1D1,
	W1D2,

	W1D3,
	W1D4,
	W2C1,
	W2C2,
	W3C1,
	W3C2,
	W4,
	W5,
	W6,

	W1C12,
	W2C35,
	W3C4,
	W4C67,
	W5C8910,
	W6C1112,
	W7C131415,
	W8C16,
	W9C17,
	W10C18,

	W11C19,
	W12C20,
	W12C21,
	Ref1,
	Ref2,
	Ref3,
	Ref4,
	Ref5,
	Code		


	) VALUES (
	?, ?, ?, ?, ?, ?, ?, ?, ?, ?,		
	?, ?, ?, ?, ?, ?, ?, ?, ?, ?,		
	?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
	?, ?, ?, ?, STR_TO_DATE( ? , '%m/%d/%Y') , ?, ?, ?, ?, ?,
	?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
	?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
	?, ?, ?, ?, ?, ?, ?, ?
	)"
	);

  	mysqli_stmt_bind_param(
	$stmt,
	'ssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss',
	$pFName,
	$pLName,
	$pEmail_H,
	$pEmail_W,
	$pAddress,
	$pCSZ,
	$pPhone_H,
	$pPhone_W,
	$pPhone_C,
	$pText,

	$pCallTime,
	$pAllSoy,
	$pAllOther,
	$pGoal,
	$pWeight,
	$pHeight,
	$pBMI,
	$pExercise,
	$pReason1,
	$pReason2,

	$pReason3,
	$pMeds1,
	$pMeds2,
	$pMeds3,
	$pMeds4,
	$pMeds5,
	$pMeds6,
	$pDr,
	$pDifficult,
	$pImproved,

	$pUsername,
	$pPassword,
	$pClientID,
	$pOrderN,
	$pOrderDate,
	$pDeliveryDate,
	$pStartDate,
	$pStartGuideCall,
	$pW1D1,
	$pW1D2,

	$pW1D3,
	$pW1D4,
	$pW2C1,
	$pW2C2,
	$pW3C1,
	$pW3C2,
	$pW4,
	$pW5,
	$pW6,
	$pW1C12,

	$pW2C35,
	$pW3C4,
	$pW4C67,
	$pW5C8910,
	$pW6C1112,
	$pW7C131415,
	$pW8C16,
	$pW9C17,
	$pW10C18,

	$pW11C19,
	$pW12C20,
	$pW12C21,
	$pRef1,
	$pRef2,
	$pRef3,
	$pRef4,
	$pRef5,
	$pCode		
	);

Option 2:

	  	$stmt = mysqli_prepare($link, "INSERT INTO tsfl_clients(
		FName,
		LName,
		Email_H,
		Email_W,
		Address,
		CSZ,
		Phone_H,
		Phone_W,
		Phone_C,
		Text,

	CallTime,
	AllSoy,
	AllOther,
	Goal,
	Weight,
	Height,
	BMI,
	Exercise,
	Reason1,
	Reason2,

	Reason3,
	Meds1,
	Meds2,
	Meds3,
	Meds4,
	Meds5,
	Meds6,
	Dr,
	Difficult,
	Improved,

	Username,
	Password,
	ClientID,
	OrderN,
	OrderDate,
	DeliveryDate,
	StartDate,
	StartGuideCall,
	W1D1,
	W1D2,

	W1D3,
	W1D4,
	W2C1,
	W2C2,
	W3C1,
	W3C2,
	W4,
	W5,
	W6,

	W1C12,
	W2C35,
	W3C4,
	W4C67,
	W5C8910,
	W6C1112,
	W7C131415,
	W8C16,
	W9C17,
	W10C18,

	W11C19,
	W12C20,
	W12C21,
	Ref1,
	Ref2,
	Ref3,
	Ref4,
	Ref5,
	Code		


	) VALUES (
	?, ?, ?, ?, ?, ?, ?, ?, ?, ?,		
	?, ?, ?, ?, ?, ?, ?, ?, ?, ?,		
	?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
	?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
	?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
	?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
	?, ?, ?, ?, ?, ?, ?, ?
	)"
	);

  	mysqli_stmt_bind_param(
	$stmt,
	'ssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss',
	$pFName,
	$pLName,
	$pEmail_H,
	$pEmail_W,
	$pAddress,
	$pCSZ,
	$pPhone_H,
	$pPhone_W,
	$pPhone_C,
	$pText,

	$pCallTime,
	$pAllSoy,
	$pAllOther,
	$pGoal,
	$pWeight,
	$pHeight,
	$pBMI,
	$pExercise,
	$pReason1,
	$pReason2,

	$pReason3,
	$pMeds1,
	$pMeds2,
	$pMeds3,
	$pMeds4,
	$pMeds5,
	$pMeds6,
	$pDr,
	$pDifficult,
	$pImproved,

	$pUsername,
	$pPassword,
	$pClientID,
	$pOrderN,
	STR_TO_DATE( $pOrderDate , '%m/%d/%Y'),
	$pDeliveryDate,
	$pStartDate,
	$pStartGuideCall,
	$pW1D1,
	$pW1D2,

	$pW1D3,
	$pW1D4,
	$pW2C1,
	$pW2C2,
	$pW3C1,
	$pW3C2,
	$pW4,
	$pW5,
	$pW6,
	$pW1C12,

	$pW2C35,
	$pW3C4,
	$pW4C67,
	$pW5C8910,
	$pW6C1112,
	$pW7C131415,
	$pW8C16,
	$pW9C17,
	$pW10C18,

	$pW11C19,
	$pW12C20,
	$pW12C21,
	$pRef1,
	$pRef2,
	$pRef3,
	$pRef4,
	$pRef5,
	$pCode		
	);

Square1

bradgrafelman;10977013 wrote:
If all else fails, you might just have to execute a regular query rather than a prepared statement (which is overkill anyway if you're just executing the INSERT query once).

Isn't the prepared statement used just as much for the fact that you can bind the parameters as for repetitive inserts? Are you saying just use a query and use mysqli_real_escape_string?

bradgrafelman

Does Option 1 not work? I personally don't use prepared statements, so forgive my ignorance on the topic... but it may very well be that MySQL can't use prepared statements where variables are arguments to functions rather than just part of a comparison (e.g. "WHERE foo = ?"). If this is the case, then you either a) must convert the date in PHP (again, there is more than one way to skin a cat), or b) drop the prepared statement and just use a regular query.

Option 2 doesn't make any sense because you have a MySQL function in the middle of some PHP code.

Square1 wrote:
Isn't the prepared statement used just as much for the fact that you can bind the parameters as for repetitive inserts?

Yes... are you executing this INSERT loop more than once, though?

Square1 wrote:
Are you saying just use a query and use mysqli_real_escape_string?

Yes, with the stipulation that [man]mysqli_real_escape_string/man should only be used on string data.

EDIT: Additional question for Option 1; if it doesn't work, what is/are the error message(s)?

Square1

I am inserting just once time. No loop.

There is no error with Option 1. It just doesn't add the record.

I just feel "safe" binding the parameters. I would prefer to stick to doing that .

That being the case...

Is dagon's answer the "best", "simplest" way to do this in PHP?

bradgrafelman

Square1 wrote:
There is no error with Option 1. It just doesn't add the record.

If it doesn't add the record, then there is an error. 🙂 Are you checking for errors when you execute the prepared statement?

Square1 wrote:
I just feel "safe" binding the parameters. I would prefer to stick to doing that .

You feel "safe" by not knowing how to properly sanitize data and rather blindly accept any solution that lets you remain ignorant?

If so, please PM me all of your personal bank account details... I'll keep them safe for you - never you mind how. 🙂

Square1 wrote:
Is dagon's answer the "best", "simplest" way to do this in PHP?

Well those are two very subjective ways to talk about programming. I can't really say yes or no.

Although I don't know why dagon used preg_split() instead of explode(), the rest of his code is really doing the very basics of what you're asking. It splits apart a given date, re-arranges the pieces, and then ensures that each piece is of proper length (e.g. 2011-1-2 turns into 2011-01-02).

Perhaps an even simpler solution would be to not accept the date in such a non-standard format to begin with. Why not offer the user a dropdown box for the day, month, and year? That way, there's no re-formatting to be done (since the date hasn't even been formatted yet in the first place).

Square1

The following is working. The problem I could foresee with this is if a format not using "/" is used in the input. (3.27.2011 for example):

list($m, $d, $y) = explode("/", $_POST["OrderDate"]);
$pOrderDate = $y."-".$m."-".$d;

bradgrafelman

If you wanted to be flexible, then you could go back to dagon's approach of using preg_split(). That way, you could define multiple characters to split on.

EDIT: Also, note that it's a good idea to use something like PHP's [man]checkdate/man function to validate the information supplied by the user.

Square1

Thank you all again for your help.

Question to bradgrafelman. Why don't you ALWAYS use prepared statements over queries? I do know that sanitizing input is not a big deal, but from what I have read nothing beats binding the parameters. So if you have the option, why not always use the safest? What is the downside to using prepared statements for everything?

bradgrafelman

Square1 wrote:
Why don't you ALWAYS use prepared statements over queries?

Well lets see:

If you aren't re-using the query, they can be less efficient (takes 2 round-trips to the server - one to prepare the query, one to bind & execute).
They require more code than regular queries.
Nothing "beats" binding the parameters because no method "beats" any other method. Either your data is sanitized or it isn't.
Prepared statements can't always be used (although I can't come up with an example on the spot, but there are limitations).
In the event the above happens, would you know how to provide the same sanitizing benefits that prepared statements do (hint: the answer is not simply calling [man]mysqli_real_escape_string/man, which is inappropriate for data other than strings)?

Square1

Hmmm...Ok, fair enough. I will do some more homework! 😉

As for the original example, I tweaked it to the following:

$mydate = $_POST["mydate"]; 

list($m, $d, $y) = preg_split('/(\/|\.|-| )/', $mydate);
$mydate = sprintf('%4d-%02d-%02d', $y, $m, $d);
echo $mydate;

I may have 4 or 5 date fields passing through. I would prefer not to keep re-coding this 4 or 5 times. How do I make this a function? I have been trying for about an hour and am spinning my wheels.

bradgrafelman

What have you tried? It should be as simple as wrapping the code in a function() block, using a function parameter instead of $_POST['mydate'], and [man]return[/man]'ing the data instead of echo'ing it. See this manual page for more info/examples.

Square1

OK, I think I built my first function!

function MySQL_Date($mydate)
	{
		list($m, $d, $y) = preg_split('/(\/|\.|-| )/', $mydate);
		$mydate = sprintf('&#37;4d-%02d-%02d', $y, $m, $d); 
		return $mydate;

}

And now if I add this to my auto_prepend_file I can use this on any page without rewriting the code? Or is just using an include when necessary a better choice? I can see this being a pretty common function.