By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,982 Members | 1,939 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,982 IT Pros & Developers. It's quick & easy.

sort date in sql by DDMMYYYY

P: 18
hi...
i need help on this function...i want to sort data in a table that arrange the data according to the month,date and then year.
by using this code below, i am able to sort the data ascending according to date, but, the it follows the first number in the date2 column. for example, 1/2/2012,17/2/2012,2/1/2012.....i want it to be sorted like, 2/1/2012,1/2/2012,17/2/2012......

Expand|Select|Wrap|Line Numbers
  1. <?php
  2. session_start();
  3. include("dbconn.php");
  4. if(isset($_SESSION ['username']))
  5. {
  6. ?>
  7. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  8.  
  9. <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
  10.   <head>
  11.     <title>Web site</title>
  12.     <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  13.     <meta name="title" content="Web site" />
  14.     <meta name="description" content="Site description here" />
  15.     <meta name="keywords" content="keywords here" />
  16.     <meta name="language" content="en" />
  17.     <meta name="subject" content="Site subject here" />
  18.     <meta name="robots" content="All" />
  19.     <meta name="copyright" content="Your company" />
  20.     <meta name="abstract" content="Site description here" />
  21.     <meta name="MSSmartTagsPreventParsing" content="true" />
  22.     <link id="theme" rel="stylesheet" type="text/css" href="New folder/style.css" title="theme" />
  23.     <SCRIPT LANGUAGE="JavaScript">
  24. function showDate()
  25. {
  26.     var now = new Date();
  27.     var days = new Array('Ahad','Isnin','Selasa','Rabu','Khamis','Jumaat','Sabtu');
  28.     var months = new Array('Januari','Februari','Mac','April','Mei','Jun','Julai','Ogos','September','Oktober','November','Disember');
  29.     var date = ((now.getDate()<10) ? "0" : "")+ now.getDate();
  30.     function fourdigits(number)
  31.     {
  32.         return (number < 1000) ? number + 1900 : number;
  33.     }
  34.  
  35.     tnow=new Date();
  36.     thour=now.getHours();
  37.     tmin=now.getMinutes();
  38.     tsec=now.getSeconds();
  39.  
  40.     if (tmin<=9) { tmin="0"+tmin; }
  41.     if (tsec<=9) { tsec="0"+tsec; }
  42.     if (thour<10) { thour="0"+thour; }
  43.  
  44.     today = days[now.getDay()]  +"<br>"+ date + " " + months[now.getMonth()] + "  "+ (fourdigits(now.getYear())) + " <br> " + thour + ":" + tmin +":"+ tsec;
  45.     document.getElementById("dateDiv").innerHTML = today;
  46. }
  47. setInterval("showDate()", 1000);
  48. </script>
  49.   </head>
  50.   <body>
  51.   <form action="temp12.php" name="activityform" method="post"> 
  52.     <div id="wrapper"> 
  53.       <div id="bg"> 
  54.         <div id="header"><table width="80%" cellpadding="2" cellspacing="2" align="center">
  55.     <tr>
  56.         <td align="left">&nbsp;</td>
  57.     </tr>
  58. </table></div>  
  59.         <div id="page"> 
  60.           <div id="container"> 
  61.             <!-- banner -->  
  62.             <div id="banner"></div>  
  63.             <!-- end banner -->  
  64.             <!--  content -->  
  65.             <div id="content"> 
  66.               <div id="left"> 
  67.                 <div id="sidebar"> 
  68.                   <ul class="vmenu">
  69.                     <li>
  70.                       <a href="temp6.php" shape="rect">Laman Utama</a>
  71.                       <ul>
  72.                       <li>
  73.                                <a href="temp14.php" shape="rect">Visi</a>
  74.                       <li>
  75.                       <li>
  76.                                <a href="temp14.php" shape="rect">Misi</a>
  77.                       </li>
  78.                       <li>
  79.                             <a href="temp14.php" shape="rect">Piagam Pelanggan</a>
  80.                       </li>
  81.                       </ul>
  82.                     </li>
  83.                      <li>
  84.                       <a href="temp10.php" shape="rect">Profil Saya</a>
  85.                     </li>
  86.                     <li>
  87.                       <a href="temp8.php" shape="rect">Maklumat Staf</a>
  88.                     </li>
  89.                     <li>
  90.                       <a href="temp9.php" shape="rect">Peta Lokasi</a>
  91.                     </li>
  92.                     <li>
  93.                       <a href="temp11.php" shape="rect">Tugas Pegawai</a>
  94.                       <ul >
  95.                           <li>
  96.                             <a href="temp12.php" shape="rect">Masukkan Data Baru</a>
  97.                         </li>
  98.                         <li>
  99.                             <a href="temp13.php" shape="rect">Carian Data</a>
  100.                         </li>
  101.                       </ul>
  102.                     </li>
  103.                   </ul> 
  104.                 </div><br /><br />
  105.                 <div id="sidebar"> 
  106.                   <ul class="vmenu">
  107.                   <li>
  108.                   <br />
  109.                     <table border="0" cellspacing="0" cellpadding="0"><tr><td align="center"><script type="text/javascript" src="http://www.worldtimeserver.com/clocks/embed.js"></script><script type="text/javascript" language="JavaScript">objMY=new Object;objMY.wtsclock="wtsclock001.swf";objMY.color="32CD32";objMY.wtsid="MY";objMY.width=130;objMY.height=130;objMY.wmode="transparent";showClock(objMY);</script></td></tr><tr><td align="center"><h2>Perlis</h2></td></tr></table>
  110.                   </li>
  111.                   <li><div id="dateDiv"></div></li>
  112.                   </ul> 
  113.                 </div> 
  114.               </div>  
  115.               <div id="center"> 
  116.                 <div id="welcome"> 
  117.                   <h3>TUGAS HARIAN</h3>  
  118.                   <p><table width="600" border="1">
  119.   <!--DWLayoutTable-->
  120. <tr>     
  121.     <td height="23" colspan="2" valign="top" border:0px;>&nbsp;&nbsp;&nbsp;Nama &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <?php echo $_SESSION['username']; ?></td> 
  122.   </tr>
  123.     <tr>
  124.       <td width="829" height="23" valign="top">&nbsp;&nbsp;&nbsp;Jawatan &nbsp;&nbsp; :&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td> 
  125. </tr>
  126. </table>&nbsp;</p>  
  127.   <table border="2" width="600">
  128.     <tr> 
  129.      <!--<td width="40" height="23" valign="top">&nbsp;&nbsp;&nbsp;NO </td> -->
  130.      <td width="133" height="23" valign="top">&nbsp;&nbsp;&nbsp;Tarikh</td>
  131.      <td width="523" valign="top">&nbsp;&nbsp;&nbsp;Tugasan </td> 
  132.      <td width="153" valign="top">&nbsp;&nbsp;&nbsp;Catatan </td> 
  133.   </tr>
  134.  
  135.   <tr>
  136.     <!--<td>-->
  137.     <?php
  138.     $sqlGet= "SELECT * FROM staffactivity ORDER BY date2";
  139.     $queryGet = mysql_query($sqlGet) or die("Error: " . mysql_error());
  140.     while($rGet = mysql_fetch_assoc($queryGet))
  141.     {
  142.         //echo "<table border='0'>";
  143.         //echo "<tr>";
  144.         echo "<td class=/'fonttable/'>".$rGet['date2']."</td>";
  145.         echo "<td class='fonttable'>".$rGet['task2']."</td>";
  146.         echo "<td class='fonttable'>".$rGet['note2']."</td>";
  147.         echo "</tr>";
  148.     }
  149.     //echo "</table>";
  150.     ?>
  151.     <!--</td>
  152.  </tr>-->
  153. </table> 
  154.                 </div> 
  155.               </div>  
  156.               <div style="clear:both;height:40px"></div> 
  157.             </div>  
  158.             <!-- end content --> 
  159.           </div>  
  160.           <!-- end container --> 
  161.         </div>  
  162.         <div id="footerWrapper"> 
  163.           <div id="footer"> 
  164.             <p style="padding-top:1px;"> 
  165.               <a href="#" shape="rect">Home</a> | 
  166.               <a href="#" shape="rect">Products</a> | 
  167.               <a href="#" shape="rect">Services</a> | 
  168.               <a href="#" shape="rect">News</a> | 
  169.               <a href="#" shape="rect">About</a> 
  170.             </p>  
  171.             <p><table border="0" valign="middle" align="center">
  172.               <tr>
  173.                       <td><font size="2" align="center">UNIT TEKNOLOGI MAKLUMAT, JABATAN KESIHATAN NEGERI PERLIS</font></td>
  174.               </tr>
  175.               <tr>
  176.                       <td><font size="1" align="center">Disclaimer & Copyright | Technical support by : Unit Teknologi Maklumat JKNPs
  177. Copyright @ 2011 - 2012 to Unit Teknologi Maklumat JKNPs.</font></td>
  178.               </tr>
  179.             </table></p> 
  180.           </div> 
  181.         </div> 
  182.       </div> 
  183.     </div> 
  184.     </form>
  185.   </body>
  186. </html>
  187. <?php
  188. }
  189. else{
  190. header("Location: temp1.php");
  191. }
  192. ?>
  193.  
  194. <?php 
  195. /* close connection */
  196. mysql_close($dbconn);
  197. ?>
  198.  
somebody please help me....this is urgent.....tq
Feb 22 '12 #1
Share this Question
Share on Google+
2 Replies


Dormilich
Expert Mod 5K+
P: 8,639
by using this code below, i am able to sort the data ascending according to date, but, the it follows the first number in the date2 column. for example, 1/2/2012,17/2/2012,2/1/2012.....i want it to be sorted like, 2/1/2012,1/2/2012,17/2/2012......
let me guess, your date column is not in a MySQL DATE format?
Feb 22 '12 #2

P: 18
the date is not in a MySQL format. i download it from http://www.javascriptkit.com/script/...calendar.shtml...
Feb 23 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.