check db if a phone number was added to db less than 31 days ago?

[lovephp]

hi friends could someone help with this criteria? i want to restrict entry to db if the post value number was entered into db less than 31 days ago?

 

if($homePhone == '') {
        $qry = "SELECT * FROM table WHERE homePhone='$homePhone '";
        $errmsg_arr[] = 'Data not posted, number was posted by someone less than 31 days ago';
        $errflag = true;
    }

 

in db i store time in datetime current timestamp like 2013-07-13 21:19:15

 

 

could someone help me out?

Edited July 12, 2014 by lovephp

[ginerjm]

Assuming you are using the MySQL db there are lots of functions available for date/time manipulation.  Check out this:'

 

http://dev.mysql.com/doc/refman/5.0/en/func-op-summary-ref.html

[lovephp]

yes mysql. would appreciate if someone could help me out im not getting idea how to get it done,

[Ch0cu3r]

You'd use the date_sub() aggregate function to take 31 days off of the current timestamp.

SELECT * FROM table WHERE honePhone='$homePhone' AND Your_Datetime_Column_Name_HERE < DATE_SUB(NOW(), INTERVAL 31 DAYS)

[lovephp]

i did this but data still gets posted?

 

$homePhone = mysql_real_escape_string($_POST['homePhone']);
if($homePhone != '') {
        $qry = "SELECT * FROM table WHERE homePhone='$homePhone' AND date_time < DATE_SUB(NOW(), INTERVAL 31 DAYS)";
        $result = mysql_query($qry);
            if(mysql_num_rows($result) > 0) {
                $errmsg_arr[] = 'Data not posted, number was already posted by someone less than 31 days ago';
                $errflag = true;
            }
        }

Edited July 12, 2014 by lovephp

[Barand]

I think you want > instead of <

SELECT * FROM table WHERE homePhone='$homePhone' AND date_time > CURDATE() - INTERVAL 31 DAYS

[lovephp]

still no affect data still gets submitted

 

$homePhone = mysql_real_escape_string($_POST['homePhone']);
if($homePhone != '') {
        $qry = "SELECT * FROM table WHERE homePhone='$homePhone' AND date_time > CURDATE() - INTERVAL 31 DAYS";
        $result = mysql_query($qry);
        if($result) {
            if(mysql_num_rows($result) > 0) {
                $errmsg_arr[] = 'Data not posted, number was already posted by someone less than 31 days ago';
                $errflag = true;
            }
        }
    }

my date column is like this

`date_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

Edited July 12, 2014 by lovephp

[Barand]

As we can only see this check you are making but can't see under what conditions the data actually gets submitted then cannot be of any more help.

[lovephp]

ok see i got this form where user submits survey and i want to make sure that a customer with same phone number does not get submitted into database in less than 31 days. over 31 days of being submitted it should get submitted but not less than 31 days

 

here is the code

$errmsg_arr = array();
    
    //Validation error flag
    $errflag = false;
    
    $customerName = mysql_real_escape_string($_POST['customerName']);
    $customerAddress = mysql_real_escape_string($_POST['customerAddress']);
    $homePhone = mysql_real_escape_string($_POST['homePhone']);

if($homePhone != '') {
            $time_count = 31*24*60*60;
            $match_time = time() - $time_count;
        $qry = "SELECT homePhone, count FROM data WHERE homePhone='$homePhone' AND `count` > '".$match_time."'";
        $result = mysql_query($qry);
            if(mysql_num_rows($result) > 0) {
                $errmsg_arr[] = 'Customer already exist, data was submitted less than 31 days ago, Try next!';
                $errflag = true;
            }
        }
    
    //If there are input validations, redirect back to the form
    if($errflag) {
        $_SESSION['ERRMSG_ARR'] = $errmsg_arr;
        session_write_close();
        header("location: add-record.php");
        exit();
    }

    //Create INSERT query
    $qry = "INSERT INTO data (customerName, customerAddress, homePhone, count
              ) VALUES
              ('$customerName', '$customerAddress', '$homePhone', '".time()."')";
    $result = @mysql_query($qry);
    
    //Check whether the query was successful or not
    if($result) {
        header("location: record-success.php");
        exit();
    }else {
        die("Query failed");
    }

Edited July 13, 2014 by lovephp

[lovephp]

this below lets number to be entered once but even if i change month in db to make it more than 31 days the data still not getting submitted it throws in the error message

if($homePhone != '') {
        $qry = "SELECT * FROM `table` WHERE `homePhone` = $homePhone AND `date_time` >= NOW() - INTERVAL 31 DAY";
        $result = mysql_query($qry);
            if(mysql_num_rows($result) > 0) {
                $errmsg_arr[] = 'Customer already exist, data was submitted by someone less than 31 days ago, Try next!';
                $errflag = true;
            }
        }

[lovephp]

tried it this way too but it lets post submitted just once but even if i change month in db to make it more than 31 days it wont let data get submitted

 

if($homePhone != '') {
        $qry = "SELECT homePhone, date_time FROM `table` WHERE homePhone LIKE '%".$homePhone."%' AND `date_time` > CURDATE() - INTERVAL 31 DAY";
        $result = mysql_query($qry);
            if(mysql_num_rows($result) > 0) {
                $errmsg_arr[] = 'Customer already exist, data was submitted less than 31 days ago, Try next!';
                $errflag = true;
            }
        }

Edited July 13, 2014 by lovephp

[lovephp]

Anyone?

[mac_gyver]

your code needs to ALWAYS test if the query ran without errors before trying to use any information from the query. you also need to remove the @ error suppressors from your code.

 

you would have found the days problem a lot quicker, and since your table/column names seem to be randomly changing in the posted code, its likely your query is failing due to an error, which would cause your code to think there isn't a matching row and would run the insert query.

 

since your column is a date/time value, you need to use NOW() in the interval calculation to give a date/time for the comparison with the column. i'm pretty sure comparing a date/time value with a date (that CURDATE() would return) won't compare correctly (though mysql may cast the date as a date/time value.)

 

have you ran the sql query statement directly against your database using a tool like phpmyadmin so that you know the query is doing what you expect?

 

edit: and in case it hasn't already been mentioned, the mysql_ functions are depreciated and obsolete. you should not spend your time using them in any new code as they will be removed in a future php version and the time you spend using them now will be wasted. you should be using either the mysqli_ or PDO database functions.

 

edit2:  $errmsg_arr[ ] = '...';  $errflag = true; you can use the $errmsg_arr array as the error flag. just test if the array is empty or not. this will reduce the number of lines of code, but will produce the same result.

[Barand]

mac_gyver,

 

CURDATE() works fine with datetime. It is the date with 00:00:00 time element;

mysql> SELECT * FROM date_sample;
+----+---------------------+-------+
| id | date_added          | name  |
+----+---------------------+-------+
|  1 | 2014-01-16 20:57:22 | Peter |
|  2 | 2014-01-16 20:57:22 | Paul  |
|  3 | 2014-08-16 20:57:22 | Mary  |
+----+---------------------+-------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM date_sample
    -> WHERE date_added < CURDATE();
+----+---------------------+-------+
| id | date_added          | name  |
+----+---------------------+-------+
|  1 | 2014-01-16 20:57:22 | Peter |
|  2 | 2014-01-16 20:57:22 | Paul  |
+----+---------------------+-------+
2 rows in set (0.00 sec)

or

mysql> SELECT CURDATE() - INTERVAL 8 HOUR;
+-----------------------------+
| CURDATE() - INTERVAL 8 HOUR |
+-----------------------------+
| 2014-07-12 16:00:00         |
+-----------------------------+