deepson2 Posted May 14, 2009 Share Posted May 14, 2009 Hello, my while loop is not working properly. its not considering my first row value. from second row value its working fine. it just strange. can anyone please have look on my code and tell me what is wrong in there. <? $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 = '$uid' ORDER BY r.recent_visited DESC LIMIT $start, $limit_value"); if(mysql_num_rows($sqlpage) > 0){ ?> <p class="msgText">Total <strong><?=$total_records;?></strong><?if($total_records >1){ ?> Recent Visitor's<?}else{?> Recent Visitor<?}}?></p> <? if(mysql_num_rows($sqlpage) > 0){ while($row1 = $op->select($sqlpage)){ $imagearr = explode(",",$row1['avatar']); $imagearr[0]= preg_replace("/\/avatar\//","/small/", $imagearr[0]); ?> <a href='/profile/<?=$blog->spacereplace($row1['username']);?>/' title="<?=$row1['username']?>"><img src="<?=$retpath;?>/<?=$imagearr[0];?>" width="40" height="40" alt="<?=$row1['username']?>"/></a> <? $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 > 0 ){ echo "$Y year(s) "; }else if($MON > 0){ echo "$MON month(s) "; } else if($W > 0){ echo "$W week(s) "; } else if($D > 0){ echo "$D day(s) "; } else if($H > 0){ echo "$H hour(s) "; } else if($minutes_dif > 0){ echo "$minutes_dif minute(s) "; } echo "ago"; ?>(<?=$row1['visiting_count']?>visits)<? echo"\t"; $i++; } } ?> when i echoed my query, i found that my database table is like this visitor_id visiting_count MinsSinceLastVisit 12 23 0 15 26 21 11 35 27 10 27 961 For the first row my last column value(MinsSinceLastVisit)is not increasing .it means my first value is not changing only.any suggestion? thanks in advance. Quote Link to comment https://forums.phpfreaks.com/topic/158092-solved-while-loop-is-not-working-properly/ Share on other sites More sharing options...
sqlnoob Posted May 14, 2009 Share Posted May 14, 2009 the first row is 0, so the while should start at minus 1 if i'm not mistaken, since you're moving 1 row further with each loop. the while therefore is simply not fetching the first row and starts at the second row instead Quote Link to comment https://forums.phpfreaks.com/topic/158092-solved-while-loop-is-not-working-properly/#findComment-833955 Share on other sites More sharing options...
deepson2 Posted May 14, 2009 Author Share Posted May 14, 2009 the first row is 0, so the while should start at minus 1 if i'm not mistaken, since you're moving 1 row further with each loop. the while therefore is simply not fetching the first row and starts at the second row instead thanks for your reply but where to put -1 ? you mean to say here i put -1 , if(mysql_num_rows($sqlpage) > 0){ i checked that. no luck. But after sometime when i checked my query in SQL again. it showing the changes. like this. visitor_id visiting_count MinsSinceLastVisit 12 23 2 15 26 121 11 35 227 10 27 461 but its not coming in my form output(i mean my first row's output is showing only ago not like 2 minute(s) ago or somthing else). please help me. i juset stucked ??? ??? since long long time. wait. when i refreshed my sql page it becomes 0 (last column value) i am sorry, i am just telling here what exactly happening Quote Link to comment https://forums.phpfreaks.com/topic/158092-solved-while-loop-is-not-working-properly/#findComment-833963 Share on other sites More sharing options...
kickstart Posted May 14, 2009 Share Posted May 14, 2009 Hi You are not extracting a value for $minutes_dif from the returned data. Quite how it gets any value I am not sure. Add $minutes_dif = $row1['MinsSinceLastVisit']; after the line while($row1 = $op->select($sqlpage)){. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/158092-solved-while-loop-is-not-working-properly/#findComment-833972 Share on other sites More sharing options...
deepson2 Posted May 14, 2009 Author Share Posted May 14, 2009 Hello Kieth, I am very much extracting $minutes_dif from while loop only. ok i have tried whatever you said. no luck. i would like to show my table structure here so if you could check with database as well CREATE TABLE `recent_visitor` ( `profile_owner` int(11) NOT NULL, `visitor_id` int(11) NOT NULL, `first_visited` timestamp NOT NULL default '0000-00-00 00:00:00', `recent_visited` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `visiting_count` int(11) NOT NULL default '1', PRIMARY KEY (`profile_owner`,`visitor_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; please help me Quote Link to comment https://forums.phpfreaks.com/topic/158092-solved-while-loop-is-not-working-properly/#findComment-833984 Share on other sites More sharing options...
kickstart Posted May 14, 2009 Share Posted May 14, 2009 Hi Can you post you current (updated script) and also a few lines of data for the table? All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/158092-solved-while-loop-is-not-working-properly/#findComment-834008 Share on other sites More sharing options...
deepson2 Posted May 14, 2009 Author Share Posted May 14, 2009 <? $blogdata = $blog->personaldata(); if($blogdata->author != $userid){ //echo $from; $query = $op->insert( "INSERT INTO recent_visitor (profile_owner, visitor_id, first_visited, recent_visited, visiting_count) VALUES ('".$blogdata->author."', '".$userid."', NOW(), NOW(), 1) ON DUPLICATE KEY UPDATE `visiting_count`=`visiting_count`+1"); if($query > 0){ $err_msg_shout="Shout Posted"; }else{ $err_msg_shout="Error, Try Again"; } } //echo "SELECT a.username,r.visitor_id FROM recent_visitor as r ,author as a WHERE (r.visitor_id = a.id) AND r.profile_owner = '$uid' ORDER BY r.recent_visited DESC "; $result = $op->runsql("SELECT count(*) as cnt FROM author a JOIN recent_visitor r ON a.id = r.visitor_id WHERE r.profile_owner= '$uid' "); $limit_value = 5; $ts = mysql_fetch_array($result); $total_records = $ts['cnt']; $top = $total_records / $limit_value; $total_pages = ceil($top); $start = $_REQUEST['start'] == "" ? 0 : $_REQUEST['start']; $imagarr=array(); $i=0; echo $; $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.visiting_count DESC LIMIT $start, $limit_value"); if(mysql_num_rows($sqlpage) > 0){ ?> <p class="msgText">Total <strong><?=$total_records;?></strong><?if($total_records >1){ ?> Recent Visitor's<?}else{?> Recent Visitor<?}}?></p> <? if(mysql_num_rows($sqlpage) > 0){ while($row1 = $op->select($sqlpage)){ $minutes_dif = $row1['MinsSinceLastVisit']; $imagearr = explode(",",$row1['avatar']); $imagearr[0]= preg_replace("/\/avatar\//","/small/", $imagearr[0]); ?> <a href='/profile/<?=$blog->spacereplace($row1['username']);?>/' title="<?=$row1['username']?>"><img src="<?=$retpath;?>/<?=$imagearr[0];?>" width="40" height="40" alt="<?=$row1['username']?>"/></a><? $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 > 0 ) echo "$Y year(s) "; if($MON > 0) echo "$MON month(s) "; if($W > 0) echo "$W week(s) "; if($D > 0) echo "$D day(s) "; if($H > 0) echo "$H hour(s) "; if($minutes_dif > 0) echo "$minutes_dif minute(s) "; echo "ago"; ?>(<?=$row1['visiting_count']?>visits)<? echo"\t"; $i++; } } ?> $blogdata->author = the profile page of user $userid = logged in user my table profile_owner visitor_id first_visited recent_visited visiting_count 3 22 2009-05-14 17:01:44 2009-05-14 17:09:28 5 3 7932 2009-05-14 11:39:45 2009-05-14 11:39:45 1 9 14 2009-05-12 15:31:27 2009-05-12 15:31:27 1 9 22 2009-05-14 14:55:13 2009-05-14 15:13:54 8 10 14 2009-05-12 15:31:09 2009-05-12 15:31:09 1 14 22 2009-05-14 15:13:56 2009-05-14 15:13:56 1 please ask me. please ask me if you wont understand anything. please help me Quote Link to comment https://forums.phpfreaks.com/topic/158092-solved-while-loop-is-not-working-properly/#findComment-834017 Share on other sites More sharing options...
kickstart Posted May 14, 2009 Share Posted May 14, 2009 Hi To be honest I don't know. I reformatted it so I could read it more easily, but can see nothing wrong:- <? $blogdata = $blog->personaldata(); if($blogdata->author != $userid) { //echo $from; $query = $op->insert( "INSERT INTO recent_visitor (profile_owner, visitor_id, first_visited, recent_visited, visiting_count) VALUES ('".$blogdata->author."', '".$userid."', NOW(), NOW(), 1) ON DUPLICATE KEY UPDATE `visiting_count`=`visiting_count`+1"); if($query > 0) { $err_msg_shout="Shout Posted"; } else { $err_msg_shout="Error, Try Again"; } } //echo "SELECT a.username,r.visitor_id FROM recent_visitor as r ,author as a WHERE (r.visitor_id = a.id) AND r.profile_owner = '$uid' ORDER BY r.recent_visited DESC "; $result = $op->runsql("SELECT count(*) as cnt FROM author a JOIN recent_visitor r ON a.id = r.visitor_id WHERE r.profile_owner= '$uid' "); $Ymin = 60 * 24 * 365; $Mmin = 60 * 24 * 30; $Wmin = 60 * 24 * 7; $Dmin = 60 * 24; $Hmin = 60; $limit_value = 5; $ts = mysql_fetch_array($result); $total_records = $ts['cnt']; $top = $total_records / $limit_value; $total_pages = ceil($top); $start = $_REQUEST['start'] == "" ? 0 : $_REQUEST['start']; $imagarr=array(); $i=0; echo $; $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.visiting_count DESC LIMIT $start, $limit_value"); if(mysql_num_rows($sqlpage) > 0) { ?><p class="msgText">Total <strong><?=$total_records;?></strong><?if($total_records >1){?> Recent Visitor's<?}else{?> Recent Visitor<?}?></p><? while($row1 = $op->select($sqlpage)) { $imagearr = explode(",",$row1['avatar']); $imagearr[0]= preg_replace("/\/avatar\//","/small/", $imagearr[0]); ?> <a href='/profile/<?=$blog->spacereplace($row1['username']);?>/' title="<?=$row1['username']?>"><img src="<?=$retpath;?>/<?=$imagearr[0];?>" width="40" height="40" alt="<?=$row1['username']?>"/></a><? $minutes_dif = $row1['MinsSinceLastVisit']; $Y = intval($minutes_dif / $Ymin); $minutes_dif = $minutes_dif % $Ymin; $MON = intval($minutes_dif / $Mmin); $minutes_dif = $minutes_dif % $Mmin; $W = intval($minutes_dif / $Wmin); $minutes_dif = $minutes_dif % $Wmin; $D = intval($minutes_dif / $Dmin); $minutes_dif = $minutes_dif % $Dmin; $H = intval($minutes_dif / $Hmin); $minutes_dif = $minutes_dif % $Hmin; if($Y > 0 ) echo "$Y year(s) "; if($MON > 0) echo "$MON month(s) "; if($W > 0) echo "$W week(s) "; if($D > 0) echo "$D day(s) "; if($H > 0) echo "$H hour(s) "; if($minutes_dif > 0) echo "$minutes_dif minute(s) "; echo "ago"; ?>(<?=$row1['visiting_count']?>visits)<? echo"\t"; $i++; } } ?> The SQL itself seems to be fine. Only thing I can think is that for the first row the last visited time is in the future and hence returns a negative number (which the php code would then ignore). All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/158092-solved-while-loop-is-not-working-properly/#findComment-834090 Share on other sites More sharing options...
sqlnoob Posted May 14, 2009 Share Posted May 14, 2009 thanks for your reply but where to put -1 ? you mean to say here i put -1 , if(mysql_num_rows($sqlpage) > 0){ i checked that. no luck. But after sometime when i checked my query in SQL again. it showing the changes. yes that is where i'd putting it. you were doing num row and the first row of an sql table is always numbered 0. its when you do auto-increment say for example ID's that the first row becomes 1 anyway good luck my friend. I never did a while loop before myself, I just knew the way things numbered Quote Link to comment https://forums.phpfreaks.com/topic/158092-solved-while-loop-is-not-working-properly/#findComment-834118 Share on other sites More sharing options...
kickstart Posted May 14, 2009 Share Posted May 14, 2009 Hi mysql_num_rows brings back the number of rows that the sql has found. It does not return any of the rows itself. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/158092-solved-while-loop-is-not-working-properly/#findComment-834124 Share on other sites More sharing options...
deepson2 Posted May 15, 2009 Author Share Posted May 15, 2009 kickstart, thanks a lot for being patient with me. well, we haven't think about here logically. see, i will explain, suppose A is logged in user. and he/she is visited to B's profile then A would be first recent_visited user for B's profile right. so A's time ll be never shown only. because if A again come after 5 to 10 min again on the B's profile page. then again A's recent time would be the current time only. so for anyone who is logged in and the user visited to someone else profile that time will be never shown. because it ll be their recent most time or we can say exact time which would be going on. so it ll be never shown only. one of my senior explain me this, while i was about to leave my office. and i spent(even you tried much) whole day behind it. i am going to declare this thread as solved one. if you would you like to ask me related to this topic. you can send me message. thanks a lot! Hope you understand this. and even you get the clear idea about what i am talking here. sqlnoob, thanks a lot for your reply too friend Quote Link to comment https://forums.phpfreaks.com/topic/158092-solved-while-loop-is-not-working-properly/#findComment-834542 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.