SQL Query Problem

[obrienkev]

I have 3 tables:
course{course_ID, course}
date{date_ID, date}
details{course_ID, date_ID, completed}

The details table links the course and date table.

Basically I want to select all the data from the course table.

Then I want to select all the data from the date table, which is linked to each course.

Make sense?

So something like this???...

select * from course as c, date as d, details as de where de.date_ID = de.course_ID

[saraadmin]

Is this what you need to do:

SELECT * FROM course, date, details WHERE details.course_ID = course.course_ID AND details.date_ID = date.date_ID

[obrienkev]

Hi saraadmin,

Basically I have two select menus. The first menu should contain a list of courses got from the course table.
The second menu should hold the dates relevant to each selected course.

In my database the course table and the date table are linked via the details table...
course{course_ID, course}
date{date_ID, date}
details{course_ID, date_ID, completed}

How should I go about this?

Here is my code...

<?php
    /*
    ** Class: chainedSelectors
    ** Description: This class allows you to create two selectors.  Selections
    ** made in the first selector cause the second selector to be updated.
    ** PHP is used to dynamically create the necessary JavaScript.
    */

//These constants make the code a bit more readable.  They should be
//used in in the creation of the input data arrays, too.
define("CS_FORM", 0);
define("CS_FIRST_SELECTOR", 1);
define("CS_SECOND_SELECTOR", 2);

define("CS_SOURCE_ID", 0);
define("CS_SOURCE_LABEL", 1);
define("CS_TARGET_ID", 2);
define("CS_TARGET_LABEL", 3);

class chainedSelectors
{
    /*
    ** Properties
    */

    //Array of names for the form and the two selectors.
    //Should take the form of array("myForm", "Selector1", "Selector2")
    var $names;

    //Array of data used to fill the two selectors
    var $data;

    //Unique set of choices for the first selector, generated on init
    var $uniqueChoices;

    //Calculated counts
    var $maxTargetChoices;
    var $longestTargetChoice;


    /*
    ** Methods
    */

    //constructor
    function chainedSelectors($names, $data)
    {
        /*
        **copy parameters into properties
        */
        $this->names = $names;
        $this->data = $data;

        /*
        ** traverse data, create uniqueChoices, get limits
        */        
        foreach($data as $row)
        {
            //create list of unique choices for first selector
            $this->uniqueChoices[($row[CS_SOURCE_ID])] = $row[CS_SOURCE_LABEL];    

            //find the maximum choices for target selector
            $maxPerChoice[($row[CS_SOURCE_ID])]++;

            //find longest value for target selector
            if(strlen($row[CS_TARGET_LABEL]) > $this->longestTargetChoice)
            {
                $this->longestTargetChoice=strlen($row[CS_TARGET_LABEL]);
            }
        }

        $this->maxTargetChoices = max($maxPerChoice);
    }

    //prints the JavaScript function to update second selector
    function printUpdateFunction()
    {
        /*
        ** Create some variables to make the code
        ** more readable.
        */
        $sourceSelector = "document." . $this->names[CS_FORM] . "." . 
            $this->names[CS_FIRST_SELECTOR];
        $targetSelector = "document." . $this->names[CS_FORM] . "." . 
            $this->names[CS_SECOND_SELECTOR];

        /*
        ** Start the function
        */
        print("function update" .$this->names[CS_SECOND_SELECTOR] . "()\n");

        print("{\n");

        /*
        ** Add code to clear out next selector
        */
        print("\t//clear " . $this->names[CS_SECOND_SELECTOR] . "\n");
        print("\tfor(index=0; index < $this->maxTargetChoices; index++)\n");
        print("\t{\n");
        print("\t\t" . $targetSelector . ".options[index].text = '';\n");
        print("\t\t" . $targetSelector . ".options[index].value = '';\n");
        print("\t}\n\n");
        print("\t" . $targetSelector . ".options[0].selected = true;\n\n");

        /*
        ** Add code to find which was selected
        */
        print("whichSelected = " . $sourceSelector . ".selectedIndex;\n");

        /*
        ** Add giant "if" tree that puts values into target selector
        ** based on which selection was made in source selector
        */

        //loop over each value of this selector
        foreach($this->uniqueChoices as $sourceValue=>$sourceLabel)
        {
            print("\tif(" . $sourceSelector .
                ".options[whichSelected].value == " .
                "'$sourceValue')\n");
            print("\t{\n");

            $count=0;
            foreach($this->data as $row)
            {
                if($row[0] == $sourceValue)
                {
                    $optionValue = $row[CS_TARGET_ID];
                    $optionLabel = $row[CS_TARGET_LABEL];

                    print("\t\t" . $targetSelector .
                        ".options[$count].value = '$optionValue';\n");
                    print("\t\t" . $targetSelector .
                        ".options[$count].text = '$optionLabel';\n\n");

                    $count++;
                }
            }

            print("\t}\n\n");
        }

        print("\treturn true;\n");
        print("}\n\n");

    }

    //print the two selectors
    function printSelectors()
    {
        /*
        **create prefilled first selector
        */
        $selected=TRUE;
        print("<select name=\"" . $this->names[CS_FIRST_SELECTOR] . "\" " .
            "onChange=\"update".$this->names[CS_SECOND_SELECTOR]."();\">\n");
        foreach($this->uniqueChoices as $key=>$value)
        {
            print("\t<option value=\"$key\"");
            if($selected)
            {
                print(" selected=\"selected\"");
                $selected=FALSE;
            }
            print(">$value</option>\n");
        }
        print("</select>\n");

        /*
        **create empty target selector
        */
        $dummyData = str_repeat("X", $this->longestTargetChoice);

        print("<select name=\"".$this->names[CS_SECOND_SELECTOR]."\">\n");
        for($i=0; $i < $this->maxTargetChoices; $i++)
        {
            print("\t<option value=\"\">$dummyData</option>\n");
        }
        print("</select>\n");

    }

    //prints a call to the update function
    function initialize()
    {
        print("update" .$this->names[CS_SECOND_SELECTOR] . "();\n");
    }
}
?>

And selector.php class...

<?php
    /*
    ** get chainedSelectors class
    */
    require("chainedSelectors.php");

/*
** connect to MySQL database
*/
@ $db = mysql_connect('localhost', 'k28775_pfpc', 'pfpcint');
if(!$db)
{
    print("Unable to connect to database!<br>\n");
    exit();
}

	// Select Database
	mysql_select_db('training') or die(mysql_error());


/*
** create input data
*/

//prepare names
$selectorNames = array(
    CS_FORM=>"pickAreaCode", 
    CS_FIRST_SELECTOR=>"state", 
    CS_SECOND_SELECTOR=>"areacode");

//query database, assemble data for selectors
$Query = "SELECT c.*, d.* from course c
				 join 
				 		details dl
							on dl.course_ID = c.course_ID
				 join
				    date d
						  on d.date_ID = dl.date_ID";

	$result = mysql_query($Query) or die(mysql_error());
	$num_results = mysql_num_rows($result);
	echo $num_results;
	echo '<br />'.$Query;

	if(!($DatabaseResult = mysql_query($Query, $db)))
{
    print("The query failed!<br>\n");
    exit();
}
//print_r($DatabaseResult);

while($row = mysql_fetch_object($DatabaseResult))
{
    $selectorData[] = array(
        CS_SOURCE_ID=>$row->ID, 
        CS_SOURCE_LABEL=>$row->Name, 
        CS_TARGET_ID=>$row->Code, 
        CS_TARGET_LABEL=>$row->Code);
}            

//instantiate class
$areacode = new chainedSelectors(
    $selectorNames, 
    $selectorData);
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html40/loose.dtd">
<html>
<head>
<title>Dynamic Selector</title>
<script type="text/javascript" language="JavaScript">
<?php
    $areacode->printUpdateFunction();
?>
</script>
</head>
<body>
<form name="pickAreaCode" action="testChainedSelectors.php">
<?php
    $areacode->printSelectors();
?>
<input type="submit">
</form>
<script type="text/javascript" language="JavaScript">
<?php
    $areacode->initialize();
?>
</script>
</body>
</html>

Thanks very much.

Kevin.

p.s. code got from zend.com tutorial

Here is the query they use...

$Query = "SELECT s.ID, s.Name, a.Code " .
    "FROM areacode a, state s " .
    "WHERE a.State = s.ID " .
    "ORDER BY s.Name, a.Code";

[saraadmin]

In my database the course table and the date table are linked via the details table...
course{course_ID, course}
date{date_ID, date}
details{course_ID, date_ID, completed}

Can I just clarify your database schema: you have a many-to-many relationship between 'courses' and 'dates' which is implemented using 'details' as a link table?

Basically I have two select menus. The first menu should contain a list of courses got from the course table.
The second menu should hold the dates relevant to each selected course.

I'm not quite sure what format the data structure required by your PHP classes needs to be in, but if I was doing something like this I would probably use two separate queries. The first would select all the courses:

SELECT * FROM courses

and be processed by a PHP loop; inside which I would select all the linked 'dates' table fields:

SELECT * FROM dates, details WHERE details.course_ID="$current_course_ID" AND dates.date_ID=details.date_ID

(PS. was the last SQL query I suggested any good?)