Calculating from a table drop down list

phplearner

Hours and hours of experimenting have led me to this point.

I am trying to do sales tax calculations for a final value based upon the City selected from a MySQL table drop down list.

However, it has some unique requirements (see the if-else options in the code below).

There are over 1,500 entries in the table, and whichever City is selected, I also have to associate the taxrate (AND the County to the City selected as it may need be used in the final mix). And then there is the out-of-state checkbox option in the same form (unless there is a nifty way to use TWO separate forms on the same page?).

I'm also hung up on how to yield an error message if a City has not been selected (and) the checkbox not checked. If there are no errors, then I need to go to the 'nextpage.php' file.

No pretty HTML layout here...that will come later when the darn thing gets working.

Can anyone help find the final solutions? Thanks much.

<?php

//
// --->NEED ERROR MESSAGE CODE IF NO SHIP-TO CITY OR OUT-OF-STATE CHECKBOX IS UNCHECKED IS SELECTED!!!
// ....IF NO ERRORS, TO GO TO:  nextpage.php

$error = "";
if ((isset($HTTP_POST_VARS["MM_insert"])) && ($HTTP_POST_VARS["MM_insert"] == "form1")) {

if (((isset($HTTP_POST_VARS["city"])) && ($HTTP_POST_VARS["city"] == "--Select SHIP-TO City--")) && 
   ((isset($HTTP_POST_VARS["outofstate"])) && ($HTTP_POST_VARS["outofstate"] == "unchecked"))) {
   $error .= "You must select a city or out-of-state.<br>";
//} else {
// HOW TO GO TO nextpage.php IF NO ERRORS???
//} 
}
}
?>
<html>
<head>
<title>Tax</title>
</head>
<body>

<form action="<?=$_SERVER['PHP_SELF'];?>" method="post">

<?php
$hostname = "localhost";
$database = "taxdb";
$username = "me";
$password = "";
$dbcnx = mysql_connect($hostname, $username, $password) or die(mysql_error());

$taxcity = " ";
$taxrate = " ";
$taxcounty = " ";
$outofstate = " ";

mysql_select_db($database, $dbcnx);
$query = "SELECT * FROM salestaxtable";
$list = mysql_query($query, $dbcnx) or die(mysql_error());
?>
MY STATE CUSTOMER:<br>
Please choose SHIP-TO DESTINATION City<br>
<SELECT NAME="city" size="1">
<OPTION SELECTED>--Select SHIP-TO City--
<?php
while ($row = mysql_fetch_assoc($list)) {
$taxcity=$row['taxcity'];
$taxrate=$row['taxrate'];
$taxcounty=$row['taxcounty'];
?>
<OPTION VALUE="<?php echo $taxcity?></OPTION>
<?php
}
?>
</SELECT>
<br><br>
PLEASE CHECK THIS BOX IF YOU ARE AN OUT-OF-STATE CUSTOMER<br>
<input name="outofstate" type="checkbox"> OUT-OF-STATE CUSTOMER<br>

<?php

// Tax Area Rate Configurations
if ($taxcity == "mycity") {
    $taxrate == "$taxrate";
} else {
  if ($taxcity != $mycity && $taxcounty == "mycounty") {
    $taxrate == ".08";
} else {
  if ($taxcity != $mycity && $taxcounty != "mycounty") {
    $taxrate == ".075";
} else {
  if ($outofstate == "checked") {
    $taxrate == ".00";
}

?>

<!-- taxrate ($taxrate) MUST be the taxrate for the Selected City based upon the if-else config above -->
echo "<input type=\"hidden\" name=\"taxrate\" value=\"<?php echo $taxrate?>\">"; 
<input type="hidden" name="MM_insert" value="form1">
<br>
<INPUT name="submit" type="submit" class=submitbutton value="Next Page">
&nbsp;&nbsp;&nbsp; 
<INPUT name="reset" type="reset" class=resetbutton value="Reset Form">
</form>

</body>
</html>
<?php
?>

😕

tekky

if you are looking for an error.....

<?php echo $taxcity?>

you need a ; after $taxcity to terminate your PHP command....

also I see you used $SERVER[] but still used $HTTP*VARS instead of $GET or $_POST.... may want to consider switching that...

phplearner

tekky:

Thanks for pointing out the error. I'm so blurry eyed still trying to find what may be a missing { or }.

Not sure exactly what you mean about the SERVER vs. HTTP part(s).

barand

Tekky

::From PHP manual - Basic syntax::

The closing tag for the block will include the immediately trailing newline if one is present. Also, the closing tag automatically implies a semicolon; you do not need to have a semicolon terminating the last line of a PHP block.

barand

phplearner

By the time you get to the "// Tax Area Rate Configurations "
section you've read all the data so the values will always be those from the last record read.

Do that after the post when you know what was selected, if any.

phplearner

(barand):

Hey...aren't you the guy with the COOL table display stuff???

Thanks for the info, but how will my calculation stuff work unless the form is 'submitted' reflecting the actual chosen value?

BTW, I retweaked this section and at least no longer have any errors showing when the page 1st comes up.

//Tax Area Rate Configurations
$mycity = "Mega City";
$mycounty = "Zeta County";
if ($taxcity == "$mycity") {
    $taxrate == "$taxrate";
} elseif ($taxcity != $mycity && $taxcounty == "$mycounty") {
    $taxrate == ".08";
} elseif ($taxcity != $mycity && $taxcounty != "$mycounty") {
    $taxrate == ".075";
} elseif ($outofstate == "checked") {
    $taxrate == ".00";
}

The elseif on the last line seems strange, but I couldn't get it to work with just 'else'.

Still can't get the Error part to work right and just dunno how to go to 'nextpage.php' if no errors.

For some reason, the Cities are not populating the drop-down select list...it used to work.

ARRRGHHHH :mad:

tekky

Originally posted by phplearner
tekky:

Thanks for pointing out the error. I'm so blurry eyed still trying to find what may be a missing { or }.

Not sure exactly what you mean about the SERVER vs. HTTP part(s).

you are making use of the new superglobal $_SERVER

but just before it you are using the antiquated method of $HTTP_POST_VARS when you could also use the new superglobals for $_POST

and Barand... interesting, never knew that nor seen it mentioned in any books I've read 🙂

phplearner

Thanks for the info. I copied in the HTTP code from another script I have and revised it but just can not get it to work.

FYI, I found another error:

echo "<input type=\"hidden\" name=\"taxrate\" value=\"<?php echo $taxrate?>\">";

This was in the HTML section :mad:

But I'm still not getting my drop down list from the MySQL table which I had last night!

barand

yep, that's me.

Is this ow the calc should be?

//Tax Area Rate Configurations 
$mycity = "Mega City"; 
$mycounty = "Zeta County"; 

if ($outofstate == "checked") { 
    $taxrate == ".00"; 
}
else {
    if ($taxcity == "$mycity") { 
        $taxrate == "$taxrate"; 
    } 
    else {
        if ($taxcounty == "$mycounty")  

            $taxrate == ".08"; 
         else  

            $taxrate == ".075"; 
    }
}

barand

<?php
while ($row = mysql_fetch_assoc($list)) {
$taxcity=$row['taxcity'];
$taxrate=$row['taxrate'];
$taxcounty=$row['taxcounty'];
?>
<OPTION VALUE="<?php echo $taxcity?>"> </OPTION>
<?php
}
?>

phplearner

Bizarre...

In: <input type=\"hidden\" name=\"taxrate\" value=\"<?php echo $taxrate?>\">

I am getting this showing: \"7.25\"

This is actually the 1st tax rate in the MySQL table, but this shows with nothing selected, and City names are still not displaying.

phplearner

I found out why no City names were displaying:

<OPTION VALUE="<?php echo $taxcity?>"></OPTION>

This used to be:

<OPTION VALUE="<?php echo $taxcity?>"><?php echo $taxcity?></OPTION>

I just tweaked it up to:

<OPTION VALUE="<?php echo $taxcity?>"><?php echo $taxcity?> - <?php echo $taxrate?>%</OPTION>

Displays cool...

But, if an 8.5% tax rate city is selected, the tax rate still ends up being another rate. I just don't understand why.

phplearner

Hmmm...

while ($row = mysql_fetch_assoc($list)) {
$taxcity=$row['taxcity'];
$taxrate=$row['taxrate'];
$taxcounty=$row['taxcounty'];
?>
<OPTION VALUE="<?php echo $taxcity?>"><?php echo $taxcity?></OPTION>
}

The end result is always the LAST entry in the table. I also tried mysql_fetch_array but it was the same. mysql_fetch_row would not display entries.

2AM now...I've had it.

phplearner

ARRRRGGGGHHHH...

I decided to now split this out into two separate pages, but the darn stuff just ain't working.

The 'hidden' $variables are always the last entry in the db table and not the selected City.

My assignment operators may be whacked. I'm burned out.

"HELP!!!"

form.php

<?php
session_start();

?>
<html>
<head>
<title>Tax Options</title>
</head>
<body>

<form action="calc.php" method="post">

<?php
$hostname = "localhost";
$database = "taxdb";
$username = "me";
$password = "";
$dbcnx = mysql_connect($hostname, $username, $password) or die(mysql_error());

$taxcity = " ";
$taxrate = " ";
$taxcounty = " ";

mysql_select_db($database, $dbcnx);
$query = "SELECT * FROM taxtab;es";
$list = mysql_query($query, $dbcnx) or die(mysql_error());
?>
MY STATE CUSTOMER:<br>
Please choose SHIP-TO DESTINATION City<br>
<SELECT NAME="city" size="1">
<OPTION SELECTED>--Select SHIP-TO City--</OPTION>
<?php
while ($row = mysql_fetch_assoc($list)) {
$taxcity=$row['taxcity'];
$taxrate=$row['taxrate'];
$taxcounty=$row['taxcounty'];
?>
<OPTION VALUE="<?php echo $taxrate?>"><?php echo $taxcity?></OPTION>
<?php
}
?>
</SELECT>
<br><br>
PLEASE CHECK THIS BOX IF YOU ARE AN OUT-OF-STATE CUSTOMER<br>
<input name="outofstate" type="checkbox"> OUT-OF-STATE CUSTOMER<br>

<input type="hidden" name="taxrate" value="<?php echo $taxrate?>">
<input type="hidden" name="taxcity" value="<?php echo $taxcity?>">
<input type="hidden" name="taxcounty" value="<?php echo $taxcounty?>">
<input type="hidden" name="outofstate" value="<?php echo $outofstate?>">
<br>
<INPUT name="submit" type="submit" value="Next Page">
&nbsp;&nbsp;&nbsp; 
<INPUT name="reset" type="reset" value="Reset Form">
</form>

</body>
</html>
<?php
?>

calc.php
[code=php]
?>
<html>
<head>
<title>Tax Calulcation</title>
</head>
<body>
<?php
$taxcity = $HTTP_POST_VARS["taxcity"];
$taxrate = $HTTP_POST_VARS['taxrate'];
$taxcounty = $HTTP_POST_VARS['taxcounty'];
$outofstate = $HTTP_POST_VARS['outofstate'];
$taxarea = " ";
echo $taxcity;
echo $taxrate;
echo $taxcounty;

//Tax Area Rate Configurations
$mycity = "Sunnyvale";
$mycounty = "Santa Clara";
if ($taxcity == "$mycity") {
    $taxrate == "$taxrate";
	$taxarea == "$mycity";
} elseif ($taxcity != $mycity && $taxcounty == "$mycounty") {
    $taxrate == ".0825";
	$taxarea == "$mycounty";
} elseif ($taxcity != $mycity && $taxcounty !=="$mycounty") {
    $taxrate == ".0725";
	$taxarea == "State Base Rate";
} elseif ($outofstate == "checked") {
    $taxrate == ".00";
	$taxarea == "Out-Of-State"; 
}
echo "Tax Area: "."$taxarea"."<br><br>";
echo "Tax Rate: "."$taxrate";
?>
</body>
</html>
<?php
?>

barand

I think you should re-read my second post on this topic

phplearner

Originally posted by barand
phplearner

By the time you get to the "// Tax Area Rate Configurations "
section you've read all the data so the values will always be those from the last record read.

Do that after the post when you know what was selected, if any.

Thanks, barand. I tried doing it on a 2nd page but it is still getting the last record instead of the Selected one. I'm stumped!!!

barand

Try it this way

<html>
<head>
<title>Tax Options</title>
</head>
<body>

<form action="calc.php" method="post">

<?php
$hostname = "localhost";
$database = "taxdb";
$username = "me";
$password = "";
$dbcnx = mysql_connect($hostname, $username, $password) or die(mysql_error());

$taxcity = " ";
$taxrate = " ";
$taxcounty = " ";

mysql_select_db($database, $dbcnx);
$query = "SELECT taxcity FROM taxtables";
$list = mysql_query($query, $dbcnx) or die(mysql_error());
?>
MY STATE CUSTOMER:<br>
Please choose SHIP-TO DESTINATION City<br>
<SELECT NAME="city" size="1">
<OPTION SELECTED>--Select SHIP-TO City--</OPTION>
<?php
while ($row = mysql_fetch_assoc($list)) {
$taxcity=$row['taxcity'];
?>
<OPTION VALUE="<?php echo $taxcity?>"><?php echo $taxcity?></OPTION>
<?php
}
?>
</SELECT>
<br><br>
PLEASE CHECK THIS BOX IF YOU ARE AN OUT-OF-STATE CUSTOMER<br>
<input name="outofstate" type="checkbox"> OUT-OF-STATE CUSTOMER<br>
<INPUT name="submit" type="submit" value="Next Page">
&nbsp;&nbsp;&nbsp;
<INPUT name="reset" type="reset" value="Reset Form">
</form>

</body>
</html>

calc.php
[code=php]
?>
<html>
<head>
<title>Tax Calulcation</title>
</head>
<body>
<?php
$taxcity = $HTTP_POST_VARS["taxcity"];
$outofstate = $HTTP_POST_VARS['outofstate'];
$taxarea = " ";

if ($outofstate) {
    $taxrate = 0.00;
    $taxarea == "Out-Of-State";
} else {
    $sql = "SELECT taxrate, taxcounty FROM taxtables WHERE taxcity='$taxcity'";
    $res = mysql_query($sql);
    list($taxrate,$taxcounty)=mysql_fetch_row($res);

echo $taxcity;
echo $taxrate;
echo $taxcounty;

//Tax Area Rate Configurations
$mycity = "Sunnyvale";
$mycounty = "Santa Clara";
if ($taxcity == "$mycity") {
    $taxrate == "$taxrate";
    $taxarea == "$mycity";
} elseif ($taxcity != $mycity && $taxcounty == "$mycounty") {
    $taxrate == ".0825";
    $taxarea == "$mycounty";
} elseif ($taxcity != $mycity && $taxcounty !=="$mycounty") {
    $taxrate == ".0725";
    $taxarea == "State Base Rate";
}
}
echo "Tax Area: "."$taxarea"."<br><br>";
echo "Tax Rate: "."$taxrate";
?>
</body>
</html>

hth

phplearner

(barand):

Thanks much...tried it but getting:

UNDEFINED INDEX ERRORS IN:

$taxcity = $HTTP_POST_VARS["taxcity"]; 
$outofstate = $HTTP_POST_VARS['outofstate'];

(And) mysql_fetch_row(): supplied argument is not a valid MySQL result resource IN:

list($taxrate,$taxcounty)=mysql_fetch_row($res);

Not sure how to fix these.

barand

These 2 lines should be

$taxcity = $HTTP_POST_VARS["city"];

$sql = "SELECT taxrate, taxcounty FROM saletaxtables WHERE taxcity='$taxcity'";

phplearner

Hi barnand:

Tnx again for your help here, but still have a few loose screws.

If I choose out-of-state, what shows on calc.php is just '0' instead of .00 so not sure why.
If I choose an actual city from the drop down list, calc.php gives me the following errors (and there is NO sales tax amount displaying):

A. Notice: Undefined index: outofstate in:

$outofstate = $HTTP_POST_VARS['outofstate'];

B. Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in:

list($taxrate,$taxcounty)=mysql_fetch_row($res);

3. The city selected was: Augora ... what shows displays is:

AgouraTax Area: 

Tax Rate:

Dunno what to do. Maybe you will know?

Tnx.