How do I rewrite this code to count mysql rows?

[Jeffro]

The following works, but I don't want to have to write 50 query statements for 50 states.  How can I just write one query (using a variable I presume) and then echo the variable count each time for each state?  I'm just trying to list the total number of rows for each state, that exist in the db. 

 

<?         
$result = mysql_query("SELECT * FROM mytable WHERE source = 'alabama'") 
or die(mysql_error());  
$num_rows = mysql_num_rows($result);

?>

href="/state/alabama/">alabama</a> (<?php echo("$num_rows");?>) <br>

<?
$result = mysql_query("SELECT * FROM mytable WHERE source = 'alaska'") 
or die(mysql_error());  
$num_rows = mysql_num_rows($result);
?>
href="/state/alaska/">alaska</a>  (<?php echo("$num_rows");?>) <br>   

 

The above would give output such as:

alabama (122)

alaska (212)

[harristweed]

http://www.tizag.com/mysqlTutorial/mysqlcount.php

[Jeffro]

http://www.tizag.com/mysqlTutorial/mysqlcount.php

 

I'm not wanting to display an ordered list of all results.  I was asking how to use a variable in combination with the results. 

Anyone? 

 

I'm hoping to write the query at the top of the page and then say,

 

There are count($row) results for Arizona. 

There are count($row) results for Florida.

There are.... etc.... 

[souper]

Maybe something like this? Use an array to list all 50 states then use a foreach loop to display:

 

<?

$states = array( 'Arizona', 'New York', ....... );

foreach( $states as $value )
{
   $result = mysql_query("SELECT * FROM mytable WHERE source = '$value'") 
   or die(mysql_error());  
   $num_rows = mysql_num_rows($result);


   echo "<a href='/state/$value/'>$value</a> ($num_rows)<br>";
}

?>

 

I'm not positive this will work though, haven't checked.

[Muddy_Funster]

erm...learn how to use MySQL COUNT.....like someone sugested already.

SELECT source, count(*) totals FROM table GROUP BY source

@ souper 

[PFMaBiSmAd]

You would do exactly what the link that harristweed posted shows.

 

If you use GROUP BY source, the query will consolidate all the rows for each state together. If you use SELECT source, COUNT(*) as cnt in the SELECT term, you can reference the state name using $row['source'] and you can reference the count for that state using $row['cnt']

 

@souper, don't put queries inside of loops. You can almost always use one query to get the data you want in the order and format that you want it.

[souper]

Thanks for the info PFM!

[Jeffro]

You would do exactly what the link that harristweed posted shows.

 

If you use GROUP BY source, the query will consolidate all the rows for each state together. If you use SELECT source, COUNT(*) as cnt in the SELECT term, you can reference the state name using $row['source'] and you can reference the count for that state using $row['cnt']

 

@souper, don't put queries inside of loops. You can almost always use one query to get the data you want in the order and format that you want it.

 

Got it.  Thank you! 

 

And Muddy... The whole point of my posting  here was to "learn" how to use the count.  I was struggling to understand.  When that happens, I go to a forum to try and make more sense of it... which PFM has now clarified.  See how that works?   

[Muddy_Funster]

You would do exactly what the link that harristweed posted shows.

...

 

Yeah, I sure do see where your coming from on that one. 

[Jeffro]

You would do exactly what the link that harristweed posted shows.

...

 

Yeah, I sure do see where your coming from on that one. 

 

hehe..  I know it doesn't make sense to you, but brother..  I went there and looked and couldn't make it work.  So this was all I had left.  Sorry to have bothered you. 

[Jeffro]

You would do exactly what the link that harristweed posted shows.

 

If you use GROUP BY source, the query will consolidate all the rows for each state together. If you use SELECT source, COUNT(*) as cnt in the SELECT term, you can reference the state name using $row['source'] and you can reference the count for that state using $row['cnt']

 

@souper, don't put queries inside of loops. You can almost always use one query to get the data you want in the order and format that you want it.

 

To read what you wrote, I thought it finally clicked, but when trying to make it happen, it's still not working.  Using your query, I get a result set in mysql like this:

source cnt
alabama	314
alaska	89
arizona	193
arkansas	39
california	271
colorado	148

So far so good!  That's just what I need. 

Now... when you say I can reference the state count using $row['cnt'].. I've tried the following (some from the tutorial link and some just trying different things for a result):

$query = ("SELECT source, COUNT(*) as cnt from states group by source") or die(mysql_error());  
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){

echo $row['cnt(source)'];
echo $row('source');
echo $row['COUNT(source)'];
echo $row['COUNT(arizona)'];
}

 

Nothing I try works.  I just don't get anything at all being printed to the page.

Isn't there a way I can just say:  Arizona is listed X number of times - where X is the cnt result count (193) of arizona?  Sorry to beat a dead horse.  I'm no programmer.. just trying to update one little part of my personal site. 

Thanks!

[PFMaBiSmAd]

echo "{$row['source']} is listed {$row['cnt']} times.<br />";

[Psycho]

$query = "SELECT source, COUNT(*) as cnt FROM states GROUP BY source";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result))
{
    echo "State: {$row['source']}, Count: {$row['cnt']} <br />\n";
}

[Jeffro]

echo "{$row['source']} is listed {$row['cnt']} times.<br />";

 

That works great...  lists every state with the count next to it.  What I'm wondering is if I can pull a result out of the middle of that list or is presenting it like that the only way (if so, I'll make do)?  Can I just grab a random state out of the middle and say 'Illinois has X results' and not list the rest?  I actually plan to include them all.. but I was going to put different results in different tables based on regions.

Thanks!

[PFMaBiSmAd]

If you want all the results available on the page, but want to randomly access the values, you would generally store the results in an array -

 

$query = "SELECT source, COUNT(*) as cnt FROM states GROUP BY source";
$result = mysql_query($query) or die(mysql_error());
$data = array();
while($row = mysql_fetch_array($result))
{
    $data[$row['source']] = $row['cnt'];
}

 

// display a specific value somewhere -
echo $data['colorado'];

[Jeffro]

If you want all the results available on the page, but want to randomly access the values, you would generally store the results in an array -

 

$query = "SELECT source, COUNT(*) as cnt FROM states GROUP BY source";
$result = mysql_query($query) or die(mysql_error());
$data = array();
while($row = mysql_fetch_array($result))
{
    $data[$row['source']] = $row['cnt'];
}

 

// display a specific value somewhere -
echo $data['colorado'];

 

PERFECT!!  Thanks so much.  Made my day..  sorry to be so programatically challenged.   

 

Hope you have a great day.