Resolved My SQL Database PHP Inquiry

logepoge1 · 12-06-2012, 01:33 AM

This is the statement I have tested in PhpMyAdmin and it works:

Code:

SELECT customers.CompanyName, orders.CustomerID, orders.OrderID, order_details.OrderID, order_details.ProductID, products.ProductID, products.ProductName, products.UnitPrice, products.UnitsInStock, orders.shipVia, shippers.shipperId, shippers.CompanyName, orders.Freight
FROM customers, orders, order_details, products, shippers
WHERE customers.CompanyName = "Around"
AND customers.CustomerID = orders.CustomerID
AND orders.OrderID = '10308'
AND orders.OrderID = order_details.OrderID
AND order_details.ProductID = products.productID
AND orders.ShipVia = shippers.ShipperID
ORDER BY products.productName, products.UnitsInStock, products.UnitPrice, shippers.CompanyName, orders.Freight

What I want to do it have my php file pull information from the results into a table. I already have my config files and everything set up. I just need help with getting this stuff into a table. I have never really been good at doing the tables, the forms are easy.

Here is my php code from one of my files that leads to this file. If you can please help me rewrite it with a table:

Code:

<?php
	require('includes/header.php');
	
?>



<form name='customer' method='get' action='order_details.php' >
<p>Below are the order numbers for this customer: </p><select name='customer'>
<?php
	$sql = "SELECT customers.CompanyName, customers.CustomerID, orders.OrderID
		FROM customers, orders
		WHERE customers.CompanyName LIKE '" . $_GET['customer'] . "%'
		AND customers.CustomerID LIKE orders.CustomerID;";
	
	$result = mysql_query($sql) or die(mysql_error());
	
		while ($row = mysql_fetch_assoc($result))
		{
			echo '<option value =' .  $row['OrderID'] . '>' . $row['OrderID'] . '</option>';
		}
		
?>

</select>
&nbsp;<input type='submit' name='submit' />

</form>

<?php
require('includes/footer.php'); 

?>

The table needs to show product name, quantity, unit price, shipper, shipping cost(freight), and total cost of order.

Old Pedant · 12-06-2012, 02:05 AM

Well, just for starters, why are you using

Code:

	$sql = "SELECT customers.CompanyName, customers.CustomerID, orders.OrderID
		FROM customers, orders
		WHERE customers.CompanyName LIKE '" . $_GET['customer'] . "%'
		AND customers.CustomerID LIKE orders.CustomerID;";

instead of the query you say works??

logepoge1 · 12-06-2012, 02:10 AM

Quote:

Originally Posted by Old Pedant

Well, just for starters, why are you using

Code:

	$sql = "SELECT customers.CompanyName, customers.CustomerID, orders.OrderID
		FROM customers, orders
		WHERE customers.CompanyName LIKE '" . $_GET['customer'] . "%'
		AND customers.CustomerID LIKE orders.CustomerID;";

instead of the query you say works??

Sorry about that. That is just from a different page. Here:

Code:

$sql = "SELECT customers.CompanyName, orders.CustomerID, orders.OrderID, order_details.OrderID, order_details.ProductID, products.ProductID, products.ProductName, products.UnitPrice, products.UnitsInStock, orders.shipVia, shippers.shipperId, shippers.CompanyName, orders.Freight
	FROM customers, orders, order_details, products, shippers
	WHERE customers.CustomerID = orders.CustomerID
	AND orders.OrderID = '" . $_GET['order'] . "%'
	AND orders.OrderID = order_details.OrderID
	AND order_details.ProductID = products.productID
	AND orders.ShipVia = shippers.ShipperID
	ORDER BY products.productName, products.UnitsInStock, products.UnitPrice, shippers.CompanyName, orders.Freight";

Old Pedant · 12-06-2012, 02:13 AM

Oh, never mind. I see why you posted what you did. Sorry. DOH on me.

Okay...back a bit later.

logepoge1 · 12-06-2012, 02:24 AM

Quote:

Originally Posted by Old Pedant

Oh, never mind. I see why you posted what you did. Sorry. DOH on me.

Okay...back a bit later.

Okay thanks. I will just work on some other pages.

Old Pedant · 12-06-2012, 02:27 AM

Do you want to show *ALL* orders in the table?

Or just one specific order?

Or just one specific customer?

logepoge1 · 12-06-2012, 02:35 AM

It is by order number. First I run a page to select customer. Then order number. What you are seeing is the final page where I want it to show the table.

Old Pedant · 12-06-2012, 02:48 AM

I see that now. Sorry...missed the $_GET[] in there.

But you forgot to include the QUANTITY for each product! I am going to assume that it is in order_details.quantity

Old Pedant · 12-06-2012, 02:50 AM

The % here is a mistake:

Code:

	AND orders.OrderID = '" . $_GET['order'] . "%'

Also the '...' around $_GET['order'] is a mistake if ORDERID is always a number.

Plus you need to "sanitize" the $_GET value, to prevent SQL injection.

logepoge1 · 12-06-2012, 02:55 AM

Quote:

Originally Posted by Old Pedant

The % here is a mistake:

Code:

	AND orders.OrderID = '" . $_GET['order'] . "%'

Also the '...' around $_GET['order'] is a mistake if ORDERID is always a number.

Plus you need to "sanitize" the $_GET value, to prevent SQL injection.

I figured it out before this. But thanks anyways.

Old Pedant · 12-06-2012, 03:08 AM

You mean you had me writing PHP code for 30 minutes when I don't even *USE* PHP???

AAARRRGGGGHHHH!!

Well...FWIW, and remembering I don't use PHP, here's what I came up with:

Code:

<html>
<body>
<?php

... make your DB connection here ...

$orderid = (int)$_GET["order"]; // ensure that orderid *IS* an integer!  no SQL injection

$sql = "SELECT customers.CompanyName, 
               orders.CustomerID, orders.OrderID, orders.Freight
               order_details.Quantity,
               products.ProductID, products.ProductName, products.UnitPrice,  
               shippers.CompanyName AS shipCompany
	FROM customers, orders, order_details, products, shippers
	WHERE customers.CustomerID = orders.CustomerID
	AND orders.OrderID = " . $orderid . "
	AND orders.OrderID = order_details.OrderID
	AND order_details.ProductID = products.productID
	AND orders.ShipVia = shippers.ShipperID
	ORDER BY products.productName";

$result = mysql_query($sql) or die(mysql_error());

// *MUST* have at least one row:
$row = mysql_fetch_assoc($result) or die(mysql_error());

// get things started:
$shipcost = $row["Freight"];
$shipvia = $row["shipCompany"];
$total = $shipcost;

// first output order header:
?>

<table border="1">
<tr>
    <th>Company</th>
    <th>Order#</th>
    <th colspan="5">&nbsp;</th>
</tr>
<tr>
    <td><? echo $row["CompanyName"]; ?></td>
    <td><? echo $orderid; ?></td>
    <td colspan="7">&nbsp;</td>
</tr>
<tr>
    <th colspan="2">&nbsp;</th>
    <th>Product</th>
    <th>Prod #</th>
    <th>Quantity</th>
    <th>Price</th>
    <th>Cost</th>
</tr>

<?php
// now output one row per record for the order details:
while ( true )
{
    $price = $row["UnitPrice"};
    $qty = $row["quantity"]
    $cost = $price * $qty;
    $total += $cost;
?>
<tr>
    <td colspan="2">&nbsp;</td>
    <td><? echo $row["ProductName"]; ?></td>
    <td><? echo $row["ProductID"]; ?></td>
    <td><? echo $qty; ?></td>
    <td><? echo $price; ?></td>
    <td><? echo $cost; ?></td>
</tr>
<?php
    // try to get next row
    $row = mysql_fetch_assoc($result);
    if ( $row == FALSE ) { break; /* out of while loop */ }
} /* end of while loop

// ouput totals, etc.
?>
<tr>
    <th colspan="5">&nbsp;</th>
    <th>Ship Via</th>
    <th>Ship Cost</th>
</tr>
<tr>
    <td colspan="5">&nbsp;</td>
    <td><? echo $shipvia; ?></td>
    <td><? echo $shipcost; ?></td>
</tr>
<tr>
    <td colspan="7"><br/></td>
</tr>
<tr>
    <td colspan="6" style="text-align: right; font-weight: bold;">TOTAL</td>
    <td><?echo $total; ?></td>
</tr>
</table>
</body>
</html>

How close is it to what you had?

logepoge1 · 12-06-2012, 04:00 AM

Quote:

Originally Posted by Old Pedant

You mean you had me writing PHP code for 30 minutes when I don't even *USE* PHP???

AAARRRGGGGHHHH!!

Well...FWIW, and remembering I don't use PHP, here's what I came up with:

Code:

<html>
<body>
<?php

... make your DB connection here ...

$orderid = (int)$_GET["order"]; // ensure that orderid *IS* an integer!  no SQL injection

$sql = "SELECT customers.CompanyName, 
               orders.CustomerID, orders.OrderID, orders.Freight
               order_details.Quantity,
               products.ProductID, products.ProductName, products.UnitPrice,  
               shippers.CompanyName AS shipCompany
	FROM customers, orders, order_details, products, shippers
	WHERE customers.CustomerID = orders.CustomerID
	AND orders.OrderID = " . $orderid . "
	AND orders.OrderID = order_details.OrderID
	AND order_details.ProductID = products.productID
	AND orders.ShipVia = shippers.ShipperID
	ORDER BY products.productName";

$result = mysql_query($sql) or die(mysql_error());

// *MUST* have at least one row:
$row = mysql_fetch_assoc($result) or die(mysql_error());

// get things started:
$shipcost = $row["Freight"];
$shipvia = $row["shipCompany"];
$total = $shipcost;

// first output order header:
?>

<table border="1">
<tr>
    <th>Company</th>
    <th>Order#</th>
    <th colspan="5">&nbsp;</th>
</tr>
<tr>
    <td><? echo $row["CompanyName"]; ?></td>
    <td><? echo $orderid; ?></td>
    <td colspan="7">&nbsp;</td>
</tr>
<tr>
    <th colspan="2">&nbsp;</th>
    <th>Product</th>
    <th>Prod #</th>
    <th>Quantity</th>
    <th>Price</th>
    <th>Cost</th>
</tr>

<?php
// now output one row per record for the order details:
while ( true )
{
    $price = $row["UnitPrice"};
    $qty = $row["quantity"]
    $cost = $price * $qty;
    $total += $cost;
?>
<tr>
    <td colspan="2">&nbsp;</td>
    <td><? echo $row["ProductName"]; ?></td>
    <td><? echo $row["ProductID"]; ?></td>
    <td><? echo $qty; ?></td>
    <td><? echo $price; ?></td>
    <td><? echo $cost; ?></td>
</tr>
<?php
    // try to get next row
    $row = mysql_fetch_assoc($result);
    if ( $row == FALSE ) { break; /* out of while loop */ }
} /* end of while loop

// ouput totals, etc.
?>
<tr>
    <th colspan="5">&nbsp;</th>
    <th>Ship Via</th>
    <th>Ship Cost</th>
</tr>
<tr>
    <td colspan="5">&nbsp;</td>
    <td><? echo $shipvia; ?></td>
    <td><? echo $shipcost; ?></td>
</tr>
<tr>
    <td colspan="7"><br/></td>
</tr>
<tr>
    <td colspan="6" style="text-align: right; font-weight: bold;">TOTAL</td>
    <td><?echo $total; ?></td>
</tr>
</table>
</body>
</html>

How close is it to what you had?

Im Sorry. Here is what I got:

Code:

<?php
	require('includes/header.php');
	
?>



<?php
	$sql = "SELECT customers.CompanyName, orders.CustomerID, orders.OrderID, order_details.OrderID, order_details.ProductID, products.ProductID, products.ProductName, order_details.UnitPrice, order_details.Quantity, orders.shipVia, shippers.shipperId, shippers.CompanyName, orders.Freight
	FROM customers, orders, order_details, products, shippers
	WHERE customers.CustomerID = orders.CustomerID
	AND orders.OrderID = '" . $_GET['order'] . "%'
	AND orders.OrderID = order_details.OrderID
	AND order_details.ProductID = products.productID
	AND orders.ShipVia = shippers.ShipperID
	ORDER BY products.productName, products.UnitsInStock, products.UnitPrice, shippers.CompanyName, orders.Freight";
	
	$result = mysql_query($sql) or die(mysql_error());
	
echo "<table border='1'>
<tr>
<th>Product Name</th>
<th>Quantity</th>
<th>Unit Price</th>
<th>Shipper</th>
<th>Shipping Cost</th>
<th>Total Cost</th>
</tr>";
while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['ProductName'] . "</td>";
  echo "<td>" . $row['Quantity'] . "</td>";
  echo "<td>" . $row['UnitPrice'] . "</td>";
  echo "<td>" . $row['CompanyName'] . "</td>";
  echo "<td>" . $row['Freight'] . "</td>";
  }
echo "</table>";
		
?>



<?php
require('includes/footer.php'); 

?>

How do I multiple the Column with UnitPrice by the Column with Quantity and then add the column with Freight to that total and display the total?

Sorry about that though. it just came to me where I remembered that.

Old Pedant · 12-06-2012, 06:00 PM

Quote:

How do I multiple the Column with UnitPrice by the Column with Quantity and then add the column with Freight to that total and display the total?

By using the code I wrote for you. It does all of that. Or it does if I wrote the PHP correctly. Again, I don't use PHP.