SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Jul 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql php array help

    Hello, I have a database that im struggling with. Im ok with basic mysql which is what ive been using for a while now but im stuck on the following and any help would be greatly appreciated.

    I have two tables

    Table one (items)

    ID | order number | description | price
    ---------------------------------------
    01 | sd01 | blah blah | 22.22

    and

    table two (orders)

    ID | customer id | order number | paid
    ---------------------------------------
    01 | 3 | sd01 | Y


    Basically im setting up a invoice system for myself to use. The items table will store all the invoice items in e.g. if i sold some paperclips i would put

    ID | order number | description | price
    ---------------------------------------
    01 | sd01 | paperclip | 1.22

    When filling in the main invoice form all the data except the items+item details are stored in the orders table.


    Now my problem is as follows;

    since i never know how many line items i will have e.g. i could have 1 or 70 items in an invoice i need the form to dynamically add new line items when a button is clicked.

    (like on Code Examples -> Building a Dynamic Form using Javascript and innerHTML. Add form elements in realtime without refreshing the page.)

    now so far i think i will be able to do the above BUT i dont know how to take all this and submit it to the database. From what i can gather on

    (PHP Form with array of fields)

    im looking at using arrays which i havent used before (i think!)

    any help would be appreciated.

  2. #2
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You could just loop through the POST and dynamically build the query so that you do everything in one INSERT.
    PHP Code:
    <?php
    if(isset($_POST['submit'])) {
        
        
    // init the values var
        
    $values '';
        
        
    // loop through each post
        // enclose it in single quotes and append it with a comma
        
    foreach($_POST['field'] as $i => $value){
            
    $values .= "'".$value."',";
        }
        
        
    // remove the last comma
        
    $values rtrim($values,',');
        
        
    $query "INSERT INTO table (field) VALUES ($values)";
        
        
    // check the query
        
    var_dump($query);
    }
    ?>

    <form method="post">
    <input type="text" name="field[]" value="one"><br>
    <input type="text" name="field[]" value="two"><br>
    <input type="text" name="field[]" value="three"><br>
    <input type="submit" name="submit" value="submit">
    </form>

  3. #3
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    Slightly streamlined....
    PHP Code:
        foreach($_POST['field'] as $i => $value){ 
            
    $values .= "'".$value."',"
        } 
         
        
    // remove the last comma 
        
    $values rtrim($values,','); 
    becomes
    PHP Code:
       $values "'".implode("','",$_POST['field'])."'"


    PS: Both these solutions assume that that values are all strings. If some of them are numbers, you'll need to do a little bit more work.

  4. #4
    SitePoint Member
    Join Date
    Jul 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hello,

    thanks for your help.

    ok ill be honest that for now is a little over my head lol my php skills are still growing!

    i have an alternative way of entering the details for the invoice system for now but once its all finished ill be back on here seeing if i can improve on it using your suggestions.

    Thanks for your help

  5. #5
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by web_design_sheff View Post
    hello,

    thanks for your help.

    ok ill be honest that for now is a little over my head lol my php skills are still growing!

    i have an alternative way of entering the details for the invoice system for now but once its all finished ill be back on here seeing if i can improve on it using your suggestions.

    Thanks for your help
    Each field is a line item, so it has to be set up as an array, hence the brackets in the name (line_item[]). This will allow PHP to just loop through each POSTed line item and add it to the query during the foreach loop.

    Let's say your form has two line items, such as this
    PHP Code:
    <input type="text" name="line_item[]" value="line item one">
    <
    input type="text" name="line_item[]" value="line item two"
    and then you submitted the form, your SQL query would look like this
    PHP Code:
    "INSERT INTO my_table_name (line_items) VALUES ('line item one','line item two')" 
    You're submitting all of the line items in ONE query, which is much more efficient.

    Use print_r($_POST) to see what's actually being submitted.

    PHP Code:
    <?php
    if(isset($_POST['submit'])) {
        echo 
    '<pre>';
        
    print_r($_POST);
        echo 
    '</pre>';
    ?>
    Just play around with the example code. Change the names, press submit and see what happens. That's the best way to learn.

  6. #6
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    "Break it, then learn how to fix it so you dont break it again."

    incidentally, tgavin, that line wont quite work - if you want to send multiple rows, the INSERT...VALUES syntax is INSERT [INTO] table_name [(fieldlistCSV)] VALUES (valuelistCSV)[,(valuelistCSV)]*

    or in practical terms.
    PHP Code:
    "INSERT INTO my_table_name (line_items) VALUES ('line item one'),('line item two')" 

  7. #7
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You're right. Thanks for the clarification. It's one of those "I think I remember how to do this..." instances - but you haven't done it for a while.

    Sorry for any confusion.


Bookmarks

Posting Permissions

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