Help Pls: html/php/sql form to only echo fields if less than 4 submissions

mipollo

I'm building a volunteer form/database for a non-profit who is looking to only let volunteers sign up if there are less than 4 other volunteers signed up for the same shift time. I have a SQL table set up to collect the individual's personal info and their chosen volunteer time. I set up a secondary table for the html/php form to pull drop down shift times for certain tasks. I only want to show these times if there are spots available (volunteers<4).

I have tried a few different ways like: right now the database has Name, Address, Phone, GateShift, NightShift, etc. If the person chooses GateShift 5-7 it would input to the database as "1" if they choose 7-9pm the database would show "2" if they choose nothing it would be "0". So I tried counting how many instances of "1s" and "2s" there are and if there were less than 4 I would want to show that time. But I couldn't get this to work and I think I might have been going about it the wrong way.

The problem is that there are a ton of different tasks and times associated with the tasks.

Does anyone have a suggestion on the best way to organize something like this and/or how to only echo a form field if the spot is available.

Many thanks for any help!

big_nerd

mipollo,

You can use a MySQL SELECT statement to see how many of a certain value there are.

However, you haven't really given much information as to the database structure, so its difficult to help.

Some questions:

If its a shift based on a task, where are the tasks held? Is it in the same table, or a different one?
You mentioned:
mipollo wrote:
right now the database has Name, Address, Phone, GateShift, NightShift, etc.

So I am unsure of what you mean by 1 or 2 for Gate or Night Shift if they are separate columns.

If you are able to show your CREATE TABLE code (or a quick structure printout) with a quick description of what the columns are for, I am sure someone (including myself) can help you to quickly retrieve a count of available shifts, or propose a structure modification that will allow it.

Once we have the information we can assist you a bit better.

Note: be sure not to place any private or proprietary information here, if you have to, mask any information when posting.

mipollo

Hi Big.Nerd (I like your handle) thanks for your reply.

So I've come a bit further than my first post. I have one column for each shift (gate shift, night shift) and if a user selects the 1st shift for 'gate shift' the value is 1 if the select the 2nd shift, the value is 2. This all goes in the gate shift column. I can now calculate the amount of 1's and the amount of 2's (yay!) by doing this:

mysql_select_db($database_pride_volun, $pride_volun);
$query_rsMarch = "SELECT id_volun, COUNT(*) AS total FROM info_volun WHERE gateshift_volun in(2) group by gateshift_volun";
$rsMarch = mysql_query($query_rsMarch, $pride_volun) or die(mysql_error());
$row_rsMarch= mysql_fetch_array ($rsMarch);
$totalRows_rsMarch = mysql_num_rows($rsMarch);

So I can now echo the total of 2's in gateshift_volun and I can change the 2 to 1 to calculate the total of 1's in gateshift_volun and in both cases I can display the shift if there are available spots:
<?php if ($row_rsMarch['total'] <4) echo 'SHIFT' ?>

However, the problem is that I have like 2 or 3 different shifts for each position (2 for gateshift, 2 for night shift, etc) and there are like 10 positions. Do I have to create 30 different recordsets or calls to the database or is there a way to be more general and get specific in the php? What's the best practice?

I tried taking the 'in(2)' out of the sql and putting this in the PHP and got really excited cause it worked for '1' but then it doesn't work for any other number! (rsVolun is a general connection to the table):
<?php
if (!($row_rsVolun['gateshift_volun']==1) and ($row_rsMarch['total'] <4))
echo '<input type="radio" name="gateshift_volun" id="gateshift_volun" value="1"/>';
else echo '[Sorry, shift full]';
?> Saturday 5:00-9:00pm

<-- That works

     <?php
	 if (!($row_rsVolun['gateshift_volun']==2) and ($row_rsMarch['total'] <4))
	 echo '<input type="radio" name="gateshift_volun" id="gateshift_volun" value="1"/>'; 
	 else echo '[Sorry, shift full]'; 
	 ?> Saturday 5:00-9:00pm

<--- That does not work

Any idea why? Or if I'm totally going about this the wrong way, any suggestions on how best to do this?

Thanks again! I really appreciate your help!