| Index
» Projects
» phpMyEdit
» Forum
» Need help with SQL expression |
 |
plemieux
Joined: 11 May 2005
Posts: 20
Posted: 2005-09-12 15:23
Back to top
Reply with quote
|
I am dealing with a table with a large number of records (a list of course) in which there is a parent/child relationship. I don't want to reference the table to build a select menu because of the large number of records. Since each course has an arbitrary number used to identify it, I would like to make the user write this number as a reference and then convert it to the id when recording (in order to maintain referential integrity if the course number happens to change). Of course, I also need to convert it back to course number on read.
I tried:
| Code: |
$opts['fdd']['parent'] = array(
'name' => 'Parent',
'select' => 'T',
'maxlen' => 10,
'sort' => true,
'help' => 'XXX-XXX-XX',
'sql' => '(select numero from cours where id = $val)',
'sqlw' => 'if($val!="",(select id from cours where numero = "$val"),$val)'
); |
but neither read or write work...
|
plemieux
Joined: 11 May 2005
Posts: 20
Posted: 2005-09-12 16:58
Back to top
Reply with quote
|
Ok. Done half the trip. The read subquery now work:
| Code: |
$opts['fdd']['parent'] = array(
'name' => 'Cours parent (numéro)',
'select' => 'T',
'maxlen' => 10,
'default' => '0',
'sort' => true,
'help' => 'XXX-XXX-XX',
'sql' => 'parent as parentid, if(parent != 0, (select numero from cours where id = parentid), parent)',
'sqlw' => 'if($val != 0,(select id from cours where numero = "$val"),$val)'
);
|
The query needed a reference in order to work.
Now for the write, error message is:
| Quote: |
| You can't specify target table 'cours' for update in FROM clause |
Any hints?
|
plemieux
Joined: 11 May 2005
Posts: 20
Posted: 2005-09-12 20:48
Back to top
Reply with quote
|
Finally, I have found it cannot be done because:
Use a trigger instead.
|
|
Post new topic
Reply to topic
|
|
|
|
Login
Registration
Slovak
