mysql problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
cbcampbell
Forum Newbie
Posts: 19
Joined: Mon Jun 09, 2003 12:12 am

mysql problem

Post by cbcampbell »

I'm trying to get a invoicing system I'm writing to correctly enter values into a table. I have a form that has items, quatity purchased, and price for each. In my php app, I have all these values entered (correctly?) in the script. But when I look at the entry into the invoice table after i click SUBMIT, none of the variables were multiplied or added.

Here is the code:

Code: Select all

<?php
<?
include("dbinfo.inc.php");

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$pt0_total="$pt0_qty * $pt0_price";
$pt1_total="$pt1_qty * $pt1_price";
$pt2_total="$pt2_qty * $pt2_price";
$pt3_total="$pt3_qty * $pt3_price";
$pt4_total="$pt4_qty * $pt4_price";
$pt5_total="$pt5_qty * $pt5_price";
$pt6_total="$pt6_qty * $pt6_price";
$pt7_total="$pt7_qty * $pt7_price";
$pt8_total="$pt8_qty * $pt8_price";
$pt9_total="$pt9_qty * $pt9_price";

$pt_total="$pt0_total + $pt1_total + $pt2_total + $pt3_total + $pt4_total
+ $pt5_total + $pt6_total + $pt7_total + $pt8_total + $pt9_total";

$subtotal="$inv_labor + $pt_total";

$tax_rt=".0825";
$tax="$subtotal * $tax_rt";

$total="$subtotal + $tax";

$query = "insert into invoices values ('',now(),
'$inv_ponum','$inv_empnum','$inv_cstname','$inv_cstadd','$inv_cstcity',
'$inv_cstst','$inv_cstzip','$inv_disc','$inv_rep','$pt0_qty','$pt0_desc',
'$pt0_price','$pt1_qty','$pt1_desc','$pt1_price','$pt2_qty','$pt2_desc',
'$pt2_price','$pt3_qty','$pt3_desc','$pt3_price','$pt4_qty','$pt4_desc',
'$pt4_price','$pt5_qty','$pt5_desc','$pt5_price','$pt6_qty','$pt6_desc',
'$pt6_price','$pt7_qty','$pt7_desc','$pt7_price','$pt8_qty','$pt8_desc',
'$pt8_price','$pt9_qty','$pt9_desc','$pt9_price','$inv_labor','$pt_total',
'$subtotal','$tax','$total')";

mysql_query($query);

mysql_close();

echo "<font size='6'>Thank You.</font><br><br>
<a href='invoices.htm'>Return</a> - <a href='viewinv.php'>View Invoices</a>";
?>
?>
The output into the database is this example...I even filled out the customer PO field, and it didn't add it to the db.

1 (inv_id)
2003-06-19 (inv_date)
(inv_ponum) # blank field ?????????
02 (inv_empnum)
joe (inv_cstname)
555 Elkwood Ave. (inv_cstadd)
beeville (inv_cstcity)
TX (inv_cstst)
78104 (inv_cstzip)
this is a discrepancy (inv_disc)
we had a few repirs (inv_rep)
3 (pt1_qty) # This is the item quantity field
radiator (pt1_desc)
40.00(pt1_price) # This field is Price EACH
1 (pt2_qty)
spark plug (pt2_desc)
5.00 (pt2_price)
75.00 (inv_labor)
3.00 (pt_total) # this is an incorrect amount - should be $125.00
75.00 (subtotal) # incorrect - should be $200.00
75.00 (tax) # in TX tax_rt is 8.25% - total tax should be $16.50
75.00 (total) # incorrect - should be $216.50

for some reason my script isn't working...what am I doing wrong?! Also, is their an easier way to have parts added like that in a form? Cause right now, entering in all those part is tedious...and I may need more than 10 parts to an invoice. But then that would mean I would have to add fields dynamically to my table right? Is that even possible?
User avatar
cbcampbell
Forum Newbie
Posts: 19
Joined: Mon Jun 09, 2003 12:12 am

Post by cbcampbell »

problem fixed!...it was a syntax error. I was using quotes instead of parentheses in my variable multiplications. Some one should have told me...

RTFM :roll:
User avatar
trollll
Forum Contributor
Posts: 181
Joined: Tue Jun 10, 2003 11:56 pm
Location: Round Rock, TX
Contact:

Post by trollll »

As far as the math issue goes, you should try removing the quotes around the operations. With the quotes it will result in each variable holding a string "x + y" instead of evaluating x + y and assigning it to the variable. If you have the PHP print out the query to the page you should see exactly what it has done...

Sorry! Got distracted and just got back to the computer. Started writing this about ten minutes after you posted the first time. :oops:

As far as the table thing goes, you'd benefit more from splitting it into a table for a transaction with all of the transaction-specific data and have another one for each item (transaction_id, qty, desc, price) and do a left join to handle getting a grand total/tax/etc. That way everything would stay in third normal form and make it much eaiser to deal with down the road. :)
Post Reply