Comparison between a timestamp in MYSQL table with a PHP variable NOT Working - PHP MYSQL

Question

I'm trying to filter out repeated values entering into a MySQL table, by comparing the input PHP variable with the timestamp of an entry already present in the table and only if they don't match, the input PHP variable is entered into the table.

$user1_date = mysql_real_escape_string($user1_date); // the date variable
$user1_temp1 = mysql_real_escape_string($user1_temp1);
$user1_temp2 = mysql_real_escape_string($user1_temp2);
$user1_temp3 = mysql_real_escape_string($user1_temp3); 
$user1_date = date("Y-m-d H:i:s", strtotime($user1_date));  //Typecasting PHP variable into timestamp       

$sql_check = "SELECT * FROM user_details WHERE temp_date ='$user1_date'";
$result_check = mysql_query($sql_check);
$num_rows_check = mysql_num_rows($result_check);

if ($num_rows_check == 0) // To check if there is no entry in the table with the same date and time as input PHP variable
{
$sql_insert = "INSERT INTO data_hour (user_id, temp1, temp_date, temp2, temp3)
VALUES (1,'$user1_temp1', '$user1_date', '$user1_temp2', '$user1_temp3')";
$result_insert = mysql_query($sql_insert);
}

temp_date is a column in the table of type timestamp. Even when the $user1_date is the same as the temp_date(timestamp) column for one of the entries in the table, it considers it as not equal and is inserting it into the table and hence I'm getting repeated values. I'm guessing the WHERE temp_date = '$user1_date'is not working properly. Some troubleshooting that I have done included

Changing '$user1_date' to just $user1_date in the WHERE statement
Changing the WHERE clause as follows WHERE temp_date = (date)'$user1_date'

It will be great if somebody can help me out with this!

score 1 · Accepted Answer · 2014-03-16 02:16:11Z

1

A nice easy solution would be giving temp_date a UNIQUE INDEX in your Mysql Table, as that would not allow the same value to be inserted twice. This would also make your operations more efficient, as you wouldn't have to do SELECT * every time you want to insert something.

However, for what you're doing, I think I see your problem; there are some quirks in your logic so I'll try to dispel them here. (Hopefully?) this will make your program cleaner and you'll be able to pinpoint the error, if not eliminate it altogether.

Examining this piece of code:

// $user1_date doesn't have a value here! //
$user1_date = mysql_real_escape_string($user1_date);
...
$user1_date = date("Y-m-d H:i:s", strtotime($user1_date));

Error 1 - You escape the string before ever setting a value.

What you are doing is that you are using mysql_real_escape_string() before $user1_date is ever defined.

Correction:

// Getting better, but not done. //
$user1_date = date("Y-m-d H:i:s", strtotime($user1_date));
...
$user1_date = mysql_real_escape_string($user1_date);

Error 2 - You do not give the date() function appropriate parameters

The date() function in PHP expects a timestamp, which is just an int. You can easily get the time with time(), so that should rectify your problem

Correction:

// You use strtotime($user1_date), but you should use time() //
$user1_date = date("Y-m-d H:i:s", time());
...
$user1_date = mysql_real_escape_string($user1_date);

These are small mistakes, but they can be deadly. Like I said, you should assign temp_date to a UNIQUE INDEX in your MySQL table, but make sure to correct these errors listed as well.

Let me know how it goes!

edited Mar 16, 2014 at 2:16

answered Mar 16, 2014 at 0:07

user569322

Sign up to request clarification or add additional context in comments.

2 Comments

user3314391 Over a year ago

Thanks @Ken!! I had totally forgotten about making it an unique index. Damn! I also took into account of what you said with regard to Error 1. As far as Error 2 is concerned, I am using strtotime() because I want the date of the input variable to be typecasted into a timestamp. time() will timestamp with the current time and I will lose the time stored in the variable. But anyways, all is fine! Thanks!

user569322 Over a year ago

No problem! And for Error 2, you're using strtotime($user1_date) to define $user1_date... but does $user1_date have a value at that point? If not, its null, and will not work. And if it works, please accept the answer! :)

Collectives™ on Stack Overflow

Comparison between a timestamp in MYSQL table with a PHP variable NOT Working - PHP MYSQL

1 Answer 1

Examining this piece of code:

Error 1 - You escape the string before ever setting a value.

Error 2 - You do not give the date() function appropriate parameters

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Examining this piece of code:

Error 1 - You escape the string before ever setting a value.

Error 2 - You do not give the date() function appropriate parameters

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related