Query shows no results

[EchoFool]

Hey,

 

I have a query which lists users in hospital and how much time they have left to wait based on "now" and "Arrival" time stamps.... but it works until i a WHERE and ORDER BY clause.

 

Im trying to only display people who have 1 minute left or greater in the WHERE CLAUSE

 

And ORDER BY minutes left in an ASC order but when i add them my query fails to find anything... this is my query:

 

 

<?php
SELECT UserID,Reason,TIMESTAMPDIFF(MINUTE,'Arrival','".serverdate()."')AS MinutesPassed,Minutes

FROM ".tablename().".hospital 

WHERE (TIMESTAMPDIFF(MINUTE,'Arrival','".serverdate()."')- Minutes) > 0 

ORDER BY (TIMESTAMPDIFF(MINUTE,'Arrival','".serverdate()."')- Minutes) ASC
?>

 

Hope you can help with my problem...

Time stamps and serverdate function are informat of: yyyy-mm-dd hh:mm:ss

[JustLikeIcarus]

What does the following query return

 

SELECT TIMESTAMPDIFF(MINUTE, `arrival` , NOW() )
FROM hospital 

 

Im assuming that the arrival column is of type DATETIME correct?

[EchoFool]

Arrival is TimeStamp YYYY-MM-DD HH:mm:ss

 

2010-03-27 02:14:10   

 

It resulted in : 0 minutes!

 

Which would be correct as 0 minutes had passed from when i inserted the row.

 

But now lets say field : Minutes is 10... that means they have to 10 till the result > or = to Minutes which is where my WHERE Clause comes in, i want to check where "how long they've been waiting from row insert" taken from "mintes - how long they have to wait" and find only where its > 0 minutes left.

 

Then order by minutes left ASC order but it finds nothing with my WHERE and ORDER part.

[EchoFool]

edit: hmm it seems to be stuck on 0 now matter how many minutes have passed =/

[JustLikeIcarus]

Ok so out of curiosity what does the following return?

 

SELECT TIMESTAMPDIFF(MINUTE, `arrival` , NOW() ) as MinutesPassed, Minutes
FROM hospital
WHERE TIMESTAMPDIFF(MINUTE, `arrival` , NOW() ) - Minutes > 0
ORDER BY MinutesPassed

 

if that returns nothing, then see what this returns

 

SELECT TIMESTAMPDIFF(MINUTE, `arrival` , NOW() ) as MinutesPassed, TIMESTAMPDIFF(MINUTE, `arrival` , NOW() ) - Minutes as MinutesLeft
FROM hospital
ORDER BY MinutesPassed

[EchoFool]

With the first query - where is "MinutesWaited" coming from as that would be undefined ?

 

The second query resulted in this:

<?php
echo $row['MinutesPassed'] .' passed  '. $row['MinutesLeft'].' left ';
?>

0 passed  -10 left 

[JustLikeIcarus]

With the first query - where is "MinutesWaited" coming from as that would be undefined ?

 

The second query resulted in this:

<?php
echo $row['MinutesPassed'] .' passed  '. $row['MinutesLeft'].' left ';
?>

0 passed  -10 left 

 

Yeah sorry that should have been MinutesPassed.  But I also noticed that your Minutes left math is backwards. try this.

SELECT TIMESTAMPDIFF(MINUTE, `arrival` , NOW() ) as MinutesPassed, Minutes - TIMESTAMPDIFF(MINUTE, `arrival` , NOW() ) as MinutesLeft
FROM hospital
ORDER BY MinutesPassed

[EchoFool]

Okay thats better! Although the ORDERBY is last part not working.

 

As people can be in different lengths of waiting times other than just the same wait..

 

Person 1 has  2 minutes have passed and was a 20 minute wait = 18 mins to go.

Person 2 has  2 minutes have and was a 10 minute wait = 8 mins to go.

 

So person 2 would be before person 1 in terms of the order by. Im thinking a subtraction but can maths be used in an orderby?

[JustLikeIcarus]

Yes you can use math in the order by section.  Sounds like you want to prioritize people by taking into account Their total estimated wait and how long they have waited. Is that right?

[EchoFool]

No just how long is left for them to wait - don't want to over complicate it

[JustLikeIcarus]

Ok so using my previous query sort it like this

 

SELECT TIMESTAMPDIFF(MINUTE, `arrival` , NOW() ) as MinutesPassed, Minutes - TIMESTAMPDIFF(MINUTE, `arrival` , NOW() ) as MinutesLeft
FROM hospital
ORDER BY MinutesLeft

[EchoFool]

Okay that worked but the WHERE clause is missing yet for some reason with this:

 

<?

SELECT TIMESTAMPDIFF(MINUTE, `arrival` , NOW() ) as MinutesPassed, Minutes - TIMESTAMPDIFF(MINUTE, `arrival` , NOW() ) as MinutesLeft
FROM hospital
WHERE MinutesLeft > 0 
ORDER BY MinutesLeft ASC

?>

 

I get : Unknown column 'MinutesLeft' in 'where clause'

Makes no sense as without it - the ORDER BY doesn't give an unknown column error :S So it must be setting but not useable in a WHERE ?

[JustLikeIcarus]

That's because you can't use column aliases for functions in a where clause. You have to use the actual finction.

[EchoFool]

Thank you everything seems to be in working order !