Is this possible?

Client Joined: Oct 07, 2003 Posts: 735 Location: Ohio

I don't know if this is supported in MySQL. At least the version I am on.

4.1.1

Code:




$sql =$db->sql_query("INSERT INTO ".$prefix."_tl_session_grades (Session_ID, SOMS_KEY, UID, Group_ID, IRAT_Grade)


(SELECT a.Session_ID, b.SOMS_KEY, a.StudentID, c.Group_ID, a.Total_Percent FROM ".$prefix."_tl_session_grade_import a


JOIN ".$prefix."_tl_students b ON (a.StudentID = b.U_Account)


JOIN ".$prefix."_tl_group_students c ON (b.SOMS_KEY = c.SOMS_KEY)


AND a.Session_ID = '$Session_ID'");


if (!$sql) {echo("<p>Error performing query: " . mysql_error() . "</p>");}

Posted: Wed Dec 19, 2007 7:15 pm

Whew, you are well beyond me in the complexity of that proposed SQL statement. I've spent the last 20 minutes looking at various versions of the MYSQL manual and it has taught me a lot, but not enough to answer your question. We'll need a true Guru to visit to provide enlightenment.

And, I am glad you have that echo of the error condition if it happens. That's a great step forward. But how about stuffing the whole sql statement into a variable that can be echoed out too? Break this stuff down step by step so you see exactly how the SQL you are trying to generate gets interpreted right before MYSQL tries to execute it. Where you have "$sql =$db->sql_query(" ... that is where I think it would be best to have just $sql = ""INSERT INTO ".$prefix."_tl_session_grades (Session_ID, ... bla bla and then say $result = $db->sql_query($sql).

The variables $sql and $result don't have any intrinsic value and could just as well be $foobar and $foobar1 but I'm concerned there is confusion going on about their function. You are using $sql the way Nuke programmers typically use $result.

Anyway, if you could "capture" what is being put into a separate $sql variable and echo it, it would show exactly what MYSQL is looking at after all the variable substitutions have taken place and give our GURU a better chance of figuring it out.

Posted: Thu Dec 20, 2007 7:54 am

Well I need to grab the SOMS_KEY from the students table, and Group_ID from the groups table hence the joins to

Code:

JOIN ".$prefix."_tl_students b ON (a.StudentID = b.U_Account)


JOIN ".$prefix."_tl_group_students c ON (b.SOMS_KEY = c.SOMS_KEY)

I have the StudentID in the imported table so I use that to join on, and to get the correct Group_ID I match up the SOMS_KEY from the Group table and Students table.

I had been using the following but could not get it to work.

Code:

$getallinfo = $db->sql_query("SELECT * FROM ".$prefix."_tl_session_grade_import a


JOIN ".$prefix."_tl_students b ON (a.StudentID = b.U_Account)


JOIN ".$prefix."_tl_group_students c ON (b.SOMS_KEY = c.SOMS_KEY)


AND a.Session_ID = '$Session_ID'");


if (!$getallinfo) {echo("<p>Error performing query: " . mysql_error() . "</p>");}  


while ($row = $db->sql_fetchrow($getallinfo)) {


      $SOMS_KEY = $row['StudentID']; 


      $Session_ID = $row['Session_ID'];      


      $UID = $row['U_Account'];       


      $IRAT_Grade = $row['Total_Percent'];   


      $Group_ID = $row['Group_ID'];


}         





$sql = $db->sql_query("INSERT INTO ".$prefix."_tl_session_grades (Session_ID, SOMS_KEY, UID, Group_ID, IRAT_Grade, Academic_Year)". "VALUES ('$Session_ID', '$SOMS_KEY', '$UID', '$Group_ID', '$IRAT_Grade', '$Academic_Year')");


if (!$sql) {echo("<p>Error performing query: " . mysql_error() . "</p>");}

Posted: Thu Dec 20, 2007 8:44 am

I hate to sound like a broken record, but the way you are coding this you don't know if your sql statement is being formed the way you intend it be to formed or whether it is going to MYSQL exactly as you intend and just not working (generating a MYSQL error). You also don't tell us if you are seeing a MYSQL error. Folks will be better able to help if you could echo out exactly what the sql statement is that's going to sql_query. And if there's an error message what that message is.

Site Admin/Owner Joined: Aug 27, 2002 Posts: 17088

This is a MySQL question and not a PHP question so I am moving the thread.

Without understanding the schema and the relationships between the tables, I would suggest that you break this down into separate/simpler steps and store the interim results in temporary tables. Then, once you get it working, you can see if you need/want to make it so complex. In other words keep it simple.

Posted: Thu Dec 20, 2007 9:14 am

My latest method of inserting these records:

Code:




$getallinfo = "SELECT * FROM ".$prefix."_tl_session_grade_import a


JOIN ".$prefix."_tl_students b ON (a.StudentID = b.U_Account)


JOIN ".$prefix."_tl_group_students c ON (b.SOMS_KEY = c.SOMS_KEY)


WHERE a.Session_ID = '$Session_ID'";


$result = $db->sql_query($getallinfo);


if (!$result) {echo("<p>Error performing query: " . mysql_error() . "</p>");}  


while ($row = $db->sql_fetchrow($result)) {


      $SOMS_KEY = $row['StudentID']; 


      $Session_ID = $row['Session_ID'];      


      $UID = $row['U_Account'];       


      $IRAT_Grade = $row['Total_Percent'];   


      $Group_ID = $row['Group_ID'];


}         





$sql = "INSERT INTO ".$prefix."_tl_session_grades (Session_ID, SOMS_KEY, UID, Group_ID, IRAT_Grade, Academic_Year)". "VALUES ('$Session_ID', '$SOMS_KEY', '$UID', '$Group_ID', '$IRAT_Grade', '$Academic_Year')";


$result = $db->sql_query($sql);


if (!$result) {echo("<p>Error performing query: " . mysql_error() . "</p>");}

Currently all that is getting inserted is the $Academic_Year

I am getting no error message.

I inserted

Code:

 die('<br />$sql = '.$sql.'<br />');

in my INSERT query and got back ...

Code:

$sql = INSERT INTO atlas_tl_session_grades (Session_ID, SOMS_KEY, UID, Group_ID, IRAT_Grade, Academic_Year)VALUES ('', '', '', '', '', '0709')

When I use phpmyadmin to test my query

Code:




SELECT b.SOMS_KEY, b.U_Account, a.Total_Percent, a.Session_ID, c.Group_ID  FROM atlas_tl_session_grade_import a


JOIN atlas_tl_students b ON (a.StudentID = b.U_Account)


JOIN atlas_tl_group_students c ON (b.SOMS_KEY = c.SOMS_KEY)


WHERE a.Session_ID = '8'

I get 104 records returned.

grrrrr.

This is going to bug me until I get back to work in January. Taking off Saturday for Florida.

WooHoo!

Posted: Fri Dec 21, 2007 8:23 am

LOL. I was thinking about your problem and came here to suggest that you use Phpmyadmin to test your queries. You beat me to it.

Looking again at your code, doesn't the insert statement have to be inside the while loop? That's the most immediate thing I see. The other suggestion I'd have is to try the join query out in phpmyadmin.

Posted: Fri Dec 21, 2007 8:32 am

Strange the INSERT query doesn't seem to pick up the value of Session_ID

Code:




$Session_ID = $_GET['Session_ID'];

However it does pick it up previously as the following code works.

Code:




//Check the Students UID and compare to the imported table


$checkUID = $db->sql_query("SELECT a.StudentID, b.U_Account  FROM ".$prefix."_tl_session_grade_import a


JOIN ".$prefix."_tl_students b 


WHERE a.StudentID != b.U_Account


AND a.Session_ID = '$Session_ID'");





$error_total = $db->sql_numrows($checkUID);

Posted: Fri Dec 21, 2007 10:21 am

Well I got this working, and before I take my long holiday break. WOOHOO!

Here is the completed page.

Code:

<?php


/************************************************************************/


/* Academic Team Learning & Assessment System                           */


/* ==========================================                           */


/* Author: Steven Donovan                                               */


/* Web Developer                                                        */


/* Boonshoft School of Medicine                                         */


/* 10/25/2007                                                           */


/*                                                                      */


/************************************************************************/


if(!defined('TL_ADMIN')) { die("Illegal Access Detected!!!"); }


$modname = "Team_Learning";


include("header.php");


TLAdminMenu();





$Session_ID = $_GET['Session_ID'];


//Get the academic year


$Get_Academic_Year = $db->sql_query("SELECT Academic_Year FROM ".$prefix."_tl_config");  


while ($info = $db->sql_fetchrow($Get_Academic_Year)) {


      $Academic_Year = $info['Academic_Year']; 


      }


      


//Check the Students UID and compare to the imported table


$checkUID = $db->sql_query("SELECT a.StudentID, b.U_Account  FROM ".$prefix."_tl_session_grade_import a


JOIN ".$prefix."_tl_students b 


WHERE a.StudentID != b.U_Account


AND a.Session_ID = '$Session_ID'");





$error_total = $db->sql_numrows($checkUID);


//We have unmatched UID's!


if ($error_total > 0) {


OpenTable();


echo"<tr><td>There are errors in the IRAT imported table.  These need to be fixed before writing to the grades table.  Ensure all student UID's match for all current students already in the system.</td></tr>";


echo"<tr><td><input type=\"button\" value=\"Back\" onClick=\"history.go(-1)\"></td></tr>";


Closetable();


}else{


//No errors detected so import IRAT grades from _tl_session_grade_import into _tl_session_grades table.





$sql = ("INSERT INTO ".$prefix."_tl_session_grades (Session_ID, SOMS_KEY, UID, Group_ID, IRAT_Grade)


(SELECT a.Session_ID, b.SOMS_KEY, a.StudentID, c.Group_ID, a.Total_Percent FROM ".$prefix."_tl_session_grade_import a


JOIN ".$prefix."_tl_students b ON (a.StudentID = b.U_Account)


JOIN ".$prefix."_tl_group_students c ON (b.SOMS_KEY = c.SOMS_KEY)


JOIN ".$prefix."_tl_session d


WHERE a.Session_ID = d.Session_ID)");


$result = $db->sql_query($sql);


if (!$result) {echo("<p>Error performing query: " . mysql_error() . "</p>");} 





//Get the academic year and update table


$Get_Academic_Year = $db->sql_query("UPDATE ".$prefix."_tl_session_grades SET Academic_Year = (SELECT Academic_Year FROM ".$prefix."_tl_config)"); 


}     


header("Location: ".$admin_file.".php?op=TLViewIratGrades&amp;Session_ID='$Session_ID'");





?>

Instead of using the passed Session_ID from the url (should have worked) I joined it to the tl_session table and matched there instead.

Code:




WHERE a.Session_ID = d.Session_ID)");

Then used the sub query I constructed and then updated after with the academic year value.

After the holidays we are setting up another server with the latest "stable" PHP and MySQL so I will then move all this over to the new server and have more stuff to play with. Cool

Subqueries are great.