IF THEN Question

[countrygyrl]

How would I add to the code below.  This code works great for me but on occasion there will be times when there are matching values in invoice_id. What I want to do is say if the invoice_id matches a previous invoice_id, add the new amount in total to the total in the matching row, don't make another table row. If it does not match any of the previous invoice_id values then make a new table row. Can anyone please help me?

 

$result = mysql_query("SELECT * FROM si_invoices 
  LEFT JOIN si_invoice_items ON si_invoices.id = si_invoice_items.invoice_id 
  WHERE si_invoices.customer_id='$_SESSION[user_id]' ORDER BY invoice_id");
$num=mysql_num_rows($result);

$i=0;
while ($i < $num) {

$inv_id=mysql_result($result,$i,"id");
$inv_invoice_id=mysql_result($result,$i,"invoice_id");
$inv_customer_id=mysql_result($result,$i,"customer_id");
$inv_gross_total=mysql_result($result,$i,"gross_total");
$inv_description=mysql_result($result,$i,"description");
$inv_total=mysql_result($result,$i,"total");
$inv_total=number_format($inv_total, 2);
$inv_date=mysql_result($result,$i,"date");
$inv_date=substr($inv_date, 0, -9);
$pay= mysql_query("select * from si_payment where ac_inv_id='$inv_invoice_id'");


echo "<tr class=\"d".($i & 1)."\"><td valign='top'>$inv_date</td><td valign='top'>$inv_invoice_id</td><td valign='top'>$$inv_total</td><td><a href='invoices/Invoice$inv_invoice_id.pdf' target='_blank'><img src='../images/pdf_icon.gif' width='17' height='17' alt='PDF File' border='0' /></a></td></tr>";

$i++;
}

[Psycho]

You can't add a value to HTML content after you have written that content to the page. You just need to add a GROUP BY clause to your query as well as specifically set the values to obtain - don't rely on *. Also, there is no need to get the number of rows and performa a while loop on that. And you should never, never do queries within loops. I don't even see what you are using the $pay result for.

 

$query = "SELECT invoice_id, date, SUM(total) as invoiceTotal
          FROM si_invoices
          LEFT JOIN si_invoice_items ON si_invoices.id = si_invoice_items.invoice_id
          WHERE si_invoices.customer_id='$_SESSION[user_id]'
          GROUP BY invoice_id
          ORDER BY invoice_id"
$result = mysql_query($query);

$row=0;
while ($record = mysql_fetch_assoc($result))
{
    $class = 'd' . ($row % 2);
    $inv_date=substr($record['date'], 0, -9);

    echo "<tr class=\"{$class}\">";
    echo "<td valign='top'>{$inv_date}</td>";
    echo "<td valign='top'>{$record['invoice_id']}</td>";
    echo "<td valign='top'>${$record['invoiceTotal']}</td>";
    echo "<td><a href='invoices/Invoice{$record['invoice_id']}.pdf' target='_blank'>";
    echo "<img src='../images/pdf_icon.gif' width='17' height='17' alt='PDF File' border='0' /></a></td>";
    echo "</tr>";
    $row++;
}

[countrygyrl]

It seems to grab the invoice_id and date just fine, but not the total for each one, any idea why?

[countrygyrl]

Thank you very much for your awesome help, I found the problem, there was a $ sign where there shouldn't have been but you probably put it there to test me  Here is the final working code, in case it will help anyone else:

$query = "SELECT invoice_id, date, SUM(total) as invoiceTotal
          FROM si_invoices
          LEFT JOIN si_invoice_items ON si_invoices.id = si_invoice_items.invoice_id
          WHERE si_invoices.customer_id='$_SESSION[user_id]'
          GROUP BY invoice_id
          ORDER BY invoice_id";
$result = mysql_query($query);

$row=0;
while ($record = mysql_fetch_assoc($result))
{
    $class = 'd' . ($row % 2);
    $inv_date=substr($record['date'], 0, -9);
$inv_total=$record['invoiceTotal'];
$invoice_total=number_format($inv_total, 2);

    echo "<tr class=\"{$class}\">";
    echo "<td valign='top'>{$inv_date}</td>";
    echo "<td valign='top'>{$record['invoice_id']}</td>";
    echo "<td valign='top'>$invoice_total</td>";
    echo "<td><a href='invoices/Invoice{$record['invoice_id']}.pdf' target='_blank'>";
    echo "<img src='../images/pdf_icon.gif' width='17' height='17' alt='PDF File' border='0' /></a></td>";
    echo "</tr>";
    $row++;
}