msql query

edited May 2011 in Internet & Media
function NOW(){ return date('Y-m-d'); }
$rs_ctime = mysql_query("select `date` from `users` where `id` ='$user_id'") or die(mysql_error());
list($date) = mysql_fetch_row($rs_ctime);

if( (now() - $ctime) > 2days) {
////this is where i am stuck
i want to echo the result of the date each user registered until today.if the days are more than two days then i want to sent an email etc.

please help

Comments

  • ButtersButters CA Icrontian
    edited May 2011
    couldn't you just change your query so that selects older dates instead of evaluating in another function?
  • edited May 2011
    what would i have to do to change the query
  • AlexDeGruvenAlexDeGruven Wut? Meechigan Icrontian
    edited May 2011
    What format is the date field in?

    All you would have to do is set your query to something like "select <whatever> from <wherever> where `date` > <somedate> order by `date`"

    Then you would only get users whose last date is older than the date you specify, which you can then use to build the rest of what you're looking at.
  • edited May 2011
    Butters wrote:
    couldn't you just change your query so that selects older dates instead of evaluating in another function?
    horsespeed wrote:
    how do i change the query
  • edited May 2011
    0000-00-00 year month date (2011-05-01)
    when user registers the date is recorded . i want to check if user has not activated link after two days so that an email will be automatically sent via cron
  • AlexDeGruvenAlexDeGruven Wut? Meechigan Icrontian
    edited May 2011
    So grab your date from PHP, subtract 2 days, format it in that manner, and use that for your query date (change it to a less-than, rather than the greater-than I put in there).

    Then you can check and see if their activation link has been visited and send your e-mail accordingly. That reduces the length of your query, and reduces the complexity of your post-query processing.
  • edited May 2011
    i like that..only one problem though i have plenty users .how do i first grab the date per each user and then run the query
  • AlexDeGruvenAlexDeGruven Wut? Meechigan Icrontian
    edited May 2011
    Are you thinking of a specific list of users, or do you just want those who have registered more than 2 days ago, but not yet activated?
  • edited May 2011
    more than two days ago and not registered
  • AlexDeGruvenAlexDeGruven Wut? Meechigan Icrontian
    edited May 2011
    You could just add an additional qualifier. What is the activation field's content?
  • edited May 2011
    i dnt know if this will help. when the activation link is activated by user it automatically updates the approved field to 1. otherwise it wont allow them to log in.
    so the qualifier can be
    if(!$approved){
  • AlexDeGruvenAlexDeGruven Wut? Meechigan Icrontian
    edited May 2011
    That would be easy, then.

    In pseudocode, since I don't want to bother taking the time to do your work for you :)

    $day = date (d) - 2 //Gives us the number of the day minus 2. Will have to account for early in the month, etc, but you get the idea...
    $date = date (Y-m-) + $day
    $query = select <stuff> from <somewhere> where `regdate` < $date AND `approved` != 1 ORDER BY regdate

    That query will return any user data where the user has been registered for more than 2 days but has not yet activated their account.
  • edited May 2011
    thank you so much for your help. i will try it and let you know. btw if i want to test it using echo for the result will the echo"$query" be right
  • AlexDeGruvenAlexDeGruven Wut? Meechigan Icrontian
    edited May 2011
    Yeah, you can build the query into a $query variable and then echo that out. When it looks the way you want, you can then issue (I'm assuming you're using php):

    $result = mysql_query($query);

    And then grab your rows in whatever method you feel is appropriate.
  • edited May 2011
    Parse error: syntax error, unexpected ')' in /var/www/vhosts/mysite.co.za/httpdocs/mycronscript.php on line 20

    i am getting the above error for the line marked error
    $day = date (d) - 2; //Gives us the number of the day minus 2. Will have to account for early in the month, etc, but you get the idea...
    $date = date (Y-m-) + $day; ERROR
    $query = select <user_name> from users where `date` < $date AND `approved` != 1 ORDER BY date
    $result = mysql_query($query);
    echo "($user_name)";
  • KwitkoKwitko Sheriff of Banning (Retired) By the thing near the stuff Icrontian
    edited May 2011
    $date = date ("Y-m-$day") ;
    
  • edited May 2011
    Parse error: syntax error, unexpected T_STRING

    $query = select user_name from users where `date` < $date AND `approved` != 1 ORDER BY date
  • edited May 2011
    $query = select 'user_name' from users where `date` < $date AND `approved` != 1 ORDER BY date

    Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING
  • KwitkoKwitko Sheriff of Banning (Retired) By the thing near the stuff Icrontian
    edited May 2011
    You're better off using this to set your date two days ago:
    $date = date("Y-m-d",strtotime("-2 days"));
    
  • AlexDeGruvenAlexDeGruven Wut? Meechigan Icrontian
    edited May 2011
    I gave it to you in pseudocode. That wasn't intended to be used literally. I don't have the time during my work hours to go through and create/debug code.

    What Kwitko posted should work for what you're looking for, and will account for a first-of-the-month date.
  • edited May 2011
    $query = select 'user_name' from users where `date` < $date AND `approved` != 1 ORDER BY date

    Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING

    'date' above is the field on my database when user registered. what is the unexpected T_CONSTANT
  • KwitkoKwitko Sheriff of Banning (Retired) By the thing near the stuff Icrontian
    edited May 2011
  • ButtersButters CA Icrontian
    edited May 2011
    Please make sure that everything in your code that supposed to be in quotations are in quotations and everything that needs to end with a semicolon, end with a semicolon.
Sign In or Register to comment.