sql order by date starting with 000-00-00

[runnerjp]

Hello in my sql database i store a present date as 0000-00-00

 

how can i orderby the date so it startes with 0000-00-00 then works its way down form todays date

$sql = 'SELECT * FROM user_references WHERE employment_id=\''.$rowe['id'].'\' AND user_id=\''.$user_id.'\' AND status=\'active\'';

  e.g

 

0000-00-00

2012-06-04

2012-03-02

[algidDes702]

Does your database have a 'date' or 'timestamp' column to query along with your other fields?

eg:

$sql = 'SELECT * FROM user_references WHERE employment_id=\''.$rowe['id'].'\' AND user_id=\''.$user_id.'\' AND status=\'active\'' ORDER BY date ;

 

it was difficult for me to follow your quotes and concatenations. Here is a good guide to follow if this didnt help, but it will order your query results by however you like using the existing fields in your database.

 

http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

 

thanks

 

algidDes523

[requinix]

Add a condition for the date: either it's "0000-00-00" or it's on/after today's date. Then a normal ORDER BY should take care of the rest.

[Psycho]

I guess my first question is why are you storing 000-00-00 in the first place? Why are you not storing the present date as, well, the present date?

 

But, if you have a valid reason for doing that (which I can't think of any) a simple ORDER BY on the date column will not get what you need because you want the '0000-00-00' date (the smallest amount) to be first followed by dates that then start at the highest and then decrease. So, you will need to add a virtual sort value to get the '0000-00-00' dates first then sort descending.

 

$sql = "SELECT *, IF(date='0000-00-00', 0, 1) as firstSort
        FROM user_references
        WHERE employment_id='{$rowe['id']}'
          AND user_id='{$user_id}'
          AND status='active'
        ORDER BY firstSort ASC, date DESC";

[runnerjp]

i get the error

 

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in /var/www/vhosts/reference-bank.com/profile.php on line 350 Call Stack: 0.0000 630584 1. {main}() /var/www/vhosts/reference-bank.com/index.php:0 0.0002 631104 2. require_once('/var/www/vhosts/reference-bank.com/inc/config.php') /var/www/vhosts/reference-bank.com/index.php:1 0.0034 694496 3. include('/var/www/vhosts/reference-bank.com/profile.php') /var/www/vhosts/reference-bank.com/inc/config.php:64

[runnerjp]

i store the date like this

 

$attended_from = "$from_year-$from_month-$from_day";

$attended_to = "$to_year-$to_month-$to_day";

 

if ($to_present=='yes') $attended_to = '0000-00-00';

 

$sql = "INSERT INTO education (user_id,created,updated,title,attended_from,attended_to,location) VALUE ('$user_id',NOW(),NOW(),'$title','$attended_from','$attended_to','$location')";

[requinix]

Add a condition for the date: either it's "0000-00-00" or it's on/after today's date. Then a normal ORDER BY should take care of the rest.

Ah, the other way.

 

Besides Psycho's code (which is how I'd do it), you can UNION two queries: SELECT the ones that are 0000-00-00 and tack on the ones that aren't (and are before today's date).

[runnerjp]

any idea why im getting the error Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean

[tipsmail7]

any idea why im getting the error Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean

maybe your query was wrong so the return from function mysql_query() was "FALSE" (boolean type)

 

http://www.php.net/manual/en/function.mysql-query.php

http://php.net/manual/en/function.mysql-fetch-assoc.php

[PFMaBiSmAd]

any idea why im getting the error Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean

 

Yes, you either have a problem in your query or in your php code. What does your error checking and error reporting logic you (should already) have in your code say as to why and where the query failed?

 

In any case we cannot possibly help you without seeing your code from where the actual query is being formed up through the point where the mysql_fetch_assoc error is occurring at and if there's a loop involved, you need to post all the code inside the loop too since it could be overwriting the result resource.

[Psycho]

Did you use the query I provided "as is"? Did you change the field names as needed? I can't see your database and, therefore, cannot know if the field name I used (i.e. 'date') was the name you used. Wherever you are running your query ensure you validate if the query was successful. If not, echo the error to the page for debugging purposes.

 

$result = mysql_query($sql) or die(mysql_error());

 

By the ay. It looks like you are using the value '0000-00-00' to indicate that the user is still attending - i.e. there is no end date. I would suggest a more logical approach would be to simply set that field as NULL.

[Barand]

No need for the "IF" in the query

 

 

+------------+----+

| thedate    | id |

+------------+----+

| 2010-02-01 |  1 |

| 0000-00-00 |  2 |

| 2011-05-30 |  3 |

| 0000-00-00 |  4 |

| 2012-04-19 |  5 |

+------------+----+

SELECT * FROM dates d
ORDER BY thedate='0000-00-00' DESC, thedate DESC

 

+------------+----+

| thedate    | id |

+------------+----+

| 0000-00-00 |  2 |

| 0000-00-00 |  4 |

| 2012-04-19 |  5 |

| 2011-05-30 |  3 |

| 2010-02-01 |  1 |

+------------+----+

[Psycho]

SELECT * FROM dates d
ORDER BY thedate='0000-00-00' DESC, thedate DESC

 

Interesting. Never knew you could put a conditional in the ORDER BY.

[fenway]

SELECT * FROM dates d
ORDER BY thedate='0000-00-00' DESC, thedate DESC

 

Interesting. Never knew you could put a conditional in the ORDER BY.

Yup -- any expression -- that why ORDER BY FIELD() works.

 

Incidentally, MySQL knows about zero dates -- you can just refer to them as '0' in your clauses -- much easier, and less error-prone.

[deragoku]

I can not see your database, and therefore can not know if I use the field name is the name you are using.