title: calculate order location in mysql query
body: <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>