JavaScript

25-Feb-17 11:36

Even though this forum got me to where I am now on this project due largely the generous help of Richard Deeming, I have tried hard to avoid coming back here but I am having so much difficulty getting to the finish line which is this last bit.

First, on the screen that we use to preview data, I have this:

PHP

<body>
<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" value="<?php echo $_POST['sourcename' . $id]; ?>">
<input type="hidden" name="sourceaddress" value="<?php echo $_POST['sourceaddress' . $id]; ?>">
<input type="hidden" name="income" value="<?php echo $_POST['income' . $id]; ?>">
<?php }?>
<?php foreach ($row2IDs as $id) { ?>
<input type="hidden" name="spousename" value="<?php echo $_POST['spousename' . $id]; ?>">
<input type="hidden" name="spouseAddress" value="<?php echo $_POST['spouseAddress' . $id]; ?>">
<input type="hidden" name="spouseIncome" 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>

There is more but I thought this is relevant part.

Then on the next page (final.php) after user clicks the submit button, I have this:

PHP

<?php

// Database connection
$conn = mysqli_connect("localhost","myusername","mypass","myDB");
if(!$conn) {
die('Problem in database connection: ' . mysql_error());
}

// Data insertion into database

$query = 'INSERT INTO `mydb`.`wp_mytable` ( `employeename`, `ttitle`, `email` )'
     . ' VALUES ( ? , ? , ? )';
if( $sth = mysqli_prepare($conn,$query) ) {
   mysqli_stmt_bind_param($sth,'sss'
      ,$_POST["employeename"]
      ,$_POST["ttitle"]
      ,$_POST["email"]
   );
   if( mysqli_stmt_execute($sth) ) {
     echo 'Successfully created employee record;';
   } else {
      printf("Error: %s\n",mysqli_stmt_error($sth));
   }
} else {
   printf("Error: %s\n",mysqli_connect_error($conn));
}
$last_id = mysqli_insert_id($conn);


$sql = 'INSERT INTO `myDB`.`wp_myTable` ( `employeeID`'
     . ', `sourcename`, `sourceaddress`, `income`,`spousename`,`spouseAddress`,`spouseincome` )'
     . ' VALUES ( ? , ? , ? , ? , ? , ? , ? )';

if( $sth = mysqli_prepare($conn,$sql) ) {
for($i=0; $i<count($_POST['sourcename']); $i++)
{
$stmt->execute(array($last_id,
$_POST['sourcename'][$i],
$_POST['sourceaddress'][$i],
$_POST['income'][$i]),
$_POST['spousename'][$i],
$_POST['spouseAddress'][$i],
$_POST['spouseIncome'][$i]));
 }

   if( mysqli_stmt_execute($sth) ) {
     echo '<h1>Success!</h1><br>Your record successfully submitted <br><br><h1>Thank You!</h1';
   } else {
      printf("Error: %s\n",mysqli_stmt_error($sth));
   }
} else {
   printf("Error: %s\n",mysqli_connect_error($conn));
}
?>

If I can get records to submit correctly, I am done with this project.

However, I am stuck here with the following error messages:

Notice: Undefined variable: stmt in C:\xampp\htdocs\closures\forms\final.php on line 37

Fatal error: Call to a member function execute() on null in C:\xampp\htdocs\closures\forms\final.php on line 37

This error starts on this line:

$stmt->execute(array($last_id,...

I have spent my entire weekend on this without any progress. I will be truly grateful once again for solution.

Thanks

modified 28-Feb-17 11:38am.

Richard MacCutchan25-Feb-17 20:47

25-Feb-17 20:47

samflex wrote:
Notice: Undefined variable: stmt in C:\xampp\htdocs\closures\forms\final.php on line 37

You have never declared the variable stmt but you are trying to use it in the statement at line 37. You probably meant to write

PHP

$sth->execute(array($last_id,

Using more descriptive variable names would help to find such errors more easily.

samflex25-Feb-17 21:25

25-Feb-17 21:25

Thank you very much Richard.

Resolved those issues but created two new ones:

Warning: mysqli_stmt::execute() expects exactly 0 parameters, 4 given in C:\xampp\htdocs\closures\forms\final.php on line 43
Error: No data supplied for parameters in prepared statement

Any ideas?

Richard MacCutchan25-Feb-17 21:29

25-Feb-17 21:29

samflex wrote:
Any ideas?

The best idea is to make use of the documentation: PHP: mysqli_stmt::execute - Manual[^].

samflex25-Feb-17 21:37

25-Feb-17 21:37

I think the issue is that I have 7 parameters and need to bound them with the $sth as in

PHP

mysqli_stmt_bind_param($sth,'sssssss'($last_id, ...

but how do I integrate this with array?

Richard MacCutchan25-Feb-17 21:42

25-Feb-17 21:42

See PHP: mysqli_stmt::bind_param - Manual[^].

samflex25-Feb-17 21:47

25-Feb-17 21:47

Thanks Richard but I know how to do that though.

You can see I have that on one of the INSERT statements in my original post.

I just can't use with array.

That's where the struggle comes in.

Richard MacCutchan26-Feb-17 21:06

26-Feb-17 21:06

samflex wrote:
I just can't use with array.

Why not, it is exactly the same issue?

samflex27-Feb-17 0:34

27-Feb-17 0:34

Sorry sir but why can't I get a sample from you if you know how?

I would have solved it by now if I know the answer.

I have been playing around with it since your last response and can't get it to work.

Richard MacCutchan27-Feb-17 0:42

27-Feb-17 0:42

samflex wrote:
can't get it to work.

Sorry but I do not understand what your problem is. Whether you have individual items, or items in an array, you can still provide their references in your call to mysqli_stmt_bind_param

samflex27-Feb-17 3:22

27-Feb-17 3:22

Ok thanks for your help.

Hopefully, someone else will be able to help.

Here is my INSERT statement:

PHP

$sql = 'INSERT INTO `disclosures`.`wp_disclosures` ( `employeeID`'
     . ', `sourcename`, `sourceaddress`, `income`,`spousename`,`spouseAddress`,`spouseincome` )'
     . ' VALUES ( ? , ? , ? , ? , ? , ? , ? )';

if( $sth = mysqli_prepare($conn,$sql) ) {
for($i=0; $i<count($last_id); $i++)
{
$sth->execute(array($last_id,
$_POST['sourcename'][$i],
$_POST['sourceaddress'][$i],
$_POST['income'][$i]),
$_POST['spousename'][$i],
$_POST['spouseAddress'][$i],
$_POST['spouseIncome'][$i]);
 }

Just given the way the INSERT statement is written, I am getting the following error:

rning: mysqli_stmt::execute() expects exactly 0 parameters, 4 given in C:\xampp\htdocs\closures\forms\final.php on line 43
Error: No data supplied for parameters in prepared statement

But I believe that I am getting those errors because I could not bind the parameters like I did with the other INSERT statement like this:

PHP

( $sth = mysqli_prepare($conn,$query) ) {
   mysqli_stmt_bind_param($sth,'sss'
      ,$_POST["employeename"]
      ,$_POST["ttitle"]
      ,$_POST["email"]
   );

Notice this line:

sqli_stmt_bind_param($sth,'sss'

I need to find a way to add something like this:

sqli_stmt_bind_param($sth,'ssssssss' to my INSERT statement to get resolve the problem.

Just can't figure out how to integrate that with the mysqli_prepare(...) statement or the $sth->execute(array($last_id,... array statement.

Richard MacCutchan27-Feb-17 3:33

27-Feb-17 3:33

You are just repeating your original question, and I already explained the answer. What exactly is the problem in adding the parameter references to the mysqli_stmt_bind_param statement? All you need is:

PHP

mysqli_stmt_bind_param($sth,'ssssssss',
        parameter1,
        parameter2,
        parameter3,
        parameter4,
        parameter5,
        parameter6,
        parameter7,
        parameter8
      );

samflex27-Feb-17 4:04

27-Feb-17 4:04

Sorry sir but I keep getting half-baked answers that confuse me more.

I tried what you just gave.

Here it is again. I am very sorry for keep dumping code.

PHP

if( $sth = mysqli_prepare($conn,$sql) ) {
for($i=0; $i<count($last_id); $i++)
{
$sth->execute(array(
mysqli_stmt_bind_param($sth,'sssssss',
$last_id,
$_POST['sourcename'][$i],
$_POST['sourceaddress'][$i],
$_POST['income'][$i]),
$_POST['spousename'][$i],
$_POST['spouseAddress'][$i],
$_POST['spouseIncome'][$i]));
 }

This now gives me the same error I got before when I tried it like that:

Fatal error: Only variables can be passed by reference in C:\xampp\htdocs\closures\forms\final.php on line 40

I am just trying to show what I have tried.

Again, integrating binding the parameters with the array is an issue that I am struggling with.

This is just the las phase of this annoying project.

Richard MacCutchan27-Feb-17 4:21

27-Feb-17 4:21

PHP

$sth->execute(array(

What is the array expression supposed to mean here?

samflex27-Feb-17 4:31

27-Feb-17 4:31

I may be using it incorrectly but without the array, I am getting an error that says I am trying to insert string when I should be inserting array of records.

Richard MacCutchan27-Feb-17 4:38

27-Feb-17 4:38

I do not think it helps that you are trying to use compound statements, which make it more difficult to find the error. Try something like:

PHP

if( $sth = mysqli_prepare($conn,$sql) ) {
    for($i=0; $i<count($last_id); $i++)
    {
        mysqli_stmt_bind_param($sth,'sssssss',
        $last_id,
        $_POST['sourcename'][$i],
        $_POST['sourceaddress'][$i],
        $_POST['income'][$i]),
        $_POST['spousename'][$i],
        $_POST['spouseAddress'][$i],
        $_POST['spouseIncome'][$i]));
        $sth->execute();
    }
}

samflex27-Feb-17 5:35

27-Feb-17 5:35

still getting the same error - Only variables can be passed by reference.

Richard MacCutchan27-Feb-17 6:49

27-Feb-17 6:49

Then you need to look more closely at what these parameters are.

Richard Deeming27-Feb-17 7:56

Richard Deeming

27-Feb-17 7:56

I'm no PHP expert, but the examples in the documentation[^] seem to suggest something like this should work:

PHP

if( $sth = mysqli_prepare($conn,$sql) ) {
    mysqli_stmt_bind_param($sth, 'sssssss',
        $last_id,
        $sourcename,
        $sourceaddress,
        $income,
        $spousename,
        $spouseAddress,
        $spouseIncome);
    
    for ($i=0; $i<count($_POST['sourcename']); $i++)
    {
        $sourcename = $_POST['sourcename'][$i];
        $sourceaddress = $_POST['sourceaddress'][$i];
        $income = $_POST['income'][$i];
        $spousename = $_POST['spousename'][$i];
        $spouseAddress = $_POST['spouseAddress'][$i];
        $spouseIncome = $_POST['spouseIncome'][$i]);
        mysqli_stmt_execute($sth);
    }
    
    mysqli_stmt_close($sth);
}

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

samflex27-Feb-17 8:09

27-Feb-17 8:09

Hi Richard,

For one thing, this is the furthest I have gone with this query.

FINALLY, it was able to save something to the database.

Incredible.

There are just two issues,

1, It is inserting only one row
2,It is just inserting only one character.

For instance, if first name is Richard, it inserts R.

If Karin, it inserts K.

But atleast, something is getting inserted.

This is awesome.

Thank you very sir.

Always to the rescue!

samflex27-Feb-17 11:44

27-Feb-17 11:44

I am so close to getting it to work correctly.

Looking at this page below, I discovered the way I had the hidden form fields coded was the reason only one character of each value was getting inserted into the database.

After tweaking it, full record with full values are now getting inserted - YEA!

I still have one more issue and it is to get all records inserted instead of just the first row.

Perhaps, the hidden form fields still have something to do with it?

If anyone can spot this, I will be extremely grateful.

PHP

<?php
error_reporting(E_ALL);
//echo "DEBUG POST DATA: <pre>".print_r($_POST, 1)."

";
if(isset($_POST['submitted'])) {
if(trim($_POST['employeename']) === '') {
$nameError = 'Please enter your name.';
$hasError = true;
} else {
$employeename = $_POST['employeename'];
}
if(trim($_POST['email']) === '') {
$emailError = 'Please enter your email address.';
$hasError = true;
} else if (!preg_match("/^[[:alnum:]][a-z0-9_.-]*@[a-z0-9.-]+\.[a-z]{2,4}$/i", trim($_POST['email']))) {
$emailError = 'You entered an invalid email address.';
$hasError = true;
} else {
$email = trim($_POST['email']);
}

if(trim($_POST['ttitle']) === '') {
$nameError = 'Please enter your title.';
$hasError = true;
} else {
$ttitle = $_POST['ttitle'];
}

$rowIDs = $_POST['rowIDs'];
$row2IDs = $_POST['row2IDs'];

echo 'Employee Name: '. $employeename .'
';
echo 'Employee Title:' . $ttitle .'
';
echo 'Your email: '. $email . '
';
echo '

';

foreach ($rowIDs as $id) {
$sourcename = $_POST['sourcename' . $id];
$sourceaddress = $_POST['sourceaddress' . $id];
$income = $_POST['income' . $id];

echo 'Name: '. $sourcename . '
';
echo 'Address: '. $sourceaddress . '
';
echo 'Income: '. $income . '

';
}

foreach ($row2IDs as $id) {
$spousename = $_POST['spousename' . $id];
$spouseAddress = $_POST['spouseAddress' . $id];
$spouseIncome = $_POST['spouseIncome' . $id];

echo 'Name: '. $spousename . '
';
echo 'Address: '. $spouseAddress . '
';
echo 'spouseIncome: '. $spouseIncome . '

';
}

}
?>

Return to make changes

Richard MacCutchan27-Feb-17 21:28

27-Feb-17 21:28

The issues, as I tried to point out yesterday, is that you are not presenting proper variable values in your calls to MySQL. You must capture the data from the user into proper variables which the database code can read. Like Richard D. I am not a PHP expert, but it is fairly clear that PHP treats its variables somewhat strangely.

Richard Deeming28-Feb-17 1:09

Richard Deeming

28-Feb-17 1:09

Is the debug code printing out the correct data?

The debug code is taking the row numbers from rowIDs and row2IDs, and appending them to the field names. It expects fields called sourcename1, sourcename2, etc. (This matches the answer I posted to your previous question.)

The database code you posted is expecting the fields to be named sourcename[], sourceaddress[] etc. in the HTML. It expects them all to have the same number of values, which doesn't match the code from your previous question - you might have three "source" rows and only one "spouse" row. You'd also need to check what happens if one of the fields was left blank - if PHP doesn't leave a blank entry in the array, you might end up putting the address for row 3 with the name from row 2.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

samflex28-Feb-17 1:23