Welcome Guest, Not a member yet? Register   Sign In
Same "ID" for Multiple Records (A bit different to what your thinking)

Hi All,

As part of a personal project, I'm creating a little order system for my favourite food - pizza.

It's going fairly well, I'm mostly done but just finishing off some models.

I'm after some advice/solution on the best way to do this.

I've included the function I'm working on below. What's happening is that there's multiple checkboxes on the view to select the products, depending on which of these are selected when submitted the ID of that product (pulled from the DB and then parsed as the checkbox value) is put into an array with the others and passed off to the model below.

Then, once it gets there, It's then checked if there is/isn't data. We'll presume there is in this case (validation prevents it being submitted if nothing's selected, but this is just another layer) - Because there's multiple values but they need to go into a different record each (due to DB structure explained below) but what I'm trying to do is have them withhold the same Order ID number so they can be linked to an order with the database relationship/other model functions.

When using select_max()/insert_id(), due to the nature of the setup, when adding an order id, It increments the value as per the foreach loop or inserts a value of 0/1/2 in the order_id field of each record and doesn't increment.

insert_id() won't work, as order_id can't be set as the PK because of the duplicate data it's holding.

Based on the current setup (seen below), or a modification, what's the best way to

Model Function
function createNewOrder($customer_id,$order_type_id,$selected_pizza_products,$selected_sides_products,$order_instructions) {
        $database_products = "TEST_neworders";
        $database_orders = "TEST_neworders";
        // add pizzas
        if($selected_pizza_products) {
            foreach ($selected_pizza_products as $product_pizza) {
              $this->db->set('product_ids', $product_pizza);
              $this->db->set('order_type_id', $order_type_id);
              $this->db->set('customer_id', $customer_id);
              //$this->db->set('order_id', $order_id_set);
        } // end insert products - pizza
        // add sides
        if($selected_sides_products) {
            foreach ($selected_sides_products as $product_side) {
              $this->db->set('product_ids', $product_side);
              $this->db->set('order_type_id', $order_type_id);
              $this->db->set('customer_id', $customer_id);
              //$this->db->set('order_id', $order_id_set);
        } // end insert products - sides

Controller Function
function process() {
        // get form field posted data and save as a variable      
        $customer_id = $this->input->post('customer');
        $order_type_id = $this->input->post('order_type');    
        $selected_pizza_products = $this->input->post('product-pizza');
        $selected_sides_products = $this->input->post('product-sides');
        $order_instructions = $this->input->post('special-instructions');

DB Structure
Not the final table, but very representative of what's seen in the proper table.
- customer_id : integer : fk to customers.customers_id
- type_id : integer : fk to order_types:type_id
- products_id : varchar fk to products.product_id
- order_id : int or varchar : fk to orders.order_id

(The orders table holds other info such as date mate, a randomly generated reference number for the customer and any order notes - this table holds all the main information and id's to join with the other tables and so on..)

The reason I'm storing my data like this is that #1 it keeps my database clean, #2 it's a better solution for storing all the different product data for the single order and #3 it

I'd really appreciate any help!

If there's any other snips of code, or questions you've got feel free to ask.


Theme © iAndrew 2016 - Forum software by © MyBB