Jump to content
#FlattenTheCurve ×
Revolutsio

Code not working

Recommended Posts

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

 

order.png

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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.

 

Share this post


Link to post
Share on other sites

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 by Barand

Share this post


Link to post
Share on other sites

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

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

Edited by Barand

Share this post


Link to post
Share on other sites
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 by Revolutsio
MORE INFORMATION

Share this post


Link to post
Share on other sites

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']

Share this post


Link to post
Share on other sites
	$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

Share this post


Link to post
Share on other sites

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 by Barand

Share this post


Link to post
Share on other sites

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

 

nw.png

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites

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

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

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites
1 hour ago, Barand said:

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

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.