Ah, I had left out the third column the first time! Thanks.
Now I can insert and not create dupes but for some reason it is not
updating.
Here's the code:
if ( ($_POST['action'] == 'process') && (!sizeof($message) ) )
{
foreach($_POST as $key=>$val)
{
//find key/val sets within posts which are both numeric
if(is_numeric($key) && is_numeric($val))
{
$nkey=$key;
//add these values ( q_id, a_id ) to sql statement
$qanda[] .= "('1' , '" . $nkey . "' , '" . $val .
"')";
}
//find key/val sets within sub-arrays of $_POST
which are numeric
if(is_array($val))
{
foreach ($val as $akey=>$aval)
{
//add these values ( q_id, a_id ) to sql
statement
$qanda[] .= "('1' , '" . $key . "' , '" .
$aval . "')";
}
}
}
$q_a_sql="INSERT INTO" . $userTable . "(u_id, q_id, a_id )
VALUES " . (implode(",",$qanda)) . "on duplicate key
UPDATE a_id = VALUES(a_id);";
if($q_a_result = mysql_query($q_a_sql))
{
unset($_SESSION['required_fields']);
$cat = $_POST['cat']+1;
include_once(QUESTIONS . 'q.inc');
}
?
Kristen G. Thorson wrote:
How is it your plan "thwarted?" It looks fine to me, but maybe I'm
missing something. The only thing I can think is that you're not
defining your table keys correctly to correctly use ON DUPLICATE KEY.
Do you have a key defined for all three columns *together*?
mysql> create table user_answers (u_id int(11) not null, q_id int(11)
not null, a_id int(11) not null, unique( u_id, q_id, a_id ) );
Query OK 0 rows affected (0.22 sec)
mysql> insert into user_answers (u_id,q_id,a_id) values
(1,1,1),(1,1,2),(1,2,1),(1,1,1) on duplicate key update a_id=values(a_id);
Query OK, 5 rows affected (0.01 sec)
Records: 4 Duplicates: 1 Warnings: 0
mysql>select * from user_answers;
+------+------+------+
| u_id | q_id | a_id |
+------+------+------+
| 1 | 1 | 1 |
| 1 | 1 | 2 |
| 1 | 1 | 3 |
+------+------+------+
3 rows in set (0.00 sec)
So, three different answers for the same user & same question. The one
duplicate did not cause an error because of the ON DUPLICATE KEY. This
looks like it's what you're trying to do, so then what's your error?
kgt
Jack Jackson wrote:
Hi,
Thanks to everyone's help, that multipage monster of a form is now
working properly (yay!).
One problem I have though is that I stick the answers as each page is
completed into a table. If the user hits the back button, rather than
adding a new row to the table I'd rather update it if it's there.
That's fairly straightforward when it's 1:1 questions to answers:
$q_a_sql='INSERT INTO $userAnswerTable
( q_id, a_id )
VALUES ' . (implode(",",$qanda))
. ' on duplicate key UPDATE a_id = VALUES(a_id);';
But when it's 1:n, such as with checkboxes, this neat little plan of
mine is thwarted.
So if I change the userAnswerTable to three columns, u_id, q_id and
a_id, is there a way I can do all the 1:1 and 1:n in the manner I wish?
TIA,
JJ
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
