I have the following code, but when I sort it sort all date of each row, then it shows all of them in each row.
How can I fix it to only show that row date. in it own row.
<?php
$res = $pdo->query("SELECT created_at FROM cmc_my_munte ORDER BY created_at DESC ");
$today = new DateTime('now');
foreach ($res as $row)
{
$dt = new DateTime($row['created_at']);
$diff = $dt->diff($today)->format("%y years -- %m months -- %d days");
echo "{$row['created_at']} | $diff <br>";
}
The easiest way is to add DISTINCT to the query, to indicate you don’t want duplicate results.
The query would then become:
SELECT DISTINCT created_at FROM cmc_my_munte ORDER BY created_at DESC
What you may be interested in is
SELECT DISTINCT created_at, COUNT(created_at) AS count FROM cmc_my_munte GROUP BY created_at ORDER BY created_at DESC
Then you have $row['created_at'] that contains the date when the coin(s) was/were created and $row['count'] that contains how many coins you have of this date.
Thank you for trying to help
I did change the code, but is still shows all of the dates in one row.
<td>
<?php
$res = $pdo->query("SELECT DISTINCT CREATED_AT, COUNT(CREATED_AT) AS count FROM cmc_my_munte GROUP BY CREATED_AT ORDER BY CREATED_AT DESC ");
$today = new DateTime('now');
foreach ($res as $row) {
$dt = new DateTime($row['CREATED_AT']);
$diff = $dt->diff($today)->format("%y Jare -- %m Maande -- %d days");
echo "{$row['CREATED_AT']} | $diff <br>";
}
?>
</td>
I do not know where you se the coins count,?
You need to show or describe what exactly is wrong with the current output. What output do you want?
My output should be like this
Row 1 .0 Jare – 0 Maande – 3 Dae
Row 2 0 Jare – 0 Maande – 7 Dae
Row 3 0 Jare – 0 Maande – 9 Dae
Row 4 0 Jare – 0 Maande – 10 Dae
BUT
It is now showing it like this, the date are correct, but it is everything from all rows onto 1 row
Row1
0 Jare – 0 Maande – 3 Dae
0 Jare – 0 Maande – 7 Dae
0 Jare – 0 Maande – 9 Dae
0 Jare – 0 Maande – 10 Dae
0 Jare – 0 Maande – 11 Dae
0 Jare – 0 Maande – 14 Dae
0 Jare – 0 Maande – 19 Dae
0 Jare – 0 Maande – 20 Dae
0 Jare – 0 Maande – 23 Dae
0 Jare – 0 Maande – 25 Dae
0 Jare – 1 Maande – 1 Dae
0 Jare – 1 Maande – 5 Dae
0 Jare – 1 Maande – 9 Dae
0 Jare – 1 Maande – 10 Dae
0 Jare – 1 Maande – 11 Dae
Row 2
0 Jare – 0 Maande – 3 Dae
0 Jare – 0 Maande – 7 Dae
0 Jare – 0 Maande – 9 Dae
0 Jare – 0 Maande – 10 Dae
0 Jare – 0 Maande – 11 Dae
0 Jare – 0 Maande – 14 Dae
0 Jare – 0 Maande – 19 Dae
0 Jare – 0 Maande – 20 Dae
0 Jare – 0 Maande – 23 Dae
0 Jare – 0 Maande – 25 Dae
0 Jare – 1 Maande – 1 Dae
0 Jare – 1 Maande – 5 Dae
0 Jare – 1 Maande – 9 Dae
0 Jare – 1 Maande – 10 Dae
0 Jare – 1 Maande – 11 Dae
I need it to sort it in order of days/months and years, it is showing all the dates in the wrong one, but it is showing all in one row, for every row.
There’s nothing in the posted code responsible for any major rows of things. The posted code is doing what it was written to do, loop over all the rows of data that the posted SELECT query matches. If the posted code is part of some other code, you would need to show all the relevant code. Just posting a small part of the problem doesn’t help us to help you. If you are running the posted code inside of a loop, that’s looping over some other related data, you should instead use one JOIN… query to get all the data that you want in the order that you want it.
So, backup and provide us with the overall goal you are trying to achieve, not just a small snippet of what you are trying to make work.
Based on information posted elsewhere, these year/month/day values are being output inside a <td>...</td> cell. The input value being converted already exists, as $checkSqlRow[“CREATED_AT”], inside of some existing, non-posted looping code.
This is what happens when you don’t take into account all the relevant information about a problem and just try to make each small part ‘work’ as though it is the only thing going on. This is the equivalent of painting yourself into a corner. You end up redoing things over and over.
The reason for the data not sorting correctly, and a solution, has already been given elsewhere - these converted values are strings and are being sorted character by character. The overall question becomes, are you sorting this data dynamically in the browser using a data-table widget or can it be sorted once on the server when the main query is executed?
This is the two td that I use to get the info from. the CREATED_AT is in my SQL, but the one where I need it to show how old it is is only in my HTML table, and both of them is in td’s
<td style="color:#EFE1CE">
<?php
$date = $checkSqlRow["CREATED_AT"];
$newDate = date("Y M d", strtotime($date));
echo $newDate;
?>
</td>
<td>
<?php
$res = $pdo->query("SELECT DISTINCT created_at FROM cmc_my_munte GROUP BY created_at ORDER BY created_at DESC ");
$today = new DateTime('now');
foreach ($res as $row)
{
$dt = new DateTime($row['created_at']);
$diff = $dt->diff($today)->format("%y Jare -- %m Maande -- %d Dae");
echo "$diff <br>";
}
?>
</td>
The following code did work, but, when I click on sort it did not sort it correct, so it was said I must use $pdo and not $conn
<td>
<?php
$date1 = $date = $checkSqlRow["CREATED_AT"];
$date2 = date("y-m-d"); // this returns today's date
$diff = abs(strtotime($date2) - strtotime($date1));
$years = floor($diff / (365 * 60 * 60 * 24));
$months = floor(
($diff - $years * 365 * 60 * 60 * 24) / (30 * 60 * 60 * 24)
);
$days = floor(
($diff -
$years * 365 * 60 * 60 * 24 -
$months * 30 * 60 * 60 * 24) /
(60 * 60 * 24)
);
echo $years . " Jare -- \n";
echo $months . " Maande -- \n";
echo $days . " Dae\n";
?>
</td>
We are having a communication problem. Are you using the google chrome browser’s built-in language translation to get replies into your native language?
The query/code you were given elsewhere, to produce the formatted year/month/day value, was based on a lack of context about what you are actually trying to accomplish. When someone posted a complete query that gets all of the rows of data from the cmc_my_munte table and loops over them, it should have occurred to you that this is not what you are doing, because you already have a query that’s getting the data that you want and you are already looping over the rows of data from that query. The only thing you should keep from that solution is that you can use a php datetime/diff call to replace those 11 lines of existing code.
Since you didn’t answer the point about sorting this data in the browser or in the server-side code, I’ll assume, based on the - clicking on a header, javascript, ascending, descending statements that you are/do want to perform this sorting dynamically in the browser. To do so, you must pad the year, month, and day fields so that the values in each field are the same length, giving a fixed length for the entire year/month/day string. Fortunately, the php datetime/diff/format has a solution. Instead of using - format("%y Jare -- %m Maande -- %d Dae") use - format("%Y Jare -- %M Maande -- %D Dae")
As to the use of a database connection variable named $pdo, containing a PDO connection, that was because you copied and pasted the solution that was given rather than to examine it to determine what if anything it had to do with what you are doing. If you now have a successful connection using the PDO extension, you should continue to use it throughout your application, since the PDO extension is much simpler and better designed than the mysqli extension.
Thank you for your info.
I know it is a language barrier, I have difficulty to explain what I want, even in my own language.
I can see how it must look, but to put it on paper 
, But thank you for be patient with me. Yes I did change everything to $pdo in my project. Busy going thru all of my code to see where the problem is.
I
I’ma take a stab at this, because I think we’re getting a little forest-for-the-trees here. I’ll stick it through Google Translate to try and eliminate the language barrier, but i’m not gonna guarantee any better translation than that 
Google Translate identifies your language as Afrikaans.
Please correct me where my understanding fails.
Korrigeer my asseblief waar my begrip misluk.
You want to take the query;
Jy wil die navraag neem;
SELECT DISTINCT created_at FROM cmc_my_munte GROUP BY created_at ORDER BY created_at DESC
And output a series of table rows, each containing 2 cells; the first cell contains the date. The second cell contains the date differential between that date and today.
En voer 'n reeks tabelrye uit, wat elk 2 selle bevat; die eerste sel bevat die datum. Die tweede sel bevat die datumverskil tussen daardie datum en vandag.
You currently have the following code:
Jy het tans die volgende kode:
<td style="color:#EFE1CE">
<?php
$date = $checkSqlRow["CREATED_AT"];
$newDate = date("Y M d", strtotime($date));
echo $newDate;
?>
</td>
<td>
<?php
$res = $pdo->query("SELECT DISTINCT created_at FROM cmc_my_munte GROUP BY created_at ORDER BY created_at DESC ");
$today = new DateTime('now');
foreach ($res as $row)
{
$dt = new DateTime($row['created_at']);
$diff = $dt->diff($today)->format("%y Jare -- %m Maande -- %d Dae");
echo "$diff <br>";
}
?>
</td>
I have a couple of questions about this, because you’re checking a value “$checkSqlRow”, that somehow has a value outside of your query. Is there some other query and loop that is being performed?
Ek het 'n paar vrae hieroor, want jy kontroleer 'n waarde “$checkSqlRow”, wat op een of ander manier 'n waarde buite jou navraag het. Is daar 'n ander navraag en lus wat uitgevoer word?
If you’ve already got a record from the first query, what is the field in the first query that links to the cmc_my_munte table?
As jy reeds 'n rekord van die eerste navraag gekry het, wat is die veld in die eerste navraag wat na die cmc_my_munte-tabel skakel?
If you do have a query and loop around this code, it may be beneficial to our understanding if you show us that too. The more of your code we can see, the better we can help you.
As jy wel 'n navraag het en 'n lus rondom hierdie kode het, kan dit voordelig wees vir ons begrip as jy dit ook vir ons wys. Hoe meer van jou kode ons kan sien, hoe beter kan ons jou help.
The SELECT DISTINCT … attempt was based on a misunderstanding of the OPs statement about what is wrong with the output and the query/foreach loop itself, inside that <td></td> cell is based on a misunderstanding of what the overall goal is. Both of these things have nothing to do with the actual problem (except that the OP’s ~11 lines of code to produce the desired output format in that cell can be replaced with ~3 lines of code.)
The REAL problem is that the resulting format that the OP is initially putting into that <td></td> cell does not sort properly as a string. In the following example output in that cell, the leading ‘1’ character in the ‘13’ value causes ‘13’ to be less than ‘2’, which is the incorrect result.
0 Jare -- 0 Maande -- 13 Dae
0 Jare -- 0 Maande -- 2 DaeThe solution is to pad the fields with leading zeros so that each field is the same length, with the same significance to each character position in the string, and will sort properly -
00 Jare -- 00 Maande -- 02 Dae
00 Jare -- 00 Maande -- 13 DaeWell the first solution is to remove the inner loop, because that’s not doing what the user wants, if it is as you say. 
The second will be to pad the rows, or else intercept the sorting function to sort on a user-defined function (which is, i agree, probably overkill, unless the OP is really insistent on that formatting).
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.