May 7, 2012

What is wrong with this query? Why are results different locally?

Question by StackAttack

I am writing a php site using a mysql database for a class of mine and cannot for the life of me figure out what is wrong with it. I have a query that works locally on my machine (on an identical db to the one on the teacher’s server) but when I upload it, it doesn’t work. The problem is that the query is returning 0 results even though the db has info in it that should be showing.

function bigAssQuery($whereCondition)

    $queries[] = 'CREATE TEMPORARY TABLE subAssignments (SELECT ua.assignmentid, ua.assignmentnum, ua.description
                    FROM Course c JOIN UserAssignment ua ON ua.crn = c.CRN AND ua.term = c.term
                    WHERE c.CRN = "'.$_SESSION["crnum"].'" AND c.term = "'.$_SESSION["mysem"].'")';

    $queries[] = 'CREATE TEMPORARY TABLE subStudents (SELECT s.studentid, s.lastname, s.firstname
                    FROM Course c JOIN Student s ON s.crn = c.CRN AND s.term = c.term
                    WHERE c.CRN = "'.$_SESSION["crnum"].'" AND c.term = "'.$_SESSION["mysem"].'")';

    $queries[] = 'CREATE TEMPORARY TABLE subRubric(SELECT assignmentid, re.rubricelementid, re.learning_goal_char
                    FROM RubricElement re JOIN RubricAssignmentRelation rar ON re.rubricelementid = rar.rubricelementid)';

    $queries[] = 'CREATE TEMPORARY TABLE subAssignRub(SELECT subAssignments.assignmentid, rubricelementid, learning_goal_char, assignmentnum, description
                    FROM subRubric JOIN subAssignments ON subAssignments.assignmentid = subRubric.assignmentid)';

    $queries[] = 'CREATE TEMPORARY TABLE subAssignRubStud (SELECT *
                    FROM subAssignRub CROSS JOIN subStudents)';

    $queries[] = 'CREATE TEMPORARY TABLE subAssignInstRubStud (SELECT sars.assignmentid, ai.ainstanceid, rubricelementid, learning_goal_char, assignmentnum, description, sars.studentid, lastname, firstname
                    FROM subAssignRubStud sars LEFT JOIN AssignmentInstance ai ON sars.studentid = ai.studentid AND sars.assignmentid = ai.assignmentid)';

    $queries[] = 'CREATE TEMPORARY TABLE subTotal (SELECT assignmentid, siars.ainstanceid, s.ainstanceid As scoreAID, siars.rubricelementid, learning_goal_char, assignmentnum, description, studentid, lastname, firstname, score
                    FROM subAssignInstRubStud siars LEFT JOIN Score s ON siars.ainstanceid = s.ainstanceid AND siars.rubricelementid = s.rubricelementid
                    ORDER BY lastname, assignmentid)';

    $queries[] = 'SELECT *
        FROM subTotal
        '.$whereCondition.' Order By lastname, assignmentnum, learning_goal_char';  


Then when the db is queried the code looks like this. . .

$queries = bigAssQuery($whereCondition);

$result = 1;
foreach($queries as $query)
    $result = $db->query($query);

        echo '<script type="text/javascript"> 
                window.onload=function(){ alert("Error: Could not extract course information. Please try again later."); } 
             </script> ';

$num_rows = $result->num_rows;

I assure you that the local and remote databases are identical. I see no reason why no results are coming back. I did test a few simple temp tables to see if the server wasn’t reading those tables for some reason, but they weren’t an issue in my tests. I would try with nested subqueries, but it gets so convoluted so quickly that I can’t organize it. Maybe there is a better way?
Also, just to clarify the queries aren’t failing, they just aren’t returning anything when I know that they should.
I apologize for the wall of text, but any help is appreciated.

EDIT: I really don’t know which of the queries the problem lies. I do know that I’m probably missing some important information. Part of that lies in my web inexperience. I test locally first because I’ve got the debugger working, but I honestly don’t know how to do remote debugging. I’m using netbeans and xdebug. If someone could suggest a how to get remote debugging set up I would probably be able to come up with some better data. Any suggestions would be helpful

EDIT AGAIN: Found the problem. Embarrassingly enough it was an error in data entry; one of my foreign keys was incorrectly entered. Thanks everybody for pointing me in the right direction.

Answer by Starx

On having a quick look, your code is stoping the execution of the PHP inappropriately. You should at least the let the remainder to continue. Simply exit out of loop using break; instead.

    echo '<script type="text/javascript"> 
            window.onload=function(){ alert("Error: Could not extract course information. Please try again later."); } 
         </script> ';
    break; //exit the loop only NOT THE PHP's Execution

Furthermore, check every query individually and run them separately on phpMyAdmin to see, if they are executing correctly. Find the break point and fix the error.


Please fill the form - I will response as fast as I can!