[SOLVED] Some sort of math issue??

ReeceSayer · May 17, 2009

okay so i need to find a total price for an order but i have the orders and prices in different tables... for example... in one table i have:

(Sandwich Table)

orderid sandwich price

1 QtyCheese 1.99

2 QtyHam 2.29

3 QtyBLT 2.09

4 QtyCoke 0.79

5 QtyDrPepper 0.79

6 QtyFanta 0.79

and in the other i have:

(Preorder Table)

orderid username QtyCheese QtyBLT QtyHam QtyCoke QtyFanta QtyDrPepper DelCentre DelAddress DelAddressL2 DelPostCode OrderExp orderdate

5 RSayer 2 3 1 4 5 6 Dewsbury uigubvr vhjkvnjkvjk jkbrfjkbe 6 2009-05-13 11:59:31

(sorry not sure i could format this properly)

Basically i need some code to take the values from the second table, Preorder, and times them by the prices i have set out...

Is there any way to do this?

I think i've set the tables out quite awkwardly... but any help would be great!!! Thanks

Reece

ReeceSayer · May 18, 2009

sorry im not really with it at the minute... I'll post the code..


<?php
include("connection.php");
require_once "header.php"; 
// Include the database connection file.
  $username = $_SESSION['username'];
  
   $sql = "SELECT orderID, username, QtyHam, QtyCheese, QtyBLT, QtyCoke, QtyFanta, QtyDrPepper, DelCentre, DelAddress, DelAddressL2, DelPostCode
   FROM sandwich WHERE username='$username'";
   
   $result = mysql_query($sql) or die('MySQL Error!<br />Query: '.htmlentities($sql).'<br />'.mysql_error());
?>  
<!-- end of php   start of xhtml -->
<!-- xhtml files require a DOCTYPE, then an html tag and then the head -->
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en" >
<!-- head defines Page Title, Character set and Stylesheet link -->
<head>
<title>Course Data</title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<link href="login.css" rel="stylesheet" type="text/css" />
</head>
<!-- After the head, comes the body of your web page -->
<body>
<div class="container">
<CENTER>
	<!-- A Heading for the page -->
	<h1>Order List</h1>
<!-- A table to display the data -->
<table class="neat" border="2">
	<tr>
		<th>orderID</th>
		<th>Qty Ham</th>
		<th>Qty Cheese</th>
		<th>Qty BLT</th>			
		<th>Qty Coca-Cola</th>
		<th>Qty Fanta</th>
		<th>Qty Dr. Pepper</th>
		<th>Price Total</th>
		<th>Delivery Centre</th>			
		<th>Delivery Address Line 1</th>
		<th>Delivery Address Line 2</th>
		<th>Delivery Post Code</th>
	</tr>
</CENTER>
<!-- enclose some PHP code to collect the data from the table in the database -->
	<?php
		// LOOP through all the records in the database table
		while ($row = mysql_fetch_array($result)) {
			echo "<tr>";
			echo "<td>" . $row['orderID'] . "</td>";
			echo "<td>" . $row['QtyHam'] . "</td>";
			echo "<td>" . $row['QtyCheese'] . "</td>";
			echo "<td>" . $row['QtyBLT'] . "</td>";
			echo "<td>" . $row['QtyCoke'] . "</td>";
			echo "<td>" . $row['QtyFanta'] . "</td>";
			echo "<td>" . $row['QtyDrPepper'] . "</td>";
							echo "<td>" . $row['Price'] . "</td>"; //Need to insert a total value here...
			echo "<td>" . $row['DelCentre'] . "</td>";
			echo "<td>" . $row['DelAddress'] . "</td>";
			echo "<td>" . $row['DelAddressL2'] . "</td>";
			echo "<td>" . $row['DelPostCode'] . "</td>";
			echo "</tr>";
		}

		// Free up the memory used for $result
		mysql_free_result($result);
		// Close the database connection
		mysql_close();
	?>
<!-- Close the XHTML table -->
</table>
<p> </p>
<p><a href="home.php">Home</a></p>
<p><a href="logout.php">Logout</a></p>
</div> 
</body>
</html>
<?php
require_once "footer.php"; 
?>

Thanks

Daniel0 · May 18, 2009

Have a read over this:

http://en.wikipedia.org/wiki/Database_normalization

It won't solve your issue, but your database isn't normalized.

ReeceSayer · May 18, 2009

i know... i've done a poor job but i need a quick fix as i have to present this tomorrow... its not neccessary but i'd rather have it done...

i had a rethink and tried to edit the code and got this:

<?php
include("connection.php");
require_once "header.php"; 
// Include the database connection file.
  $username = $_SESSION['username'];
  
   $sql = "SELECT orderID, username, QtyHam, QtyCheese, QtyBLT, QtyCoke, QtyFanta, QtyDrPepper, DelCentre, DelAddress, DelAddressL2, DelPostCode
   FROM sandwich WHERE username='$username'";

//set quantities for each sandwich
   $QtyHam = "SELECT QtyHam FROM sandwich WHERE username='$username'";
      $QtyCheese = "SELECT QtyCheeseFROM sandwich WHERE username='$username'";
     $QtyBLT = "SELECT QtyBLT FROM sandwich WHERE username='$username'";
	    $QtyCoke = "SELECT QtyCoke FROM sandwich WHERE username='$username'";
		   $QtyFanta = "SELECT QtyFanta FROM sandwich WHERE username='$username'";
      $QtyDrPepper = "SELECT QtyDrPepper FROM sandwich WHERE username='$username'";
  
  
//set order values for each sandwich
   $Ham = "SELECT price FROM preorder WHERE sandwich = 'QtyHam'";
   $Cheese = "SELECT price FROM preorder WHERE sandwich = 'QtyCheese'"; 
   $BLT = "SELECT price FROM preorder WHERE sandwich = 'QtyBLT'";  
   $Coke = "SELECT price FROM preorder WHERE sandwich = 'QtyCoke'";   
   $Fanta = "SELECT price FROM preorder WHERE sandwich = 'QtyFanta'";
   $DrPepper = "SELECT price FROM preorder WHERE sandwich = 'QtyDrPepper'";
   
   $total = $Ham * $QtyHam;
   
   $result = mysql_query($sql) or die('MySQL Error!<br />Query: '.htmlentities($sql).'<br />'.mysql_error());
//   $result2 = mysql_query($total) or die('MySQL Error!<br />Query: '.htmlentities($total).'<br />'.mysql_error());
?>  
<!-- end of php   start of xhtml -->
<!-- xhtml files require a DOCTYPE, then an html tag and then the head -->
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en" >
<!-- head defines Page Title, Character set and Stylesheet link -->
<head>
<title>Course Data</title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<link href="login.css" rel="stylesheet" type="text/css" />
</head>
<!-- After the head, comes the body of your web page -->
<body>
<div class="container">
<CENTER>
	<!-- A Heading for the page -->
	<h1>Order List</h1>
<!-- A table to display the data -->
<table class="neat" border="2">
	<tr>
		<th>orderID</th>
		<th>Qty Ham</th>
		<th>Qty Cheese</th>
		<th>Qty BLT</th>			
		<th>Qty Coca-Cola</th>
		<th>Qty Fanta</th>
		<th>Qty Dr. Pepper</th>
		<th>Delivery Centre</th>			
		<th>Delivery Address Line 1</th>
		<th>Delivery Address Line 2</th>
		<th>Delivery Post Code</th>
		<th>Price Total</th>
	</tr>
</CENTER>
<!-- enclose some PHP code to collect the data from the table in the database -->
	<?php
		// LOOP through all the records in the database table
		while ($row = mysql_fetch_array($result)) {
			echo "<tr>";
			echo "<td>" . $row['orderID'] . "</td>";
			echo "<td>" . $row['QtyHam'] . "</td>";
			echo "<td>" . $row['QtyCheese'] . "</td>";
			echo "<td>" . $row['QtyBLT'] . "</td>";
			echo "<td>" . $row['QtyCoke'] . "</td>";
			echo "<td>" . $row['QtyFanta'] . "</td>";
			echo "<td>" . $row['QtyDrPepper'] . "</td>";
			echo "<td>" . $row['DelCentre'] . "</td>";
			echo "<td>" . $row['DelAddress'] . "</td>";
			echo "<td>" . $row['DelAddressL2'] . "</td>";
			echo "<td>" . $row['DelPostCode'] . "</td>";
		}
			echo "<td>" . $total['Price Total'] . "</td>";
			echo "</tr>";

/*					while ($row = mysql_fetch_array($result2))
		{
			echo "<td>" . $row['Price Total'] . "</td>";
			echo "</tr>";
		}
	*/

		// Free up the memory used for $result
		mysql_free_result($result);
		// Close the database connection
		mysql_close();
	?>
<!-- Close the XHTML table -->
</table>
<p> </p>
<p><a href="home.php">Home</a></p>
<p><a href="logout.php">Logout</a></p>
</div> 
</body>
</html>
<?php
require_once "footer.php"; 
?>

Thankyou

ReeceSayer · May 18, 2009

All sorted thanks!!!

final code is:

<?php
include("connection.php");
require_once "header.php"; 
// Include the database connection file.
  $username = $_SESSION['username'];
  
   $sql = "SELECT orderID, username, QtyHam, QtyCheese, QtyBLT, QtyCoke, QtyFanta, QtyDrPepper, DelCentre, DelAddress, DelAddressL2, DelPostCode
   FROM sandwich WHERE username='$username'";

//set quantities for each sandwich
   $QtyHam = "SELECT QtyHam FROM sandwich WHERE username='$username'";
      $QtyCheese = "SELECT QtyCheese FROM sandwich WHERE username='$username'";
     $QtyBLT = "SELECT QtyBLT FROM sandwich WHERE username='$username'";
	    $QtyCoke = "SELECT QtyCoke FROM sandwich WHERE username='$username'";
		   $QtyFanta = "SELECT QtyFanta FROM sandwich WHERE username='$username'";
      $QtyDrPepper = "SELECT QtyDrPepper FROM sandwich WHERE username='$username'";
  
  
//set order values for each sandwich
   $Ham = "SELECT price FROM sandwiches WHERE sandwich = 'QtyHam'";
   $Cheese = "SELECT price FROM sandwiches WHERE sandwich = 'QtyCheese'"; 
   $BLT = "SELECT price FROM sandwiches WHERE sandwich = 'QtyBLT'";  
   $Coke = "SELECT price FROM sandwiches WHERE sandwich = 'QtyCoke'";   
   $Fanta = "SELECT price FROM sandwiches WHERE sandwich = 'QtyFanta'";
   $DrPepper = "SELECT price FROM sandwiches WHERE sandwich = 'QtyDrPepper'";

//set price values
   $result = mysql_query($sql) or die('MySQL Error!<br />Query: '.htmlentities($sql).'<br />'.mysql_error());
   $price = mysql_query($Ham) or die('MySQL Error!<br />Query: '.htmlentities($Ham).'<br />'.mysql_error());
   $price2 = mysql_query($Cheese) or die('MySQL Error!<br />Query: '.htmlentities($Cheese).'<br />'.mysql_error());
   $price3 = mysql_query($BLT) or die('MySQL Error!<br />Query: '.htmlentities($BLT).'<br />'.mysql_error());
   $price4 = mysql_query($Coke) or die('MySQL Error!<br />Query: '.htmlentities($Coke).'<br />'.mysql_error());
   $price5 = mysql_query($Fanta) or die('MySQL Error!<br />Query: '.htmlentities($Fanta).'<br />'.mysql_error());
   $price6 = mysql_query($DrPepper) or die('MySQL Error!<br />Query: '.htmlentities($DrPepper).'<br />'.mysql_error());
   
// quantity values

   $quantity = mysql_query($QtyHam) or die('MySQL Error!<br />Query: '.htmlentities($QtyHam).'<br />'.mysql_error());
   $quantity2 = mysql_query($QtyCheese) or die('MySQL Error!<br />Query: '.htmlentities($QtyCheese).'<br />'.mysql_error());
   $quantity3 = mysql_query($QtyBLT) or die('MySQL Error!<br />Query: '.htmlentities($QtyBLT).'<br />'.mysql_error());
   $quantity4 = mysql_query($QtyCoke) or die('MySQL Error!<br />Query: '.htmlentities($QtyCoke).'<br />'.mysql_error());
   $quantity5 = mysql_query($QtyFanta) or die('MySQL Error!<br />Query: '.htmlentities($QtyFanta).'<br />'.mysql_error());
   $quantity6 = mysql_query($QtyDrPepper) or die('MySQL Error!<br />Query: '.htmlentities($QtyDrPepper).'<br />'.mysql_error());

//fetch price result
$Hamprice = mysql_fetch_row($price);
$Cheeseprice = mysql_fetch_row($price2);
	$BLTprice = mysql_fetch_row($price3);
		$Cokeprice = mysql_fetch_row($price4);
			$Fantaprice = mysql_fetch_row($price5);
				$DrPepperprice = mysql_fetch_row($price6);

//fetch quantity result
$QtyHamprice = mysql_fetch_row($quantity);
$QtyCheeseprice = mysql_fetch_row($quantity2);
	$QtyBLTprice = mysql_fetch_row($quantity3);
		$QtyCokeprice = mysql_fetch_row($quantity4);
			$QtyFantaprice = mysql_fetch_row($quantity5);
				$QtyDrPepperprice = mysql_fetch_row($quantity6);

$total = $QtyHamprice[0] * $Hamprice[0] +
		$QtyCheeseprice[0] * $Cheeseprice[0] +
		$QtyBLTprice[0] * $BLTprice[0] +
		$QtyCokeprice[0] * $Cokeprice[0] +
		$QtyFantaprice[0] * $Fantaprice[0] +
		$QtyDrPepperprice[0] * $DrPepperprice[0]
;

?>  
<!-- end of php   start of xhtml -->
<!-- xhtml files require a DOCTYPE, then an html tag and then the head -->
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en" >
<!-- head defines Page Title, Character set and Stylesheet link -->
<head>
<title>Course Data</title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<link href="login.css" rel="stylesheet" type="text/css" />
</head>
<!-- After the head, comes the body of your web page -->
<body>
<div class="container">
<CENTER>
	<!-- A Heading for the page -->
	<h1>Order List</h1>
<!-- A table to display the data -->
<table class="neat" border="2">
	<tr>
		<th>orderID</th>
		<th>Qty Ham</th>
		<th>Qty Cheese</th>
		<th>Qty BLT</th>			
		<th>Qty Coca-Cola</th>
		<th>Qty Fanta</th>
		<th>Qty Dr. Pepper</th>
		<th>Delivery Centre</th>			
		<th>Delivery Address Line 1</th>
		<th>Delivery Address Line 2</th>
		<th>Delivery Post Code</th>
	</tr>
</CENTER>
<!-- enclose some PHP code to collect the data from the table in the database -->
	<?php

		// LOOP through all the records in the database table
		while ($row = mysql_fetch_array($result)) {
			echo "<tr>";
			echo "<td>" . $row['orderID'] . "</td>";
			echo "<td>" . $row['QtyHam'] . "</td>";
			echo "<td>" . $row['QtyCheese'] . "</td>";
			echo "<td>" . $row['QtyBLT'] . "</td>";
			echo "<td>" . $row['QtyCoke'] . "</td>";
			echo "<td>" . $row['QtyFanta'] . "</td>";
			echo "<td>" . $row['QtyDrPepper'] . "</td>";
			echo "<td>" . $row['DelCentre'] . "</td>";
			echo "<td>" . $row['DelAddress'] . "</td>";
			echo "<td>" . $row['DelAddressL2'] . "</td>";
			echo "<td>" . $row['DelPostCode'] . "</td>";
			echo "</tr><br><br>";
		}

		// Free up the memory used for $result
		mysql_free_result($result);
		// Close the database connection
		mysql_close();
	?>
<!-- Close the XHTML table -->
</table>
<p>
<?php
echo "Your total is: &#8356;<b>" . $total . "</b>";
?>
</p>
<p><a href="home.php">Home</a></p>
<p><a href="logout.php">Logout</a></p>
</div> 
</body>
</html>
<?php
require_once "footer.php"; 
?>

This appears to be working for me... thanks alot

Sign In

[SOLVED] Some sort of math issue??

Recommended Posts

ReeceSayer

Link to comment

Share on other sites

ReeceSayer

Link to comment

Share on other sites

Daniel0

Link to comment

Share on other sites

ReeceSayer

Link to comment

Share on other sites

ReeceSayer

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information