Jump to content

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


lovephp

Recommended Posts

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 by lovephp
Link to comment
Share on other sites

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 by lovephp
Link to comment
Share on other sites

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 by lovephp
Link to comment
Share on other sites

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 by lovephp
Link to comment
Share on other sites

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;
            }
        }

Link to comment
Share on other sites

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 by lovephp
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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         |
+-----------------------------+
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.