store invoice in database

[ianhaney]

Hi

 

I am trying to make this invoice script store in a database

 

https://css-tricks.com/editable-invoice-v2/

 

I have managed to get invoice_id, customer_name, item, description and qty stored in the database but cant get the following saved

 

date

unit_cost

price

subtotal

total

amount_paid

balance_due

 

the date gets stored as 0000-00-00 and the rest gets stored as 0.00

 

it uses javascript as well for the totals to update themselves when new lines are added in the invoice

 

When I click submit, I get the following errors

 

Notice: Undefined index: price in /home/sites/it-doneright.co.uk/public_html/admin/sale-items/new-invoice.php on line 78 Notice: Undefined index: subtotal in /home/sites/it-doneright.co.uk/public_html/admin/sale-items/new-invoice.php on line 78 Notice: Undefined index: balance_due in /home/sites/it-doneright.co.uk/public_html/admin/sale-items/new-invoice.php on line 79

 

Below is the coding I have so far

<?php
ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);
?>
    
<?php
if(isset($_POST["submit"])){
$hostname='localhost';
$username='';
$password='';

try {
$dbh = new PDO("mysql:host=$hostname;dbname=dbname",$username,$password);

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // <== add this line
$sql = "INSERT INTO invoices (invoice_id, customer_name, date, item, description, unit_cost, qty, price, subtotal, amount_paid, balance_due)
VALUES ('".$_POST["invoice_id"]."','".$_POST["customer_name"]."','".$_POST["date"]."','".$_POST["item"]."','".$_POST["description"]."','".$_POST["unit_cost"]."','".$_POST["qty"]."','".$_POST["price"]."','".$_POST["subtotal"]."',
'".$_POST["amount_paid"]."','".$_POST["balance_due"]."')";
if ($dbh->query($sql)) {
echo "<script type= 'text/javascript'>alert('New Record Inserted Successfully');</script>";
}
else{
echo "<script type= 'text/javascript'>alert('Data not successfully Inserted.');</script>";
}

$dbh = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}

}

?>

<div id="page-wrap">
		
        <form action="" method="post">
        
		<textarea id="header">INVOICE</textarea>
		
		<div id="identity">
		
            <textarea id="address">
            
            </textarea>

            <div id="logo">

              <div id="logoctr">
                <a href="javascript:;" id="change-logo" title="Change logo">Change Logo</a>
                <a href="javascript:;" id="save-logo" title="Save changes">Save</a>
                |
                <a href="javascript:;" id="delete-logo" title="Delete logo">Delete Logo</a>
                <a href="javascript:;" id="cancel-logo" title="Cancel changes">Cancel</a>
              </div>

              <div id="logohelp">
                <input id="imageloc" type="text" size="50" value="" /><br />
                (max width: 540px, max height: 100px)
              </div>
              <img id="image" src="images/logo/it-done-right.jpg" />
            </div>
		
		</div>
		
		<div style="clear:both"></div>
		
		<div id="customer">

            <textarea id="customer-title" name="customer_name">
            Widget Corp.
c/o Steve Widget
</textarea>

            <table id="meta">
                <tr>
                    <td class="meta-head">Invoice #</td>
                    <td><textarea name="invoice_id">000123</textarea></td>
                </tr>
                <tr>

                    <td class="meta-head">Date</td>
                    <td><textarea id="date" name="date">December 15, 2009</textarea></td>
                </tr>
                <tr>
                    <td class="meta-head">Amount Due</td>
                    <td><div class="due">£875.00</div></td>
                </tr>

            </table>
		
		</div>
		
		<table id="items">
		
		  <tr>
		      <th>Item</th>
		      <th>Description</th>
		      <th>Unit Cost</th>
		      <th>Quantity</th>
		      <th>Price</th>
		  </tr>
		  
		  <tr class="item-row">
		      <td class="item-name"><div class="delete-wpr"><textarea name="item">Web Updates</textarea><a class="delete" href="javascript:;" title="Remove row">X</a></div></td>
		      <td class="description"><textarea name="description">Monthly web updates for http://widgetcorp.com (Nov. 1 - Nov. 30, 2009)</textarea></td>
		      <td><textarea class="cost" name="unit_cost">£650.00</textarea></td>
		      <td><textarea class="qty" name="qty">1</textarea></td>
		      <td><span class="price"><textarea class="price" name="price">£650.00</textarea></span></td>
		  </tr>
		  
		  <tr id="hiderow">
		    <td colspan="5"><a id="addrow" href="javascript:;" title="Add a row">Add a row</a></td>
		  </tr>
		  
		  <tr>
		      <td colspan="2" class="blank"> </td>
		      <td colspan="2" class="total-line">Subtotal</td>
		      <td class="total-value"><div id="subtotal"><textarea name="subtotal">£875.00</textarea></div></td>
		  </tr>
		  <tr>

		      <td colspan="2" class="blank"> </td>
		      <td colspan="2" class="total-line">Total</td>
		      <td class="total-value"><div id="total"><textarea name="total">£875.00</textarea></div></td>
		  </tr>
		  <tr>
		      <td colspan="2" class="blank"> </td>
		      <td colspan="2" class="total-line">Amount Paid</td>

		      <td class="total-value"><textarea id="paid" name="amount_paid">£0.00</textarea></td>
		  </tr>
		  <tr>
		      <td colspan="2" class="blank"> </td>
		      <td colspan="2" class="total-line balance">Balance Due</td>
		      <td class="total-value balance"><div class="due"><textarea name="balance_due">£875.00</textarea></div></td>
		  </tr>
		
		</table>
		
		<div id="terms">
		  <h5>Terms</h5>
		  <textarea>NET 30 Days. Finance Charge of 1.5% will be made on unpaid balances after 30 days.</textarea>
		</div>
        
        <div style="clear: both;">
        <input type="submit" value=" Submit " name="submit"/>
        </div>
	</form>
	</div>

I know I have to do more code to prevent SQL attacks but just want to get it fully working first and will then do the SQL attack coding

[Barand]

Your date format is invalid for storing in a database table, it needs converting to YYYY-mm-DD.

 

Numeric values cannot contain "£".

[ianhaney]

Ahh ok, I got the date sorted and saving in the database

 

just got to sort the £ issue now as that is being added in the javascript coding, if I take it out from that, it comes up NaN on the invoice

 

Is there a way to work around it, should I paste it in the javascript forum or keep it here as is bit of both javascript and PHP

Edited December 20, 2015 by ianhaney

[mac_gyver]

you would strip the currency symbol from the submitted form data, in the php code.

 

i hope you are only using this as a learning tool for inserting form data into a database table?  this example code is not how you would do this work-flow in real life. it is only a demonstration of some css and javascript tricks and mixes editable form design elements and form data elements.

 

if you were doing this for real, the form design elements (logo, company information...) would be created/edited and persistently stored somewhere and could not be changed by the person adding items to the invoice. if you are creating an invoice for a new customer, you would first add the customer information to `customer` db table. to create a new invoice for a customer, you would select the customer from a list of customers from the `customer` db table. this would create a new entry in an `invoice` db table. only the customer_id would be stored in the invoice table and would be used to retrieve the customer information from the `customer` db table to display it on the invoice. adding an entry to the `invoice` db table would assign the invoice number/invoice_id for this invoice. you would then select/enter items for the invoice. these items would be stored in an `invoice_details` table and be related to the invoice they belong to using the invoice_id.