0

I am likely to get in trouble with you guys again but please help.

When I attempt to submit records to the database, I get the following error:

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in C:\xampp\htdocs\closures\forms\final.php on line 64

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in C:\xampp\htdocs\closures\forms\final.php on line 75

Below is the code causing the error:

//grab the last inserted ID from previous INSERT statement (not included here)
$last_id = mysqli_insert_id($conn);


//Database connection
$pdo = new PDO("mysql:dbname=myDB;host=localhost","myusername","mypassword",array(PDO::ATTR_PERSISTENT => true));

$sql = 'INSERT INTO `myDB`.`myTable` ( `employeeID`'
     . ', `sourcename`, `sourceaddress`, `income`,`spousename`,`spouseAddress`,`spouseincome` )'
     . ' VALUES ( ? , ? , ? , ? , ? , ? , ? )';
     $stmt = $pdo->prepare($sql);


//My problems have to do with the two foreach loops below
foreach($_POST['sourcename'] as $id=>$not_used)
{
    $stmt->execute(array(
            $last_id,
            $_POST['sourcename'][$id],
            $_POST['sourceaddress'][$id],
            $_POST['income'][$id]));
}

// loop over the second set of arrays, for the spouse income sources
foreach($_POST['spousename'] as $id=>$not_used)
{
    $stmt->execute(array(
            $last_id,
            $_POST['spousename'][$id],
            $_POST['spouseAddress'][$id],
            $_POST['spouseIncome'][$id]));
}

I know the error is happening because the INSERT is expecting each of the foreach loops to have have same number of bound variables as the parameters in the INSERT statement as shown:

VALUES ( ? , ? , ? , ? , ? , ? , ? )

How do I reconcile to the two foreach loops into so the numbers equal the same as the INSERT statement?

If you don't quite understand what I mean please, please ask for clarification.

I would rather you yell at me but help than having to go to work on Monday without resolving this issue.

Thanks a lot

//updated code:

<form action='final.php' method = 'POST'>
<input type="hidden" name="employeename" value="<?php echo $employeename; ?>">
<input type="hidden" name="ttitle" value="<?php echo $ttitle; ?>">
<input type="hidden" name="email" value="<?php echo $email; ?>">
<?php foreach ($rowIDs as $id) { ?>
<input type="hidden" name="sourcename[rowIDs]" value="<?php echo $_POST['sourcename' . $id]; ?>">
<input type="hidden" name="sourceaddress[rowIDs]" value="<?php echo $_POST['sourceaddress' . $id]; ?>">
<input type="hidden" name="income[rowIDs]" value="<?php echo $_POST['income' . $id]; ?>">
<?php }?>
<?php foreach ($row2IDs as $id) { ?>
<input type="hidden" name="spousename[row2IDs]" value="<?php echo $_POST['spousename' . $id]; ?>">
<input type="hidden" name="spouseAddress[row2IDs]" value="<?php echo $_POST['spouseAddress' . $id]; ?>">
<input type="hidden" name="spouseIncome[row2IDs]" value="<?php echo $_POST['spouseIncome' . $id]; ?>">
<?php } ?>
<a href="javascript:history.go(-1)" data-icon="back" data-role="button" data-theme="b">Return to make changes</a>
<input type="submit" value="submit" />
</form>
Tairoc
  • 635
  • 1
  • 6
  • 18
  • I'm not sure if I understood, but you want to pass the 7 parameters withour having them all? – jlHertel Feb 25 '17 at 19:01
  • Your code implies that a user could have multiple spouses, but your database does not seem to accommodate this. – apokryfos Feb 25 '17 at 19:08
  • @apokryfos, first let me thank you both for not yelling at me. The way this is set up, each employee is being required to declare his/her sources of additional income other than their regular salary. So, there is a box for an employee to provide his/her name, email and title. This information is required just once only. This information is also submitted into the employee table. Then, then there is a row to declare spouse's source of income. This could be one source or more. If ore than once, click add button to add more; hence the array. same with several others like organ.. – Tairoc Feb 25 '17 at 19:11
  • Each row needs to be complete with source and spouse meaning you need a single loop that provides all 7 required arguments. However it would make more sense to me if this was actually split into 2 inserts, one inserting into an employees additional sources of income table and one inserting in an employees spouse details table. Meaning 2 completely differently written inserts. – apokryfos Feb 25 '17 at 19:13
  • @apokryfos, that's what I was trying to explain before I run out of space. There are two tables, one to contain employee info. Then another to contain all the employee's additional sources of income other than salary. This includes dealings with wife/husband, organizations, etc. So if I am an employee and my ID is 1. Then once I compete the form and my wife has three different sources of income, my ID will be linked to those. – Tairoc Feb 25 '17 at 19:17
  • Common man, this is not a duplicate. Title may be the same but two completely different issues. I am really hoping to get help from serious people. – Tairoc Feb 25 '17 at 19:31
  • No, the solution from that thread is not the same as the issue I am having. Please review my question and review the solution from that thread. It will not work for me. – Tairoc Feb 25 '17 at 19:33
  • Looking at the code, it appears imperative that when you have a record in `$_POST['sourcename']`, there is always one corresponding record in `$_POST['spousename']`. Meaning, it's the structure of `$_POST` array that is source of the issue, rather than the query (or the undesired loop) itself. Therefore, the problem should ideally be solved from the page which submits this `$_POST`, than you having to write an almost unreliable logic to achieve the end goal here. However, as it stands right now, there's only going to be a twisted solution to it.. – Dhruv Saxena Feb 25 '17 at 20:23
  • (1) Remove both `foreach` loops. (2) Use instead: `for($i=0; $iexecute(array($last_id, $_POST['sourcename'][$i], $_POST['sourceaddress'][$i], $_POST['income'][$i]), $_POST['spousename'][$i], $_POST['spouseAddress'][$i], $_POST['spouseIncome'][$i])); }` (3) This includes all the fields at once in the `execute` command for every iteration mapped by `$i`, which is what the `INSERT` expects. Yet again, I'd stress that this shouldn't be considered as the best workaround. The focus should be set on improving the structure of data submitted to this page. – Dhruv Saxena Feb 25 '17 at 20:24
  • @DhruvSaxena, thank you for your constructive post. Please take a look at the form fields that get sent to this page and see if anything needs to change. I updated at my original post. I really appreciate your help. I will do anything to get this working because there is a lot riding on this on Monday. – Tairoc Feb 25 '17 at 20:38

0 Answers0