...

View Full Version : count rows?



markman641
09-05-2011, 05:17 AM
Okay, this is much harder then I originally thought. I'm trying to display results
but I want it to get the row 'ref' from members and count the number of members that have the same name under ref and have 'offerscompleted' > 0

Its pretty hard to explain. But im trying to make a referral contest. Its supposed to show the username, the number of refs that person has that has completed one offer

ASTP001
09-05-2011, 08:00 AM
Please be more descriptive. I am not sure how your tables are structured, or what it is exactly that you are trying to do.

markman641
09-05-2011, 08:57 AM
It's extremely difficult to explain..

So lets say a member Member1 signs up at my website under the ref Admin. Lets say this member1 completes an offer so now his offerscompleted > 0 now. The user Admin has just earned one referral towards the contest. How would I make it so it counts the number of members that have the same ref and 1 offer completed?

myfayt
09-05-2011, 06:21 PM
mysql_query("UPDATE users SET offerscompleted=offerscompleted+1 WHERE ref='Admin' AND member='member1' LIMIT 1");

Change it into variables to select which member gets it.

or if you want to get the total Admins that have 1 offer point, you'd do this.


$query = mysql_query("SELECT * FROM users WHERE ref='Admin' AND offerscompleted > '0'");
if (mysql_num_rows($query) == 0) {
echo 'No Matches';
}
else {
echo 'There is matches';
}

markman641
09-05-2011, 07:45 PM
$query = mysql_query("SELECT * FROM users WHERE ref='Admin' AND offerscompleted > '0'");
if (mysql_num_rows($query) == 0) {
echo 'No Matches';
}
else {
echo 'There is matches';
}

Right, But that counts only the number of refs the Admin has. What about other members?

ASTP001
09-05-2011, 08:31 PM
You run the script as you needed on the page that will display the value. If you want to fetch the ref number for all the people at the same time, put that in the while loop under the query SELECT * FROM users... so

myfayt
09-05-2011, 08:33 PM
You have to change it into variables for your website, like this.


$query = mysql_query("SELECT * FROM users WHERE ref='".$userrow['status']."' AND offerscompleted > '0'");
if (mysql_num_rows($query) == 0) {
echo 'No Matches';
}
else {
echo 'There is matches';
}

So if your main user row was $userrow, you'd have a field named ref
and in that field would be like Member, Admin, Guest.

That would auto load in the query and show you for that ref.

markman641
09-05-2011, 09:16 PM
I'm confused now. What is the
WHERE ref='".$userrow['status']."'

????

myfayt
09-05-2011, 10:38 PM
status would be whatever the name of the field in your database is, where Admin is under.

markman641
09-06-2011, 12:04 AM
I don't think you quite understand what im trying to accomplish.

myfayt
09-06-2011, 12:24 AM
Okay well I've done my best with the little information I had to go on. So if you'd like someone else to look at it, that's okay.

guelphdad
09-06-2011, 03:06 AM
I don't think you quite understand what im trying to accomplish.

your lack of details contributes to the problem. Show a basic layout of your table and an example of the final results. It will be easier to assist you at that point.

markman641
09-06-2011, 06:05 AM
Is really hard to explain.

I'm trying to count the number of members that have the same name under Ref.

So if i have 10 members, 3 have Admin under Ref and 2 of them completed an offer. Then there is a member named Animal that has 7 refs and all of them have completed an offer i want it to display that admin has 2 refs and Animal has 7.

ASTP001
09-06-2011, 07:14 AM
Are you using php in conjunction with your SQL? Could we please see the code that you have so far, maybe it will be clarified what you're trying to do that way.

markman641
09-06-2011, 08:34 AM
I don't have any code so far since I have n clue how to do it. and yes im using php

guelphdad
09-06-2011, 03:55 PM
Do you have a table? Yes then in your mysql client run:

SHOW CREATE TABLE tablename -- substitute the actual name of your table.

post the results here between [ code ] and [\ code] tags.

Then either dump your table using mysqldump or actually create the INSERT statements by hand.

That allows someone to replicate your table and understand the problem easier.

Since you are currently counting something you'll need a COUNT(*) in your SELECT query. You'll also need a GROUP BY clause to group all Animal together and all Admin together.

Also, while it is still early in your database life ... get out of the habit of using SELECT * and actually name the columns you need.

This may not work for you but modify accordingly:



SELECT
ref,
count(*) as Total_Referrals
FROM
users
WHERE
offerscompleted > 0
GROUP BY
ref

markman641
09-07-2011, 03:07 AM
SELECT
ref,
count(*) as Total_Referrals
FROM
users
WHERE
offerscompleted > 0
GROUP BY
ref


Hmm, interesting. Not too sure how i would output the count though

Old Pedant
09-07-2011, 03:57 AM
*START* by showing the layout of the table(s).

And, even better, showing the *CONTENT* of the table(s).

You really haven't managed to describe this in any way that anybody seems able to fathom. So showing data may be your only chance.

guelphdad
09-07-2011, 06:06 AM
Hmm, interesting. Not too sure how i would output the count though

Then you should really read a tutorial on using mysql and php together. Seems like you don't understand the basics.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum