Is there a better way to count a column?

[Toody]

I'm am brand new to php and just found this forum this morning. I spent five hours yesterday trying to figure out how to count values in a column, but thanks to a post here I have now figured out how.

 

However, the way I coded it seems a bit long.

 

The column values are Y, N and null. I want to find out how many members (Y) and how many non-members I have on my site (N, null). The code works with the results below, but is there a shorter way to code it?

 

RESULTS:

 

There are 5000 members.

There are 20000 non-members.

There are 25000 total users.

 

CODE:

          $con = mysql_connect("localhost", "xxxxxxxxxx", "xxxxxxxxxx")

 

or die(mysql_error());

        mysql_select_db("xxxxxxxxxx") or die(mysql_error());

$result = mysql_query("SELECT activate, COUNT(activate) FROM

 

customers WHERE activate='y'");

   

// Print out result

while($row = mysql_fetch_array($result))

{

  echo "There are ". $row['COUNT(activate)'] ." members.";

  echo "<br />";

        }

mysql_close($con);

 

          $con = mysql_connect("localhost", "xxxxxxxxxx", "xxxxxxxxxx")

 

or die(mysql_error());

        mysql_select_db("xxxxxxxxxx") or die(mysql_error());

 

        $result = mysql_query("SELECT activate, COUNT(activate) FROM

 

customers WHERE activate!='y'");

   

// Print out result

while($row = mysql_fetch_array($result))

{

  echo "There are ". $row['COUNT(activate)'] ." non-members.";

  echo "<br />";

        }

 

mysql_close($con);

 

          $con = mysql_connect("localhost", "xxxxxxxxxx", "xxxxxxxxxx")

 

or die(mysql_error());

        mysql_select_db("xxxxxxxxxx") or die(mysql_error());

 

        $result = mysql_query("SELECT activate, COUNT(activate) FROM

 

customers WHERE activate!='0'");

   

// Print out result

while($row = mysql_fetch_array($result))

{

  echo "There are ". $row['COUNT(activate)'] ." total users.";

  echo "<br />";

        }

 

mysql_close($con);

[ignace]

SELECT (CASE activate WHEN 'y' THEN 'Members' WHEN 'n' THEN 'Non-Members' ELSE 'Undefined' END) AS member_group,

  count(*) AS total_count

FROM customers

GROUP BY activate

 

Members: ..

Non-Members: ..

Undefined: ..

[Toody]

Thanks! I'll give it a whirl.

[Toody]

You know. I think the solution is way above my skill level. I'm gonna have to read more and stick to bloated code for now.

 

Something that I hope would be simple that I can't seem to get the right syntax is subtracting 2 from the members. How exactly does it go in here?

 

// Print out result

while($row = mysql_fetch_array($result))

{

  echo "There are ". $row['COUNT(activate)'] ." members.";

  echo "<br />";

        }

 

Any help is appreciated.

 

[cyberRobot]

Maybe I missed something, but is there a reason you need to open and close the database connection so many times? You should be able to do something like this:

 

//CONNECT TO THE DATABASE
$con = mysql_connect("localhost", "xxxxxxxxxx", "xxxxxxxxxx")  or die(mysql_error());
mysql_select_db("xxxxxxxxxx") or die(mysql_error()); 

//GET AND DISPLAY THE NUMBER OF MEMBERS
$result = mysql_query("SELECT activate, COUNT(activate) FROM customers WHERE activate='y'");
while($row = mysql_fetch_array($result)) {
    echo "There are ". $row['COUNT(activate)'] ." members.";
    echo "<br />";
}

//GET AND DISPLAY THE NUMBER OF NON-MEMBERS
$result = mysql_query("SELECT activate, COUNT(activate) FROM customers WHERE activate!='y'");
while($row = mysql_fetch_array($result)) {
    echo "There are ". $row['COUNT(activate)'] ." non-members.";
    echo "<br />";
}

//GET AND DISPLAY THE NUMBER OF TOTAL USERS
$result = mysql_query("SELECT activate, COUNT(activate) FROM customers WHERE activate!='0'");
while($row = mysql_fetch_array($result)) {
    echo "There are ". $row['COUNT(activate)'] ." total users.";
    echo "<br />";
}

//CLOSE THE DATABASE CONNECTION
mysql_close($con);

[cyberRobot]

If all you need to do is count the members, non-members, and get the total; you could also do something like:

 

//CONNECT TO THE DATABASE
$con = mysql_connect("localhost", "xxxxxxxxxx", "xxxxxxxxxx") or die(mysql_error());
mysql_select_db("xxxxxxxxxx") or die(mysql_error()); 

//INTIAILZE VARIABLES
$numMembers = 0;
$numNonMembers = 0;

//FIGURE OUT HOW MANY MEMBERS THERE ARE
if($result = mysql_query("SELECT activate, COUNT(activate) FROM customers WHERE activate='y'")) {
    $numMembers = mysql_num_rows($result);
    echo "There are $numMembers members.<br />";
}

//FIGURE OUT HOW MANY NON-MEMBERS THERE ARE
if($result = mysql_query("SELECT activate, COUNT(activate) FROM customers WHERE activate!='y'")) {
    $numNonMembers = mysql_num_rows($result);
    echo "There are $numNonMembers non-members.<br />";
}

//FIGURE OUT HOW MANY TOTAL MEMBERS THERE ARE
$totalMembers = $numMembers + $numNonMembers
echo "There are $totalMembers total users.<br />";

//CLOSE DATABASE CONNECTION
mysql_close($con);

[cyberRobot]

Something that I hope would be simple that I can't seem to get the right syntax is subtracting 2 from the members. How exactly does it go in here?

 

// Print out result

while($row = mysql_fetch_array($result))

{

  echo "There are ". $row['COUNT(activate)'] ." members.";

  echo "<br />";

        }

 

 

This should work if I understand your question correctly:

 

// Print out result
while($row = mysql_fetch_array($result)) {
    echo "There are " . $row['COUNT(activate)'] - 2 . " members.<br />";
}

 

 

Note that I've never seen a column referred to like "$row['COUNT(activate)']". Does that work? I usually build the query like:

 

$result = mysql_query("SELECT activate, COUNT(activate) AS numMembers FROM  customers WHERE activate='y'");
while($row = mysql_fetch_array($result)) {
    echo "There are " . $row['numMembers'] - 2 . " members.<br />";
}

[Toody]

Thanks for your help cyberRobot. I tried the code and it was not accurate. I got:

 

There are 1 members.

There are 1 non-members.

There are 2 total users.

 

Any suggestions?

 

Also, I was able to figure out how to subtract 2. I did this:

 

$row=$row['COUNT(activate)'] - 2

 

and it worked.

[jcbones]

Ignace gave the most appropriate answer, and I can help you with the coding.

 

$sql = "SELECT (CASE activate WHEN 'y' THEN 'Members' WHEN 'n' THEN 'Non-Members' ELSE 'Undefined' END) AS member_group,
  count(*) AS total_count
FROM customers
GROUP BY activate";

$result = mysql_query($sql);
// Print out result
while($row = mysql_fetch_array($result))
{
   echo "There are ". $row['total_count'] ." " . $row['member_group'] . "<br/>\n";
   echo "<br />";
        }

 

Should spit it right out to you.

 

[cyberRobot]

Thanks for your help cyberRobot. I tried the code and it was not accurate. I got:

 

There are 1 members.

There are 1 non-members.

There are 2 total users.

 

Any suggestions?

 

Also, I was able to figure out how to subtract 2. I did this:

 

$row=$row['COUNT(activate)'] - 2

 

and it worked.

 

 

Yeah sorry about that, I wasn't aware of how the count() feature works. After a quick search, it sounds like it gives you 1 row every time. So using mysql_num_rows() will always return 1.

 

Also, I agree that Ignace solutions looks like a better option. I can understand if you don't want to make the switch though. I have several complicated SQL queries in my scripts which work great. But when the time comes where I need to edit them it takes a little longer to remember how everything works. This makes maintenance a little more complicated...especially if you don't live and breathe SQL code.

 

 

If you prefer to stick with your code, you should be able to eliminate one of the SQL queries by doing something like this:

 

//CONNECT TO THE DATABASE
$con = mysql_connect("localhost", "xxxxxxxxxx", "xxxxxxxxxx")  or die(mysql_error());
mysql_select_db("xxxxxxxxxx") or die(mysql_error());

//INTIAILZE VARIABLES
$numMembers = 0;
$numNonMembers = 0;

//GET AND DISPLAY THE NUMBER OF MEMBERS
$result = mysql_query("SELECT activate, COUNT(activate) FROM customers WHERE activate='y'");
while($row = mysql_fetch_array($result)) {
    $numMembers = $row['COUNT(activate)'];
    echo "There are $numMembers members.<br />";
}

//GET AND DISPLAY THE NUMBER OF NON-MEMBERS
$result = mysql_query("SELECT activate, COUNT(activate) FROM customers WHERE activate!='y'");
while($row = mysql_fetch_array($result)) {
    $numNonMembers = $row['COUNT(activate)'];
    echo "There are $numNonMembers non-members.<br />";
}

//FIGURE OUT HOW MANY TOTAL MEMBERS THERE ARE
$totalMembers = $numMembers + $numNonMembers
echo "There are $totalMembers total users.<br />";

//CLOSE THE DATABASE CONNECTION
mysql_close($con);

 

 

Note that I'm a little confused by what's going on with the "activate" column. In your OP, it sounds like "activate" will contain one of three possibilities. It will contain:

• y - if they are a member
  
• n or null - if they are not a member
  

 

But your last SQL query had activate!='0' in it. Is there a chance that some records will have '0' in the activate column? If so, what does that mean?

[Toody]

Thanks to all! It works, but I need a little tweaking. I am brand new to php, but it's starting to make a little sense. What I got was:

 

There are 10,000 Undefined

 

There are 5,000 Members

 

There are 5,000 Non-Members

 

 

I think I have to create different echo statements because what I'd like to get is:

 

There are 5,000 Members (I would need to subtract 2 from the total number)

 

There are 5,000 Non-Members (this would have to add 'n' and undefined as some non-members do not have 'n' in the activate column.)

 

(total users isn't important, but would be okay and would have to add 'y', 'n' & 'undefined')

 

And just so I understand,

 

AS member_group assigns the "Then"

 

and

 

count(*) AS total_count assigns "When"

 

in terms of the echo statement?

 

[jcbones]

member_group assigns the name "Members","Non-Members","Undefined".  total_count assigns the number for each.

 

The database will return 3 rows.

 

Row 1: Members(member_group) , *number*(total_count)

Row 2: Non-Members(member_group), *number*(total_count)

Row 3: Undefined(member_group), *number*(total_count).

 

To subtract 2.

$totalcount = ($row['member_group'] == 'Members') ? $row['total_count'] - 2 : $row['total_count'];
echo $row['member_group'] . ' ' . $totalcount . '<br />';

 

[Toody]

Thanks for your help jc. I was able to shorten the code a bit and am looking forward to the time when I know more about php than I don't know.