Problem with simple PDO insert

[dottomm]

Hello and thank you in advance for any help that can be provided.
I am fairly new to PDO and am starting my first development project using PDO.

I am tryin to insert three form field values into a database. However one form field is an array.
The insert will work but the page times out before redircting to the next page. I am not sure about this insert statement as I think it is the $i = $i + 1; loop that causes it time out.

What I would like to write is:
For each indicator entered add the user_id and the indicator.

Can anybody tell me where I am going wrong and how I can fix it?
Here is my insert code

$user='root';
$pass='root';
$user_id='3';

$dbh = new PDO('mysql:host=127.0.0.1;dbname=databsename', $user, $pass);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$i = 0;
while($i < count($indicator)){
  if(trim($indicator[$i]) != ""){
     $stmt = $dbh->prepare("INSERT INTO worksheet SET user_id='$user_id' dateTime='NOW()', indicator='{$indicator[$i]}'");

     $stmt->execute();
          $i = $i + 1;
  HEADER("LOCATION:form-page2.php");

      }

    }

Thanks

[cereal]

Hi,

what is the size of the array? There are few errors:

1. a syntax error: you are missing a comma between user_id and dateTime;
2. dateTime, it does not matter the case, is a reserved word, so use backticks.

Also you could move prepare() outside the loop and use placeholders:

$stmt = $dbh->prepare("INSERT INTO `worksheet` SET `user_id` = :user_id, `dateTime` = NOW(), `indicator` = :indicator");

And set an array into execute() to define the values of each loop:

$stmt->execute([':user_id' => $user_id, ':indicator' => $indicator[$i]]);

You should move the header() outside the loop, too, but after, otherwise you keep setting it at each iteration. Right after that set exit, to stop the execution and make the server redirect. To recap:

$stmt = $dbh->prepare("INSERT INTO `worksheet` SET `user_id` = :user_id, `dateTime` = NOW(), `indicator` = :indicator");

while($i < count($indicator))
{
    $this->execute([':user_id' => $user_id, ':indicator' => $indicator[$i]]);
    $i++;
}

header('Location: form-page2.php');
exit;

See if these changes, in particular the syntax error, makes a difference. Otherwise, as you defined the exception mode, set a try catch block to see if PDO sends an exception.

[dottomm]

Thank you very much. I was able to find a similar solution that is currently working for me. I appreciate you taking the time to explain this to me.

Do you have any experience with multidimensional arrays?

I need to loop through a 'Name', Email', 'Role' array insert. something like for each Name added to the form ->insert 'Name, Email, Role'

Here is an example of the html form

<form action='<?php echo "{$_SERVER['PHP_SELF']}"; ?>' method='post' name='contact'>
        <!--First Person -->
        <input type='text' name='Name[]'  value='' > 
            <input type='text' name='email[]'   >
            <input type='checkbox' name='role[]' value='Teacher'> 
            <input type='checkbox' name='role[]' value='Student'>

<!-- second person add here--><!-- Can dynamically add multiple rows-->
        <input type='text' name='Name[]'  value='' > 
            <input type='text' name='email[]'   >
            <input type='checkbox' name='role[]' value='Teacher'> 
            <input type='checkbox' name='role[]' value='Student'>

            <input type='submit' value='Submit' >

Here is what I've tried so far

$name=array();
$stmt = $pdo->prepare("INSERT INTO contact (user_id = : user_id, name = : name, email = :email,  =: role");

while($i < count($name))
{
$this->execute([':user_id' => $user_id, ':name' => $name[$i]], 'email' => $email[i], 'role' => $role[i]);
    $i++;

    }

But it's not working. Any suggestions or resources would be greatly appreciated.

Thanks in advance

[cereal]

There is a missing $ in $email[$i] and $role[$i].

Then you are passing three arguments to the execute() method: one array and then email and role with a syntax that would probably send some warnings.

$this cannot be used in this context, use $stmt.

In the prepare() method, the syntax to define the placeholder is not correct, the format is :keyword, not : keyword, nor =: keyword and not even = : keyword. And it is missing the last column: role.

The query has also another issue, a bracket: (, I think here you were trying to mix the two available syntaxes for inserts in MySQL:

INSERT INTO table (column, column) VALUES('', '');
INSERT INTO table SET column = '', column = '';

The first is the standard, the second is a peculiarity of MySQL.

To recap:

$stmt = $pdo->prepare("INSERT INTO `contact` (`user_id`, `name`, `email`, `role`) VALUES (:user_id, :name, :email, :role)");

while($i < count($name))
{
    $stmt->execute([':user_id' => $user_id, ':name' => $name[$i], ':email' => $email[$i], ':role' => $role[$i]]);
    $i++;
}

However there is still an issue. In the form there are some checkbox, by default if none are selected, the checkboxes will not be set by the browser and so the POST array will miss them. So, if your forms has fields for two identities (Person1, Person2) and you select only Teacher for Person2, you will get role[0] => Teacher. Value, that according to the loop, will be associated to Person1, not to Person2, as expected.

To avoid this, you have to define groups in the HTML form by defining the index of each sub array, take this for example:

<?php
    $post = $_POST ? $_POST : NULL;
?>
<!DOCTYPE html>
<html>
<head>
    <title>Form Array</title>
    <style type='text/css'>
        label[for] { display:inline-block; font-weight:600; min-width:75px; }
    </style>
</head>
<body>

<form method='post'>
    <!-- First Person -->
    <p>
        <label for='name1'>Name</label>
        <input type='text' id='name1' name='name[0]' value=''>
    </p>

    <p>
        <label for='email1'>E-mail</label>
        <input type='text' id='email1' name='email[0]'>
    </p>

    <p>
        <label for=''>Role</label>
        <label><input type='checkbox' name='role[0][0]' value='Teacher'> Teacher</label>
        <label><input type='checkbox' name='role[0][1]' value='Student'> Student</label>
    </p>

    <hr>

    <!-- second person add here-->
    <!-- Can dynamically add multiple rows-->

    <p>
        <label for='name2'>Name</label>
        <input type='text' id='name2' name='name[1]' value=''>
    </p>

    <p>
        <label for='email2'>E-mail</label>
        <input type='text' id='email2' name='email[1]'>
    </p>

    <p>
        <label for=''>Role</label>
        <label><input type='checkbox' name='role[1][0]' value='Teacher'> Teacher</label>
        <label><input type='checkbox' name='role[1][1]' value='Student'> Student</label>
    </p>

    <p>
        <input type='submit' value='Submit'>
    </p>

</form>

<pre>
<?php
    print_r($post);
?>
</pre>

</body>
</html>

If you select Student for Person1 and Teacher for Person2 you get an array that looks like this:

Array
(
    [name] => Array
        (
            [0] => 
            [1] => 
        )

    [email] => Array
        (
            [0] => 
            [1] => 
        )

    [role] => Array
        (
            [0] => Array
                (
                    [1] => Student
                )

            [1] => Array
                (
                    [0] => Teacher
                )
        )
)

Now the index key allows to associate role[0][1] with [name][0] and role[1][0] with name[1].

There is still an issue: by using checkboxes, for Person2 it could be selected both and the script will get:

[role] => Array
    (
        ...
        [1] => Array
            (
                [0] => Teacher
                [1] => Student
            )
    )

You have to decide how you want to store this information: CSV, multiple columns, multiple tables... read point 8. Splitting the Big Table: Normalization and the suggested links and evaluate the type of queries that you will have to build with this information.

See also if you get other suggestions for a better approach. Bye!

[dottomm]

First. Thank you so much. You've helped me understand how arrays works so much better. I greatly appreciate it.

I think instead of checkboxes I can use <select> to choose the role. In testing; it seems to work well and should make validation easier.

The arrays displayed are exactly as what I am try to do, but the insert statement doesn't seem to work. I'm not getting an error but nothing is going into the table. Any ideas?

Thank you so much.

[cereal]

$name is defined? If not the loop will not run:

while($i < count($name))
{
    $stmt->execute([':user_id' => $user_id, ':name' => $name[$i], ':email' => $email[$i], ':role' => $role[$i]]);
    $i++;
}

Also, you could add debugDumpParams() to see what looks like the prepared statement:

while($i < count($name))
{
    $stmt->execute([':user_id' => $user_id, ':name' => $name[$i], ':email' => $email[$i], ':role' => $role[$i]]);
    print_r($stmt->debugDumpParams());
    die;
}

This will stop the execution after the first loop and will show the contents of the query. If it does not help paste the result here and also the code.

[dottomm]

Here is the code I am using:

$pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=$charset", $username, $password,
    array(
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_PERSISTENT => false,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES $charset COLLATE $collate"
    )
);
$name=array();
$email=array();
$role=array();

$rows = [];
$rows[] = [
    'user_id' => $user_id,
    'name' => $_POST['name'],
    'email' => $_POST['email'],
    'role' => $_POST['role']
];

$stmt = $pdo->prepare("INSERT INTO `contact` (`user_id`, `name`, `email`, `role`) VALUES (:user_id, :name, :email, :role)");
while($i < count($name))
{
 $stmt->execute([':user_id' => $user_id, ':name' => $name[$i], ':email' => $email[$i], ':role' => $role[$i]]);

}

  }

}

And Here is the HTML

<form method='post'>
    <!-- First Person -->
    <p>
        <label for='name1'>Name</label>
        <input type='text' id='name1' name='name[0]' value=''>
    </p>
    <p>
        <label for='email1'>E-mail</label>
        <input type='text' id='email1' name='email[0]'>
    </p>
    <p>
        <label for=''>Role</label>
        <select name='role[0]' id='role1'>
          <option value='none'>Please select role
          <option value='teacher'>Teacher
            <option value='student'>Student
          </select>
    </p>
    <hr>
    <!-- second person add here-->
    <!-- Can dynamically add multiple rows-->
    <p>
        <label for='name2'>Name</label>
        <input type='text' id='name2' name='name[1]' value=''>
    </p>
    <p>
        <label for='email2'>E-mail</label>
        <input type='text' id='email2' name='email[1]'>
    </p>
    <p>
        <label for=''>Role</label>
        <select name='role[1]' id='role2'>
          <option value='none'>Please select role
          <option value='teacher'>Teacher
            <option value='student'>Student
          </select>
    </p>
    <p>
        <input type='submit' value='Submit'>
    </p>
</form>

Here is the array it displays

Array
(
    [name] => Array
        (
            [0] => Tom
            [1] => Ted
        )

    [email] => Array
        (
            [0] => [e-mail]
            [1] => [e-mail]
        )

    [role] => Array
        (
            [0] => teacher
            [1] => teacher
        )

)

I tired the print_r($stmt->debugDumpParams()); but it didn't display anything.

Again, thank you.

[cereal]

You're welcome ;)

This part is not correct:

$name=array();
$email=array();
$role=array();

$rows = [];
$rows[] = [
    'user_id' => $user_id,
    'name' => $_POST['name'],
    'email' => $_POST['email'],
    'role' => $_POST['role']
];

Your loop does:

while(1 < count($name))

It's like writing:

while(1 < count([]))

the print_r($stmt->debugDumpParams()); but it didn't display anything.

It will never run because $name, as array, is empty. Do:

$name  = $_POST['name'];
$email = $_POST['email'];
$role  = $_POST['role'];

each variable will be an array. I'm keeping this simple, here instead of $_POST you should use filter_input_array(), but this can be refined when you get the script to work. You can, also, remove the $rows initialization and assignement, unless you don't need it for something else.