Submit timestamp to mysql

0
votes

I am trying to add a timestamp to my database when I update a form, and for reason that I do not know, I am getting an error... and when just trying to insert the year, month, day I get "1988" inserted into my database. I use a similar timestamp elsewhere on the same site and it works fine. What am I doing wrong?

Note: yes I know I should be using mysqli and I'm vulnerable to sql injection. I plan on converting the entire site later in the year.

    $homeScore = ((strlen($game['homeScore']) > 0) ? $game['homeScore'] : 'NULL');
    $homeOdds = (str_replace("\xBD", ".5", $homeScore));
    $visitorScore = ((strlen($game['visitorScore']) > 0) ? $game['visitorScore'] : 'NULL');
    $visitorOdds = (str_replace("\xBD", ".5", $visitorScore));

    $odds_timestamp = date("Y-m-d g:i:s A");

    $sql = "update " . $db_prefix . "schedule ";
    $sql .= " set odds_timestamp = " . $odds_timestamp . ", homeOdds = " . $homeOdds . ", visitorOdds = " . $visitorOdds . "";
    $sql .= " where gameID = " . $game['gameID'];
    mysql_query($sql) or die('Error updating odds: ' . mysql_error());
4
phpmysql
what about the column itself, is it DATETIME? – Funk Forty Niner
It was DATETIME and that didn't work, so I changed it to VARCHAR, same error either way – Mark Jones
echo $odds_timestamp = date("Y-m-d g:i:s A"); what does that show? it's a string ;-) – Funk Forty Niner
You would be best advised to make the datatype a DATETIME or TIMESTAMP as if you keep the VARCHAR it will make the data much more difficult to process later. Just change the date() to produce a legal MYSQL data format – RiggsFolly

4 Answers

3
votes

You have missing (single) quotes " . $odds_timestamp . "

that will need to be '" . $odds_timestamp . "' since it will contain characters that MySQL will complain about... being hyphens.

  • That is a string.

Now, if any of your other variables are also strings, they too need to be quoted as shown.

I.e.: '" . $string . "' as opposed to " . $integer . "

More on string literals:

Pay attention to Riggs' comments, one of which being:

"You would be best advised to make the datatype a DATETIME or TIMESTAMP as if you keep the VARCHAR it will make the data much more difficult to process later. Just change the date() to produce a legal MYSQL data format"

Using a VARCHAR won't fix it, as it still considered as a string literal for the column.

New comments by Riggs:

"You would be best advised to make the datatype a DATETIME or TIMESTAMP as if you keep the VARCHAR it will make the data much more difficult to process later. Just change the date() to produce a legal MYSQL data format. You can always add the AM/PM when you present the date time to any user. VARCHAR date/time will really mess with your selection criteria later as well. Remember - Database for DATA, Browser for PRESENTATION"

1
votes

You can use MySQL's NOW() function, which returns current datetime.

0
votes

Try adding a timestamp column in the database table with an on update set current timestamp clause.

Heres a simple example from MySQL Documentation:

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Should take care of it and cut out the middle man. Win-win.

0
votes

Without error message, Its difficult to say something. or if you can print your query it will be helpful.

but try with.

odds_timestamp = '" . $odds_timestamp . "'

to make it explicit string.