SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 39

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2013
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question How do I loop through a form and post to database?

    Hi,
    I have a form with multiple tables, and I need to post the data into a single database table. Look at attached images.
    tables.pngform.jpg

    Here is a code example of how my form is generated:
    PHP Code:
    <?php
                $sql 
    "SELECT * FROM blocks_table WHERE series='100 Series'"// 100 SERIES --------------------------------------------------------------------
                
    $result mysql_query($sql)or die(mysql_error());
                
    $series 100;
                
    $rowcounter 0
                echo 
    '<table class="styled-table">';
                echo 
    '<tr><th>100 Series</th><th>Per Pallet</th><th>Quantity</th><th>Pallets</th><th>Extras</th></tr>';
                while(
    $row mysql_fetch_array($result)){
                    
    $rowcounter++;
                    echo 
    '<tr><td>'.$row['type'].'</td>
                    <td><input type="text" name="'
    .$series.'perpallet'.$rowcounter.'" id="'.$series.'perpallet'.$rowcounter.'" style="width:50px;" value="'.$row['per_pallet'].'" readonly /></td>
                    <td><input type="text" name="'
    .$series.'quantity'.$rowcounter.'" id="'.$series.'quantity'.$rowcounter.'" style="width:50px;" value="0" onChange="getTotal('.$series.','.$rowcounter.');" /></td>
                    <td><input type="text" name="'
    .$series.'pallets'.$rowcounter.'" id="'.$series.'pallets'.$rowcounter.'" style="width:50px;" value="0" class="pallets" readonly /></td>
                    <td><input type="text" name="'
    .$series.'extras'.$rowcounter.'" id="'.$series.'extras'.$rowcounter.'" style="width:50px;" value="0" class="extras" readonly /></td>
                    </tr>'
    ;
                }
                echo 
    '</table>';
            
    ?>
            </div>
            <div class="left">
            <?php
                $sql 
    "SELECT * FROM blocks_table WHERE series='150 Series'"// 150 SERIES --------------------------------------------------------------------
                
    $result mysql_query($sql)or die(mysql_error());
                
    $series 150;
                
    $rowcounter 0;
                echo 
    '<table class="styled-table">';
                echo 
    '<tr><th>150 Series</th><th>Per Pallet</th><th>Quantity</th><th>Pallets</th><th>Extras</th></tr>';
                while(
    $row mysql_fetch_array($result)){
                    
    $rowcounter++;
                    echo 
    '<tr><td>'.$row['type'].'</td>
                    <td><input type="text" name="'
    .$series.'perpallet'.$rowcounter.'" id="'.$series.'perpallet'.$rowcounter.'" style="width:50px;" value="'.$row['per_pallet'].'" readonly /></td>
                    <td><input type="text" name="'
    .$series.'quantity'.$rowcounter.'" id="'.$series.'quantity'.$rowcounter.'" style="width:50px;" value="0" onChange="getTotal('.$series.','.$rowcounter.');" /></td>
                    <td><input type="text" name="'
    .$series.'pallets'.$rowcounter.'" id="'.$series.'pallets'.$rowcounter.'" style="width:50px;" value="0" class="pallets" readonly /></td>
                    <td><input type="text" name="'
    .$series.'extras'.$rowcounter.'" id="'.$series.'extras'.$rowcounter.'" style="width:50px;" value="0" class="extras" readonly /></td>
                    </tr>'
    ;
                }
                echo 
    '</table>';
            
    ?>
            </div>
    Inserting the data in the order_table is simple enough, because it is not repeating information. The data from the above tables (there are more than these two), will all be inserted into the same table order_details_table.

    How do I loop through these tables, and insert the data in the database table???

    thanks

  2. #2
    SitePoint Evangelist captainccs's Avatar
    Join Date
    Mar 2004
    Location
    Caracas, Venezuela
    Posts
    515
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    If I understand the question, you want to loop through each series to insert the data in a MySQL table. If so, use arrays. Add square brackets to the input fields names (using heredoc style):
    PHP Code:
    name="{$series}perpallet[{$rowcounter}]" 

    PHP Code:
    $rowcounter 0;  
    echo 
    '<table class="styled-table">'
    echo 
    '<tr><th>100 Series</th><th>Per Pallet</th><th>Quantity</th><th>Pallets</th><th>Extras</th></tr>'
    while(
    $row mysql_fetch_array($result)){ 
        
    $rowcounter++; 
        
    $output = <<<DATADATA
        <tr><td></td> 
        <td><input type="text" name="
    {$series}perpallet[{$rowcounter}]" id="{$series}perpallet{$rowcounter}" style="width:50px;" value="{$row['per_pallet']}" readonly /></td> 
        <td><input type="text" name="
    {$series}quantity[{$rowcounter}]" id="{$series}quantity{$rowcounter}" style="width:50px;" value="0" onChange="getTotal({$series},{$rowcounter});" /></td> 
        <td><input type="text" name="
    {$series}pallets[{$rowcounter}]" id="{$series}pallets{$rowcounter}" style="width:50px;" value="0" class="pallets" readonly /></td> 
        <td><input type="text" name="
    {$series}extras[{$rowcounter}]" id="{$series}extras{$rowcounter}" style="width:50px;" value="0" class="extras" readonly /></td> 
        </tr>
    DATADATA;
        echo 
    $output;

    echo 
    '</table>'
    Denny Schlesinger
    web services

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2013
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry because I'm a newbie in PHP and doing this using the forum and google searches.

    So once they are arrays, I just use simple insert statements to insert the data? Don't I have to loop through the html tables to grab the information? Do I use simple insert for every html table individually?

    thanks for your help

  4. #4
    SitePoint Evangelist captainccs's Avatar
    Join Date
    Mar 2004
    Location
    Caracas, Venezuela
    Posts
    515
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ROUBOS View Post
    Sorry because I'm a newbie in PHP and doing this using the forum and google searches.

    So once they are arrays, I just use simple insert statements to insert the data? Don't I have to loop through the html tables to grab the information? Do I use simple insert for every html table individually?

    thanks for your help
    After you add the square brackets to the name tag, add
    PHP Code:
    print_r ($_POST); 
    to the top of your action page. Then fill out a test form and submit it. The print_r() function will show you what the form returns. With that in hand you can plan the sql statements. Basically you'll be doing the following:
    PHP Code:
    foreach($_POST['name'] AS $key=>$value) {
        
    $sql "insert into.....";
        
    $result mysql_query($sql) or die("didn't work");

    Let me know how it goes.
    Denny Schlesinger
    web services

  5. #5
    SitePoint Enthusiast
    Join Date
    Apr 2013
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, thanks for your help.
    I'm getting an error "Parse error: syntax error, unexpected '<<' (T_SL) in C:\xampp\htdocs\hb_bots\bots\order-form.php on line 170"

    Do I need to do something for the heredoc style to be picked up? Using XAMPP locally on my machine for this.

    appreciate your help

  6. #6
    SitePoint Evangelist captainccs's Avatar
    Join Date
    Mar 2004
    Location
    Caracas, Venezuela
    Posts
    515
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ROUBOS View Post
    Hi, thanks for your help.
    I'm getting an error "Parse error: syntax error, unexpected '<<' (T_SL) in C:\xampp\htdocs\hb_bots\bots\order-form.php on line 170"

    Do I need to do something for the heredoc style to be picked up? Using XAMPP locally on my machine for this.

    appreciate your help
    heredoc should work with XAMPP as far as I know. Can you post the code that's giving you problems? Maybe you have extra spaces after the identifier, that's a common problem.
    Denny Schlesinger
    web services

  7. #7
    SitePoint Enthusiast
    Join Date
    Apr 2013
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    getting errors when using

    PHP Code:
    foreach($_POST['name'] AS $key=>$value) {
        
    $sql "INSERT INTO order_table (company_name, delivery_address, order_date, delivery_date, status, hb_checked, loaded_by, pallets, extra_blocks, extra_pallets, total_pallets)
        VALUES
        ('
    $_POST[companyname]','$_POST[deliveryaddress]','$_POST[orderdate]','$_POST[deliverydate]','$_POST[status]','$_POST[checked]','$_POST[loadedby]','$_POST[sumpallets]','$_POST[sumextras]','$_POST[grandtotal]')";
        
    $result mysql_query($sql) or die("didn't work");
    }
    ?> 
    Notice: Undefined index: name in C:\xampp\htdocs\hb_bots\bots\insert-order.php on line 8

    Warning: Invalid argument supplied for foreach() in C:\xampp\htdocs\hb_bots\bots\insert-order.php on line 8

  8. #8
    SitePoint Enthusiast rajeev13's Avatar
    Join Date
    Nov 2012
    Location
    New Delhi,India
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ROUBOS View Post
    getting errors when using

    PHP Code:
    foreach($_POST['name'] AS $key=>$value) {
        
    $sql "INSERT INTO order_table (company_name, delivery_address, order_date, delivery_date, status, hb_checked, loaded_by, pallets, extra_blocks, extra_pallets, total_pallets)
        VALUES
        ('
    $_POST[companyname]','$_POST[deliveryaddress]','$_POST[orderdate]','$_POST[deliverydate]','$_POST[status]','$_POST[checked]','$_POST[loadedby]','$_POST[sumpallets]','$_POST[sumextras]','$_POST[grandtotal]')";
        
    $result mysql_query($sql) or die("didn't work");
    }
    ?> 
    Notice: Undefined index: name in C:\xampp\htdocs\hb_bots\bots\insert-order.php on line 8

    Warning: Invalid argument supplied for foreach() in C:\xampp\htdocs\hb_bots\bots\insert-order.php on line 8

    sorry but i dont see any input type whose name is "name" in your page.
    there is no array "name".
    like u have $_POST['companyname'],'$_POST[deliveryaddress]','$_POST[orderdate] and others there is not having any $_POST['name'].

  9. #9
    SitePoint Enthusiast
    Join Date
    Apr 2013
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Confused now
    is there another way to get data from this:
    PHP Code:
    <?php
                $sql 
    "SELECT * FROM blocks_table WHERE series='150 Series'"// 150 SERIES --------------------------------------------------------------------
                
    $result mysql_query($sql)or die(mysql_error());
                
    $series 150;
                
    $rowcounter 0;
                echo 
    '<table class="styled-table">';
                echo 
    '<tr><th>150 Series</th><th>Per Pallet</th><th>Quantity</th><th>Pallets</th><th>Extras</th></tr>';
                while(
    $row mysql_fetch_array($result)){
                    
    $rowcounter++;
                    echo 
    '<tr><td>'.$row['type'].'</td>
                    <td><input type="text" name="'
    .$series.'perpallet'.$rowcounter.'" id="'.$series.'perpallet'.$rowcounter.'" style="width:50px;" value="'.$row['per_pallet'].'" readonly /></td>
                    <td><input type="text" name="'
    .$series.'quantity'.$rowcounter.'" id="'.$series.'quantity'.$rowcounter.'" style="width:50px;" value="0" onChange="getTotal('.$series.','.$rowcounter.');" /></td>
                    <td><input type="text" name="'
    .$series.'pallets'.$rowcounter.'" id="'.$series.'pallets'.$rowcounter.'" style="width:50px;" value="0" class="pallets" readonly /></td>
                    <td><input type="text" name="'
    .$series.'extras'.$rowcounter.'" id="'.$series.'extras'.$rowcounter.'" style="width:50px;" value="0" class="extras" readonly /></td>
                    </tr>'
    ;
                }
                echo 
    '</table>';
            
    ?>
    and insert it into a table?

  10. #10
    SitePoint Enthusiast
    Join Date
    Apr 2013
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK now with this php:
    PHP Code:
    <?php
                $sql 
    "SELECT * FROM blocks_table WHERE series='100 Series'"// 100 SERIES --------------------------------------------------------------------
                
    $result mysql_query($sql)or die(mysql_error());
                
    $series 100;
                
    $rowcounter 0;  
    echo 
    '<table class="styled-table">'
    echo 
    '<tr><th>100 Series</th><th>Per Pallet</th><th>Quantity</th><th>Pallets</th><th>Extras</th></tr>'
    while(
    $row mysql_fetch_array($result)){ 
        
    $rowcounter++; 
        
    $output = <<<DATADATA
        <tr><td>{$row["type"]}</td></td>
        <td><input type="text" name="
    {$series}perpallet[{$rowcounter}]" id="{$series}perpallet{$rowcounter}" style="width:50px;" value="{$row['per_pallet']}" readonly /></td> 
        <td><input type="text" name="
    {$series}quantity[{$rowcounter}]" id="{$series}quantity{$rowcounter}" style="width:50px;" value="0" onChange="getTotal({$series},{$rowcounter});" /></td> 
        <td><input type="text" name="
    {$series}pallets[{$rowcounter}]" id="{$series}pallets{$rowcounter}" style="width:50px;" value="0" class="pallets" readonly /></td> 
        <td><input type="text" name="
    {$series}extras[{$rowcounter}]" id="{$series}extras{$rowcounter}" style="width:50px;" value="0" class="extras" readonly /></td> 
        </tr>
    DATADATA;
        echo 
    $output;

    echo 
    '</table>';
            
    ?>
    and the form calling another file which has the following:
    PHP Code:
    <?php
    print_r 
    ($_POST); 
    ?> 


    <?php

    foreach($_POST AS $key=>$value) {
        
    $sql "INSERT INTO order_table (company_name, delivery_address, order_date, delivery_date, status, hb_checked, loaded_by, pallets, extra_blocks, extra_pallets, total_pallets)
        VALUES
        ('
    $_POST[companyname]','$_POST[deliveryaddress]','$_POST[orderdate]','$_POST[deliverydate]','$_POST[status]','$_POST[checked]','$_POST[loadedby]','$_POST[sumpallets]','$_POST[sumextras]','$_POST[grandtotal]')";
        
    $result mysql_query($sql) or die("didn't work");
    }
    ?>
    I get the following. It does not insert the data:
    PHP Code:
    Array ( [companyname] => NACA [deliveryaddress] => Stuart Park [orderdate] => 2013-04-18 [deliverydate] => 2013-05-16 [status] => Completed [checked] => Yes [loadedby] => MANOLIS [sumpallets] => [sumextras] => 624 [makeup] => [grandtotal] => [submit] => Submit Order [100perpallet] => Array ( [1] => 180 [2] => 240 [3] => 360 [4] => 576 [5] => 144 [6] => 288 [7] => 280 [8] => 560 [9] => 360 [10] => 480 [11] => 720 [12] => 1152 ) [100quantity] => Array ( [1] => 654 [2] => 154 [3] => 356 [4] => [5] => [6] => [7] => [8] => [9] => [10] => [11] => [12] => ) [100pallets] => Array ( [1] => [2] => [3] => [4] => [5] => [6] => [7] => [8] => [9] => [10] => [11] => [12] => ) [100extras] => Array ( [1] => 114 [2] => 154 [3] => 356 [4] => [5] => [6] => [7] => [8] => [9] => [10] => [11] => [12] => ) ) didn't work 

  11. #11
    SitePoint Enthusiast
    Join Date
    Apr 2013
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok say I do this:
    PHP Code:
    foreach($_POST['100quantity'] AS $key=>$value) {
        
    $sql="INSERT INTO order_details_table ('block_quantity') VALUES ('$_POST[blockquantity]')";
        
    $result mysql_query($sql) or die("didn't work");

    the value to insert into the table does not have the name blockquantity in the print_r($_POST). So how do I do it? This value is what is in the form.

  12. #12
    SitePoint Enthusiast
    Join Date
    Apr 2013
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I can insert the values for these:
    PHP Code:
    Array
    (
        [
    companyname] => InfoShell
        
    [deliveryaddress] => safasfasf
        
    [orderdate] => 2013-04-08
        
    [deliverydate] => 2013-04-23
        
    [status] => Pending
        
    [checked] => No
        
    [loadedby] => afsfsfsf
        
    [sumpallets] => 2
        
    [sumextras] => 351
        
    [makeup] => 2
        
    [grandtotal] => 4
        
    [submit] => Submit Order 
    The problem I have is with looping and inserting the following in a different table:
    PHP Code:
    [100type] => Array
            (
                [
    1] => 10.01
                
    [2] => 10.02
                
    [3] => 10.03
                
    [4] => 10.04
                
    [5] => 10.31
                
    [6] => 10.83
                
    [7] => 10.702
                
    [8] => 10.772
                
    [9] => 10.71
                
    [10] => 10.72
                
    [11] => 10.73
                
    [12] => 10.74
            
    )

        [
    100perpallet] => Array
            (
                [
    1] => 180
                
    [2] => 240
                
    [3] => 360
                
    [4] => 576
                
    [5] => 144
                
    [6] => 288
                
    [7] => 280
                
    [8] => 560
                
    [9] => 360
                
    [10] => 480
                
    [11] => 720
                
    [12] => 1152
            
    )

        [
    100quantity] => Array
            (
                [
    1] => 234
                
    [2] => 324
                
    [3] => 213
                
    [4] => 0
                
    [5] => 0
                
    [6] => 0
                
    [7] => 0
                
    [8] => 0
                
    [9] => 0
                
    [10] => 0
                
    [11] => 0
                
    [12] => 0
            
    )

        [
    100pallets] => Array
            (
                [
    1] => 1
                
    [2] => 1
                
    [3] => 0
                
    [4] => 0
                
    [5] => 0
                
    [6] => 0
                
    [7] => 0
                
    [8] => 0
                
    [9] => 0
                
    [10] => 0
                
    [11] => 0
                
    [12] => 0
            
    )

        [
    100extras] => Array
            (
                [
    1] => 54
                
    [2] => 84
                
    [3] => 213
                
    [4] => 0
                
    [5] => 0
                
    [6] => 0
                
    [7] => 0
                
    [8] => 0
                
    [9] => 0
                
    [10] => 0
                
    [11] => 0
                
    [12] => 0
            


  13. #13
    SitePoint Evangelist captainccs's Avatar
    Join Date
    Mar 2004
    Location
    Caracas, Venezuela
    Posts
    515
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    To insert the values you would need something like this. It takes the values for the first product, then the second one, and so on until it loops through the 12 products
    PHP Code:
    <?php
    foreach($_POST['100type'] AS $key=>$value) {
        
    $sql "INSERT INTO table
               (field1, field2, field3, field4, field5)
               VALUES ('
    {$value}',
                       '
    {$_POST['100perpallet'][$key]}',
                       '
    {$_POST['100quantity'][$key]}',
                       '
    {$_POST['100pallets'][$key]}',
                       '
    {$_POST['100extras'][$key]}'           
                      )"
        
    $result mysl_query($sql) or die('Could not insert data');
    }
    ?>
    Denny Schlesinger
    web services

  14. #14
    SitePoint Enthusiast
    Join Date
    Apr 2013
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    must be doing something wrong.. keep getting "Could not insert data".

    this is what I've tried:
    PHP Code:
    foreach($_POST['100type'] AS $key=>$value) {
        
    $sql "INSERT INTO order_details_table
               (block_type,block_quantity)
                          VALUES ('
    {$value}',
                       '
    {$_POST['100type'][$key]}',
                       '
    {$_POST['100quantity'][$key]}'          
                      )"
    ;
        
    $result mysql_query($sql) or die('Could not insert data');
    }

    ?> 
    the table I need to enter values in has 4 fields
    id
    order_id
    block_type
    block_quantity

    Do I need to insert a value for the id and order_id? and insert NULL?

  15. #15
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,047
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    That query define 2 columns but 3 values. It looks like the order_id needs to be added as the first column if that is the proper value for $value. Furthermore, I don't think you need to quote the order_id value because it is *probably an integer data type column. I say that because it sounds like a foreign key to what would be an auto increment primary key. Though I could be wrong. So just pointing that out.

    PHP Code:
    foreach($_POST['100type'] AS $key=>$value) {
        
    $sql "INSERT INTO order_details_table
               (order_id,block_type,block_quantity)
                          VALUES (
    {$value},
                       '
    {$_POST['100type'][$key]}',
                       '
    {$_POST['100quantity'][$key]}'          
                      )"
    ;
        
    $result mysql_query($sql) or die('Could not insert data');
    }

    ?> 
    Also, please look into preventing SQL injection. Right now this query is susceptible to a SQL injection attack. Ideally variable binding and prepared statements should be used to prevent SQL injection. Though the standard MySQL adapter does not support that easily. If this is a single isolated script I would highly recommend looking into an adaptor that provides an interface to easily use prepared statements with variable binding. Otherwise, the values can be escaped using mysql_real_escape_string. Though using mysql_real_escape_string is not the preferred nor most absolute method to prevent SQL injection. Again, if this is a single isolated script than consider using mysqli or pdo instead of the standard mysql functions. Otherwise, look into updating the antire application to use a more modern adaptor that supports prepared statements with variable binding.
    The only code I hate more than my own is everyone else's.

  16. #16
    SitePoint Enthusiast
    Join Date
    Apr 2013
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Still cannot insert data

    order_id is a foreign key. below is what i tried with all columns.
    id
    order_id - FK
    block_type
    block_quantity
    block_pallets
    block_extras

    PHP Code:
    foreach($_POST['100type'] AS $key=>$value) {
        
    $sql "INSERT INTO order_details_table
               (id,order_id,block_type,block_quantity,block_pallets,block_extras)
                          VALUES (null,
    {$value},
                        '
    {$_POST['100type'][$key]}',
                        '
    {$_POST['100quantity'][$key]}',
                '
    {$_POST['100pallets'][$key]}',
                '
    {$_POST['100extras'][$key]}'                     
                      )"
    ;
        
    $result mysql_query($sql) or die('Could not insert data');

    thanks for the advice on security. I should look into it.

  17. #17
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,047
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    What I would recommend than is to print the SQL statement and run it directly in MySQL though a GUI client or the terminal.

    PHP Code:
    foreach($_POST['100type'] AS $key=>$value) {
        
    $sql "INSERT INTO order_details_table
               (id,order_id,block_type,block_quantity,block_pallets,block_extras)
                          VALUES (null,
    {$value},
                        '
    {$_POST['100type'][$key]}',
                        '
    {$_POST['100quantity'][$key]}',
                '
    {$_POST['100pallets'][$key]}',
                '
    {$_POST['100extras'][$key]}'                     
                      )"
    ;

    echo 
    "<p>$sql</p>"// copy and paste this query into your MySQL client of choice and use the error to figure out what needs to be done

        
    $result mysql_query($sql) or die('Could not insert data');

    When a query errors or is not functioning in the way intended the best thing to do is print it out and run it directly in MySQL. That will provide a good indication as to the whether the query is malformed or the issue is application side. In some cases just printing it might reveal details like missing values that are expected to exist yet don't.
    The only code I hate more than my own is everyone else's.

  18. #18
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,047
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    What I would recommend than is to print the SQL statement and run it directly in MySQL though a GUI client or the terminal.

    PHP Code:
    foreach($_POST['100type'] AS $key=>$value) {
        
    $sql "INSERT INTO order_details_table
               (id,order_id,block_type,block_quantity,block_pallets,block_extras)
                          VALUES (null,
    {$value},
                        '
    {$_POST['100type'][$key]}',
                        '
    {$_POST['100quantity'][$key]}',
                '
    {$_POST['100pallets'][$key]}',
                '
    {$_POST['100extras'][$key]}'                     
                      )"
    ;

    echo 
    "<p>$sql</p>"// copy and paste this query into your MySQL client of choice and use the error to figure out what needs to be done

        
    $result mysql_query($sql) or die('Could not insert data');

    The only code I hate more than my own is everyone else's.

  19. #19
    SitePoint Enthusiast
    Join Date
    Apr 2013
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the foreign key is causing it
    #1452 - Cannot add or update a child row: a foreign key constraint fails (`bots`.`order_details_table`, CONSTRAINT `order_details_table_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `order_table` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)


    how do I copy the id from the first table to the order_id in the second table? that's the foreign key that needs to be populated. They are all in one form.
    some data are inputed in one table and some in the other

  20. #20
    SitePoint Enthusiast rajeev13's Avatar
    Join Date
    Nov 2012
    Location
    New Delhi,India
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ROUBOS View Post
    the foreign key is causing it
    #1452 - Cannot add or update a child row: a foreign key constraint fails (`bots`.`order_details_table`, CONSTRAINT `order_details_table_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `order_table` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)


    how do I copy the id from the first table to the order_id in the second table? that's the foreign key that needs to be populated. They are all in one form.
    some data are inputed in one table and some in the other
    mysql_insert_id() :- Retrieves the ID generated for an AUTO_INCREMENT column by the previous query (usually INSERT).
    you may retain this id to insert into your order_id column.

  21. #21
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,047
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    That error means that there is no row in the table order_table with a column id containing the $value.

    This can be confirmed by running the below query replacing the $value with the expected integer.

    Code SQL:
    SELECT COUNT(*) ct FROM order_table WHERE id = $value

    A single row should be returned with a value of 0.

    Once that is confirmed the solution is to reference a row that does exist or create the row in that table.

    It is good though that you are using a foreign key. Otherwise, you would essentially have an orphaned row in the order details table.
    The only code I hate more than my own is everyone else's.

  22. #22
    SitePoint Enthusiast
    Join Date
    Apr 2013
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    #1054 - Unknown column '$value' in 'where clause'

  23. #23
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,047
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    $value is meant to a token/placeholder for the actual value of the order_id. Go back to the printed query and copy the value of the order_id column that would be inserted.
    The only code I hate more than my own is everyone else's.

  24. #24
    SitePoint Enthusiast
    Join Date
    Apr 2013
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    when I run this: SELECT COUNT(*) ct FROM order_table WHERE id = 1
    it works with no error

    the id should be 1 right? first record. and that value should be entered into the foreign key

    how do I access the id of the first table in order to insert its value into the foreign key?

  25. #25
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,047
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    PHP Code:
    foreach($_POST['100type'] AS $key=>$value) {
        
    $sql "INSERT INTO order_details_table
               (id,order_id,block_type,block_quantity,block_pallets,block_extras)"
    .
                          
    "VALUES (null,{$value},"// <- you want the value of the $value variable
                        
    "'{$_POST['100type'][$key]}',
                        '
    {$_POST['100quantity'][$key]}',
                '
    {$_POST['100pallets'][$key]}',
                '
    {$_POST['100extras'][$key]}'"                    
                      
    )";

    echo "
    <p>$sql</p>"; // copy and paste this query into your MySQL client of choice and use the error to figure out what needs to be done

        
    $result = mysql_query($sql) or die('Could not insert data');

    The only code I hate more than my own is everyone else's.


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
  •