Counting rows?

[Epidemic]

Hey,

 

I am trying to count how many members in my authentication panel have bought an auth. So I want to be able to count how many members have one month auth which uses the server time to set when the auth will run out (so when you buy an auth it will add 1 month of seconds to the server and store it in the database). I am also offering unlimited auths which is done by changing an int from 0 to 1. All I want to do, I want to count how many people have got a 1 month auth and an unlimited auth and display it as one number.

 

I know how to count the total rows but I don't how to do this. Any help would be great!

 

Josh

[dropfaith]

if i read this right all you need is a where clause?

 

 

or this

 

<?php

$link = mysql_connect("localhost", "mysql_user", "mysql_password");
mysql_select_db("database", $link);

$result = mysql_query("SELECT * FROM table1 WHERE FIELDNAME = '1'", $link);
$num_rows = mysql_num_rows($result);

echo "$num_rows Rows\n";

?>

[Epidemic]

if i read this right all you need is a where clause?

 

 

or this

 

<?php

$link = mysql_connect("localhost", "mysql_user", "mysql_password");
mysql_select_db("database", $link);

$result = mysql_query("SELECT * FROM table1 WHERE FIELDNAME = '1'", $link);
$num_rows = mysql_num_rows($result);

echo "$num_rows Rows\n";

?>

 

That will count the users who have an unlimited auth but some users don't have that and have a monthly time. The monthly time is done by using the current server time and adding one month on top of it, and storing it into the database, in its own separate field. Then the auth runs out after the current server time goes past the server time I have stored in the database.

[fenway]

Since we don't know your table structure, we can't guess.

[Epidemic]

I have my database set out like so,

 

 

The expiration fields are where the server time + one month are place and the Unl fields are where 1 or 0 is stored.

 

This is what I have so far,

 

<?php
include "config.php";
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("Database does not exist!");

$result1 = mysql_query("SELECT * FROM $tbl_name WHERE fletchUnl = '1'");
$num_rows1 = mysql_num_rows($result1);

$result2 = mysql_query("SELECT * FROM $tbl_name WHERE plankUnl = '1'");
$num_rows2 = mysql_num_rows($result2);

$num_rows = $num_rows1 + $num_rows2;

echo $num_rows;
?>

 

This counts the unlimited auths but not the monthly ones.

[fenway]

I'm confused about fletch and plank -- what do those mean? why are there booleans being stored in INT columns?

[Epidemic]

I'm confused about fletch and plank -- what do those mean? why are there booleans being stored in INT columns?

 

The fletch and plank are my product name prefixes and I just do booleans as ints. It's the way I learnt. I am just trying to make a statistics part in my website and I want to count how many people who have currently got access to the script.

[corbin]

SELECT COUNT(*) FROM table_name WHERE flecthUnl = 1 OR plankUnl = 1 OR fletchExpiration > UNIX_TIMESTAMP() OR plankExpiration > UNIX_TIMESTAMP();

 

 

Should do it.

 

Just checks if the columns are one, or if they're past now.

 

 

Your expiration columns store the expiration time, yes?  Not when the user paid?

[Epidemic]

SELECT COUNT(*) FROM table_name WHERE flecthUnl = 1 OR plankUnl = 1 OR fletchExpiration > UNIX_TIMESTAMP() OR plankExpiration > UNIX_TIMESTAMP();

 

 

Should do it.

 

Just checks if the columns are one, or if they're past now.

 

 

Your expiration columns store the expiration time, yes?  Not when the user paid?

 

That was the sort of thing I was thinking of but I didn't know you could use OR or > in MySQL.

 

I am currently using:

 

<?php
include "config.php";
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("Database does not exist!");

$query="SELECT COUNT(*) FROM members WHERE fletchUnl = '1' OR plankUnl = '1' OR fletchExpiration > UNIX_TIMESTAMP() OR plankExpiration > UNIX_TIMESTAMP()";
$result=mysql_query($query);

echo $result;
?>

 

But it is returning "Resource id #4". Any ideas on what is causing this?

[fenway]

$result is a recordset...

 

echo mysql_result( $result, 0 0 )

[Epidemic]

The code I was using before would only count a user if they had 1 type of auth. But if they had 2 it would still count it as 1.

 

So I tried this code instead. But it is only returning 0.

 

<?php
include "config.php";
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("Database does not exist!");

$query1="SELECT COUNT(*) FROM members WHERE fletchUnl = '1' OR fletchExpiration > UNIX_TIMESTAMP()";
$result1=mysql_query($query2);

$query2="SELECT COUNT(*) FROM members WHERE plankUnl = '1' OR plankExpiration > UNIX_TIMESTAMP()";
$result2=mysql_query($query3);

$query3="SELECT COUNT(*) FROM members WHERE fireUnl = '1' OR fireExpiration > UNIX_TIMESTAMP()";
$result3=mysql_query($query4);

$totalFletch = $result1;
$totalPlank = $result2;
$totalFire = $result3;

echo $totalFletch + $totalPlank + $totalFire;
?>

[corbin]

I don't get what you're trying to do?  Are you trying to only get unique members?

 

 

SELECT COUNT(*) FROM members WHERE (fletchUnl = 1 OR fletchExpiration > UNIX_TIMESTAMP()) OR (plankUnl = 1 OR plankExpiration > UNIX_TIMESTAMP()) OR (fireUnl = 1 OR fireExpiration > UNIX_TIMESTAMP());

[Epidemic]

The users who use my panel have 2 options for each item. They can buy a 1 month auth, or a lifetime auth. There are three products, fletch, plank and fire. I am trying to count the total amount of auths bought by seeing how many 1's there are in the Unl columns and how many server times that are bigger than the current time in all the columns.

[Epidemic]

So can anyone help me?

[fenway]

The code I was using before would only count a user if they had 1 type of auth. But if they had 2 it would still count it as 1.

 

So I tried this code instead. But it is only returning 0.

AGAIN, that's because you're not retrieving the value from the recordset correctly -- see my earlier post.

[Gamic]

Hopefully this will point you in the right direction.

<?php
$query1="SELECT COUNT(*) as countOfFletch FROM members WHERE fletchUnl = '1' OR fletchExpiration > UNIX_TIMESTAMP()";
$result1=mysql_query($query1);
if($result1){ // if result is a valid result resource
$answer1 = mysql_fetch_array ($result1, MYSQL_ASSOC)
echo $answer1["countOfFletch"]
} else {
echo "There was an error with query1.";
}//...
?>

 

Please also note you're calling mysql_query() on queries you have not yet defined (

$query1="SELECT COUNT(*) FROM members WHERE fletchUnl = '1' OR fletchExpiration > UNIX_TIMESTAMP()";
$result1=mysql_query($query2)