Trying to compare 2 member tables...

Hi there,
I am pulling the members from one database and comparing it to the members of another and trying to count how many are on both membership sites... this returns a count but is much lower than it should be. What did I do wrong? thanks!

The first sql connection grabbed all the usernames okay (which is called membername and was put in a list) but then the second wont match and count correctly on the different database.

This is the 2nd connection to do so:

$connection = mysql_connect ("localhost","myusername", "mypassword") or die ("Cannot make the connection");
$db = mysql_select_db ("database",$connection) or die ("Cannot connect to database");
$sql_query1 = "SELECT user,count(*) as count FROM members WHERE user='$membername'";
$result = mysql_query($sql_query);
$mamount2 = mysql_num_rows($result);

  echo "$mamount2 are on both websites.";
yglobalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dvz-Commented:
$connection = mysql_connect ("localhost","myusername", "mypassword") or die ("Cannot make the connection");
$db = mysql_select_db ("database",$connection) or die ("Cannot connect to database");
$sql_query1 = "SELECT user,count(*) as count FROM members WHERE user='$membername'";
$result = mysql_query($sql_query1);
$mamount2 = mysql_num_rows($result);


do you have a previous $sql_query already defined?  cause your query var in this case is $sql_query1  and you were originally querying "mysql_query($sql_query)"
0
yglobalAuthor Commented:
oops, I didnt see that $sql_query1 - thanks!

But when I put this in it repeatedly shows
"are on both websites. are on both websites. are on both websites. are on both websites. are on both websites. are on both websites....."
and no count, it should say it once
0
dvz-Commented:
can you post all of the code?  it seems that there's a while loop there.
0
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

yglobalAuthor Commented:
OK, it is changed now. It counts all followers (members) on the first site OK. But it does not compare the member usernames and count how many is also on the 2nd site.

            $nrfollow = simplexml_load_file("http://somesite.com/users/show/$username.xml");
            $nrfollow = $nrfollow->followers_count;


            {
                  $followers = simplexml_load_file("http://$username:$password@somesite.com/statuses/followers/$username.xml?page=$i");
                  foreach ($followers as $follower)

$connection = mysql_connect ("localhost","myusername", "mypassword") or die ("Cannot make the connection");
$db = mysql_select_db ("mydatabase",$connection) or die ("Cannot connect to database");
$sql_query1 = "SELECT count(*) as count FROM members WHERE user='$follower'";
$result = mysql_query($sql_query1);
$mamount2 = mysql_num_rows($result);

  echo "$mamount2 are on both websites. And TOTAL of $nrfollow followers on the other site";


            }
0
dvz-Commented:
first...move the connection to the top..and maintain the connection, closing it at the end of the page...saves connection times / speed

second, if you're trying to compare members in two tables, what about using the SQL INNER JOIN command...it returns only rows where there is an entry/match in both tables

http://www.w3schools.com/SQl/sql_join_inner.asp
<?php
//connect to database only 1 time and maintain connection
$connection = mysql_connect ("localhost","myusername", "mypassword") or die ("Cannot make the connection");
$db = mysql_select_db ("mydatabase",$connection) or die ("Cannot connect to database");
 
            $nrfollow = simplexml_load_file("http://somesite.com/users/show/$username.xml");
            $nrfollow = $nrfollow->followers_count;
 
 
            {
                  $followers = simplexml_load_file("http://$username:$password@somesite.com/statuses/followers/$username.xml?page=$i");
                  foreach ($followers as $follower)
 
 
$sql_query1 = "SELECT count(*) as count FROM members WHERE user='$follower'";
$result = mysql_query($sql_query1);
$mamount2 = mysql_num_rows($result);
 
  echo "$mamount2 are on both websites. And TOTAL of $nrfollow followers on the other site";
 
 
            }

Open in new window

0
yglobalAuthor Commented:
OK, I put that up there and is counting all on the first one as usual but it says 1 for the other (when I know its hundreds) it says
"1 are on both websites. And TOTAL of 909 followers on the other site"
0
dvz-Commented:
hrm.  you can always try a debugging...and try this query out.

select count(*) from secondTable

and see how many it prints out?  just to verify...

first step of diagnosing in this case, verify the input.
0
yglobalAuthor Commented:
I did this below and it says 1 also, is this what you mean?

$sql_query2 = "SELECT count(*) as count FROM members";
$result = mysql_query($sql_query2);
$mamount22 = mysql_num_rows($result);
0
dvz-Commented:
hrm. try changing the query to SELECT count(*) as cnt FROM members

I'm not sure if count is a reserved word atm or not.
0
dvz-Commented:
DOH!

$mamount22 = mysql_num_rows($result);

that is only going to return 1 row...the count(*) counts all records and returns a single record as a result

that's the issue

try:

$mamount22 = mysql_fetch_row($result);
echo $mamount22[0];
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
yglobalAuthor Commented:
thank you so much! :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.