[SOLVED] query is not working on sever

[deepson2]

Hello,

 

I was putting my new module into my server but its working on my local m/c.  its seems it showing me the error for TIMESTAMPDIFF.

 

my locals version is

MySQL client version: 4.1.7

Server version: 5.0.22-community-nt

 

and for server it is

MySQL client version: 4.1.11

Server version: 4.1.25 debian-mtl

 

and this is my query which runs perfectly on my local m/c

 

$sqlpage    = $op->runsql("SELECT  a.username,a.avatar,r.visitor_id,r.visiting_count,TIMESTAMPDIFF(MINUTE, r.recent_visited, NOW()) AS MinsSinceLastVisit FROM recent_visitor as r ,author as a WHERE (r.visitor_id = a.id) AND r.profile_owner = '$blogdata->author' ORDER BY r.recent_visited DESC  LIMIT $start, $limit_value");

 

and the error is as bellow:

 

 

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(MINUTE, r.recent_visited, NOW()) AS MinsSinceLastVisit FROM recent_visitor as r' at line 1

 

can anyone tell me why its not working on my server?

 

thanks in advance.

 

 

[trq]

TIMESTAMPDIFF requires at least 5.0.0

[deepson2]

thanks for your reply,

 

we have been using this server version since long time. if we upgrade this new version then do you think it ll be affected on the query/data are already there inthe database/pages?

 

or is there any option for timestampdiff?

[kickstart]

Hi

 

Bit of a fudge, but you could use TIMEDIFF (returns the difference in hours, mins and seconds) and then convert that to seconds using TIME_TO_SEC. If you want the result in mins then divide the result by 60:-

 

$sqlpage    = $op->runsql("SELECT  a.username,a.avatar,r.visitor_id,r.visiting_count,(TIME_TO_SEC(TIMEDIFF(now(),r.recent_visited)) / 60) AS MinsSinceLastVisit FROM recent_visitor as r ,author as a WHERE (r.visitor_id = a.id) AND r.profile_owner = '$blogdata->author' ORDER BY r.recent_visited DESC  LIMIT $start, $limit_value");

 

Although an upgrade if safe to do so it probably the best thing to do.

 

All the best

 

Keith

[deepson2]

Hello Keith,

it seems your are query genius. right ??

 

as you know both are your queries only. i checked my output it seems both the query's are equivalent. coz you may remeber that i was counting the min,hours for the users last visit.

 

so with your new query result is like this

MinsSinceLastVisit

1581.6333

10290.8833

11560.0000

24675.4500

36355.9500

36473.3167

37411.9500

and with timestampdiff, it is like this

MinsSinceLastVisit

1590

10299

11568

24684

36364

36482

37420

37764

37844

so i guess it is preety similar. because i was donig somthing like this

 $minutes_dif = $row1['MinsSinceLastVisit'];
       $Ymin = 60 * 24 * 365;
       $Mmin = 60 * 24 * 30;
       $Wmin = 60 * 24 * 7;
       $Dmin = 60 * 24;
       $Hmin = 60;


       $Y = (int)($minutes_dif / $Ymin);
       $minutes_dif = $minutes_dif % $Ymin;

       $MON = (int)($minutes_dif / $Mmin);
       $minutes_dif = $minutes_dif % $Mmin;

       $W = (int)($minutes_dif / $Wmin);
       $minutes_dif = $minutes_dif % $Wmin;

       $D = (int)($minutes_dif / $Dmin);
       $minutes_dif = $minutes_dif % $Dmin;

       $H = (int)($minutes_dif / $Hmin);
       $minutes_dif = $minutes_dif % $Hmin;

        if($Y == 1 ){
   	echo "$Y year ";
        }else if($Y > 0 ){
   	echo "$Y year(s) ";
         }else if($MON ==1 ){
   	  echo "$MON month ";
   	  }else if($MON > 0){
	 echo "$MON month(s) ";
   	   } else if($W == 1){
   	   echo "$W week ";
   	   } else if($W > 0){
	    echo "$W week(s) ";
   	   }else if($D == 1){
   	   echo "$D day ";
   	   } else if($D > 0){
	   echo "$D day(s) ";
   	   }else if($H == 1){
            echo "$H hour ";
	   }else if($H > 0){
            echo "$H hours ";
            }else if($minutes_dif == 1){
            echo "$minutes_dif minute ";
           }else if($minutes_dif > 0) echo "$minutes_dif minute(s) ";
           else if($minutes_dif <=0) echo "About a minute ";
      echo "ago";

so could you please check that and tell me if i need to do any changes with my query?

 

and another question

 

we have been using this server version since long time. if we upgrade this new version then do you think it ll be affected on the query/data are already there inthe database/pages?

 

please do reply.

 

[kickstart]

Hi

 

At a guess there was 9 seconds between your 2 runs.

 

Probably best to do a conversion to integer ( CAST((TIME_TO_SEC(TIMEDIFF(now(),r.recent_visited)) / 60) AS UNSIGNED) ), but it should work like that.

 

Changing the version of SQL could possibly give problems and would be worth testing first. However long term it is the best solution.

 

All the best

 

Keith

[deepson2]

Hello keith

 

( CAST((TIME_TO_SEC(TIMEDIFF(now(),r.recent_visited)) / 60) AS UNSIGNED) )

using this i got the same result like i used to get with timestampdiff. thanks a lot.

 

Changing the version of SQL could possibly give problems and would be worth testing first. However long term it is the best solution.

 

thnaks for your suggestion. ll take to my seniors.