Jump to content


Photo

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


  • Please log in to reply
13 replies to this topic

#1 lovephp

lovephp

    Advanced Member

  • Members
  • PipPipPip
  • 235 posts

Posted 12 July 2014 - 11:34 AM

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, 12 July 2014 - 11:36 AM.


#2 ginerjm

ginerjm

    Advanced Member

  • Members
  • PipPipPip
  • 1,766 posts
  • LocationVoorheesville NY

Posted 12 July 2014 - 12:04 PM

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

 

http://dev.mysql.com...ummary-ref.html


JG

PS - If you're posting here you should be using:
        error_reporting(E_ALL | E_NOTICE);
        ini_set('display_errors', '1');

at the top of ALL php code while you develop it!


#3 lovephp

lovephp

    Advanced Member

  • Members
  • PipPipPip
  • 235 posts

Posted 12 July 2014 - 12:35 PM

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



#4 Ch0cu3r

Ch0cu3r

    Advanced Member

  • Moderators
  • 2,324 posts

Posted 12 July 2014 - 01:01 PM

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)


#5 lovephp

lovephp

    Advanced Member

  • Members
  • PipPipPip
  • 235 posts

Posted 12 July 2014 - 01:33 PM

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, 12 July 2014 - 01:38 PM.


#6 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,423 posts
  • LocationCheshire, UK

Posted 12 July 2014 - 01:50 PM

I think you want > instead of <

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

moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#7 lovephp

lovephp

    Advanced Member

  • Members
  • PipPipPip
  • 235 posts

Posted 12 July 2014 - 02:10 PM

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, 12 July 2014 - 02:15 PM.


#8 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,423 posts
  • LocationCheshire, UK

Posted 12 July 2014 - 03:53 PM

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.


moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#9 lovephp

lovephp

    Advanced Member

  • Members
  • PipPipPip
  • 235 posts

Posted 12 July 2014 - 11:19 PM

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, 12 July 2014 - 11:24 PM.


#10 lovephp

lovephp

    Advanced Member

  • Members
  • PipPipPip
  • 235 posts

Posted 13 July 2014 - 12:19 AM

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



#11 lovephp

lovephp

    Advanced Member

  • Members
  • PipPipPip
  • 235 posts

Posted 13 July 2014 - 12:32 AM

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, 13 July 2014 - 12:32 AM.


#12 lovephp

lovephp

    Advanced Member

  • Members
  • PipPipPip
  • 235 posts

Posted 13 July 2014 - 10:15 AM

Anyone?

#13 mac_gyver

mac_gyver

    Advanced Member

  • Administrators
  • 2,567 posts

Posted 13 July 2014 - 10:53 AM

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.


multi-purpose programming fool. well written source-code should be self-documenting. well written code should be self-troubleshooting. 


#14 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,423 posts
  • LocationCheshire, UK

Posted 13 July 2014 - 03:04 PM

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         |
+-----------------------------+

moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com