Code not working

[Revolutsio]

I have some PHP code that should give me the dates in year order but it is giving me the date order.

So it  shows a list of 10 items in the following order see attached png

it should show the ten items i sorted by year then month then day,

<?php
  $sql = "SELECT GAME, YR, PLATFORM, PUBLISHER, FINISHED FROM games WHERE COMPLETED='✔' ORDER BY FINISHED DESC LIMIT 10;";
  $result = mysqli_query($conn, $sql) or die("Bad Query: $sql");
  $num_rows = mysqli_num_rows($result);?>

  <?php while ($row = mysqli_fetch_assoc($result)):
   ?>


  <tr>
    <td><?php echo $row['GAME']; ?></td>
    <td><?php echo $row['PLATFORM']; ?></td>
    <td><?php echo date('d F Y', strtotime($row['FINISHED'])); ?></td>
  <tr>

  <?php endwhile; ?>

This use to work on until this week

 

[cyberRobot]

You could use MySQL's YEAR() function to extract the year from the FINISHED column. Then sort by that.
https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_year

Edited December 19, 2019 by cyberRobot
corrected the function name

[cyberRobot]

Sorry, I misread your post. Are the values in the FINISHED column stored as a MySQL DATE type? If not, switching the column type will make this much easier.

[Revolutsio]

2 minutes ago, cyberRobot said:

Sorry, I misread your post. Are the values in the FINISHED column stored as a MySQL DATE type? If not, switching the column type will make this much easier.

no there are stored as varchar, the database is from a google spreadsheet

[Barand]

What format are you storing them in? You can get away with varchar so long as they are in" yyyy-mm-dd" format.

31 minutes ago, Revolutsio said:

WHERE COMPLETED='✔'

Why the **** are you storing values as '✔' in your database?

[gw1500se]

I recommend you convert those to date format. Not a difficult task that needs to be done only once. It will make not only this problem go away but any future code that needs to use dates will be easier as well.

[Revolutsio]

1 minute ago, Barand said:

What format are you storing them in? You can get away with varchar so long as they are in" yyyy-mm-dd" format.

Why the **** are you storing values as '✔' in your database?

As i said in a previous post, I have put this database together on a google spreadsheet and downloaded it to a csv doc and then imported it to Mysql with PHPMyAdmin.

The date format on the sheet is in the format dd-mm-yyyy.

As above the tick is from the spreadsheet and it shows if i have completed a game.

 

[gw1500se]

Converting it to date format is then relatively simple.

[Barand]

You can use the sql function str_to_date() to convert your dates to the correct format.

In the mean time you can also use that in your query

SELECT GAME, YR, PLATFORM, PUBLISHER, STR_TO_DATE(FINISHED, '%d-%m-%Y') as FINISHED 
FROM games 
WHERE COMPLETED='✔' 
ORDER BY STR_TO_DATE(FINISHED, '%d-%m-%Y') DESC 
LIMIT 10

 

Edited December 19, 2019 by Barand

[Revolutsio]

Thank you everybody for your help, I remembered that the code uses the date format and it now works.

[Barand]

As for that '✔', store in your table as 1/0 for completed/not completed.

You convert to a '✔' on output, not on input.

Edited December 19, 2019 by Barand

[Revolutsio]

11 hours ago, Barand said:

As for that '✔', store in your table as 1/0 for completed/not completed.

You convert to a '✔' on output, not on input.

Thank you i will try this.

 

Now with the date query, when i show the page with items that have not been completed i get the date '0000-00-00' how do i change this , i searched on here and found the same problem but it did not work with my code.

How do i make this date blank if  'FINISHED' = '0000-00-00'

 

if (FINISHED='0000-00-00', '', FINISHED) as FINISHED

i have tried this but get '01 January 1970' and if I put a space in the second quotes I get today's date , a little bit confused

Edited December 20, 2019 by Revolutsio
MORE INFORMATION

[Barand]

if () and else

are useful in such situations

[Barand]

You have a similar problem in your original code when the finished date is blank

echo date('d F Y', strtotime(''));         //--> 01 January 1970

With STR_TO_DATE(), if the date string is blank you get 0000-00-00

mysql> select finished
    ->      , str_to_date(finished, '%d-%m-%Y')
    -> from games;
+------------+-----------------------------------+
| finished   | str_to_date(finished, '%d-%m-%Y') |
+------------+-----------------------------------+
|            | 0000-00-00                        |
| 20-12-2019 | 2019-12-20                        |
+------------+-----------------------------------+

I recommend formatting the date in the query instead of in php EG

SELECT FINISHED
     , CASE WHEN FINISHED = ''
        THEN ''
        ELSE DATE_FORMAT(STR_TO_DATE(FINISHED, '%d-%m-%Y'), '%d %M %Y') 
        END as DATE_FINISHED
FROM games;

+------------+------------------+
| FINISHED   | DATE_FINISHED    |
+------------+------------------+
|            |                  |
| 20-12-2019 | 20 December 2019 |
+------------+------------------+

Then in the php code , just output $row['DATE_FINISHED']

[Revolutsio]

	$date = $row['FINISHED'];
	IF($date == '0000-00-00') {
	$date='';
	} else {
		$parts = explode('-',$date);
		$date = date('d-m-Y', mktime(0,0,0,$parts[1],$parts[2],$parts[0]));
	}

I have tried this code and i get 30 November -0001

[Barand]

What does $row['FINISHED'] contain when you get that result?

EDIT And why didn't you just use strtotime() again?

$date = date('d F Y', strtotime($date))

 

Edited December 20, 2019 by Barand

[Revolutsio]

with the code i put in i get the 30 November -0001

i tried to use phpmyadmin to use the select command from your reply and i get all blank when echo finished

 

[Barand]

What does you FINISHED column contain if there is no finished date yet? (I am assuming a blank string)

Can you provide some sample data? (Dump of your 'games' table)

[Revolutsio]

8 minutes ago, Barand said:

What does you FINISHED column contain if there is no finished date yet? (I am assuming a blank string)

Can you provide some sample data? (Dump of your 'games' table)

what file extension would you like?

[Barand]

By default a dump (export) file is .sql.

But a .csv would be fine if you prefer to provide it that way

[Revolutsio]

Hi I cannot upload anything but a pictures or videos

[Barand]

I hadn't realised that you had already converted your finished column to the correct DATE type and format. Well done.

SELECT CASE WHEN FINISHED = 0
		THEN ''
		ELSE DATE_FORMAT(FINISHED, '%d %M %Y') 
       END as FINISHED
FROM games

 

[Revolutsio]

could tell me why i should put this bit of code please

[Barand]

1 hour ago, Barand said:

I recommend formatting the date in the query instead of in php

 

[Revolutsio]

That worked, now it is printing the date like this '13 03, 2008' is there a way to get the date of completed games date in this format '13 March 2018' please

 

And thank you for all you help.