Mysql

[TheFilmGod]

Mysql:

 

ID | brunch | dance | game

 

Id is auto increment.

 

Brunch, dance and game have only the choose of "N" or "Y", null is not present in this case.

 

I would like to count the number of "Y" 's in column brunch.

Count the number of "y" 's in column dance, same for game.

 

How do I do this?

 

database = wwpknights

table = homecoming

 

please help. I'm a noob.

[Fadion]

Im sure someone will give a better and faster alternative (ie. using COUNT) as im not so good with mysql, but this is my 2 cents:

 

$query = mysql_query("SELECT * FROM homecoming WHERE brunch='Y'");
$countBrunch = mysql_num_rows;

 

The same for the other columns.

[TheFilmGod]

<?php

// Connects the script to MYSQL
require_once("connect.php");

// Select database
mysql_select_db(wwpknights);

// Select whole table
$result_set = mysql_query ( "SELECT * FROM homecoming" );

// Count how many people submitted info
$number_of_fields = mysql_num_fields ( $result_set );

// Echo the number of people...

echo "$number_of_fields is the number of people who submitted info";



// Counting Separate Elements


// Branch Yes
$brunch_sel = mysql_query("SELECT * FROM homecoming WHERE brunch='Y'");
$brunch = mysql_num_fields ( $brunch_sel );

// Dance Yes
$dance_sel = mysql_query("SELECT * FROM homecoming WHERE dance='Y'");
$dance = mysql_num_fields ( $dance_sel );

// Game Yes
$game_sel = mysql_query("SELECT * FROM homecoming WHERE game='Y'");
$game = mysql_num_fields ( $game_sel );



// Echo number of yes in each category activity

echo "$brunch are attending the branch";
echo "$dance are attending the dance";
echo "$game are attending the game";

?>

 

No error, it just gives me all 5 for the echo statements. While there is a lot more than 5 total fields and def not equal y in all categories! What did I do wrong?

[Fadion]

Normaly that it returns 5 as u have five fields. Dont use mysql_num_fields() as it is for counting fields, use mysql_num_rows() instead.

[pocobueno1388]

I wouldn't use "SELECT *" in all the queries...that means your selecting all the fields, and you don't need ANY of them.

 

Use this instead:

SELECT COUNT(*) FROM...

[chronister]

To elaborate on pocobueno1388's post...

 

<?php

$query="SELECT count(*) as brunch_count FROM homecoming WHERE brunch='Y'";
$result=mysql_query($query);
$row=mysql_fetch_rows($result);

$brunch_total=$row->brunch_count;

echo 'There are '. $brunch_total .' y\'s in the brunch column';
?>

 

 

Make sure you use the as NAME in queries your using count otherwise you will not have a "handle" to grab the results by.

 

Nate

Repeat for each column