Solved

Trying to compare 2 member tables...

Posted on 2009-07-14
11
182 Views
Last Modified: 2013-12-13
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.";
0
Comment
Question by:yglobal
  • 6
  • 5
11 Comments
 
LVL 5

Expert Comment

by:dvz-
ID: 24854050
$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
 

Author Comment

by:yglobal
ID: 24854136
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
 
LVL 5

Expert Comment

by:dvz-
ID: 24854175
can you post all of the code?  it seems that there's a while loop there.
0
 

Author Comment

by:yglobal
ID: 24854223
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
 
LVL 5

Expert Comment

by:dvz-
ID: 24854282
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:yglobal
ID: 24854365
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
 
LVL 5

Expert Comment

by:dvz-
ID: 24854476
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
 

Author Comment

by:yglobal
ID: 24854530
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
 
LVL 5

Expert Comment

by:dvz-
ID: 24854624
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
 
LVL 5

Accepted Solution

by:
dvz- earned 500 total points
ID: 24854652
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
 

Author Closing Comment

by:yglobal
ID: 31603480
thank you so much! :)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now