...

View Full Version : IF THEN Question



countrygirl
04-04-2010, 09:17 PM
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 not 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++;
}

guelphdad
04-05-2010, 04:28 AM
See INSERT ON DUPLICATE UPDATE syntax in the manual

countrygirl
04-05-2010, 04:33 AM
In case this will help anyone in the future, here is the working code:


$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++;
}

guelphdad
04-05-2010, 07:05 PM
Sorry I read your question as needing to insert a new row in your database table, you meant in displaying your records.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum