|
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.
|
|
|
|
|
samflex wrote: I just can't use with array.
Why not, it is exactly the same issue?
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
Ok thanks for your help.
Hopefully, someone else will be able to help.
Here is my INSERT statement:
$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:
( $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.
|
|
|
|
|
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:
mysqli_stmt_bind_param($sth,'ssssssss',
parameter1,
parameter2,
parameter3,
parameter4,
parameter5,
parameter6,
parameter7,
parameter8
);
|
|
|
|
|
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.
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.
|
|
|
|
|
$sth->execute(array(
What is the array expression supposed to mean here?
|
|
|
|
|
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.
|
|
|
|
|
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:
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();
}
}
|
|
|
|
|
still getting the same error - Only variables can be passed by reference.
|
|
|
|
|
Then you need to look more closely at what these parameters are.
|
|
|
|
|
I'm no PHP expert, but the examples in the documentation[^] seem to suggest something like this should work:
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
|
|
|
|
|
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!
|
|
|
|
|
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
error_reporting(E_ALL);
";
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
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
Thank you both.
This has been the most depressing project I have been on and it is because of just this one.
At this point, I am willing to pay to get this to work because the pressure from upper management right now is too intense.
Ok Richard, yesterday, I posted the markup just to show if the problem could be coming from there.
With your solution in mind, here is the adjusted markup that allowed it to at least submit to the db even though it was submitting just one row:
<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; ?>">
<input type="hidden" name="sourcename[]" value="<?php echo $_POST['sourcename']; ?>">
<input type="hidden" name="sourceaddress[]" value="<?php echo $_POST['sourceaddress']; ?>">
<input type="hidden" name="income[]" value="<?php echo $_POST['income' . $id]; ?>">
<input type="hidden" name="spousename[]" value="<?php echo $_POST['spousename']; ?>">
<input type="hidden" name="spouseAddress[]" value="<?php echo $_POST['spouseAddress']; ?>">
<input type="hidden" name="spouseIncome[]" value="<?php echo $_POST['spouseIncome']; ?>">
<input type="submit" value="submit" />
</form>
I know you guys say you are no php experts but you are still 10 times more knowledgeable about php than me.
Given the solution the great Richard D posted yesterday. what could be adjusted with the above markup of hidden form fields to match with Richard's solution.
The problem could be with this markup of the processing code.
This has become agonizing.
Note: the fields that don't have [] are not array.
|
|
|
|
|
Start by rendering all of the required fields, with the correct names and values:
<?php foreach ($rowIDs as $id) { ?>
<input type="hidden" name="rowIDs[]" value="<?php echo $id; ?>">
<input type="hidden" name="<?php echo 'sourcename' . $id ?>" value="<?php echo $_POST['sourcename' . $id]; ?>">
<input type="hidden" name="<?php echo 'sourceaddress' . $id ?>" value="<?php echo $_POST['sourceaddress' . $id]; ?>">
<input type="hidden" name="<?php echo 'income' . $id ?>" value="<?php echo $_POST['income' . $id]; ?>">
<?php }?>
<?php foreach ($row2IDs as $id) { ?>
<input type="hidden" name="row2IDs[]" value="<?php echo $id; ?>">
<input type="hidden" name="<?php echo 'spousename' . $id ?>" value="<?php echo $_POST['spousename' . $id]; ?>">
<input type="hidden" name="<?php echo 'spouseAddress' . $id ?>" value="<?php echo $_POST['spouseAddress' . $id]; ?>">
<input type="hidden" name="<?php echo 'spouseIncome' . $id ?>" value="<?php echo $_POST['spouseIncome' . $id]; ?>">
<?php } ?>
In final.php, you read them using the same code:
$rowIDs = $_POST['rowIDs'];
foreach ($rowIDs as $id) {
$sourcename = $_POST['sourcename' . $id];
$sourceaddress = $_POST['sourceaddress' . $id];
$income = $_POST['income' . $id];
...
}
$row2IDs = $_POST['row2IDs'];
foreach ($row2IDs as $id) {
$spousename = $_POST['spousename' . $id];
$spouseAddress = $_POST['spouseAddress' . $id];
$spouseIncome = $_POST['spouseIncome' . $id];
...
}
The only slight issue is that you won't be able to have the source and spouse on the same row in your database table, because they're independent lists. You'll have to put them on different rows:
$sql = 'INSERT INTO `myDB`.`wp_myTable` ( `employeeID`'
. ', `sourcename`, `sourceaddress`, `income` )'
. ' VALUES ( ? , ? , ? , ? )';
if( $sth = mysqli_prepare($conn,$sql) ) {
mysqli_stmt_bind_param($sth, 'ssss',
$last_id,
$sourcename,
$sourceaddress,
$income);
$rowIDs = $_POST['rowIDs'];
foreach ($rowIDs as $id) {
$sourcename = $_POST['sourcename' . $id];
$sourceaddress = $_POST['sourceaddress' . $id];
$income = $_POST['income' . $id];
mysqli_stmt_execute($sth);
}
mysqli_stmt_close($sth);
}
$sql = 'INSERT INTO `myDB`.`wp_myTable` ( `employeeID`'
. ', `spousename`,`spouseAddress`,`spouseincome` )'
. ' VALUES ( ? , ? , ? , ? )';
if( $sth = mysqli_prepare($conn,$sql) ) {
mysqli_stmt_bind_param($sth, 'ssss',
$last_id,
$spousename,
$spouseAddress,
$spouseIncome);
$row2IDs = $_POST['row2IDs'];
foreach ($row2IDs as $id) {
$spousename = $_POST['spousename' . $id];
$spouseAddress = $_POST['spouseAddress' . $id];
$spouseIncome = $_POST['spouseIncome' . $id];
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
|
|
|
|
|
YEA!!!!!!!!!!!!!!!!!!!!!!
wow!!!
I don't know if I should stand up and dance.
WOW!
My manager gave till 9AM tomorrow to get it working. Needless to say, I didn't sleep well last night.
Sorry I am just responding now because I just got to work.
I responded earlier from work.
Richard D, I don't know if you remember me saying this a couple of years ago.
There is guy called Bill Wilkinson, one of the best in this business.
Like you, he would say, not familiar with this but he ends up solving it.
Like him, anytime you are involved in my thread, I feel a weight lifted off me because like him, you always see that the issue is resolved.
THANK YOU.
Ok, let me calm down and ask a few questions.
As you may know, there are many more fields than this.
From your comment, they will all end up inserting into different rows.
The good news is that they belong to same employeeID.
So, how do I tie them up together when I attempt to display the records?
I probably need UNION ALL and GROUP BY?
Boy, I feel so relieved.
As always, thanks for coming to my rescue - again!
You are such a load of talent. Is there something you don't know about technology?
I have spent over a week on this with really great experts and they still could not get it to work for me.
|
|
|
|
|
Personally, I'd be inclined to split them into multiple tables - "employee details", "source details", and "spouse details". You'd have a one-to-many relationship from the employee details table to the source details table, and another from the employee details table to the spouse details table.
To display the full details, you'd then have three queries: one to get the employee details; one to get the list of source details; and one to get the list of spouse details.
SELECT `employeename`, `ttitle`, `email` FROM `myDB`.`wp_employeeDetails` WHERE `employeeID` = ?
SELECT `sourcename`, `sourceaddress`, `income` FROM `myDB`.`wp_sourceDetails` WHERE `employeeID` = ?
SELECT `spousename`, `spouseAddress`, `spouseincome` FROM `myDB`.`wp_spouseDetails` WHERE `employeeID` = ?
You could do that with your current design, but you'd need some way to identify whether a record was the employee details, the source details, or the spouse details. It could get quite messy. 
SELECT `employeename`, `ttitle`, `email` FROM `myDB`.`wp_myTable` WHERE `employeeID` = ? AND `employeename` Is Not Null
SELECT `sourcename`, `sourceaddress`, `income` FROM `myDB`.`wp_myTable` WHERE `employeeID` = ? AND `sourcename` Is Not Null
SELECT `spousename`, `spouseAddress`, `spouseincome` FROM `myDB`.`wp_myTable` WHERE `employeeID` = ? AND `spousename` Is Not Null
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Ok I see.
So, let me say for instance that I have these three tables (they will be up to 7).
CEATE TABLE employees (
employeeid INT NOT NULL,
employeename varchar(25),
ttitle varchar(25),
email varchar(25),
PRIMARY KEY (employeeid)
);
create table wp_spousedetails
(
spouseid INT NOT NULL,
name varchar(30) not null,
foreign key (employeeid) references menus(employeeid),
primary key (spouseid)
);
create table wp_sourcedetails
(
sourceid INT NOT NULL,
name varchar(30) not null,
foreign key (employeeid) references menus(employeeid),
primary key (sourceid)
);
Please don't worry if there is any syntax error with these. That's not the point of posting these ddl statements. I can fix those.
My question is to reference them all, all I need is employeeid, no?
Select e.employeename, e.ttitle, e.email, so.sourcename,so.sourceincome, sp.spousename,sp.spouseincome.... from employees e on so.employeeid = e.employeeid, sp.employeeid = e.employeeid.
That's really all I need right?
I am trying to calm down from excitement. This has been agonizing.
Thank you, thank you sir
|
|
|
|
|
Essentially, but if you join all of the tables together, you're going to get the details repeated across multiple rows.
For example, if employee 1 has 3 sources and 2 spouses, you'll get six rows:
Employee Source Spouse
-----------------------------
1 A X
1 A Y
1 B X
1 B Y
1 C X
1 C Y
That may or may not work, depending on your requirements.
With three separate queries, filtered by the employee ID, you'd get one record for the employee, three records for the sources, and two records for the spouses.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
LAST question (I can't believe that I have arrived at this), the query that I need between the two queries you posted is the bottom portion of the queries?
|
|
|
|
|
From this message[^], the second set of queries should work with your current, single-table design. You'd use the first set if you split the data into three tables.
Obviously, you'll need to fix up the table and column names to match your database, and add any missing columns to the queries.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
