>>>>> "C" == "Claudia" <[EMAIL PROTECTED]> writes:
> I would like to sum totals for unknown values.
> Here is the design
> Course names are entered in separate table -- courses If the course
> is still being tested the course status = open This status is
> stored in the Course table. Each course name is unique in this
> table.
> Test information including test time is entered in a different
> table -- issues The issues table includes the test time and course
> name values. Several entries with the same course name will be
> made to this table
> I would like to sum the total time tested based on Open courses.
> First I would like to know how to select the correct courses from
> the issues database based on the course_status in the courses
> databases.
> Would the following statement work??
> $query = "select issues.course_name, issues.time_tested from issues
> where courses.course_status = 'open'";
Almost, but you need the courses table in the 'from' part of the query
and you need some kind of relation between the two tables.
$query = "SELECT issues.course_name, issues.time_tested " .
"FROM issues, courses " .
"WHERE courses.course_status = 'open' AND "
"courses.course_name = issues.course_name";
As an aside, it's usually not a good idea to use something like a name
to relate two tables. It would have been better to give each course an
ID number and made that the primary key for the courses table. And then
stored the ID for the relevant course in the issues table instead of the
name. Your query would only change slightly, to something like this:
$query = "SELECT courses.course_name, issues.time_tested " .
"FROM issues, courses " .
"WHERE courses.course_status = 'open' AND "
"courses.course_id = issues.course_id";
But it would let you correct typos in the course names without worrying
about the issues table. It would also let you have more than one course
of the same name (one current 'open' course and several old ones perhaps)
> Then I need to sum the time_tested fields for all courses with the
> same name in the issues database that were just selected as being
> Open.
By the sounds of it, you could let the database do all the work, by
changing your query to something like this:
$query = "SELECT issues.course_name, SUM(issues.time_tested) AS total_time " .
"FROM issues, courses " .
"WHERE courses.course_status = 'open' AND " .
"courses.course_name = issues.course_name " .
"GROUP BY issues.course_name";
The result would be a single entry for each course with the total time.
hope this helps
-robin
--
Robin Vickery.................................................
BlueCarrots, 14th Floor, 20 Eastbourne Terrace, London, W2 6LE
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
