Sending proper date formats from PHP to SQL Server
I have a couple computed columns in SQL that processes data from two other date columns in my database as you see here below. It works fine, until I started inserting values from my PHP page that I recently developed.
What I notice is that the variables from my PHP are inserted into the SQL database then the computed columns won't work. I have the PHP code below where I assigned a value to the variable $CurrentDate and when that gets inserted into the entrytime column in my database with datatype datetime, then the computed column doesn't work.
Additionally, I have an HTML field as you see here below that collects the birthdate of individuals. But when the data is inserted into my SQL column with datatype date, the computed column again doesn't work.
It seems that there is an issue with the datatype I am sending to my database. How can I send a date or datetime datatype to my SQL database from PHP?
What I notice is that the variables from my PHP are inserted into the SQL database then the computed columns won't work. I have the PHP code below where I assigned a value to the variable $CurrentDate and when that gets inserted into the entrytime column in my database with datatype datetime, then the computed column doesn't work.
<?php
if(isset($_POST['submit'])) {
date_default_timezone_set("America/Chicago");
$CurrentDate = date('Y-m-d H:i:s');Additionally, I have an HTML field as you see here below that collects the birthdate of individuals. But when the data is inserted into my SQL column with datatype date, the computed column again doesn't work.
It seems that there is an issue with the datatype I am sending to my database. How can I send a date or datetime datatype to my SQL database from PHP?
You have to use format yyyy-mm-dd or mm/dd/yyyy.
So the problem is not with computed column but the format of date inserted into database.
Computed column in not computed at insert/update time but only when you select it.
Run a profiler trace to check which date format is sent to SQL Server from PHP.
So the problem is not with computed column but the format of date inserted into database.
Computed column in not computed at insert/update time but only when you select it.
Run a profiler trace to check which date format is sent to SQL Server from PHP.
ASKER
How do I run a profiler trace?
Are you saying that I need to adjust this code to the following in PHP?
Are you saying that I need to adjust this code to the following in PHP?
<?php
if(isset($_POST['submit'])) {
date_default_timezone_set("America/Chicago");
$CurrentDate = date('YYYY-mm-dd H:i:s');
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I run the following insert command with no errors
$sql = "INSERT INTO tblOrgRegistrations(RegDate,EntryTime,lastname,firstname,birthdate,agency,AgencyID,Org,YouthCommittee,Gender,Address,City,Zip,Phone,Email,Race,Ethnicity,Language,Emergencycontact,EmerPhone,Notes,Referral,Service,CommunityArea,Fiscal) VALUES ('$CurrentDate','$CurrentDate','$LastName', '$FirstName','$Birthdate','$ServiceArea','$AgencyID','TB','1','$Gender','$Address','$City','$Zip','$Phone','$Email','$Race','$Ethnicity','$Language','$EmergencyContact','$EmergencyPhone','$Medical','$Referral','$Services','$ServiceArea','$Fiscal')";
$result = sqlsrv_query($conn, $sql);
still there is a missing code/logic
1. how do you set $Birthdate
2. what do you have in SQL table right now
3. when you open the table, or query it using SSMS or any other tool, what resultset do you get for rows where BirtDate is set properly?
1. how do you set $Birthdate
2. what do you have in SQL table right now
3. when you open the table, or query it using SSMS or any other tool, what resultset do you get for rows where BirtDate is set properly?
What I notice is that the variables from my PHP are inserted into the SQL database then the computed columns won't work.
can you post a screenshot that show what is not working...
is it empty? is it wrong calculation? is it giving error?
ASKER
My goodness. It seems to be working now. I must have looked at the wrong column. Here are the computed columns and the columns that were inserted from the PHP page. I'm sorry and thanks for getting me to take another look at it again.
Make sure that the date format is one of these yyyy-mm-dd hh:mm:ss or mm/dd/yyyy hh:mm:ss.
Make sure that it is not an empty string.
Make sure that it is not an empty string.
nothing to do date format...
I dont see any problem here...
data is inserted properly and calculated columns should work as long as you have data in BirthDate
I dont see any problem here...
data is inserted properly and calculated columns should work as long as you have data in BirthDate
I'm sorry and thanks for getting me to take another look at it again.
thats why I asked too many questions :)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hain you were on the right track. It wouldn't go into the column in the first place if it wasn't right
You should have selected https://www.experts-exchange.com/questions/29204553/Sending-proper-date-formats-from-PHP-to-SQL-Server.html#a43221322 as part of your solution. The question was about formating the date for SQL server and that answer is right on as far as posting form data to SQL server.
You should have selected...as part of your solution.but the issue is not date format or inserting data to SQL...
No need to change anything...
2. what do you have in the table for birthdate
3. when you insert/update, do you get any error?