|
|
Posted by Jack Jackson on 08/03/05 00:49
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
>>
>
>
>
Navigation:
[Reply to this message]
|