How would you add form input values array into multiple rows in MySQL table?

[imgrooot]

For eg. I have this code.  Right now it only inserts 1 row with 1 value each.  How can I make it in a array so that it inserts 2 rows with 2 different values each?

<?php
	$title = $_POST['title'];
	$desc = $_POST['description'];
	
	$insert = $db->prepare("INSERT INTO record(title, description) VALUES(:title, :description)");
	$insert->bindParam(':title', $title);
	$insert->bindParam(':description', $desc);
	if($insert->execute()) {
		
		echo 'success.';
		
	} else {
		
		echo 'failed.';
	}
?>

<input type="text" name="title[]" value="" />
<input type="text" name="title[]" value="" />
<input type="text" name="description[]" value="" />
<input type="text" name="description[]" value="" />

Edited November 16, 2015 by imgrooot

[benanamen]

if ($_POST)
    {
    $insertStmt = $db->prepare("INSERT INTO datatable (field1, field2) VALUES (?,?)");
    
    for ($i = 0; $i < count($_POST['field1']); $i++)
        {
        $insertStmt->execute(array(
            $_POST['field1'][$i],
            $_POST['field2'][$i]
        ));
        }
    }

[imgrooot]

if ($_POST)

    {

    $insertStmt = $db->prepare("INSERT INTO datatable (field1, field2) VALUES (?,?)");

    

    for ($i = 0; $i < count($_POST['field1']); $i++)

        {

        $insertStmt->execute(array(

            $_POST['field1'][$i],

            $_POST['field2'][$i]

        ));

        }

    }

 

 

Well that's a unique approach.  One more thing.  What if I want to insert a unique non array field in the same stmt as the array fields?

 

Like this. Where would I add that in your code?

$_POST['record_id'];

[benanamen]

if ($_POST)

    {
    $insertStmt = $db->prepare("INSERT INTO datatable (record_id, field1, field2) VALUES (?,?,?)");
    
    for ($i = 0; $i < count($_POST['field1']); $i++)
        {
        $insertStmt->execute(array(
            $_POST['record_id'],
            $_POST['field1'][$i],
            $_POST['field2'][$i]
        ));
        }
    }

Edited November 16, 2015 by benanamen

[imgrooot]

if ($_POST)

    {

    $insertStmt = $db->prepare("INSERT INTO datatable (record_id, field1, field2) VALUES (?,?,?)");

    

    for ($i = 0; $i < count($_POST['field1']); $i++)

        {

        $insertStmt->execute(array(

            $_POST['record_id'],

            $_POST['field1'][$i],

            $_POST['field2'][$i]

        ));

        }

    }

 

 

Got it.  Last question.  Do I subsitute the $_POST fields with my title and description I have from input values? 

 

Like this.

for ($i = 0; $i < count($_POST['field1']); $i++)
        {
        $insertStmt->execute(array(
            $_POST['record_id'],
            $_POST['title'][$i],
            $_POST['description'][$i]
        ));
        }
    }

If so, does the "count($_POST['field1'])" stays the same or what?

Edited November 16, 2015 by imgrooot

[benanamen]

Do I subsitute the $_POST fields with my title and description I have from input values? 

YES

 

does the "count($_POST['field1'])" stays the same or what?

 

No, change it to  $_POST['title']

Edited November 16, 2015 by benanamen

[imgrooot]

Alright so I have it working now.  For future reference, here's the entire code based on what "benanamen" wrote.

// get record id from url parameter
$record_id = $_GET['record_id'];

if(isset($_POST['submit']) {

	$insertStmt = $db->prepare("INSERT INTO datatable (record_id, title, description) VALUES (?,?,?)");
	
	for ($i = 0; $i < count($_POST['title']); $i++)
		{
		$insertStmt->execute(array(
			$record_id,
			$_POST['title'][$i],
			$_POST['description'][$i]
		));
		
		if($insertStmt == true) {
		
			echo 'success';
			
			
		} else {
			echo 'false';
		}
	 }
}

<form action="" method="post">
	<input type="text" name="title[]" value="" />
	<input type="text" name="title[]" value="" />
	<input type="text" name="description[]" value="" />
	<input type="text" name="description[]" value="" />
	<input type="submit" name="submit" value="Post" />
</form>

[benanamen]

FYI: This is completely unnecessary. Don't create extra code that does nothing.

 

$record_id = $_GET['record_id'];

 

 

You dont need all this either:

if(isset($_POST['submit'])

 

Just use:

if ($_POST)

 

Also, all the following is unnecessary. Just put your query in a try/catch block. If the query fails you will catch the error. For a global solution to catching errors you can use set_exception_handler. What you have below will not tell you anything about the error should you have one.

 

if($insertStmt == true) {
        
            echo 'success';
            
            
        } else {
            echo 'false';
        }

Edited November 16, 2015 by benanamen

[imgrooot]

FYI: This is completely unnecessary. Don't create extra code that does nothing.

 

$record_id = $_GET['record_id'];

 

 

You dont need all this either:

if(isset($_POST['submit'])

 

Just use:

if ($_POST)

 

Also, all the following is unnecessary. Just put your query in a try/catch block. If the query fails you will catch the error. For a global solution to catching errors you can use set_exception_handler. What you have below will not tell you anything about the error should you have one.

 

if($insertStmt == true) {

        

            echo 'success';

            

            

        } else {

            echo 'false';

        }

 

I am only showing part of my real code.  This record id is needed my case. 

$record_id = $_GET['record_id'];

As for this.  Can you please explain why having only $_POST is better than isset method?

if(isset($_POST['submit'])

As for the try/catch block.  Are you saying to do something like this instead?

for ($i = 0; $i < count($_POST['title']); $i++) {
		
		try {
		
			$insertStmt->execute(array(
				$record_id,
				$_POST['title'][$i],
				$_POST['description'][$i]
			));
			
			echo 'Your post was a success.';
		
		} catch (Exception $e) {
			echo 'Caught exception: ',  $e->getMessage(), "\n";
			echo 'Your post was a failure.';
		}
	 }

[benanamen]

 I know a record id is needed. What I am saying is you don't need to create an extra variable unless you are transforming it somehow.

 

Can you please explain why having only $_POST is better than isset method?

If $_POST is true, your form has been submitted so no need to see if submit isset. Less code, much cleaner and does the same exact thing.

 

 

As for the try/catch block.  Are you saying to do something like this instead?

Yes, but no. You dont want to display the system error messages to the user. The way I handle errors is display some generic message to the user, log the error to my error log and have the system email me the details as soon as there is an error. I have an option to turn debugging on in which case it will display the full errors in the app during development.

 

If you use set_exception_handler you don't have to keep writing try/catch blocks all over.

Edited November 17, 2015 by benanamen

[imgrooot]

 I know a record id is needed. What I am saying is you don't need to create an extra variable unless you are transforming it somehow.

 

If $_POST is true, your form has been submitted so no need to see if submit isset. Less code, much cleaner and does the same exact thing.

 

 

Yes, but no. You dont want to display the system error messages to the user. The way I handle errors is display some generic message to the user, log the error to my error log and have the system email me the details as soon as there is an error. I have an option to turn debugging on in which case it will display the full errors in the app during development.

 

If you use set_exception_handler you don't have to keep writing try/catch blocks all over.

 

 

Ahh I see.  I will check out set_exception_handler. 

 

Thanks for clearing it up.

[imgrooot]

I have one more question "benanamen".

 

The code seems to always insert in the db, even if I don't require it. For eg. I have it set so that if input fields are not empty, it insert. If not, do nothing.  But it still inserts for some reason. 
What do you think is going on?

if(!empty($_POST['option_title']) && !empty($_POST['option_quantity']) && !empty($_POST['option_retail_price']) && !empty($_POST['option_discount_price'])) {
						
						$insert_options = $db->prepare("INSERT INTO item_options(item_id, option_title, option_quantity, option_retail_price, option_discount_price) VALUES(?,?,?,?,?)");

						for($i = 0; $i < count($_POST['option_title']); $i++) {
							
							$insert_options->execute(array(
								$get_item_id,
								$_POST['option_title'][$i],
								$_POST['option_quantity'][$i],
								$_POST['option_retail_price'][$i],
								$_POST['option_discount_price'][$i]
							));
							
							
						}
						
					} else {
					  echo 'do nothing.';
					}

Edited November 17, 2015 by imgrooot

[Barand]

You are testing that the title, quantity and price arrays are not empty, but you could still have blank values inside those arrays

[imgrooot]

You are testing that the title, quantity and price arrays are not empty, but you could still have blank values inside those arrays

 

Got it.

 

I have the solution now.  I have to add array_filter to it like this.

!empty(array_filter($_POST['option_title']))

[Barand]

That will only make things worse and remove the empty items. You could now have a situation where

title array         quantity array          price array
-----------         --------------          ------------
item 1                  5                       10.00  
item 2                  3                        
item 3                  2                     1000.00

After filtering the array, you get
 

title array         quantity array          price array
-----------         --------------          ------------
item 1                  5                       10.00  
item 2                  3                     1000.00    
item 3                  2   

so you are now charging for item 2 at the item 3 price

[imgrooot]

That will only make things worse and remove the empty items. You could now have a situation where

title array         quantity array          price array
-----------         --------------          ------------
item 1                  5                       10.00  
item 2                  3                        
item 3                  2                     1000.00

After filtering the array, you get

 

title array         quantity array          price array
-----------         --------------          ------------
item 1                  5                       10.00  
item 2                  3                     1000.00    
item 3                  2   

so you are now charging for item 2 at the item 3 price

 

I see. So what would be the alternative solution to this problem?

[Barand]

Check the individual values inside the loop, before calling execute()

[imgrooot]

Check the individual values inside the loop, before calling execute()

 

I take it it's something like this? If so, it doesn't work.

$insert_options = $db->prepare("INSERT INTO item_options(item_id, option_title, option_quantity, option_retail_price, option_discount_price) VALUES(?,?,?,?,?)");

						for($i = 0; $i < count($_POST['option_title']) && $i < count($_POST['option_quantity']) && $i < count($_POST['option_retail_price']) && $i < count($_POST['option_discount_price']); $i++) {
							
							$insert_options->execute(array(
								$get_item_id,
								$_POST['option_title'][$i],
								$_POST['option_quantity'][$i],
								$_POST['option_retail_price'][$i],
								$_POST['option_discount_price'][$i]
							));
							
							
						}

[Barand]

for($i = 0; $i < count($_POST['option_title']); $i++) {


    if (trim($_POST['option_title'][$i]) != '' 

        && trim($_POST['option_quantity'][$i]) != ''

        && trim($_POST['option_retail_price'][$i]) != ''

        && trim($_POST['option_discount_price'][$i]) != '')                            

        {

            $insert_options->execute(array(

                $get_item_id,

                $_POST['option_title'][$i],

                $_POST['option_quantity'][$i],

                $_POST['option_retail_price'][$i],

                $_POST['option_discount_price'][$i]

            ));

        }                    

}                            

[imgrooot]

for($i = 0; $i < count($_POST['option_title']); $i++) {

    if (trim($_POST['option_title'][$i]) != '' 
        && trim($_POST['option_quantity'][$i]) != ''
        && trim($_POST['option_retail_price'][$i]) != ''
        && trim($_POST['option_discount_price'][$i]) != '')                            
        {
            $insert_options->execute(array(
                $get_item_id,
                $_POST['option_title'][$i],
                $_POST['option_quantity'][$i],
                $_POST['option_retail_price'][$i],
                $_POST['option_discount_price'][$i]
            ));
        }                    
}                            
    

 

 

Perfect. That works. 

 

Thank you for clearing it up.

[benanamen]

OP, Just learned something new. if(isset($_POST['submit']) can FAIL in Internet Explorer if form is submitted with all empty fields, and there is no name attritbute for submit therefore anything within that code will fail since it will never run.

 

The no fail way is to do:

 

if($_SERVER['REQUEST_METHOD'] == 'POST') 

 

I am digging deep into if ($_POST) to see if there are any issues I don't know about with that method.

Edited November 19, 2015 by benanamen

[imgrooot]

OP, Just learned something new. if(isset($_POST['submit']) can FAIL in Internet Explorer if form is submitted with all empty fields, and there is no name attritbute for submit therefore anything within that code will fail since it will never run.

 

The no fail way is to do:

 

if($_SERVER['REQUEST_METHOD'] == 'POST') 

 

I am digging deep into if ($_POST) to see if there are any issues I don't know about with that method.

 

Got ya. Will keep it in mind.  Thanks.