State and Province Query

Manat

Hello all,

We have a form, and when people fill out the form, obviously it goes into our MySQL database.

Now my question is, I want to create a little read only admin interface where non-techies could go to that url and see the number of entrants in New York, number of Entrants in Canada.

So basically, how many entrants from Canada, how many from U.S and then filter that data by state and province under it's appropriate country.

i.e.

Canada: 2 Entrants
Vancouver: 1 entrant
Ontario: 1 entrant

U.S.: 3 entrants
NYC: 2 entrants
New Jersey: 1 entrant
ect......

Can anyone tell me what my query would look like?
I don't know how to count in queries....

And also, total entries.

Thanks

dniry

to make it simpler let's leave out all the extra info like name, address etc... and assume your table looks something like this:

table entrants (

entrant_id int(11),
entrant_country char(2) not null, # ie: CA or US
entrant_state varchar(20) default null

)

Note: if you are going to do a lot of statistical searches on these 2 columns, it could be a good idea to store state and country as integers and reference them in another table (ie. states or countries) with that interger as the primary key, and the text as the display value. Makes for faster searches in big tables. Put an index on those columns too to speed up further.

// getting count by country:

select entrant_country, count(entrant_country) as cnt
from entrants
group by entrant_country

// getting count by state in the US:

select entrant_state, count(entrant_state) as cnt
from entrants
where entrant_country='US'
group by entrant_state

you then loop over in php like any other query.

bastien

okay the solutions are one run two queries as you can group on different crtieria (country level and state level) at one time or you can simply run a counter to add up the amounts on the country levels

the query:

select statefield, count(statefield) from tablename group by statefield

the second query:

select country,count(country) from tablename group by country

Manat

Let's say i'm not using another table for now.

Below is my query:

<?
$sql="select country, count(country) as cntCountry
from table1
group by country";

// getting count by state:

$sql="select state, count(state) as cntState
from table1
where country='US'
group by state) as cntState";

$result = mysql_query($sql) or die(mysql_error());

I'm too sure what I do now, do I do an echo on the cnt variables?
And the looping u mentioned, how do I know when it's the end of recordset?

Thx

dniry

first, I apologize for a typo (extra paste I had not seen), I corrected it right after, but you had already used the code, the correct state count code is:

select entrant_state, count(entrant_state) as cntState
from entrants
where entrant_country='US'
group by entrant_state

then you do this:

// getting count by state:

while ($row=mysql_fetch_array($result)) {
echo "number of entrants in $row['state']: $row['cntState'] ";
}

the same pattern will apply for countries

Manat

Hi,

I tried the new code, and I get a php parse error on line 4.

One thing to keep in mind are my data types,
both country and state are varchar(15).

<?
require("dbinfo.inc.php");

sql="select state, count(state) as cntState //<-- Line 4
from table1
where country='US'
group by state";

$result = mysql_query($sql) or die(mysql_error());
?>

<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>

<?php while ($row=mysql_fetch_array($result)) {
echo "number of entrants in $row['state']: $row['cntState'] ";

} ?>

</body>
</html>

At first I put your code, but then I got an error, so I then put the query in a SQL variable, but still I got the same error.

any idea what's going on?
dbinfo.inc.php is of course the connection strings.
Thx

bastien

missed the dollar sign

$ sql="select state, count(state) as cntState //<-- Line 4

Manat

Ok, I got this error before, I don't remember what it means however.

The offending line is:

<?php while ($row=mysql_fetch_array($result)) {
echo "number of entrants in $row['state']: $row['cntState'] ";

} ?>

And in my logs, I get this error message:

PHP Parse error: parse error, expecting T_STRING' orT_VARIABLE' or `T_NUM_STRING' on the above code.

I'm guessing this is a data type conflict?

Thx

dniry

hmmmm.... can you post your entire page's code ?

Manat

Here is my entire code:

<?
require("dbconnectionstring.inc.php");

$sql="select state, count(state) as cntState
from table1
where country='US'
group by state";

$result = mysql_query($sql) or die(mysql_error());
?>

<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>

<?php while ($row=mysql_fetch_array($result)) {
echo "number of entrants in $row['state']: $row['cntState'] ";

} ?>

</body>
</html>

And the while statement is giving me this error:
PHP Parse error: parse error, expecting T_STRING' orT_VARIABLE' or `T_NUM_STRING' on the above code.

I did some looking up, it may look like a date type conflict, but it might be a problem with quotation marks.

Thx

dniry

try:

while ($row = mysql_fetch_array($result)) {

echo "number of entrants in ".$row['state'].": ".$row['cntState']." ";

}

Manat

No, it still doesn't work.
But it no longer seems to be giving me an error, but at the same time it doesn't work, it's a blank page with no result.

Thx

Manat

Yes, it works.
Thanks a lot, stupid me, the query had a where country=us, when in my database it's united states, lol.

Anyhow, how would I also display total entries, and total entries in Canada and US.

Thx

dniry

select count * from entrants.

this will give you the total number of entrants.

the count by country code is in my first reply

Manat

HI,

I did a select count * from entrants, but it said query is empty.

<? $sql="select count * from entrants";

$result = mysql_query($sql) or die(mysql_error());
?>

But what do I <?php echo?

Thanks

dniry

hem, small syntax problem in the SQL... and you also want to add as cntTotal to have a clearer variable name to work with...

$sql="select countCOLOR=red[/COLOR] from entrants as cntTotal";
$result=mysql_query($sql);
while ($row=mysql_fetch_array($result)) {
echo $row['cntTotal'];
}

practice all this stuff until you master it and know the syntax, as this is one example of the basics of application develoment you'll often need...

-David

Manat

I used the code, but I get no result.
I don't get a php parse error, all I get is a warning, undefined index cntTotal.

If I put your code at the top of the page, the page is blank, if I put it at the bottom, then I get all my state info, but no cntTotal to display, so no result is showing but it's also not resulting in a parse error.

Should I be declaring cntTotal, but at the same time I also get undefined index for cntState, but that works fine.

any Idea?

Thx

bastien

sql is incorrect...should be

 

$sql="select count(*)[b] as cntTotal[/b] from entrants";

dniry

whoopsie.... good catch Bastien... he he... still happens once in a while, even after years of SQL...

Manat

Thanks guys, it works now.

But now I have another question, hehehe.

I have a list of entrants in the database all coming from various age groups. Now, I got stats on how many from which state or province, but now, how about how many within which age group?

i.e. 8-10 = 12 entrants
11-16 8 entrants
16-25 32 entrants
26-43 342 entrants

And so on.

Now this is a wowsie. I guess I can figure out how to do it, but the way i'm thinking is a seperate sql script per age group, and i'm sure there's a better way than that.

Thanks
MK