Mathematical Query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
AliasBDI
Forum Contributor
Posts: 286
Joined: Fri Nov 15, 2002 10:35 am
Location: Spring, TX, USA

Mathematical Query

Post 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.
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post 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);
    }
}

?>
Last edited by McGruff on Wed Aug 10, 2005 12:16 pm, edited 1 time in total.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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
Post Reply