Gender Table Help

[Pug307]

I have a table in my game called gender which is a sub table to users, In the signup form you can select Male or Female. This adds the data to the table gender. What I am wanting to do is setup a part in my index page which tells you how many males and how many females are registered. How would I use a query for this?

EXAMPLE - Males 89, Females 42

This is just so the members know how many males and females are registered in the game.

Thank you in advance

[dvd871]

What are the fields named in the gender table?

Its kind of odd to have a whole seperate table just for gender.  That should be part of the main player accounts table I would think...

[Pug307]

In more detail. The main table has a tab called user. I added a tab inside user called gender and the register form submits into it either Male or Female which is selcted using radio buttons.

I have now figured that I should have used 2 seperate tabs, male and female and I could have used the count row or whatever it is.

I am just trying to figure a way of seperating male and female without changing the table and register.php

[dvd871]

Ok so is it something like male = 1 and female = 2?  or how is the male vs. female data stored?

[Pug307]

In the table under user> gender it stores the data as male and female.

[dvd871]

Ok so it would be something like this:

$sql = mysql_query("SELECT COUNT(*) as male FROM user WHERE gender="MALE") or die("Error: ".mysql_error());
$row = mysql_fetch_assoc($sql);
$male = $row['male'];

The $male var should have the count of male players from the database.

$sql = mysql_query("SELECT COUNT(*) as female FROM user WHERE gender="FEMALE") or die("Error: ".mysql_error());
$row = mysql_fetch_assoc($sql);
$female = $row['female'];

The $female var should have the count of female players from the database.

This is very rough code though, and I am simply guessing at the table names and data.  If I had the sql file for the player accounts or gender, I could tell you exactly what the code is.

Cheers,
Dan

[Zeggy]

I don't think it's so useful to have a seperate table for gender... I'm not sure what you mean by 'tabs' though... Do you mean columns?
`user` table contains the `gender` column, along with the username, password, email columns, etc.?

dvd871's code should work fine anyways

[Pug307]

Yes it will be columns then. I will give it a go and let you know of the outcome. Hopefully it will work for me 

Nope don't work, just throws an SQL Error

[dvd871]

Do you use phpMyadmin?  If you do then go to the table that has the gender info in it.  Do an export of the structure and post that here so we can see what we are dealing with.  It also helps to post what the error messages are.

[Pug307]

`gender` varchar(20) character set latin1 collate latin1_general_ci NOT NULL default '',

This piece of code below is in the signup.php form

<td bgcolor="#003366">Gender:</td><td bgcolor="#333333">&nbsp;Male:<input type="radio" value="Male" name="gender"> &nbsp;Female:<input type="radio" value="Female" name="gender"></td>

An example of the attacking status would be

Attacking:</font></span> <?=$attacking?> - this is where it displays how many members are attacking

$getattacking = fetch("SELECT COUNT(id) FROM $tab[pimp] WHERE lastattacked>$idle AND rid='$game[round]';");
  if (!isset($attacking)) $attacking=0;

  $attacking = $attacking+$getattacking;

I hope this makes some sense to you

Thanks

[dvd871]

`gender` varchar(20) character set latin1 collate latin1_general_ci NOT NULL default '',

This piece of code below is in the signup.php form

<td bgcolor="#003366">Gender:</td><td bgcolor="#333333">&nbsp;Male:<input type="radio" value="Male" name="gender"> &nbsp;Female:<input type="radio" value="Female" name="gender"></td>

An example of the attacking status would be

Attacking:</font></span> <?=$attacking?> - this is where it displays how many members are attacking

$getattacking = fetch("SELECT COUNT(id) FROM $tab[pimp] WHERE lastattacked>$idle AND rid='$game[round]';");
  if (!isset($attacking)) $attacking=0;

  $attacking = $attacking+$getattacking;

I hope this makes some sense to you

Thanks

// for male users
$msql = fetch("SELECT COUNT(*) as count FROM users WHERE gender='Male';") or die("Error: ".mysql_error());
$row = mysql_fetch_assoc($msql);
$male = $row['count'];

// for female users
$fsql = fetch("SELECT COUNT(*) as count FROM users WHERE gender='Female';") or die("Error: ".mysql_error());
$row = mysql_fetch_assoc($fsql);
$female = $row['count'];

Should work looking at the info above.

[Mgccl]

say... it is a waste to use varchar(20) for gender...
I will use int(1)
where 0,1,2 are used...
0 = not set
1 = male
2 = female
It's much less data in that way... and supposeablely faster...

[Marek]

Or even better, use an ENUM column. It stores the data as 0, 1, etc but it still lets you do a query with the corresponding string value, e.g.: SELECT * WHERE `gender` = 'male'

You give MySQL the possible values when creaitng the table: ENUM('male', 'female') and since it knows how many possibilities there are (in this case, two) it  can store it most efficiently, with the least bytes.