Hi!
I'm trying to help a non-profit organize their volunteer signups. I have the information going from an html/php form into a MySQL database with no problem. The trick is that they only want to offer a shift if it hasn't been filled by a certain # of people. So I'm trying to add up the # in the column of shift1_volun in the database info_volun and if the number = less than 4 I want to return a radio box and shift time (like 'Saturday 5-7) so that they can sign up. The radio box will input a number of 1 into the column so that I can see when we're full.
Can anyone help me? I tried this (modeled after Dreamweaver recordsets- $pride_volun is my connection)
mysql_select_db($database_pride_volun, $pride_volun);
$query_rsCount = "SELECT SUM(shift1_volun) FROM info_volun";
$rsCount = mysql_query($query_rsCount, $pride_volun) or die(mysql_error());
$row_rsCount = mysql_fetch_array ($rsCount);
$totalRows_rsCount = mysql_num_rows($rsCount);
But I didn't have any success even echoing the count with php in the body. Am I off to the right start?
Any help would be sooooo appreciated!
PHP sum MySql table column and return message if > some #
Moderator: General Moderators
- mecha_godzilla
- Forum Contributor
- Posts: 375
- Joined: Wed Apr 14, 2010 4:45 pm
- Location: UK
Re: PHP sum MySql table column and return message if > some
What you need to be aware of in your script is that you'll only ever get one row returned (the value of your SUM() calculation) so using mysql_num_rows() is a bit redundant.
You might want to consider changing your script so it looks like this:
What this script does is put the result of the SUM() calculation into a new custom row, and you can then use the list() method to assign it directly to a variable. Keep in mind that mysql_fetch_row() is only used in this instance because we're expecting only one row to be returned by the query - you'd normally use mysql_fetch_array() for one or more rows inside a while() loop.
To interpret the result you could then just use a simple if() statement like this:
HTH,
Mecha Godzilla
You might want to consider changing your script so it looks like this:
Code: Select all
mysql_select_db($database_pride_volun, $pride_volun);
$query_rsCount = "SELECT SUM(shift1_volun) as total FROM info_volun";
$rsCount = mysql_query($query_rsCount, $pride_volun) or die(mysql_error());
list($totalRows_rsCount) = mysql_fetch_row($rsCount);To interpret the result you could then just use a simple if() statement like this:
Code: Select all
if ($totalRows_rsCount < 4) {
// generate your (X)HTML code here
} else {
echo 'Sorry, no shifts available';
}Mecha Godzilla