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 14 of 14
  1. #1
    New Coder
    Join Date
    Aug 2012
    Location
    France
    Posts
    60
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Enter multiple rows from array

    I am building a table called orderItems


    CREATE TABLE `OrderItems` (
    *`orderNumber` int(5) NOT NULL,
    *`prodId` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
    *`itemNotes` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
    *`itemQuantity` decimal(4,2) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

    I have managed to enter multiple rows via php with a hard coded version:

    Code:
    $insert3 = mysql_query("INSERT INTO `christmasOrders2`.`orderItems` (
    			`orderNumber` , 
    			`prodId` ,		//p(x)
    			`itemNotes` ,		//n(x)
    			`itemQuantity`	//q(x)
    			)
    			VALUES
    			( LAST_INSERT_ID(), '1003', 'a small hat', '4'),
    			( LAST_INSERT_ID(), '1004', 'a medium hat', '4'),
    			( LAST_INSERT_ID(), '1005', 'a large hat', '4')
    			");
    		if (!$insert3)
    		{
    		die("fail 3" . mysql_error());
    		}
    		else {
    			echo "stage 3 good";
    But I need to put in values from an array :

    Array
    (
    [p1] => 1000
    [n1] => random words 1
    [q1] => 1
    [p2] => 1001
    [n2] => random words 2
    [q2] => 2
    [p3] => 1002
    [n3] => random words 3
    [q3] => 3
    )

    I don't seem to be able to figure this out. Which is the best way to do this?

    cheers
    A talent for speaking differently, rather than for arguing well, is the chief instrument of cultural change. Richard Rorty

  • #2
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,542
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Because that's a TERRIBLE array structure. Is that something you've created yourself, or are you getting it from somewhere?

    It can be worked with, but its relatively meaningless, and VERY prone to error.

    First, you have a MySQL error:

    Code:
    ( LAST_INSERT_ID(), '1003', 'a small hat', '4'),
    LAST_INSERT_ID() doesn't mean anything there. You haven't inserted yet, so how could it give anything back?

    Your table structure is lacking as well. Did you look at the normalization links provided in the other threat? They'll be super useful; you need to learn more about keys.

    First, you should have a primary key on this table, and it should be orderNumber. Next, orderNumber should also be set to auto_increment. This way, it is always unique, and will automatically fill in with a new number when you insert. Thus avoiding the issue you have above.

    Next, a better array structure would be this:

    PHP Code:
    $orders[1]['p'] = 1000;
    $orders[1]['n'] = 'random words 1';
    $orders[1]['q'] = 1;
    $orders[2]['p'] = 1001;
    $orders[2]['n'] = 'random words 2';
    $orders[2]['q'] = 2;
    $orders[3]['p'] = 1002;
    $orders[3]['n'] = 'random words 3';
    $orders[3]['q'] = 3
    So when you access $orders[1], you have only the info from orders 1. You can then loop through orders, getting back arrays that contain only the data you want.

    So with due respect, it seems like you're getting a bit ahead of yourself in application vs knowledge. This may be a good time to take a small step back, if you can afford to, and brush up on basics like multidimensional arrays, and table keys. We don't mind helping you out here, but it will probably end up saving you a lot of headaches.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,720
    Thanks
    80
    Thanked 4,514 Times in 4,478 Posts
    Quote Originally Posted by Keleth View Post
    First, you have a MySQL error:

    Code:
    ( LAST_INSERT_ID(), '1003', 'a small hat', '4'),
    LAST_INSERT_ID() doesn't mean anything there. You haven't inserted yet, so how could it give anything back?
    Not true, Keleth. He has obviously just INSERTed into the ORDERS table, and he is using LAST_INSERT_ID() to get the order number of the just inserted ORDERS record.

    First, you should have a primary key on this table, and it should be orderNumber.
    Incorrect, completely! orderNumber should be the primary key in the ORDERS table, *NOT* the orderItems table. It should be a FOREIGN KEY in this orderItems table. This table DOES NOT NEED any auto_increment field and I would *NOT* add one.

    I would use this for the table structure:
    Code:
    CREATE TABLE Customers (
        custNumber INT AUTO_INCREMENT PRIMARY KEY,
        firstName VARCHAR(50),
        lastName VARCHAR(50),
        ... etc. ...
    ) ENGINE INNODB;
    
    CREATE TABLE Orders (
        orderNumber INT AUTO_INCREMENT PRIMARY KEY,
        custNumber INT,
        orderDate DATETIME,
        ... etc. ...
        CONSTRAINT FOREIGN KEY (custNumber) REFERENCES Customers(custNumber)
    ) ENGINE INNODB;
    
    CREATE TABLE OrderItems 
    (
        orderNumber int NOT NULL,
        prodId varchar(10) NOT NULL,
        itemNotes varchar(256) NOT NULL,
        itemQuantity INT NOT NULL,
        CONSTRAINT FOREIGN KEY (orderNumber) REFERENCES Orders(orderNumber),
        PRIMARY KEY (orderNumber, prodId)
    ) ENGINE=InnoDB;
    Notice that by using a composite PRIMARY KEY, we are able to uniquely identify any orderItem quickly and easily and without needing an otherwise useless auto_increment field.

    Also, I changed itemQuantity to INT. If you really do have quantities of 3.5 and 7.3 and so on, then I guess you would want to use something other than INT, but if you do that, DECIMAL(4,2) seems inadequate to me. I would use DECIMAL(12,8) or, perhaps better, just use DOUBLE.
    Last edited by Old Pedant; 08-21-2013 at 10:13 PM.
    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.

  • #4
    New Coder
    Join Date
    Aug 2012
    Location
    France
    Posts
    60
    Thanks
    15
    Thanked 0 Times in 0 Posts

    clarifications

    Because that's a TERRIBLE array structure. Is that something you've created yourself, or are you getting it from somewhere?


    It's coming from a $_POST array from a HTML form I made to test the code once I had it working with hard coded values.


    It can be worked with, but its relatively meaningless, and VERY prone to error.
    First, you have a MySQL error: LAST_INSERT_ID() doesn't mean anything there. You haven't inserted yet, so how could it give anything back?



    I thought it would not be necessary to show the previous 50 or so lines of code in which I stored the values in firstly 'customers' primary key customerId, then "orders" primary key 'orderNumber' = auto increment, which is then passed on to the table 'orderItems' via the 'LAST_INSERT_ID()'


    Your table structure is lacking as well. Did you look at the normalization links provided in the other threat? They'll be super useful; you need to learn more about keys.

    I spent the last few days reading up on this and experimenting. I now have 4 tables, following your good advice (for which I am very grateful!), customers, orders, order items and products. After getting the input to work with hard coded values, I made a simple HTML5 form to post the data through to see if I could get it into the DB from the $_POST array, which is where I got stuck as far as the order items are concerned.

    First, you should have a primary key on this table, and it should be orderNumber. Next, orderNumber should also be set to auto_increment. This way, it is always unique, and will automatically fill in with a new number when you insert.

    This is what happens in the orders table, and seems to be working fine.


    Next, a better array structure would be this:

    I could make a multidimensional array if that is what is needed, but it's a JS dynamically generated form on the front end(already made) and will require a fair amount JS wrestling, but I have been writing JS longer so should be able to get it to happen.


    So with due respect, it seems like you're getting a bit ahead of yourself in application vs knowledge.

    Well yes, but I have no way to learn other than by reading, doing and asking questions - being stuck halfway up a mountain in the south of France.


    This may be a good time to take a small step back, if you can afford to, and brush up on basics like multidimensional arrays, and table keys.

    I have until the end of September to make v2 work, otherwise I will have to go back to last years giant spreadsheet method - which worked just fine, but even a novice like me can see that it is very ugly and wasteful! I'm doing this for a friend and also to keep my mind active!


    We don't mind helping you out here, but it will probably end up saving you a lot of headaches.

    Just to reiterate, I am very grateful for the help people give me on this forum. This is one of the good parts of the modern world. I can do headaches - you should try video editing!

    cheers

    chris
    A talent for speaking differently, rather than for arguing well, is the chief instrument of cultural change. Richard Rorty

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,720
    Thanks
    80
    Thanked 4,514 Times in 4,478 Posts
    I do agree with Keleth re the array structure, though I would not use one letter abbreviations.

    What's wrong with
    Code:
    $items[0]['prod'] = 1000;
    $items[0]['note'] = 'random words 1';
    $items[0]['qty'] = 1;
    $items[1]['prot'] = 1001;
    $items[1]['note'] = 'random words 2';
    $items[1]['qty'] = 2;
    (And note that the array starts at zero, not at one. Why/how would an array start at 1?)

    And as for the query:
    Code:
    ... 
    $sql = "INSERT INTO Orders ( ... )";
    mysql_query( $sql ) or die( mysql_error() );
    $orderid = mysql_insert_id();  // this is better than using LAST_INSERT_ID in sql
    
    $sql = "INSERT INTO orderItems (orderNumber,prodId,itemNotes,itemQuantity) VALUES ";
    $delimiter = "";
    for ( $i = 0; $i < count($items); ++$i )
    {
        $prod = mysql_real_escape_string($items[$i]["prod"]);
        $note = mysql_real_escape_string($items[$i]["note"]);
        $qty = (int) $items[$i]["qty"];  // or cast to double
    
        $sql .= "$delimiter($orderid,'$prod','$note',$qty)":
        $delimiter = ",";
    }
    mysql_query( $sql ) or die( mysql_error() );
    Although, of course, all of this SHOULD be done with mysqli, *NOT* mysql. mysql is officially and completely obsolete.
    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.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,720
    Thanks
    80
    Thanked 4,514 Times in 4,478 Posts
    Ahhh...I didn't see your post before posting my last message.

    If those values are coming form $_POST data, then yes, leave them as they come from it.

    But then, in my opinion, they should be coming for a <form> that looks like this:
    Code:
    <form ...>
    Item 1:
    <input name="prodid[]">
    <input name="notes[]">
    <input name="qty[]">
    Item 2:
    <input name="prodid[]">
    <input name="notes[]">
    <input name="qty[]">
    ... repeated ...
    If that's not what they are coming from, can you show a bit of your <form>??
    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
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,720
    Thanks
    80
    Thanked 4,514 Times in 4,478 Posts
    And if you can't tell, OddShoes, I completely *AGREE* with your database design. I think Keleth was definitely not seeing the big picture.
    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.

  • #8
    New Coder
    Join Date
    Aug 2012
    Location
    France
    Posts
    60
    Thanks
    15
    Thanked 0 Times in 0 Posts

    post array

    this is my post array
    Code:
    Array
    (
        [takenBy] => Amy
        [takenAt] => The Farm
        [deposit] => 20
        [collectDate] => 2013-08-16
        [status] => New
        [marketing] => Yes
        [firstName] => chris
        [lastName] => thorpe
        [email1] => oddshoes@mac.com
        [email2] => chris@oddshoes.fr
        [telLand] => 5568603904
        [telMob] => 12365489
        [1000] => 1
        [1000notes] => customer instructions 1
        [1001] => 2
        [1001notes] => customer instructions 2
    )
    [6] to [11] are first used to fill a row in the customer table generating unique custId on the way which then forms a row in the orders table along with [0] to [5] generating orderNo on the way which is passed to orderItems which has a row per item with orderNo, productId = [12]key, quantity [12] value and productNotes = [13]value

    the data for [0] to [11] comes from simple HTML5 input tags [12] onward is generated by JS.

    I was aware that MySQL was being superseded by MySQLi, but was sort of avoiding it 'till I felt I knew my way round the simple form a bit, same goes for JS and JQuery at the moment. Should I just go straight to it?

    cheers
    chris
    A talent for speaking differently, rather than for arguing well, is the chief instrument of cultural change. Richard Rorty

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,720
    Thanks
    80
    Thanked 4,514 Times in 4,478 Posts
    Sort of makes sense, but can I please see the actual <form> layout for the productid/quantity stuff?

    As it is, it looks from here like your <form> has fields such as:
    Code:
    Enter quantity for Product 1000 <input name="1000"/>
    Enter quantity for Product 1001 <input name="1001"/>
    Enter quantity for Product 1002 <input name="1002"/>
    Is that right?

    But if so, then presumably an order could have a quantity of *ZERO* for one or more products.

    And I need to tell you that <form> fields with names that are all numbers (or even that start with numbers) can cause problems in JavaScript code.

    I would *MUCH* prefer to see you use something like
    Code:
    Enter quantity for Product 1000 <input name="prod1000"/>
    Enter quantity for Product 1001 <input name="prod1001"/>
    Enter quantity for Product 1002 <input name="prod1002"/>
    (and we can then strip out the "prod" later, in the PHP code, quite easily).
    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
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,720
    Thanks
    80
    Thanked 4,514 Times in 4,478 Posts
    Oh, and "1000notes" should be turned around to be "notes1000", etc. Again, you don't want to start a name with a digit.
    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.

  • #11
    New Coder
    Join Date
    Aug 2012
    Location
    France
    Posts
    60
    Thanks
    15
    Thanked 0 Times in 0 Posts

    The JS that creates the form inputs

    Below is the code that generates the item and quantity form input. The system is basically a touch screen with lots of buttons, which generate first an order line to which the quantities and notes are added. when the in-putter is satisfied that the line is correct it is sent to the order display. When the order is complete and the customer details etc. have been entered the form is posted using a JS onclick function.
    The orders should not have zero quantity lines.
    I can easily reverse the form keys to put the letters at the front. I will do that.
    The quantities can be units or weights, but never go over 99 units, or 99.95 kg in 50g steps, so decimal(4,2) should be ideal.

    Code:
    // this function makes the order line from the arguments set when the product function is called, and sends it to the lineDiv			
    function SetOrderLine(productId, productName, productPrice, productdetails, cookDetails, servingDetails, sellBy, inputBy, step) {
    	document.getElementById('lineDiv').innerHTML = " ";
    	
    	var newOrderLine = document.createElement("p"); 
    		newOrderLine.setAttribute("class", "line");
    	
    	var ProductId =  document.createElement("span");
    		ProductId.setAttribute("id","idSpan");
    		ProductId.innerHTML = (productId);
    		newOrderLine.appendChild(ProductId);
    		
    	var InputBy = document.createElement("span");
    		InputBy.setAttribute("id","inputBySpan");
    		InputBy.innerHTML = ("By" + inputBy + "&nbsp;&nbsp;");
    		newOrderLine.appendChild(InputBy);
    
    	var QuantitySelect = document.createElement("input"); 
    		QuantitySelect.setAttribute("type","number");
    		QuantitySelect.setAttribute("min","0");
    		QuantitySelect.setAttribute("step",step); 
    		QuantitySelect.setAttribute("id","quantity"); 
    		QuantitySelect.setAttribute("size","5"); 
    		newOrderLine.appendChild(QuantitySelect);
    	
    	var product = document.createElement("span");
    		product.setAttribute("id", "product"); 
    		product.innerHTML = (productName); 
    		newOrderLine.appendChild(product);
    	
    	var productCost = document.createElement("span");
    		productCost.setAttribute("id", "productPrice"); 
    		productCost.innerHTML = ("£" + productPrice);
    		newOrderLine.appendChild(productCost);
    		
    	var productSellBy = document.createElement("span");
    		productSellBy.setAttribute("id", "productSellBy");
    		productSellBy.innerHTML = ("per " + sellBy);
    		newOrderLine.appendChild(productSellBy);
    		
    	var productNotes = document.createElement("input"); 
    		productNotes.setAttribute("type","Text"); 
    		productNotes.setAttribute("id","productNotes");
    		productNotes.setAttribute("class","text"); 
    		productNotes.setAttribute("size","40"); 
    		newOrderLine.appendChild(productNotes);
    		 
    	document.getElementById("lineDiv").appendChild(newOrderLine);
    	document.getElementById('pProductDetails').innerHTML = productdetails;
    	document.getElementById('pCookDetails').innerHTML = cookDetails;
    	document.getElementById('pServingDetails').innerHTML = servingDetails;
    // to hide product id
    //	document.getElementById("idSpan").style.display = "none";
    }
    
    // this reveals the customer details div when the enter customer button is clicked
    function showCustomerDiv() {
    	document.getElementById("customerDiv").style.display = "block";
      
    }
    
    lineNumber = 0;
    // this function posts order line to order div
    function postOrderLine()	{
    //update lineNumber
    	lineNumber = lineNumber +1;
    	var idNumber = idSpan.innerHTML;
    //	alert(idNumber);
    // create a line to put data from lineDiv into	
    	var newOrderLine = document.createElement("p"); 
    		newOrderLine.setAttribute("class", "line");
    		newOrderLine.setAttribute("id", "line" + lineNumber);
    
    // make remove line button
    	var deleteLine = document.createElement("button");
    	    deleteLine.className= "t5";
    	    deleteLine.value=lineNumber;
    	    deleteLine.onclick=function(){ removeLine(this); };
    	    newOrderLine.appendChild(deleteLine);
    // create span for quantity		
    	var orderLineQ =  document.createElement("span");
    		orderLineQ.innerHTML = document.getElementById('quantity').value;
    		newOrderLine.appendChild(orderLineQ);
    // create span for product
    	var product = document.createElement("span"); 
    		product.innerHTML = document.getElementById('product').innerHTML; 
    		newOrderLine.appendChild(product);
    // create span for price
    	var price = document.createElement("span"); 
    		price.innerHTML = document.getElementById('productPrice').innerHTML; 
    		newOrderLine.appendChild(price);
    // create span for sellBy
    	var sellby = document.createElement("span"); 
    		sellby.innerHTML = document.getElementById('productSellBy').innerHTML; 
    		newOrderLine.appendChild(sellby);
    // create span for notes		
    	var notes = document.createElement("span"); 
    		notes.innerHTML = "&nbsp;&nbsp;" + document.getElementById('productNotes').value; 
    		newOrderLine.appendChild(notes);		
    	
    	document.getElementById('orderP').appendChild(newOrderLine);
    	
    // create form input for product quantity
    	var orderFormQuantity =  document.createElement("input");
    		orderFormQuantity.setAttribute("name", idNumber);
    		orderFormQuantity.setAttribute("id", "prodQty" + lineNumber);
    		orderFormQuantity.value=quantity.value;
    		orderForm.appendChild(orderFormQuantity);
    		orderFormQuantity.style.display = "none";
    		
    // create form input for product notes
    	var orderFormProducNotes =  document.createElement("input");
    		orderFormProducNotes.setAttribute("name", idNumber+"notes");
    		orderFormProducNotes.setAttribute("id", "notes" + lineNumber);
    		orderFormProducNotes.value=productNotes.value;
    		orderForm.appendChild(orderFormProducNotes);
    		orderFormProducNotes.style.display = "none";
    }
    cheers

    chris
    A talent for speaking differently, rather than for arguing well, is the chief instrument of cultural change. Richard Rorty

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,720
    Thanks
    80
    Thanked 4,514 Times in 4,478 Posts
    Okay, assuming we now have $_POST values of "prod1000" and "notes1000" and so on, it's not hard.

    This means that $_POST will look like:
    Code:
        ... other pairs ...
        [telMob] => 12365489
        [prod1000] => 1
        [notes1000] => customer instructions 1
        [prod1001] => 2
        [notes1001] => customer instructions 2
        .. etc ...
    Right?

    Now, please remember that I do *NOT* use PHP. So this is mostly just from reading the PHP docs:
    Code:
    $sql = "INSERT INTO Orders ( ... )";
    mysql_query( $sql ) or die( mysql_error() );
    $orderid = mysql_insert_id();  // this is better than using LAST_INSERT_ID in sql
    
    $sql = "INSERT INTO orderItems (orderNumber,prodId,itemNotes,itemQuantity) VALUES ";
    $delimiter = "";
    foreach ($_POST as $key => $value) 
    {
        // ignore all POST keys *except* those starting with "prod":
        if ( substr( $key, 0, 4 ) == "prod" )
        {
            // strip off the "prod" to get the product id:
            $prodid= mysql_real_escape_string( substr($key,4) );
            $qty = ((double) $value); // ensure it is a number
            // find the corresponding note:
            $note = mysql_real_escape_string( $_POST["notes" . $prodid] );
    
            $sql .= "$delimiter($orderid,'$prod','$note',$qty)":
            $delimiter = ",";
        }
    }
    
    mysql_query( $sql ) or die( mysql_error() );
    That all feels right, at least. Let me know!
    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.

  • Users who have thanked Old Pedant for this post:

    oddshoes (08-22-2013)

  • #13
    New Coder
    Join Date
    Aug 2012
    Location
    France
    Posts
    60
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Smile splendid

    There were a couple of small issues:
    typo's in: $sql .= "$delimiter('$orderid','$prodId','$note','$qty')";
    and I had left some white space way back in the code which caused php to show [prod_1000], but I found it
    and bingo!
    I really am grateful for the help and patience.
    Off now to experiment with joins to get the data back out!

    cheers

    chris
    A talent for speaking differently, rather than for arguing well, is the chief instrument of cultural change. Richard Rorty

  • #14
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,720
    Thanks
    80
    Thanked 4,514 Times in 4,478 Posts
    At this rate, I'm going to have actually have start learning to really code PHP.

    You probably don't care, but the "tricks" I used there are actually just translations into PHP of some coding I did maybe 10 years ago using ASP code. I find that iterating through all the POST values is often an easy way to collect needed information.

    Glad it's working!

    Well yes, but I have no way to learn other than by reading, doing and asking questions - being stuck halfway up a mountain in the south of France.
    I'm not sure whether to feel sorry for you or not. <grin/>

    I'm guessing that despite your location English is not a second language to you? Because if it is, you have an amazing grasp of it.
    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.


  •  

    Posting Permissions

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