Solved

How do I add an additional table to a PHP script

Posted on 2009-04-14
10
165 Views
Last Modified: 2013-12-13
I am modifying a piece of PHP code to include data from a third table.  I have tried to link the third table (mdl_user_info_data); however it returns no rows, when there should be the same no of rows as when it was without the link.

I have written the sql in MySql and it returns the correct rows.

I'm not sure how to proceed from here
Cheers
Bernard
In PHP:
 

$select .= 'qa.uniqueid AS attemptuniqueid, qa.id AS attempt, ' .

                    'u.id AS userid, u.idnumber, u.department, u.firstname, u.lastname, u.picture, u.imagealt,uid.data,  ' .

                    'qa.sumgrades, qa.timefinish, qa.timestart, qa.timefinish - qa.timestart AS duration ';
 

            // This part is the same for all cases - join users and quiz_attempts tables

            $from = 'FROM '.$CFG->prefix.'user u '; 

		$from .= 'LEFT JOIN '.$CFG->prefix.'quiz_attempts qa ON qa.userid = u.id AND qa.quiz = '.$quiz->id;

            $from .= 'LEFT JOIN '.$CFG->prefix.'user_info_data uid ON uid.userid = u.id AND uid.fieldid =2';
 
 

In MySql:
 

select  qa.uniqueid AS attemptuniqueid , qa.id AS attempt, u.id AS userid, u.idnumber, u.department

, u.firstname, u.lastname, u.picture, u.imagealt, uid.data as 'Site Location', qa.sumgrades, qa.timefinish

, qa.timestart, qa.timefinish - qa.timestart AS duration 

 FROM mdl_user u  

LEFT JOIN mdl_quiz_attempts qa ON qa.userid = u.id 

LEFT JOIN mdl_user_info_data uid ON uid.userid = u.id  and uid.fieldid=2

where qa.quiz =60

Open in new window

0
Comment
Question by:BernardGBailey
  • 6
  • 4
10 Comments
 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
Can you post the final query from PHP?  It is hard to tell if the two queries are identical from the code you provided.
0
 

Author Comment

by:BernardGBailey
Comment Utility
Hi routinet,

The query from PHP is part of 15 pages of code.  I've reposted below all elements around the php query

Cheers
Bernard
 // Construct the SQL

            $select = 'SELECT '.sql_concat('u.id', '\'#\'', $db->IfNull('qa.attempt', '0')).' AS uniqueid, ';

            if ($qmsubselect) {

                $select .=

                    "(CASE " .

                    "   WHEN $qmsubselect THEN 1" .

                    "   ELSE 0 " .

                    "END) AS gradedattempt, ";

            }
 

            $select .= 'qa.uniqueid AS attemptuniqueid, qa.id AS attempt, ' .

                    'u.id AS userid, u.idnumber, u.department, u.firstname, u.lastname, u.picture, u.imagealt,uid.data, ' . 

                    'qa.sumgrades, qa.timefinish, qa.timestart, qa.timefinish - qa.timestart AS duration ';
 

            // This part is the same for all cases - join users and quiz_attempts tables

            $from = 'FROM '.$CFG->prefix.'user u '; 

		$from .= 'LEFT JOIN '.$CFG->prefix.'quiz_attempts qa ON qa.userid = u.id AND qa.quiz = '.$quiz->id;

            $from .= 'LEFT JOIN '.$CFG->prefix.'user_info_data uid ON uid.userid = u.id AND uid.fieldid =2';
 

		if ($qmsubselect && $qmfilter){

                $from .= ' AND '.$qmsubselect;

            }

            switch ($attemptsmode){

                case QUIZ_REPORT_ATTEMPTS_ALL:

                    // Show all attempts, including students who are no longer in the course

                    $where = ' WHERE qa.id IS NOT NULL AND qa.preview = 0';

                    break;

                case QUIZ_REPORT_ATTEMPTS_STUDENTS_WITH:

                    // Show only students with attempts

                    $where = ' WHERE u.id IN (' .$allowedlist. ') AND qa.preview = 0 AND qa.id IS NOT NULL';

                    break;

                case QUIZ_REPORT_ATTEMPTS_STUDENTS_WITH_NO:

                    // Show only students without attempts

                    $where = ' WHERE u.id IN (' .$allowedlist. ') AND qa.id IS NULL';

                    break;

                case QUIZ_REPORT_ATTEMPTS_ALL_STUDENTS:

                    // Show all students with or without attempts

                    $where = ' WHERE u.id IN (' .$allowedlist. ') AND (qa.preview = 0 OR qa.preview IS NULL)';

                    break;

            }
 

            $countsql = 'SELECT COUNT(DISTINCT('.sql_concat('u.id', '\'#\'', 'COALESCE(qa.attempt, 0)').')) '.$from.$where;

Open in new window

0
 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
There lies the problem.  We need to compare the query that does return data to the query that doesn't.

In your code, you'll have a line to execute the query.  It might look something like this:

$result = mysql_query($query);

Change that to this:

echo $query;  // or error_log($query)
$result = mysql_query($query);

Post that query here, as well as the query that you know returns good results.
0
 

Author Comment

by:BernardGBailey
Comment Utility
Hi routinet,

I'm sorry if I cannot see what you asked me to provide.

I'm trying to add one field from an additional table.   When I use this snippet

 // Construct the SQL
            $select = 'SELECT '.sql_concat('u.id', '\'#\'', $db->IfNull('qa.attempt', '0')).' AS uniqueid, ';
            if ($qmsubselect) {
                $select .=
                    "(CASE " .
                    "   WHEN $qmsubselect THEN 1" .
                    "   ELSE 0 " .
                    "END) AS gradedattempt, ";
            }

            $select .= 'qa.uniqueid AS attemptuniqueid, qa.id AS attempt, ' .
                    'u.id AS userid, u.idnumber, u.department, u.firstname, u.lastname, u.picture, u.imagealt, ' . //uid.data, ' .
                    'qa.sumgrades, qa.timefinish, qa.timestart, qa.timefinish - qa.timestart AS duration ';

            // This part is the same for all cases - join users and quiz_attempts tables
            $from = 'FROM '.$CFG->prefix.'user u ';
            $from .= 'LEFT JOIN '.$CFG->prefix.'quiz_attempts qa ON qa.userid = u.id AND qa.quiz = '.$quiz->id;
//            $from .= 'LEFT JOIN '.$CFG->prefix.'user_info_data uid ON uid.userid = u.id AND uid.fieldid =2';


The correct result comes up.

When I use this  (I removed the // from lines 12 & 17) I get no records returned

Does this make more sense?

Cheers
Bernard    


    // Construct the SQL

         $select = 'SELECT '.sql_concat('u.id', '\'#\'', $db->IfNull('qa.attempt', '0')).' AS uniqueid, ';

            if ($qmsubselect) {

                $select .=

                    "(CASE " .

                    "   WHEN $qmsubselect THEN 1" .

                    "   ELSE 0 " .

                    "END) AS gradedattempt, ";

            }
 

            $select .= 'qa.uniqueid AS attemptuniqueid, qa.id AS attempt, ' .

                    'u.id AS userid, u.idnumber, u.department, u.firstname, u.lastname, u.picture, u.imagealt, uid.data, ' . 

                    'qa.sumgrades, qa.timefinish, qa.timestart, qa.timefinish - qa.timestart AS duration ';
 

            // This part is the same for all cases - join users and quiz_attempts tables

            $from = 'FROM '.$CFG->prefix.'user u '; 

		$from .= 'LEFT JOIN '.$CFG->prefix.'quiz_attempts qa ON qa.userid = u.id AND qa.quiz = '.$quiz->id;

            $from .= 'LEFT JOIN '.$CFG->prefix.'user_info_data uid ON uid.userid = u.id AND uid.fieldid =2';

Open in new window

0
 

Author Comment

by:BernardGBailey
Comment Utility
The second line is line 18, not 17 as mentioned.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:BernardGBailey
Comment Utility
Some more detail here,

I've looked right through the php script and not found any relationship from the newly selected field to the table it is to show in.

This is probably why I get "Nothing to display".

Obviously the link from the select statement to the display is somewhere else or obsured so I cannot recognise it somehow in the script.  

Comments?

Cheers
Bernard
0
 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
Do you know if the query is returning an error?  I think it might be.  Change line 18 to add a space before the LEFT JOIN:

 $from .= ' LEFT JOIN '.$CFG->prefix.'user_info_data uid ON uid.userid = u.id AND uid.fieldid =2';

Open in new window

0
 

Author Comment

by:BernardGBailey
Comment Utility
routinet,

That is fantastic.  It works fine, I've been able to pull the data through to the export table as required.

But why the space?

Cheers
Bernard

0
 
LVL 50

Accepted Solution

by:
Steve Bink earned 500 total points
Comment Utility
Without the space, that section of the query looks like this:

ts qa ON qa.userid = u.id AND qa.quiz = 60LEFT JOIN user_info_data uid ON uid.userid = u.id AND uid.fieldid =2

That would result in a syntax error, since qa.quiz looks to be a numeric field testing against unquoted string data (60LEFT).  Even assuming the syntax went through, the parser would consider this an INNER JOIN (since LEFT would no longer be considered a join modifier), which would eliminate any rows not matching the final table.
0
 

Author Closing Comment

by:BernardGBailey
Comment Utility
Routinet,

 I read your profile and yes you are worthy of the title "GENIUS"

Thank you for putting your experience out there for us.

Cheers
Bernard  
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now