Solved

Trying to compare 2 member tables...

Posted on 2009-07-14
11
172 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
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 …

757 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