Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 13 of 13
  1. #1
    New Coder
    Join Date
    Oct 2012
    Posts
    44
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Exclamation My SQL Database PHP Inquiry

    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.
    Last edited by logepoge1; 12-06-2012 at 09:12 PM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,508
    Thanks
    77
    Thanked 4,378 Times in 4,343 Posts
    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??
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    Oct 2012
    Posts
    44
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    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";

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,508
    Thanks
    77
    Thanked 4,378 Times in 4,343 Posts
    Oh, never mind. I see why you posted what you did. Sorry. DOH on me.

    Okay...back a bit later.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New Coder
    Join Date
    Oct 2012
    Posts
    44
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    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.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,508
    Thanks
    77
    Thanked 4,378 Times in 4,343 Posts
    Do you want to show *ALL* orders in the table?

    Or just one specific order?

    Or just one specific customer?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    New Coder
    Join Date
    Oct 2012
    Posts
    44
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,508
    Thanks
    77
    Thanked 4,378 Times in 4,343 Posts
    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
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,508
    Thanks
    77
    Thanked 4,378 Times in 4,343 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #10
    New Coder
    Join Date
    Oct 2012
    Posts
    44
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    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.

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,508
    Thanks
    77
    Thanked 4,378 Times in 4,343 Posts
    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?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #12
    New Coder
    Join Date
    Oct 2012
    Posts
    44
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    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.

  • #13
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,508
    Thanks
    77
    Thanked 4,378 Times in 4,343 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •