Does anyone know if this is possible with MySQL and PHP...

[suttercain]

I currentley have to SQL statements to get the number of rows found (mysql_num_rows) like this..

 

<?php
$sql = mysql_query ("SELECT names FROM table WHERE name ='Frank'")
$count = mysql_num_rows($sql);
echo $count; //Works as desired.

$sql = mysql_query ("SELECT names FROM table WHERE name ='John'")
$count = mysql_num_rows($sql);
echo $count; //Works as desired.
?>

 

Can I somehow do a "row count" in another fashion and with a single mysql_query?

<?php
$sql = mysql_query ("SELECT names FROM table")
//somehow show number of John's found.
//somehow show number of Frank's found.
?>

 

Would in_array somehow come into play with what I am trying to achieve? Thanks.

 

SC

 

 

[clanstyles]

Well you can do things like:

$sql = mysql_query ("SELECT COUNT(*) FROM table WHERE name ='Frank'");

 

If thats what you mean then yes otherwise I don't understand what you mean exactly.

[suttercain]

Hi Clan styles,

 

Thanks for reply. I am trying only have to have one single mysql_query call without the WHERE clause and be able to find specific times a specific item is found in the database.

[revraz]

Well you can do query's like this

 

SELECT names

  FROM table

WHERE name='Frank' or name='John'

 

You can then fetch the results into an Array or Object and sort it.

[clanstyles]

Well you can do query's like this

 

SELECT names

  FROM table

WHERE name='Frank' or name='John'

 

You can then fetch the results into an Array or Object and sort it.

without the WHERE clause

lol idk why that seems stupid to me without a were clause but heh. Let MYSQL do as much work as possible

[revraz]

SELECT names FROM table

 

Would load all names then use a array to sort.

[roopurt18]

SELECT names FROM table

 

Would load all names then use a array to sort.

Don't take this the wrong way, but this is a horrible suggestion.  You are going to get many more items than you need and force PHP to work way harder than it has to.  If the table is very large this approach can kill the site.

 

First off, as someone else suggested, doing a SELECT COUNT(*) .. is much more efficient than selecting everything and then calling mysql_num_rows().  If all you want is a count, do not ever select everything and then count the size of the result set; the reason you do not do this is because MySQL will have to send a potentially large result-set of data when all you want is a single number, very inefficient!

 

As for your particular question:

SELECT 
  COUNT(*) AS `n`, `name` 
FROM `table`
GROUP BY `name`
ORDER BY `name`

 

And if you only want specific names:

SELECT 
  COUNT(*) AS `n`, `name` 
FROM `table`
WHERE `name` IN ('Frank', 'Bob', 'Kelly')
GROUP BY `name`
ORDER BY `name`

[cooldude832]

what I like to do is

<?php
$q = "select COUNT(*) from `Table` Where this='that'";
$r = mysql_query($q) or die(mysql_error());
$count = mysql_result($r,0);
?>

you can't do anything with the query without doing one more step, using mysql_num_rows or mysql_result are probably going to be faster than mysql_fetch_array(), but we are talking microseconds here

[hamza]

<?php

$sql = mysql_query ("SELECT names FROM table WHERE name ='Frank'")

$count = mysql_num_rows($sql);

echo $count; //Works as desired.

 

$sql = mysql_query ("SELECT names FROM table WHERE name ='John'")

$count = mysql_num_rows($sql);

echo $count; //Works as desired.

?>

 

Can I somehow do a "row count" in another fashion and with a single mysql_query?

 

Code:

<?php

$sql = mysql_query ("SELECT names FROM table")

//somehow show number of John's found.

//somehow show number of Frank's found.

?>

 

----------------------------------------------------------------------------------

if you want to row count with one query just simply do in that way

 

$sql = mysql_query ("SELECT count(names) FROM table");

 

Simply put the column in the mysql count function and know the number of rows in any coulmn.

 

if still you are facing any problem than send me your question in detail.

thats it