StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POcalculate order location in mysql query
text
Body
copied!<p>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.</p> <p>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.</p> <p>To add some clarity I mean the following</p> <pre><code>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 </code></pre> <p>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</p> <pre><code>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 </code></pre> <p>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.</p> <p>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.</p> <p>Thanks in advance for any solutions or help.</p> <p>Alan </p> <p>EDIT</p> <p>OK some additional information.</p> <ul> <li>My table I am pulling the data from is called: urn </li> <li>At present my table has 5 columns called URN_ID, URN_PROJECT_ID, URN_Date, URN_Due_Terms, URN_Doc_Type</li> </ul> <p><strong>N.B. I have updated the column data above to reflect my actual column Names</strong></p> <p>So far I have tried:</p> <pre><code>$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']; </code></pre> <p>This however only returns 1 as the value regardless of which URN_ID I have displayed.</p> <p>I have no real knowledge of statements using @ sign and as such am at a loss how to debug it.</p> <p>Any help would be good.</p> <p>Cheers. Al.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload