Fetching data in sorted form from mysql using php

Question

I am working on a php page and have a table named

computer_complaint

all i want is to fetch data from this table by the latest complaint made by user. when user files any complaint it stores in this table which gives him a unique

computer_id

and

problem_date

is column which enters the date with time when user made complaint. and same user can made complaint more than once

now i want a query by which i can able to display the latest details of that user's complaint so i need to sort the table on the last problem_date on that computer_id. My current query is

select *,date_format(cc.problem_date,'%d/%m/%Y')as problem_date,ifnull(cc.attended_on,'Pending')as com_status from computer_complaint cc,computer_master cm,computer_st_complaint_mode ccm,computer_company_master cmy WHERE cc.`status`='Active' and cm.`status`='Active' and ccm.`status`='Active' and cmy.`status`='Active' and cc.computer_id=cm.computer_id and cc.method=ccm.comp_mod_id  and cm.company_id=cmy.company_id

but it is showing all the records of that particular id while i only want the latest one Thus i want all computer_id(s) to show only the latest filed complaint not to show all the complaints for each id as this make my display screen wired.

Thanks in advance.

Lars Ebert-Harlaar · Accepted Answer · 2013-07-17 07:56:35Z

1

To fetch the last one of each cc.computer_id, you can use this:

select *,date_format(cc.problem_date,'%d/%m/%Y')as problem_date,ifnull(cc.attended_on,'Pending')as com_status from computer_complaint cc,computer_master cm,computer_st_complaint_mode ccm,computer_company_master cmy WHERE cc.`status`='Active' and cm.`status`='Active' and ccm.`status`='Active' and cmy.`status`='Active' and cc.computer_id=cm.computer_id and cc.method=ccm.comp_mod_id  and cm.company_id=cmy.company_id ORDER BY cc.problem_date DESC GROUP BY cc.computer_id

That will give you the latest entry, according to cc.problem_date!

edited Jul 17, 2013 at 7:56

answered Jul 17, 2013 at 7:43

Lars Ebert-Harlaar

3,5373 gold badges26 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2586824 Over a year ago

thanks Lars but this query only shows the last record in table i want the last record of each computer_id not only the last entered record in table.

user2586824 Over a year ago

Please don't say sorry you tried to help me and really thanks for that.

user2586824 Over a year ago

this is giving the following error "Error Msg:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY cc.computer_id' at line 1"

Collectives™ on Stack Overflow

Fetching data in sorted form from mysql using php

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related