Page 1 of 1

PHP sum MySql table column and return message if > some #

Posted: Mon Mar 28, 2011 3:24 pm
by mipollo
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!

Re: PHP sum MySql table column and return message if > some

Posted: Thu Mar 31, 2011 7:17 pm
by mecha_godzilla
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:

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);
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:

Code: Select all

if ($totalRows_rsCount < 4) {
    // generate your (X)HTML code here
} else {
    echo 'Sorry, no shifts available';
}
HTH,

Mecha Godzilla