Solved

Trying to compare 2 member tables...

Posted on 2009-07-14
11
206 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
Technology Partners: 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Ajax and PHP 4 47
issue with DB import 1 36
Length of for loop to be dynamic 2 31
PHP Curl Problem 10 35
Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

756 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