CODEWITHSUNDEEP
phpmysqlcount
Burdie87
Burdie87

Reputation: 123

calculate order location in mysql query

I am currently designing an invoicing system where invoices can be allocated to seperate projects. An invoice will only ever be allocated to one project.

At present I have a mysql table which I want to query with the invoice number, retrieve all of the invoices for the respective project that invoice is for, order them by the creation date and then(this is the bit im stuck on) based on the invoice number I will have queried with, calculate the number that invoice is out of the total invoices for that project.

To add some clarity I mean the following

URN_ID        URN_Project_ID    URN_Date
1             1                 2012-03-17
2             3                 2012-03-18
3             2                 2012-03-21
4             1                 2012-03-21
5             2                 2012-04-05
6             1                 2012-04-08
7             3                 2012-04-15
8             2                 2012-04-24
9             1                 2012-05-02
10            3                 2012-05-10

Lets say that I have queried with invoice number 6 and therefore the project is project 1. In essence I would be narrowing the information to the following

URN_ID        URN_Project_ID    URN_Date
1             1                 2012-03-17
4             1                 2012-03-21
6             1                 2012-04-08
9             1                 2012-05-02

We can see from above that I have a total of 4 invoices however I want a way to return that invoice 6 is number 3 out 4 invoices. The purpose of this is so that if someone prints a copy invoice in the future it is clear to the person viewing it was invoice 3 on that project. Finally I don't need to detail the total number of invoices on my document only the location within the total number.

Any help with this would be much appreciated as I have been searching most of the morning for a solution I can piece together with no avail. As a fall-back I can always add another column to my table and at input stage do a mysql count and then add the total number to date into the column but I would far rather learn something new and keep my table to a minimum.

Thanks in advance for any solutions or help.

Alan

EDIT

OK some additional information.

N.B. I have updated the column data above to reflect my actual column Names

So far I have tried:

$query2 = "SELECT  @curRow := @curRow + 1 AS row_number, iv.* FROM urn iv JOIN (SELECT @curRow := 0) r WHERE iv.URN_Project_ID='$urn_project_id'";
$res2 = mysql_query($query2);
$data2 = mysql_fetch_array($res2);
echo $data2['row_number'];

This however only returns 1 as the value regardless of which URN_ID I have displayed.

I have no real knowledge of statements using @ sign and as such am at a loss how to debug it.

Any help would be good.

Cheers. Al.

Upvotes: 0

Views: 179

Answers (2)

Mike Mackintosh
Mike Mackintosh

Reputation: 14237

Although it may look a little uncomfortable at first, this is probably the best way.

mysql> SET @rank=0;

mysql> SELECT @rank:=@rank+1 AS rank,OtherColumns as Column FROM Invoices .....

The @rank increment will do what you want. You should set the @rank variable before hand; there may be a way to do a case then end if rank is set or not like (not tested):

mysql> SELECT case @rank when NULL then @rank=0 else @rank:=@rank+1 end as rank FROM ....

Upvotes: 0

vogomatix
vogomatix

Reputation: 5041

You basically want to add a row number onto your result set, so something like:

SELECT  @curRow := @curRow + 1 AS row_number,
        iv.*
FROM    invoices iv
JOIN    (SELECT @curRow := 0) r
WHERE   iv.project_id=1;

This should return:

row_number invoice id    project_id    invoice_date
1          1             1             2012-03-17
2          4             1             2012-03-21
3          6             1             2012-04-08
4          9             1             2012-05-02

Upvotes: 2

Related Questions