[SOLVED] Registration Capacity

[rwalsh]

Hi there, I am working on an event registration system. What I want to achieve is that when the capacity for the event session is full the option is no longer in the <select> tag. So basically it is filtered out from the Query.

 

The query below works for getting the values accross...

 

SELECT caps.name, caps.cap, count(reg.count) as reg_live
FROM caps INNER JOIN reg ON caps.name=reg.option
WHERE caps.cap <= reg_live
GROUP BY caps.name
ORDER BY caps.name

 

... but once the value is reached for the caps.cap column, all the data disappears.

 

I know this probably is simple and I am just over looking it, but I've been tearing out my hair for the past few days trying to figure it out.

 

Any help would be appreciated.

 

[rwalsh]

So I had my WHERE clause a bit backwords

 

It should be

SELECT caps.name, caps.cap, count(reg.count) as reg_live
FROM caps INNER JOIN reg ON caps.name=reg.option
WHERE reg_live <= caps.cap
GROUP BY caps.name
ORDER BY caps.name

 

Also by doing this I get the error Unknown column 'reg_live' in 'where clause'. Which is understandable. But if I put any quotes around the alias it still doesn't work. I either get all the values (usually when using a single quote) or I get an unexpected T_STRING error.

 

[kickstart]

Hi

 

Try this:-

 

SELECT caps.name, caps.cap
FROM caps a
WHERE cap > (SELECT COUNT(*) FROM reg b where a.name = b.option)
ORDER BY name

 

All the best

 

Keith

[rwalsh]

That worked, with a little modification.

SELECT a.name, a.cap
FROM caps as a
WHERE cap > (SELECT COUNT(*) FROM reg b where a.name = b.option)
ORDER BY name

 

It was telling me there was no field for caps.name, and caps.cap, so I just changed them to a.name and a.cap and that worked!

 

Thanks a bunch, I knew it wasn't as complex of a function as I thought!

 

[rwalsh]

As I noted before this worked great. Now I need to take this a step forward from another system I have developed that is very similar. The context is the same, except I need to pull the counts from multiple fields on the reg table, ie session_1, session_2, etc up to 25. Would I use the same code, over and over for each session peace to be less than the capacity for that specific session, or is there an andor sort of function I could use?

[kickstart]

Hi

 

Think you would have to repeat it, although might be better to have the counts seperated onto a different table than reg, with one line per count.

 

All the best

 

Keith