cannot insert the right values into table

Question

I have a database named user_job_apply(id, job_id, postby_id, applier_id, flag, flag_wall, time_apply).

The "time_apply" type in my database was set to :datetime

My problem is that the following piece of code inserts everything suceesfully in my databe, but it does not insert the value for $timeString. When I look in my database it shows 0000-00-00 00:00:00. Any idea what is the problem here?

I have tested by echoing $timeString and it displays date and time with success, but i cannot pass it into my database. Any idea how to fix this?

<?php

// the next two values comes from a form 

$the_job_code =  mysql_real_escape_string($_POST['jobid']);
$the_postedby =  mysql_real_escape_string($_POST['postedby']);

// use it to get current time from user's computer

$timeString= '
<script language="javascript">
var today = new Date();
var dd = today.getDate();
var mm = today.getMonth()+1; //January is 0!
var yyyy = today.getFullYear();

if(dd<10) {
    dd="0"+dd
} 

if(mm<10) {
    mm="0"+mm
} 

today = mm+"/"+dd+"/"+yyyy + " " +today.getHours() + ":" + today.getMinutes()+":" +    today.getSeconds();
document.write(today);
</script> ';

mysql_query("INSERT INTO `user_job_apply` VALUES ('', '$the_job_code', '$the_postedby ', '$session_user_id', '1', '1', '$timeString') "); 

?>

I don't see any variable called $timeString. you have $today though. — Rahul, May 29 '14 at 18:27
yes how to fix this and make it get is as date into my database? — user2491321, May 29 '14 at 18:30
`var_dump($timeString);` that will show you what's being passed. So, if you see anything funky, you'll know why. — Funk Forty Niner, May 29 '14 at 18:30
@user2491321 I think **Timestamp** is best way to save date & time to database. And from that timestamp you can extract date & time according to your format. — Riq, May 29 '14 at 18:34
`var_dump()` produces this right `string(360) " "`? that's because your JS is invalid. You shouldn't be mixing/injecting JS into PHP like that. — Funk Forty Niner, May 29 '14 at 18:48
Since the time needs to come from the user's computer it needs to come in the request itself. The easiest way to do that would be in the query string, or a hidden field in the form you POST to your script. — Jason M, May 29 '14 at 18:49
used var_dump($timeString); and get string(374) " 05/29/2014 21:49:43 " — user2491321, May 29 '14 at 18:50
Ok, I think I may have figured it out. `var_dump()` revealed a space just before `05` => `string(359) " 05/29/2014 15:7:52"` and this is because of `$timeString=' — Funk Forty Niner, May 29 '14 at 19:09
still nothing... I changed the structure type for "time_apply" in my database from DATETIME to varchar(32). In this case it passes in it the first lines on text of $timeString variable. It passes me — user2491321, May 29 '14 at 19:17
Then try changing `'$timeString'` to `'".$timeString."'` and/or `$timeString = mysql_real_escape_string($timeString);` just after the JS — Funk Forty Niner, May 29 '14 at 19:21
I couldn't come up with anything. This answer may be of help http://stackoverflow.com/a/5607444/ and look at the rest of the answers on that page. Plus this http://stackoverflow.com/a/357/ and http://stackoverflow.com/a/22824853/ after Googling `"get timezone current user"` and `"local time"`. You will not get any results with what you're using now. You're basically wasting a lot of time with it, where you should be spending that time finding other solutions. — Funk Forty Niner, May 29 '14 at 20:30

score 0 · Answer 1 · answered May 29 '14 at 18:33

0

Maybe you need use MySQL date format, like 'YYYY-MM-DD'?

answered May 29 '14 at 18:33

stepozer

1,143
1
10
22

Sorry for my english:) How i understand you want save user local time. Then you must run some ajax request with user local JS time. Your JavaScript file run in browser, not in server. Or you can redirect user some specified url with GET parameter like /?time=2014-05-29. – stepozer May 29 '14 at 19:08

score 0 · Answer 2 · answered May 29 '14 at 18:37

0

It can be because of your Mysql localization settings and the dateformat, try what stepozer said 'YYYY-MM-DD'

today = yyyy+"/" +mm+"/"+dd+ " " +today.getHours() + ":" + today.getMinutes()+":" +    today.getSeconds();

or you can just insert the current date and time directly on your sql using the function NOW()

mysql_query("INSERT INTO `user_job_apply` VALUES ('', '$the_job_code', '$the_postedby ', '$session_user_id', '1', '1', NOW()) ");

answered May 29 '14 at 18:37

neiha

171
5

dont want now() it will give me server's time – user2491321 May 29 '14 at 18:38
I edited the answer with how you can change the format in your code – neiha May 29 '14 at 18:40

Ricardo Nunes · Answer 3 · 2014-05-29T20:49:20.000

The "today" variable defined at the bottom of your Javascript block is not defined in a format compatible with MySQL.

It should be defined like this:

today = yyyy+"-"+mm+"/"+dd+" "+today.getHours()+":"+today.getMinutes()+":"+today.getSeconds();

Like this it will output in the format needed by MySQL to save in a "datetime" field.

EDIT:

The above solution only solves part of your problem.

The main problem here is that this code runs on the server-side after the form is submitted.

You should move the javascript block to the html file where the form resides (after the form) and write the "today" variable to a hidden form input called "timeString".

After submission you should receive this variable through the post:

$timeString =  mysql_real_escape_string($_POST['timeString']);

EDIT 2:

First you need to create a hidden form input inside your form:

<input type="hidden" name="timeString" id="timeStringInput">

You need to copy the following block to your html file, right after the end of the form ():

<script language="javascript">
var today = new Date();
var dd = today.getDate();
var mm = today.getMonth()+1; //January is 0!
var yyyy = today.getFullYear();

if(dd<10) {
    dd="0"+dd
} 

if(mm<10) {
    mm="0"+mm
} 

today = yyyy+"/"+mm+"/"+dd+" "+today.getHours()+":"+today.getMinutes()+":"+   today.getSeconds();
document.getElementById('timeStringInput').value = today;
</script>

On the PHP file, replace the $timeString assignment (complete block) with:

$timeString =  mysql_real_escape_string($_POST['timeString']);

I've updated my answer to address your main problem. Basically javascript doesn't run on the server side and you need to move that block of code to the html. — Ricardo Nunes, May 29 '14 at 19:53
done it, but the problem here is that the $timeString does not passes the date and time, instead it passes a text like — user2491321, May 29 '14 at 20:00
That's why I said to move the Javascript block (the block) to the html, after the form, and write the "today" variable to a hidden form input called "timeString". Just the block, not the $timeString variable. The variable must be assigned like stated in my answer above. — Ricardo Nunes, May 29 '14 at 20:13
sorry i dont understand..can you please write me the way it should be? — user2491321, May 29 '14 at 20:44
try it but form does not submit the value... the id does not get value — user2491321, May 29 '14 at 20:58

cannot insert the right values into table

3 Answers3