This topic is locked

Button on insert with limited

3/31/2021 3:36:49 AM
PHPRunner General questions
A
aeaton2017 author

I created a button and inserting works fine but i need only need 10 records and not 33 records it does now.. Here is my code on my

Server:

$record = $button->getCurrentRecord();

$sql = "INSERT INTO forge.employee_written_tests
(test_id, question_id, correct, employee_id, complete, date_start)
select test.test_id, test.question_id, test.correct, test.employee_id, test.complete, curdate() from
(SELECT r.testid as test_id, r.id as question_id, '0' as correct, er.employee_id as employee_id, '0' as complete, curdate() FROM quiz_questions_fill as r
LEFT JOIN requirements as req on req.fill_test_id=r.testid
LEFT JOIN employee_requirement as er on er.requirement_id=req.id
left join employees as employ on employ.id=er.employee_id
WHERE (
SELECT COUNT(*) FROM quiz_questions_fill as r1
WHERE r.topics = r1.topics AND r.id < r1.id
) <= 2 and (DATEDIFF((date_add(er.effective_date, interval req.days_until_recurrence day)), CURDATE()))<=20 and er.STATUS='ACTIVE'
and employ.status='Active' and
(employ.id, r.testid) not in (select employee_id, test_id
FROM employee_written_tests where complete is not null and

(DATEDIFF( curdate(), date_start))<=30 )

ORDER BY er.employee_id asc, RAND(r.id)) as test where test.employee_id=".$record["employee_id"];
DB::Exec($sql);

where should I put the limit 10 at?
admin 4/1/2021

My guess is that it will be something like this:

$record = $button->getCurrentRecord();

$sql = "INSERT INTO forge.employee_written_tests (test_id, question_id, correct, employee_id, complete, date_start) select test.test_id, test.question_id, test.correct, test.employee_id, test.complete, curdate() from (SELECT r.testid as test_id, r.id as question_id, '0' as correct, er.employee_id as employee_id, '0' as complete, curdate() FROM quiz_questions_fill as r LEFT JOIN requirements as req on req.fill_test_id=r.testid LEFT JOIN employee_requirement as er on er.requirement_id=req.id left join employees as employ on employ.id=er.employee_id WHERE ( SELECT COUNT(*) FROM quiz_questions_fill as r1 WHERE r.topics = r1.topics AND r.id < r1.id ) <= 2 and (DATEDIFF((date_add(er.effective_date, interval req.days_until_recurrence day)), CURDATE()))<=20 and er.STATUS='ACTIVE' and employ.status='Active' and (employ.id, r.testid) not in (select employee_id, test_id FROM employee_written_tests where complete is not null and
(DATEDIFF( curdate(), date_start))<=30 )
ORDER BY er.employee_id asc, RAND(r.id)) as test where test.employee_id=".$record["employee_id"]. " LIMIT 0, 10";
DB::Exec($sql);