Row count using content as a filter

[thewomb]

Hey. im trying to do a row count but instead of the full number i want do check based on content...

eg

row can contain "vic" "nsw" "tas" etc and i want to count the number of people registered in "vic" and display it on the site allong with the number of registered users in "tas" and "nsw" is this possible?

thanx

TW

[rachel2004]

A row count from what, though? A text file, a table in a database, or... ?

[thewomb]

A row count form Mysql

row name = state..

basicaly what im trying to do is claculate how many users i have in each state. and echo that in the pages

[rachel2004]

No problem. As it stands right now, how are you collecting the info on (for example) the 50 U.S. states? For example, do you have a table called "Addresses" and there is a column called "states". In that column would one row have "ny" for a user in New York, another record would have "ca" for user in California, and so on?

Basically, give me an idea of how you collect that or give me a desciption of your table and columns. From there, I should be able to help.

Do you already have some sample code? If yes, post a snippet of where you're having a problem, but PLEASE, PLEASE use the PHP formatting options when you post so everything is indented and color-coded for easy readability.

[thewomb]

ok got a db called freelove a table called rego in there i have a field called state.

and the state field contains all state vars so it will contain NYC WA BBC ABC 123 etc... there selected from a drop dowm menu so they are all formetted the same. so no field/row for each there all contained in one row called state.

[rachel2004]

If I'm understanding you correctly, you can just use the LIKE clause in MySQL then.

For example:

$state = "nyc";

$sql = "SELECT from rego
            WHERE state LIKE '$state'";

$result = mysql_query($sql, $db_connection)
    or die("Couldn't perform query: ".mysql_error());

$count = mysql_num_rows($result);

echo"<p>Number of matches is: ".$count;

There are other things you can use in combination with the LIKE clause such as the % symbol, which will match zero or more characters. However, if you already know the exact string you are looking for, you probably won't need it.

Post back if this helped.

[thewomb]

OK ive patched this together..

mysql_select_db($database_freelove, $freelove);
$my_state = "Victoria";
$query_state_num = "SELECT state from rego WHERE state LIKE '$my_state'";
$state_num = mysql_query($query_state_num, $freelove) or die(mysql_error());
$row_state_num = mysql_fetch_assoc($state_num);
$totalRows_state_num = mysql_num_rows($state_num);

echo "<p>Number of matches is: ".$totalRows_state_num;

but how do i expand the sql query to include gender from the gender field in the database?

e.g.

$query_state_num = "SELECT state AND gender from rego WHERE state LIKE '$my_state' AND gender LIKE '$my_gender' ";

thanx for the help so far rach.

[rachel2004]

Originally posted by thewomb

but how do i expand the sql query to include gender from the gender field in the database?

e.g.

$query_state_num = "SELECT state AND gender from rego WHERE state LIKE '$my_state' AND gender LIKE '$my_gender' ";

[/B]

I don't see a reason to use the LIKE clause with $my_gender. You only have two possible choices: male or female, which could be represented as 'm' or 'f'.

You would use the LIKE clause when are looking for something that is SIMILAR to a certain string. But if you already know the strings can be either 'm' or 'f' or some other values out of two possible choices, don't bother using LIKE.

Try this for your query:

$my_gender = 'f';
$query_state_num = "SELECT state, 
                                                 gender 
                                    from rego 
                                   WHERE state LIKE '$my_state' 
                                   AND gender = '$my_gender' ";

[thewomb]

thank you rach.

much appreciated.. im one step closer to understaing mysql requests he he

;-)

TW

[thewomb]

// GENDER REGO COUNTER MALE
mysql_select_db($database_freelove, $freelove);
$my_state = "Victoria";
$his_gender = "Male";
$query_state_num = "SELECT state AND gender from rego WHERE state = '$my_state' AND gender = '$his_gender'";
$state_num = mysql_query($query_state_num, $freelove) or die(mysql_error());
$row_state_num = mysql_fetch_assoc($state_num);
$totalRows_state_num = mysql_num_rows($state_num);

// GENDER REGO COUNTER FEMALE
mysql_select_db($database_freelove, $freelove);
$her_gender = "Female";
$query_state_her = "SELECT state AND gender from rego WHERE state = '$my_state' AND gender = '$her_gender'";
$state_num_her = mysql_query($query_state_her, $freelove) or die(mysql_error());
$row_state_num_her = mysql_fetch_assoc($state_num_her);
$totalRows_state_num_her = mysql_num_rows($state_num_her);

$gender_result = "<p>We currently have <b>". $totalRows_state_num_her ." Female</b> members <br> and <b>".$totalRows_state_num." Male</b> members in <b>Victoria</b>";