dd-mm-yyyy

Marina06

Hi,

Impossible to get my Column 4 (date) correctly sorted. (I have only 16 rows in my database so far)
When "sort by date DESC" I get :
(my values coming from php are formatted this way : dd-mm-yyyy.)

22-01-2015 // Starts by January then goes to February
20-02-2015
17-02-2015
12-02-2015
etc..

and sort by date ASC :
01-02-2015 // starts on February the 1st !!!
02-02-2015
etc..

http://live.datatables.net/reworara/1/edit?js,output

this shows my js code and outputs a frozen table in JSBIN, when I want a dynamic one
The values in my dynamic table come from php/mysql, and I don't know how to simulate that with JSBin............

Anticipate thanks for your help... I've been trying & looking on internet for hours to solve this case , but I'm still stuck..
Not easy at all to find the proper way of using/ making the sort by date dd-mm-yyyy work

---

Also, the basic css found on all demos on datable.com do not update : I mean no border between each row, and a plain white background instead of alterenated colored rows...

Thank you very much for your help !!!

ignignokt

In your database, what is the data type of the column you use for the date? With server-side the sorting is handled by the database.

Marina06

Hi, Thanks for answering.
My inserting_date have a DATE format (YYYY-MM-DD) in mysql

Then I display the date this way :

$en_date_insertion= $datos['job_date_insertion'];
$explode_insertion= explode("-", $en_date_insertion);
$date_insertion_fr = $explode_insertion[2]."-".$explode_insertion[1]."-".$explode_insertion[0];

while.... Table, tr, td
echo $date_insertion_fr // = dd-mm-yyyy

ignignokt

I would echo out the query it is running then run it yourself to make sure the results are ordered correctly. That way you can eliminate that as the reason.

A better way to convert your date format would be to do this:

$date['job_date_insertion'] = date('d-m-Y', strtotime($date['job_date_insertion']));
// $date['job_date_insertion'] is now formatted as: dd-mm-yyyy

Marina06

The query worked perfectly without datatable :
SELECT * FROM job_offers j JOIN marimmo_villes v ON v.ville_id =j.job_ville ORDER BY j.job_date_insertion DESC

I've tested your code for formatting the date, and within the column, it returns date yyyy-mm-dd, ordered correctly, for sure but within a UK format when I need it dd-mm-yyyy.

But if I echo $date OUT OF the column it writes dd-mm-yyyy, so I understand the function for sorting dates in my js (see http://live.datatables.net/reworara/1/edit?js,output ) doesn't fit....

Could you kindly tell me which one makes the job. With thanks for your help :)

ignignokt

Is your query on the server-side actually changed when you sort the table? If so, that means you shouldn't need any of those JS date sorting functions because the sorting is done by the query.

Marina06

My query looks like this :

$query = bla bla bla;

<

table id="datos">
<thead>
<tr>
<th>Réf.</th>
<th>Poste</th>
<th>Type de contrat</th>
<th>Date de publication </th>
<th>Lieu</th>
<th>descriptif</th>
</tr>
</thead>

<tbody>

...

while ( $datos= $sth->fetch(PDO::FETCH_ASSOC))

  {   

            $date = date('d-m-Y', strtotime($datos['job_date_insertion']));  
             $job_id= $datos['job_id'];
             $job_intitule= strtoupper($datos['job_intitule']);
             $job_ville = $datos['ville_nom'];
             $job_cp = $datos['cp'];

        echo"    
          <tr> 
      <td> ". $job_id. "</td>
          <td>". $job_intitule ." </td>
      <td>". $datos['job_contrat'] ." </td>
          <td>" .$date." </td>
          <td>". $job_cp. " ".$job_ville ." </td>
      <td>". $datos['job_descritptif']. " </td>
          </tr>";
        }  

...

EDIT :
No matter if I put $date = date('d-m-Y', strtotime($datos['job_date_insertion']));
or my previous function explode...

The date indeed writes dd-mm-yyyy, but it doesn't get sorted as required. I remain with

22-01-2015 // Starts by January then goes to February 20-02-2015 17-02-2015 12-02-2015 etc..

and sort by date ASC : 01-02-2015 // starts on February the 1st !!! 02-02-2015 etc..

:/

Marina06

I've updated my post n°4, sorry / thanks

ignignokt

I see, so you are getting your data from the server, but you aren't actually using datatables server-side.

If you used server-side this way, then you wouldn't need to worry about JS sorting.

If you want to keep doing it the way you are now then I'm not sure what changes are needed for your JS.

Marina06

Okay.. Well I tried to do it this way first, but it didn't work... :/ so I opted for the above structure...

Thank u.

I'll try again tomorrow (as in France now it's 11pm)
with
"processing": true,
"serverSide": true,
"ajax":

And I'll come back to again ask for help or better to mark this topic finally solved.
Thanks again :)

allan

The downside of server-side processing is that you need to write the server-side script.

@Marina06 - You note that you want the format:

dd-mm-yyyy

But the data in the example you linked to is in the format:

2011/04/25

Which one is correct?

Either way, you might want to look into using this plug-in which will handle almost any date time format you can think of :-)

Allan

Marina06

Thanks to you both for your help.

Allan, the date I want to display is dd-mm-yyyy.

I've found a piece of code
http://datatables.net/forums/discussion/21811/solution-serverside-using-join-pdo-php
which I tried to implement, but I get the following error :
Fatal error: Class '__CLASS' not found in C:\wamp\www\xx\xx\ajx\datatables.class.php on line 9
and as I don't have the least knowledge of MVC, I'm unable to repair/deal with it....
Could you kindly help? please
I put (parametersdb.php, connection.class.php, datatables.class.php, instance.php) in my ajx folder.

And here's the beginning of my js. Is that correct?
$('#datos').dataTable({ "sAjaxSource": 'ajx/instance.php',

Thank you !!

Marina06

SOLVED,
indeed with moment.js

"aoColumns": [ "aTargets": [ 3 ], { "mRender": function(date, type, full) {return moment(date).format('DD-MM-YYYY'); } ]

It took me 4 days searching the web to discover all the right codes (serverside JOIN table + display & sort correctly mm-dd-yyyy)..

OK SOLVED Thanks....

mRender

{ data: null,
              render: function ( data, type, row ) {
              return (data.tbl_names.LastName+", "+data.tbl_names.FirstName);
            }},

You can use this to return 2 columns of data into 1 column.

Marina06

Hi,
Thanks for this ;) your help is much appreciated.
No, this formula doesn't match for me, but I've just found the solution at my question
How to put multiple data in a datatable column?

{ "aTargets": [ 4 ] ,  
   "mData": 4 ,  
   "mRender" : function ( data, type, full ) 
                         {  return data+' '+full[5] }
 },

Hope it can help.
http://stackoverflow.com/questions/19777075/datatables-merge-columns-together

Almost everything solved now, thanks :)

allan

@Modgility's solution is basically the same as your own. @Modgility has assumed the use of objects, while it would appear that you are using arrays, but they are basically the same thing.

The other difference is that @Modgility's solution uses the 1.10+ naming style for parameters.

Allan

Marina06

@allan @Modgility
Ok. Sorry if i'm still an ignorant with jquery ... Not my real cup of tea :D

I have another question !!!!! :)

Why does my CSS <link rel="stylesheet" href="js/DataTables-1.10.5/media/css/jquery.dataTables.css" />
doesn't applies the cells borders and backgrounds ? I have a white plain bkg instead..
Thanks :)

allan

Don't know - we would need a link to the page to be able to debug it.

Allan

Marina06

Humm.. I'm working locally, and I'm supposed to look for a new hosting as mine doesn't support PDO... so I can't give u no link right now, unfortunately.

But thanks :)