select update record from table

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
shafiq2626
Forum Commoner
Posts: 88
Joined: Wed Mar 04, 2009 1:54 am
Location: Lahore
Contact:

select update record from table

Post by shafiq2626 »

Hi
i want to select update record for multi user please check this query
following query is working.

Code: Select all

select DISTINCT cpm.customerid,cpm.date,cpm.debet,cpm.credet,cpm.invoiceno,cpm.balance from customerpaymentsheet cpm where cpm.balance > '0' and cpm.date BETWEEN '$sdate' and '$edate' group by cpm.customerid order by cpm.date DESC"
but this is displaying frist record against every user not last can you check please.
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: select update record from table

Post by JakeJ »

Since I have no idea what your data set looks like, I can't help you. If you're looking for the last of a group of records, try max() on whichever field you think is appropriate.
shafiq2626
Forum Commoner
Posts: 88
Joined: Wed Mar 04, 2009 1:54 am
Location: Lahore
Contact:

Re: select update record from table

Post by shafiq2626 »

Hi!
you can say that one user has multi record date by date and other user also same conditon.
i want to select last record against every user.
my query is selcting first record for every user.
i want to select them order by date desc

Code: Select all

"select DISTINCT cpm.customerid,cpm.date,cpm.debet,cpm.credet,cpm.invoiceno,cpm.balance from customerpaymentsheet cpm where cpm.balance > '0' and cpm.date BETWEEN '$sdate' and '$edate' group by cpm.customerid order by cpm.date DESC"
please some one can check
aneesme
Forum Newbie
Posts: 9
Joined: Wed Jan 27, 2010 4:22 am
Location: Bangalore

Re: select update record from table

Post by aneesme »

order by cpm.customerid, cpm.date DESC
shafiq2626
Forum Commoner
Posts: 88
Joined: Wed Mar 04, 2009 1:54 am
Location: Lahore
Contact:

Please help to select last record against a cusotmer

Post by shafiq2626 »

Hi!
i am using following query

Code: Select all

"select  cpm.customerid,cpm.date,cpm.debet,cpm.credet,cpm.invoiceno,cpm.balance from customerpaymentsheet cpm where cpm.balance > '0' and cpm.date BETWEEN '$sdate' and '$edate'  order by cpm.customerid,cpm.date DESC "
output you can see in attachment.

there are multple records agains one customer . Actualy i want to select his last record as you can see Muhammad shafique has two records i want to take his only top record balnce like 2000
and Azam Shmai has multiple records i want to display only his top record balance like 3200.
please any one check and help with thanks.
Attachments
balancesheet.jpg
balancesheet.jpg (163.01 KiB) Viewed 77 times
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: select update record from table

Post by JakeJ »

Try this:

"select cpm.customerid,cpm.date,cpm.debet,cpm.credet,cpm.invoiceno, MAX(cpm.balance) from customerpaymentsheet cpm where cpm.balance > '0' and cpm.date BETWEEN '$sdate' and '$edate' GROUP BY cpm.customername order by cpm.customerid, cpm.date DESC "

Notice the MAX(balance) and GROUP BY statements.
Post Reply