Jump to content


Photo

real hard database sort problam cheers.


  • Please log in to reply
17 replies to this topic

#1 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 06 August 2006 - 10:23 AM

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

i need time_check to be the last time_check in the while loop == 10:58:42
$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;

}

Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#2 king arthur

king arthur
  • Members
  • PipPipPip
  • Advanced Member
  • 335 posts
  • LocationUK HQ

Posted 06 August 2006 - 10:36 AM

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.
Sir Isaac Newton said "If I have seen farther, it is by standing on the shoulders of giants". But it is not recorded as to whether he said it before or after he was hit on the head by a falling apple.

#3 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 06 August 2006 - 10:47 AM

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.
Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#4 king arthur

king arthur
  • Members
  • PipPipPip
  • Advanced Member
  • 335 posts
  • LocationUK HQ

Posted 06 August 2006 - 10:52 AM

Hold on, so you mean that is one single string pulled from a column in one single row?
Sir Isaac Newton said "If I have seen farther, it is by standing on the shoulders of giants". But it is not recorded as to whether he said it before or after he was hit on the head by a falling apple.

#5 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 06 August 2006 - 10:55 AM

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.
Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#6 king arthur

king arthur
  • Members
  • PipPipPip
  • Advanced Member
  • 335 posts
  • LocationUK HQ

Posted 06 August 2006 - 10:58 AM

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?
Sir Isaac Newton said "If I have seen farther, it is by standing on the shoulders of giants". But it is not recorded as to whether he said it before or after he was hit on the head by a falling apple.

#7 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 06 August 2006 - 11:00 AM

that the problam it is holding the first time

i am getting 10;06;14 the first entry
Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#8 ToonMariner

ToonMariner
  • Members
  • PipPipPip
  • Advanced Member
  • 3,342 posts
  • LocationNewcastle upon Tyne, UK

Posted 06 August 2006 - 11:03 AM

<?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.

follow me on twitter @PHPsycho

#9 king arthur

king arthur
  • Members
  • PipPipPip
  • Advanced Member
  • 335 posts
  • LocationUK HQ

Posted 06 August 2006 - 11:08 AM

that the problam it is holding the first time

i am getting 10;06;14 the first entry


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.
Sir Isaac Newton said "If I have seen farther, it is by standing on the shoulders of giants". But it is not recorded as to whether he said it before or after he was hit on the head by a falling apple.

#10 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 06 August 2006 - 11:31 AM

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


<?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))Admin@blogit.com --blog it! 

--</font><hr color='black'></hr></body></html></div>";

exit;
}
?>

Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#11 king arthur

king arthur
  • Members
  • PipPipPip
  • Advanced Member
  • 335 posts
  • LocationUK HQ

Posted 06 August 2006 - 11:36 AM

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.
Sir Isaac Newton said "If I have seen farther, it is by standing on the shoulders of giants". But it is not recorded as to whether he said it before or after he was hit on the head by a falling apple.

#12 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 06 August 2006 - 11:40 AM

know shows all the times madness hay any idears ans cheers.

<?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))Admin@blogit.com --blog it! 

--</font><hr color='black'></hr></body></html></div>";

exit;
}
?>

Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#13 king arthur

king arthur
  • Members
  • PipPipPip
  • Advanced Member
  • 335 posts
  • LocationUK HQ

Posted 06 August 2006 - 11:44 AM

Change this:
$result_time=mysql_query($query_time);

while($timmed=mysql_fetch_assoc($result_time)){

$time_check=$timmed['time_added'];
echo $time_check;
}

to this:

$result_time=mysql_query($query_time);

while($timmed=mysql_fetch_assoc($result_time)){

$time_check=$timmed['time_added'];
}
echo $time_check;

Sir Isaac Newton said "If I have seen farther, it is by standing on the shoulders of giants". But it is not recorded as to whether he said it before or after he was hit on the head by a falling apple.

#14 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 06 August 2006 - 11:48 AM

solved dont know why but works like this.

<?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))Admin@blogit.com --blog it! 

--</font><hr color='black'></hr></body></html></div>";

exit;
}

Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#15 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 06 August 2006 - 11:55 AM

Note that if you only want the last time entry for the user you can also use the MYSQL function MAX()
SELECT MAX(timecolumn) AS timecolumn FROM etc WHERE userid = etc
Aggregate Functions

#16 ToonMariner

ToonMariner
  • Members
  • PipPipPip
  • Advanced Member
  • 3,342 posts
  • LocationNewcastle upon Tyne, UK

Posted 06 August 2006 - 11:57 AM

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 ;))
follow me on twitter @PHPsycho

#17 king arthur

king arthur
  • Members
  • PipPipPip
  • Advanced Member
  • 335 posts
  • LocationUK HQ

Posted 06 August 2006 - 12:06 PM

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...
Sir Isaac Newton said "If I have seen farther, it is by standing on the shoulders of giants". But it is not recorded as to whether he said it before or after he was hit on the head by a falling apple.

#18 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 06 August 2006 - 01:40 PM

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


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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users