Hi

What would the PHP be for...

Counting the amount of the field 'confirmed' where 'confirmed' equals 1 in a table..

Therefore it would display the number of active members on my site?

Thanks

-James

Hi,

assuming you are searching a database, try this: -

PHP Code:


//Define Database Variables
$host = "localhost";
$user = "";
$pass ="";
$dbname = "yourdbname";


//Run the search for members - assumes db table is called members

$query = "SELECT * from members WHERE confirmed = '1'";

//Get the result fo the search
$result = mysql_db_query($dbname, $query, $link);

//How many rows returned?
$number = mysql_num_rows($result);

//print the result to the page
print ("The number of active members on this site is $number");

//close the db connection etc 


Hope this helps

Ian

Thanks

-James

It might be better to just grab one thing, selecting them all ("SELECT *") will return the whole row, which is useless data and will use up unneeded resources.

You could also use COUNT():

PHP Code:

$sql = "SELECT COUNT(*) FROM mytable WHERE confirmed = 1"; 


Also note that 1 is a numeric value and doesn't require quotes.

Thanks

Just on thing..

How would I inset it into the PHP??

I tryed..

PHP Code:

$query = "SELECT COUNT(*) FROM users WHERE activated = 1"; 
$result = mysql_query($query); 

echo "The number of active members on this site is $result"; 


But it just displays..

The number of active members on this site is Resource id #3

Still learning PHP and probally done it wrong

Thanks

-James

Remember you need to use another mysql function to actual grab the values from a result set

mysql_fetch_row() should be sufficient for this task.

PHP Code:

 $query = "SELECT COUNT(*) FROM users WHERE activated = 1";
$result = mysql_query($query);
$num_members = mysql_fetch_row($result);

// mysql_fetch_row returns an array of all the data, and we
// only got one thing, so let's just get it
$num_members = $num_members[0];

echo "The number of active members on this site is $num_members"; 


Thanks

I had to change the first line to..

PHP Code:

$query = "SELECT COUNT(*) FROM users WHERE activated = '1'"; 


to get it to work

-James

Is `activated` a string field (TEXT, VARCHAR etc)? I would assume it would hold either 0 or 1, in which case it should be a TINYINT (1)

Or you can use a more explicit definition by using an enumeration for the MySQL data.

activated enum ('1', '0') not null default '0'

That ensures that it could be only either 1 or 0 and if there's no specification it will just assume that the user is not active, in your case, but this isn't really necessary if you don't need it. It's just to make your SQL queries more straight to the point and possibly take less space.

TINYINT(1) can also only contain values 0 or 1. And they both take 1 byte of space, so it's personal preference I s'pose.

Its set as enum('0', '1') NOT NULL Defualt '0'

-James