Database Structure For orders

Question

I need some ideas on how to edit my structure inside my database I am building a fast food order website for 1 take away client at the moment the user adds there items into the order table

Here is it the table: http://prntscr.com/g3o3kz

The user_id column is for the user who is ordering id to be stored then item id is what item they want takeawayid is the id of the take away and the status is were they order food its auto set to waiting then the take away can update it to accepted or declined.

Now the problem I'm having is the user adds 5 items into the table above for take away id 1 there will be 5 rows for the user id and take away id and item id (there are 5 items)

Now I'm onto the recent orders page and I print out all for each take away so like so

http://prntscr.com/g3o4uw

But then tomorrow if they order it will add another 5 items and it will show in the same box how would I group each order? So order 1 has xyz items then next time the order order2 has xyz and so on. because at the moment the way the database is laying out it will just all be order1 if this makes sense

Displaying the orders box


    
        
             $item) {
                        $username = $item['username'];
                        $ordersPrice = $item['ordersPrice'];
                        $orders = $item['orders'];





                        ?>
                        
                            
                                
                                    
                                        
                                            
                                                
                                            
                                        
                                        
                                            
                                                  
                                                 
"; 
                                                 ?>



    prepare("SELECT * FROM users WHERE username = ?  ");
$statement8->execute(array($_SESSION['username']));
$count8 = $statement8->fetch();

$username233 = mysql_real_escape_string($count8['id']);
 $username2312312 = strip_tags($username233);

    $statement85 = $db->prepare("SELECT * FROM orders WHERE user_id = ? AND takeawayid = ? ");
$statement85->execute(array($username2312312,$_SESSION['userid']));
$count84 = $statement85->fetch();



if($count84 ['status']== "waiting") {


?>


              

                              
                              
                     

                              





                                        
                              
                              
                     

                              







";
} else {
                        echo "" ;
}





    ?>                  






                                            
                                        
                                    
                                    
                                        
                                            
                                                
                                                    
                                                        
                                                            
                                                        
                                                    
                                                    
                                                        
                                                            
                                                                
                                                            
                                                        
                                                        
                                                            5 STARS

                                                        

                                                    

                                                
                                            Address:   
                                            
                                            
                                    
                                
                            
                            
                                Total:

query

function fetchPaidOrdersByTakeaway($connection, $takeawayId, $paid) {
    if (!isset($takeawayId)) {
        throw new Exception('Takeaway ID not provided!');
    }

    if (!isset($paid)) {
        throw new Exception('Paid not provided!');
    }

    // Sql statement.
    $sql = 'SELECT 
                ord.id AS orderId,
                usr.id AS userId,
                usr.username,
                ord.itemname,
                ord.address,
                ord.price,
                ord.date
            FROM orders AS ord 
            LEFT JOIN users AS usr ON usr.id = ord.user_id 
            WHERE 
                user_id = :takeawayid 
                AND paid = :paid 
            ORDER BY 
                usr.username ASC,
                ord.date DESC';

    // Prepare and check sql statement (returns PDO statement).
    $statement = $connection->prepare($sql);
    if (!$statement) {
        throw new Exception('The SQL statement can not be prepared!');
    }

    // Bind values to sql statement parameters.
    $statement->bindValue(':takeawayid', $_SESSION['userid'], getInputParameterDataType($_SESSION['userid']));
    $statement->bindValue(':paid', $paid, getInputParameterDataType($paid));

    // Execute and check PDO statement.
    if (!$statement->execute()) {
        throw new Exception('The PDO statement can not be executed!');
    }

    // Fetch data.
    $fetchedData = $statement->fetchAll(PDO::FETCH_ASSOC);
    if ($fetchedData === FALSE) {
        throw new Exception('Fetching data failed!');
    }

    return $fetchedData;
}

/**
 * Group orders by users.
 * 
 * @param array $orders [optional] Orders list.
 * @return array Orders list grouped by users.
 * @throws Exception
 */
function groupOrdersByUsers(array $orders = array()) {
    $groupedOrders = array();

    foreach ($orders as $order) {
        $userId = $order['userId'];
        $username = $order['username'];
        $price = $order['price'];

        // Check and add user name as key, if not already.
        if (!array_key_exists($userId, $groupedOrders)) {
            $groupedOrders[$userId] = array(
                'username' => $username,
                'orders' => array(),
                'ordersPrice' => 0,
            );
        }

        // Add order to grouped orders list.
        $groupedOrders[$userId]['orders'][] = $order;
        $groupedOrders[$userId]['ordersPrice'] += $price;
    }

    return $groupedOrders;
}

would i need a extra column maybe orderid then have all the order the same id then display each by order id ?Problem is adding the same number to each item inside the order then next order adding a new number and not having 2 the same numbers

Edit at the moment i show there cart like so

    $statement = $db->prepare("SELECT * FROM orders WHERE user_id = ? AND status = ? AND takeawayid = ? ");
// $statement->execute(array($username2,$md5password,$mod));

$statement->bindParam(1, $_SESSION['userid'], PDO::PARAM_STR, 50); // check your lengths
$statement->bindParam(2, $testing, PDO::PARAM_STR, 60);
$statement->bindParam(3, $id1, PDO::PARAM_INT);
$statement->execute();

this of course works perfect because old orders will ether be accepted or declined so the waiting status ones will be the current ones. Im just finding it hard to print out old orders and group them together

iXCray · Accepted Answer

Learn how to achieve third normal form for your database.

That magic phrase simply means that if you have something may be isolated as one entity - create a separate table for it.

In your case there should be 1 table for orders and 1 table for items, tables will be joined with foreign keys, which in Mysql are made as said in that article: MySQL foreign key examples (How to define foreign keys in MySQL)

To untie your imagination with proper tool I would recommend you to use MySQL Workbench which will help you to visualize database structure while you will design it. It may be a "From a cannon by a sparrow" approach in your particular case but will definitely help to rapidly advance your skills in future.

And finally answering your question in detail

Create items table with id, name and price fields. It will contain list of items you are offering to your users

id will be Primary key with auto increment property
Create users table with id and name fields

id will be Primary key with auto increment property
Create orders table with id and user_id fields

id will be Primary key with auto increment property

user_id will have Foreign key linked with users table
Create order_items table with id, order_id, price and quantity fields

id will be Primary key with auto increment property

order_id will have Foreign key linked with orders table

item_id will have Foreign key linked with items table

I can't understand what address and date fields are for, so attach then to appropriate table listed above.

As a result you will have list of orders for every user, with a list of items ordered inside connected with price-list. You will be able to SELECT all the joined data from all tables at once OR a part of data to show it to user or to you.

The great thing here is that there is no problem with IDs anymore.

Database Structure For orders

Answers (1)

Related Questions