Solved

Trying to compare 2 member tables...

Posted on 2009-07-14
11
218 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

630 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