How to create multiple temporary table

CREATE TEMPORARY TABLE test AS 
            (SELECT subject_id, student_id,score FROM assessments WHERE assessment_type_id=1 );


        CREATE TEMPORARY TABLE exam AS 
            (SELECT subject_id, student_id,score FROM assessments WHERE assessment_type_id=4 );

        SELECT std.id as student_id, std.surname, std.othernames, test.score as test, exam.score as exam_score FROM (SELECT id, surname, othernames FROM students) as std 
            INNER JOIN test ON test.student_id = std.id 
            INNER JOIN exam ON exam.student_id = std.id

I've used DB::select() but it gives an error at the second temporary table creation. This code works perfectly on phpmyadmin. How can i run this query on laravel ?

jlrdw

8 years ago

Level 75

You could use normal PDO with laravels getPdo()

See https://laracasts.com/discuss/channels/guides/getpdo-usage

Outside of laravel I do

$sql = "INSERT INTO g1 (ID, DOGNAME, S, FFID, MMID)"; 
$sql = $sql .  " SELECT ID, DOGNAME, S, FFID, MMID";
$sql = $sql .  " FROM DOGS WHERE FFID = {$dvar}";
$sql = $sql .  " ORDER BY DOGNAME, S";
$q   = $conn->query($sql);

But I always have those tables handy, I truncate after the operation.

I end up with 5 temp tables to loop over to get dog progeny. Like

DECENDANTS OF
WARRINGAHS HOT DIGGORTY,Y,M    16/15
====================================
BEAUTAWNS CAPT.DIGGORITY,B,M   4/5     
.    .ACREWOODS DUE ME JUSTICE,Y,M   2/2316  
.    .ASHWAYS DEAL ME IN BELQUEST,B,F   2/134   
.    .    .BELQUESTS ABBY OF KEEPSAKE,B,F   78/135   
.    .BELQUESTS CAVALIER,B,M   2/3     
.    .    .KEEPSAKES MAGGIE MAE,Y,F   1/34    
HARBORTOPS AUGUST KNIGHT,B,M   4/1422

1 like

Please or to participate in this conversation.