dd-mm-yyyy

dd-mm-yyyy

Marina06Marina06 Posts: 26Questions: 3Answers: 0

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 !!!

This question has accepted answers - jump to:

Answers

  • ignignoktignignokt Posts: 146Questions: 4Answers: 39

    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.

  • Marina06Marina06 Posts: 26Questions: 3Answers: 0

    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

  • ignignoktignignokt Posts: 146Questions: 4Answers: 39

    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
    
  • Marina06Marina06 Posts: 26Questions: 3Answers: 0
    edited February 2015

    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 :)

  • ignignoktignignokt Posts: 146Questions: 4Answers: 39
    edited February 2015

    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.

  • Marina06Marina06 Posts: 26Questions: 3Answers: 0
    edited February 2015

    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..

    :/

  • Marina06Marina06 Posts: 26Questions: 3Answers: 0
    edited February 2015

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

  • ignignoktignignokt Posts: 146Questions: 4Answers: 39

    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.

  • Marina06Marina06 Posts: 26Questions: 3Answers: 0
    edited February 2015

    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 :)

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

  • Marina06Marina06 Posts: 26Questions: 3Answers: 0

    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 !!

  • Marina06Marina06 Posts: 26Questions: 3Answers: 0
    edited February 2015

    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....

  • mRendermRender Posts: 151Questions: 26Answers: 13
    Answer ✓
    { 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.

  • Marina06Marina06 Posts: 26Questions: 3Answers: 0
    edited February 2015

    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 :)

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    @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

  • Marina06Marina06 Posts: 26Questions: 3Answers: 0

    @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 :)

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

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

    Allan

  • Marina06Marina06 Posts: 26Questions: 3Answers: 0

    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 :)

This discussion has been closed.