Page 1 of 1

Mathematical Query

Posted: Thu Oct 30, 2003 11:16 am
by AliasBDI
I have a database (MySQL) created with a table called "registrations" which records the registration information of users. I am only allowing a certain amount of users to register per "course". Right now, I'm displaying a table of all registrations. A user can click on a registration button (next to each registration) and sign up for it.

However, I want the registration button to show a different button (which reads BOOKED) when the registered course is full. The amount determining the "full" course is found in a table called "schedules."

So, here is a summary of tables and definitions:
registrations = table of records of registrations
schedules = table of records of courses
amount = field with registration limit (number) inside the "schedules" table

The list will show all schedules with REGISTER or BOOKED buttons. The BOOKED buttons are inactive (no link). The REGISTER are linked (already done).

So, a query will check the number of records (in the "registrations" table) of a specific "course" and see if it is greater or less than the "amount" set in the "course.amount" field.

If is 'less than', display REGISTER button. If 'equal to' or 'more than' display BOOKED button.

Understood? Hard to explain. But anything will help. I just need to know where to begin.

Right now I have the list displayed of all "courses" with all having the ability to REGISTER no matter what.

Posted: Thu Oct 30, 2003 6:09 pm
by McGruff
In the "register" case, create a form with a "register" submit button.

In the "booked" case, just display the data - no form.

I'd maybe create a couple of fns:

buildRegisterRow($row_data)
buildBookedRow($row_data)

As you loop through a (registrations? schedules?) query:

Code: Select all

<?php

$rows = ''; // initialise

while($result = mysql_fetch..etc..)
{
    if(..fully booked..)
    {
        $rows .= buildRegisterRow($result);

    } else {

        $rows .= buildBookedRow($result);
    }
}

?>

Posted: Tue Nov 04, 2003 1:26 pm
by Weirdan

Code: Select all

create table registrations (id int not null auto_increment, person_id int not null, course_id int not null, primary key(id));
create table courses (id int not null auto_increment, lmt int not null, primary key(id));
-- persons table defined elsewhere...
-- you can add more fields
-- fill the table with data, then:

select courses.id, if(count(registrations.id)>=lmt,"<font color=red>booked</font>","<a href=reg.php>register</a>") as status from courses left join registration on courses.id=registrations.course_id group by courses.id order by status;

-- this query will give you  formatted cell data, which you can
-- insert directly into the <td> tag.
PS: tested on MySQL 4.0.14