Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Apr 2009
    Posts
    23
    Thanks
    3
    Thanked 0 Times in 0 Posts

    IF THEN Question

    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?

    Code:
    $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++;
    }
    Last edited by countrygirl; 04-05-2010 at 12:35 AM.

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    See INSERT ON DUPLICATE UPDATE syntax in the manual

  • #3
    New Coder
    Join Date
    Apr 2009
    Posts
    23
    Thanks
    3
    Thanked 0 Times in 0 Posts
    In case this will help anyone in the future, here is the working code:

    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++;
    }
    Last edited by countrygirl; 04-05-2010 at 06:19 PM.

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Sorry I read your question as needing to insert a new row in your database table, you meant in displaying your records.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •