CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Beginner:
How to arrange lastupdate row in ascending order from database.

 



sh.ajay12
User

Feb 28, 2014, 5:13 AM

Post #1 of 6 (1462 views)
How to arrange lastupdate row in ascending order from database. Can't Post

Hello Members,

I have a database. where a field is 'lastupdate'.

When some one updates the table this field is updated. How can i select this field and arrange row in date ascending order.

here is the order in which i am getting at present.

I want to arrange them in ascending order according to date. all 2013 should come first and then the 2014.

mysql> SELECT lastupdate FROM nagios_device_test_audit order by lastupdate DESC LIMIT 8;
+---------------------------------------------+
| lastupdate |
+---------------------------------------------+
| Yusuf Karolia: Tue Nov 26 14:37:18 GMT 2013 |
| Yusuf Karolia: Tue Nov 26 14:37:18 GMT 2013 |
| Yusuf Karolia: Tue Nov 26 14:37:18 GMT 2013 |
| Yusuf Karolia: Tue Nov 26 14:24:23 GMT 2013 |
| Yusuf Karolia: Thu Feb 20 16:29:21 GMT 2014 |
| Yusuf Karolia: Thu Feb 20 15:26:09 GMT 2014 |
| Yusuf Karolia: Fri Nov 8 14:10:54 GMT 2013 |
| Yusuf Karolia: Fri Nov 8 14:10:33 GMT 2013 |
+---------------------------------------------+
8 rows in set (0.00 sec)


FishMonger
Veteran / Moderator

Feb 28, 2014, 6:25 AM

Post #2 of 6 (1457 views)
Re: [sh.ajay12] How to arrange lastupdate row in ascending order from database. [In reply to] Can't Post

DON'T put multiple types of data in a single field.

Split that field up. Put the name in a "username" varchar field and put the date in a datatime field and your problem is solved.


Code
mysql> SELECT user, lastupdate FROM nagios_device_test_audit order by lastupdate DESC LIMIT 8;



sh.ajay12
User

Feb 28, 2014, 6:46 AM

Post #3 of 6 (1455 views)
Re: [FishMonger] How to arrange lastupdate row in ascending order from database. [In reply to] Can't Post

Hello FishMonger,

this is my table description. how should i split it, as the data has been there for many months.

mysql> DESCRIBE nagios_device_test_audit;

Code
+---------------+--------------+------+-----+---------+-------+ 
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| shortname | varchar(15) | NO | | | |
| devicename | varchar(50) | NO | | | |
| testname | varchar(200) | NO | | | |
| param_value_1 | varchar(100) | NO | | | |
| param_value_2 | varchar(50) | NO | | | |
| param_value_3 | varchar(50) | NO | | | |
| param_value_4 | varchar(50) | NO | | | |
| param_value_5 | varchar(50) | NO | | | |
| changedtime | datetime | YES | | NULL | |
| lastupdate | varchar(100) | YES | | NULL | |
| action | varchar(20) | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
11 rows in set (0.00 sec)



(This post was edited by FishMonger on Feb 28, 2014, 6:56 AM)


FishMonger
Veteran / Moderator

Feb 28, 2014, 7:36 AM

Post #4 of 6 (1444 views)
Re: [sh.ajay12] How to arrange lastupdate row in ascending order from database. [In reply to] Can't Post

Is this already in production? It appears that the person that designed the db didn't know about or at least didn't follow the guidelines of "Database Normalization" very well.
http://en.wikipedia.org/wiki/Database_normalization

If it's in production, you'll need to make sure that you update all of your sql statements to accommodate the changes to the schema.

Without knowing more info on your data and how it's being gathered and populated in the db, it's hard to make any firm recommendations on a step-by-step process.

The easiest approach might be to add 2 additional fields (name and date) and use a stored procedure to split the data in the lastupdate field and put it into the new fields. Then use those fields in your query instead of the lastupdate field.


sh.ajay12
User

Mar 1, 2014, 6:12 AM

Post #5 of 6 (1425 views)
Re: [FishMonger] How to arrange lastupdate row in ascending order from database. [In reply to] Can't Post

Thanks FishMonger,

I will try to work on your tips and i will post again with few more data.


sh.ajay12
User

Mar 10, 2014, 4:25 AM

Post #6 of 6 (1269 views)
Re: [sh.ajay12] How to arrange lastupdate row in ascending order from database. [In reply to] Can't Post

Hello FishMonger and Forum Members,

Here is one example of the web page table which is showing wrong order.

How to write the stored procedure to divide the User and the time in two different columns.

Here i have attached the doc file which shows the web page table and the back end mysql table from where the information is being fetched, and the sql statement which is written in the code.
Attachments: web page table..docx (20.8 KB)

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives