redarrow Posted August 6, 2006 Share Posted August 6, 2006 i got a real hard problam here i have tried DESC and ASC but no joy and all the orders under the sun but i can not get the database while loop to ASC the last time_check from the loop to go into varable time check.please help.from the database the times from the while loop.[code]10:06:1410:46:2810:46:5012:44:4112:46:5805:04:5405:30:4310:18:3110:18:4710:23:0410:24:1010:28:5910:56:3810:58:1010:58:2510:58:42 [/code]i need time_check to be the last time_check in the while loop == 10:58:42[code]$query_time="select * from member_topics where user_id='$user_id'";$result_time=mysql_query($query_time);while($timmed=mysql_fetch_assoc($result_time)){$time_check=$timmed['time_added'];echo $time_check;}[/code] Link to comment https://forums.phpfreaks.com/topic/16696-real-hard-database-sort-problam-cheers/ Share on other sites More sharing options...
king arthur Posted August 6, 2006 Share Posted August 6, 2006 Not quite sure I understand your problem? These times are in your table 'member_topics' in that order? And you just want the variable to end up holding the last one? If you just run the code you've got it should end up holding the last value. Link to comment https://forums.phpfreaks.com/topic/16696-real-hard-database-sort-problam-cheers/#findComment-70126 Share on other sites More sharing options...
redarrow Posted August 6, 2006 Author Share Posted August 6, 2006 that is the out put of the time value,becouse the user id has got all them times but as seprate entrys thats way i wanted to do an order via ASC and get the last time and set it to the time_added varable but no luck. Link to comment https://forums.phpfreaks.com/topic/16696-real-hard-database-sort-problam-cheers/#findComment-70133 Share on other sites More sharing options...
king arthur Posted August 6, 2006 Share Posted August 6, 2006 Hold on, so you mean that is one single string pulled from a column in one single row? Link to comment https://forums.phpfreaks.com/topic/16696-real-hard-database-sort-problam-cheers/#findComment-70136 Share on other sites More sharing options...
redarrow Posted August 6, 2006 Author Share Posted August 6, 2006 all the colums with the users id from the time_added colums yep.becouse the user has 16 entrys and the date is the same and time is the only thing that the user has in the colums to get any order but nothink i tried works. Link to comment https://forums.phpfreaks.com/topic/16696-real-hard-database-sort-problam-cheers/#findComment-70138 Share on other sites More sharing options...
king arthur Posted August 6, 2006 Share Posted August 6, 2006 Right so the times are in separate rows, and you want the last one. So, run your code as it is. After the while loop, the $time_check variable will hold the value "10:58:42". Unless I'm still missing something here? Link to comment https://forums.phpfreaks.com/topic/16696-real-hard-database-sort-problam-cheers/#findComment-70139 Share on other sites More sharing options...
redarrow Posted August 6, 2006 Author Share Posted August 6, 2006 that the problam it is holding the first time i am getting 10;06;14 the first entry Link to comment https://forums.phpfreaks.com/topic/16696-real-hard-database-sort-problam-cheers/#findComment-70140 Share on other sites More sharing options...
ToonMariner Posted August 6, 2006 Share Posted August 6, 2006 <?php$query_time="select `time_added` from `member_topics` where `user_id`='$user_id' ORDER BY `time_added` DESC";$result_time=mysql_query($query_time);$timmed=mysql_fetch_assoc($result_time);$time_check=$timmed['time_added'];?>That should do it. Link to comment https://forums.phpfreaks.com/topic/16696-real-hard-database-sort-problam-cheers/#findComment-70142 Share on other sites More sharing options...
king arthur Posted August 6, 2006 Share Posted August 6, 2006 [quote author=redarrow link=topic=103158.msg410606#msg410606 date=1154862047]that the problam it is holding the first time i am getting 10;06;14 the first entry[/quote]Are you sure, because if that is the value you want, and that is the last value that was echoed, that should be the value $time_check should end up holding when the while loop finishes. But to be honest this is a poor way of entering time values into a database, you should store a timestamp and convert it into the date format you want, then you can get MySQL to sort it in whichever order you like. As it stands a sort won't work because the time values appear to be in 12 hour format. Link to comment https://forums.phpfreaks.com/topic/16696-real-hard-database-sort-problam-cheers/#findComment-70145 Share on other sites More sharing options...
redarrow Posted August 6, 2006 Author Share Posted August 6, 2006 that last post was near but didnt work all it done was add time to a time that was not in the database going mad ..............[code]<?php$query_time="select * from member_topics where user_id='$user_id' ORDER BY time_added desc";$result_time=mysql_query($query_time);$timmed=mysql_fetch_assoc($result_time);$time_check=$timmed['time_added'];echo $time_check;$check=strtotime($time_check);echo "<br>$check<br>";$time_know=time()-10;echo "<br>$time_know<br>";if($check > $time_know){echo "<html><body vlink='black' alink='black' link='black'><br><br><br><table bordercolor='black'border='4' align='center'><td align='center'><h1><font color='red'>Blob it! </font> <h1>Post valadation Warning!</h1></td></table><br><div align='center'></font><br><br><b>You must wait 2 minutes to post your next blog!</b><br><br><font color='red'>Please use this link to see all our <a href='see.php'>Blogs</a></font><br><br><br><br><br><br><br><br><br><br><br><br><br><hr color='black'><font color='red'>((c))[email protected] --blog it! --</font><hr color='black'></hr></body></html></div>";exit;}?>[/code] Link to comment https://forums.phpfreaks.com/topic/16696-real-hard-database-sort-problam-cheers/#findComment-70155 Share on other sites More sharing options...
king arthur Posted August 6, 2006 Share Posted August 6, 2006 Well that code will only get the first time in the database not the last, as you are not iterating through the results in the while loop. Link to comment https://forums.phpfreaks.com/topic/16696-real-hard-database-sort-problam-cheers/#findComment-70158 Share on other sites More sharing options...
redarrow Posted August 6, 2006 Author Share Posted August 6, 2006 know shows all the times madness hay any idears ans cheers.[code]<?php$query_time="select * from member_topics where user_id='$user_id' ORDER BY time_added desc";$result_time=mysql_query($query_time);while($timmed=mysql_fetch_assoc($result_time)){$time_check=$timmed['time_added'];echo $time_check;}$check=strtotime($time_check);echo "<br>$check<br>";$time_know=time()-10;echo "<br>$time_know<br>";if($check > $time_know){echo "<html><body vlink='black' alink='black' link='black'><br><br><br><table bordercolor='black'border='4' align='center'><td align='center'><h1><font color='red'>Blob it! </font> <h1>Post valadation Warning!</h1></td></table><br><div align='center'></font><br><br><b>You must wait 2 minutes to post your next blog!</b><br><br><font color='red'>Please use this link to see all our <a href='see.php'>Blogs</a></font><br><br><br><br><br><br><br><br><br><br><br><br><br><hr color='black'><font color='red'>((c))[email protected] --blog it! --</font><hr color='black'></hr></body></html></div>";exit;}?>[/code] Link to comment https://forums.phpfreaks.com/topic/16696-real-hard-database-sort-problam-cheers/#findComment-70159 Share on other sites More sharing options...
king arthur Posted August 6, 2006 Share Posted August 6, 2006 Change this:[code]$result_time=mysql_query($query_time);while($timmed=mysql_fetch_assoc($result_time)){$time_check=$timmed['time_added'];echo $time_check;}[/code]to this:[code]$result_time=mysql_query($query_time);while($timmed=mysql_fetch_assoc($result_time)){$time_check=$timmed['time_added'];}echo $time_check;[/code] Link to comment https://forums.phpfreaks.com/topic/16696-real-hard-database-sort-problam-cheers/#findComment-70160 Share on other sites More sharing options...
redarrow Posted August 6, 2006 Author Share Posted August 6, 2006 solved dont know why but works like this.[code]<?php$query_time="select time_added from member_topics where user_id='$user_id' ORDER BY time_added ";$result_time=mysql_query($query_time);while($timmed=mysql_fetch_assoc($result_time)){$time_check=$timmed['time_added'];}$check=strtotime($time_check);$time_know=time()-60;echo "<br>$time_know<br>";if($check > $time_know){echo "<html><body vlink='black' alink='black' link='black'><br><br><br><table bordercolor='black'border='4' align='center'><td align='center'><h1><font color='red'>Blob it! </font> <h1>Post valadation Warning!</h1></td></table><br><div align='center'></font><br><br><b>You must wait 2 minutes to post your next blog!</b><br><br><font color='red'>Please use this link to see all our <a href='see.php'>Blogs</a></font><br><br><br><br><br><br><br><br><br><br><br><br><br><hr color='black'><font color='red'>((c))[email protected] --blog it! --</font><hr color='black'></hr></body></html></div>";exit;}[/code] Link to comment https://forums.phpfreaks.com/topic/16696-real-hard-database-sort-problam-cheers/#findComment-70162 Share on other sites More sharing options...
shoz Posted August 6, 2006 Share Posted August 6, 2006 Note that if you only want the last time entry for the user you can also use the MYSQL function MAX()[code]SELECT MAX(timecolumn) AS timecolumn FROM etc WHERE userid = etc[/code][url=http://dev.mysql.com/doc/refman/4.1/en/group-by-functions.html]Aggregate Functions[/url] Link to comment https://forums.phpfreaks.com/topic/16696-real-hard-database-sort-problam-cheers/#findComment-70163 Share on other sites More sharing options...
ToonMariner Posted August 6, 2006 Share Posted August 6, 2006 it should get the LAST time that user added any thing - and he did say he only wanted the last time....redarrow - what is the out put of that script? (perhaps remove the exit; command as well ;)) Link to comment https://forums.phpfreaks.com/topic/16696-real-hard-database-sort-problam-cheers/#findComment-70164 Share on other sites More sharing options...
king arthur Posted August 6, 2006 Share Posted August 6, 2006 The problem is that he seems to be adding times in 12 hour format so any kind of sort won't work - he has to rely on getting the last row inserted, which isn't very reliable. Redarrow you should at least store the time and date together so you can sort them properly before pulling the data out. I would do it with a timestamp inserted with NOW() in the query, then when pulling it out use "UNIX_TIMESTAMP(time_added) as time_stamp" in the query, then you can compare that directly with the current time with "if $time_stamp > time() - 120" etc... Link to comment https://forums.phpfreaks.com/topic/16696-real-hard-database-sort-problam-cheers/#findComment-70169 Share on other sites More sharing options...
shoz Posted August 6, 2006 Share Posted August 6, 2006 [quote author=king arthur link=topic=103158.msg410640#msg410640 date=1154865990]The problem is that he seems to be adding times in 12 hour format so any kind of sort won't work - he has to rely on getting the last row inserted, which isn't very reliable. Redarrow you should at least store the time and date together so you can sort them properly before pulling the data out. I would do it with a timestamp inserted with NOW() in the query, then when pulling it out use "UNIX_TIMESTAMP(time_added) as time_stamp" in the query, then you can compare that directly with the current time with "if $time_stamp > time() - 120" etc...[/quote]I should have read the thread more closely. I'd have to agree. You should be storing the information in the way suggested by king arthur. After doing that you can add an index on (user_id_column, datetime column) and use the MAX() function as described. Link to comment https://forums.phpfreaks.com/topic/16696-real-hard-database-sort-problam-cheers/#findComment-70196 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.