Jump to content

Select from several tables


Nodral

Recommended Posts

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>

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.