Nodral Posted February 10, 2011 Share Posted February 10, 2011 Hi I am trying to get information from several tables within a database and produce a comparative statement that will produce a table for me. I have 3 tables which contain the following useful columns mdl_scorm ID course name [*]mdl_scorm_scoes_track ID userid(relates to mdl_user.id) scormid(relates to mdl_scorm.id) element value timemodified [*]mdl_user id firstname lastname I am trying to get all 'name' from mdl_scorm and put into a HTML table, then against each one put the value for a particular user and the time modified from mdl_scorm_scoes_track. If there is no entry in the mdl_scorm_scoes_track I need it to say incomplete. I seem to be going round in circles with this and never acheiving the required result. Please could someone have a look at the code below and offer any advice. mailout.php simply sends an email of the table to a designated address, config.php sets up a load of sitewide variables, the only one which applies to this is $USER which relates to mdl_user.id to find the current user. If you need any more info let me know. <head> <title>Induction Results</title> </head> <body><div align="center"> <?php if (isset ($_POST['return'])) { header( 'Location: http://www.mysite.co.uk' ) ; exit(); } include_once "../config.php"; include_once "../induction_output/Rmail.php"; $username = $USER->firstname . " " . $USER->lastname . " "; //BB_ Where header and CSS are printed from print_header("$site->fullname: $loginsite", $site->fullname, $navigation, $focus, '', true, '<div class="langmenu">'.$langmenu.'</div>'); ?> <?php $userid = $USER->id; echo $username . "has completed the following modules<br /><br/>"; $course = "SELECT id, name from mdl_scorm where course = 219"; $firstname = "SELECT firstname, lastname FROM mdl_user WHERE id = $userid"; $course = mysql_query($course); $table = "cmi.core.lesson_status"; echo '<table border="1" cellpadding="10"><tr><td>Module Name</td><td>Status</td><td>Completion Date</td></tr>'; while ($course_row = mysql_fetch_assoc($course)) { echo "<tr><td>" . $course_row['name'] . "</td>"; $user_value = 'SELECT userid, timemodified, value FROM mdl_scorm_scoes_track WHERE element ="cmi.core.lesson_status" AND scormid = ' . $course_row['id']; $user_value = mysql_query($user_value); if (!isset ($user_value)){ echo "help"; } while($process = mysql_fetch_assoc($user_value)){ if ($process['userid']==$userid) { echo "<td>" . $process ['value'] . "</td><td>" . $process['timemodified'] . "</td>"; } else { echo "<td>Incomplete</td><td></td>"; } } echo "</tr>"; } echo "</table>"; if (isset ($_POST['happy'])) { include_once "../induction_output/mailout.php"; exit(); } else { echo '<form action="" method="post">'; echo '<input type="submit" name="happy" value="Confirm Details and Send to Human Resources"></input>'; echo '</form>'; } ?> <form action="" method="post"> <input type="submit" name="happy">Confirm Details and Send to Human Resources</input> </form> </div></body> Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted February 10, 2011 Share Posted February 10, 2011 What you're looking for uses JOINs to run a single query against multiple tables for a specific result set based on all tables being linked through one or more common fields and the combined result set matching the criteria. JOINs are one of the most common functions in SQL, and you really should research them in depth in order to get a good understanding of them. You really want to avoid nesting queries inside of loops, it's potential to cause serrious problems is quite high. Quote Link to comment Share on other sites More sharing options...
Nodral Posted February 10, 2011 Author Share Posted February 10, 2011 I was thinking down those lines and I can do basic joins where I select values from one table based on values within linked other tables. However, how do I select 1 value from 1 table and another value from another table based on values in linked tables. Sorry about this, I'm a bit of a newbie to mysql Quote Link to comment Share on other sites More sharing options...
fenway Posted February 13, 2011 Share Posted February 13, 2011 You mean a JOIN? Quote Link to comment Share on other sites More sharing options...
Nodral Posted February 14, 2011 Author Share Posted February 14, 2011 I can do a JOIN. What I don't undderstand is how to extract data from 2 table based on the same conditons. ie if conditions in rows in tables a & b are true, extract line data from tables b & c Quote Link to comment Share on other sites More sharing options...
fenway Posted February 16, 2011 Share Posted February 16, 2011 I don't understand what you mean. Quote Link to comment Share on other sites More sharing options...
Nodral Posted February 16, 2011 Author Share Posted February 16, 2011 I can do this for example SELECT mdl_question.id, questiontext FROM mdl_question INNER JOIN mdl_quiz_question_instances ON mdl_question.id = question INNER JOIN mdl_quiz ON mdl_quiz_question_instances.quiz = mdl_quiz.id WHERE course = 233. But how do I SELECT from 2 Tables, eg SELECT Name (from table A) AND SELECT address (from table B) WHERE id = 6 (in table A) AND id = 12(in Table B) Quote Link to comment Share on other sites More sharing options...
fenway Posted February 17, 2011 Share Posted February 17, 2011 Don't try and describe what you want in pseudo-SQL -- explain it in plain Engilsh. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.