mysql Query

compare and select from 2 tables

[Jaunty Edward]

Hi,
Please help me with the query. I have 2 tables users and members. tbl user has basic info of the site visitors while members has detailed info. I want to find out who are the users that have not become members so far:

Here is the query that gives me the list of users who have become members, while I want users that have not become members.

select users.id, users.username, members.username
from users, members
where users.username = members.username

Please help
Thanks

[omoutop]

Hi!

It would be better to have both visitors and members in a sinlge table and add a Flag where 1=members and 0=visitors...I think its better that way...I use that way in my sites...and its very easy to see whois registered or not...ex:

select username from users where flag = 0........

what do u think?

[Jaunty Edward]

Hi,

Actually I have not given you the actual details of the tables. Actually one of the tbl has inportand details like password and CC details while the other has complete details. One of the table is accessed by almost everyone in the organisation while the other is kept secure.

I just changed the db structure to explain the problem.

Thanks

[omoutop]

if u still want to try it your way with two different tables u can try this:

Members have detailed info while users have not, logically members should have a required field which they have to fill in which does not exist in the users table...so thats the idea...lets say that this required field is email...just as an example

$query3 = "select username from members join users on members.username = users.username where email=''";
$result3 = mysql_query($query3);

while ($row=mysql_fetch_assoc($result3))
{
$username = $row['username'];
echo $username;
};
this piece of code will find those who dont have an email and thsu, are not registered as members...
this might work or not....u can try...i will also try to find another logical way...
hope it helps...

[Jaunty Edward]

Hi,

thanks for your help but there is some error in the query.

#1052 - Column: 'username' in field list is ambiguous

can you please check it. I am a little weak with JOIN.

Thanks

[omoutop]

try this: also check that table names and field names are absolutely right in the script and DB and post again

$query3 = "select * from members join users on members.username = users.username where email=''";
$result3 = mysql_query($query3);

while ($row=mysql_fetch_assoc($result3))
{
$username = $row['username'];
echo $username;
};

[Jaunty Edward]

Hi,

I had tried this before but now it says

#1052 - Column: 'email' in where clause is ambiguous

I checked every tbl and feild is currect. I am using phpmyadmin before I put the query into php so I am checking only queries.

Thanks

[omoutop]

email is just an example:

as said before:

Members have detailed info while users have not, logically members should have a required field which they have to fill in which does not exist in the users table...so thats the idea...lets say that this required field is email...just as an example

change email with a required field of the members table and this field must not exist on the users table....

[Jaunty Edward]

Hi,

thank you so much for taking trourble that was my mistake. Though now the query works its only showing 3 results while I try to take out the details on the basis of fname which is a must for members table.

3 records while the number should be something like 400+

Thanks once again

[omoutop]

try this version and post again:
(dont forget to check names, fields etc.)
$query3 = "select * from users join members on users.username = members.username where members.fname=''";
$result3 = mysql_query($query3);

while ($row=mysql_fetch_assoc($result3))
{
$username = $row['username'];
echo $username;
};

[Jaunty Edward]

Hi,

still 3 records, however this time I get results form both tbls means * selects all the fields from both tables(its not a problem for me, I will select them later)

Thanks Again

[omoutop]

$query3 = "select * from users join members on users.username = members.username where members.fname=''";

select * from users .......it does not select from both tables but from table users...

Which is tha table that contains all usernames?ALL OF THEM (members or not)?

[Jaunty Edward]

Hi,

Both tbls have the field username while tbl users have some details members have complete details.

Members also have fname while users do not have fname.

Thanks

[Jaunty Edward]

Hi,

also users have 5000 entries while members have 4500 so users have all the entries.

Thanks

[omoutop]

Which table contains all usernames form both members.username and users.username?

[Jaunty Edward]

users

[omoutop]

so if users have all entries then this is the script...if its not working then we will need another logic...

$query3 = "select * from users join members on users.username = members.username where members.fname=''";
$result3 = mysql_query($query3);
while ($row=mysql_fetch_assoc($result3))
{
$username = $row['username'];
echo $username;
};
However u can still use my logic which is better (i use it and works fine...!)
go to table users...add the missing (members)fields in order to get all users there...add a flag as mentioned before to distinguish users and members, ask users to add more info when they become members...use that info and update tha table...its simple, faster and far better my friend...if u dont know how i can guide u getting to that point....go and create a test database and add 10 users only in the way i told u...

[Jaunty Edward]

Hi,

1 record returned...bhoo hoo hoo

yes I know that the flag system works great infact I use it on one of my site. But clients are clients(Boss and clients are same) they want it the way they like it.

Anyways thanks a million for putting your time into this. I will keep trying my luck.

Thanks once again.

[omoutop]

"clients are clients(Boss and clients are same) they want it the way they like it" VERY TRUE, I know what u mean, but neither clients nor bosses are programmers/devellopers...they just want a correct result...its up to the programmer my friend to reach such result in any way/logic he prefers or just knows that is right..in your case the approach is wrong and u know it...its up to u to explain some things as an expert inorder to show them that such approach is slower, less efficient, loads server more and more...in plain words present our approach and try to defend it by presenting advantages-disadvantages...i ll try to post what u want once I find some time.....my current project right now is KILLING ME....

[omoutop]

Any news with the script?

try version this also:
$query3 = "select distinct username as username from users join members on users.username = members.username where users.username <> members.username ";
$result3 = mysql_query($query3);
while ($row=mysql_fetch_assoc($result3))
{
$username = $row['username'];
echo $username;
};
just a thought..it might not work

[omoutop]

THINK I GOT IT!

Try this:

$query = "select username from users";

while ($result = mysql_query($query))
{
$username = $result;
$query2 = "select username from members where username = '$username'";

while ($result2 = mysql_query($query2))
{
$num_rows = mysql_num_rows($result2);
if ($num_rows>0)
{
echo $username;
}

};

};

If it is not working tell me where the problem is...i can try something else I have in mind...
plz post if it is working!

[arran]

Alternatively, you could do it in one line:

select users.id, users.username from users where users.username not in (select members.username from members)

arran.

[omoutop]

yes arran u r right!

Jaunty I got it my way anyway....
here is the code u need.

$query = "select * from users";

$result = mysql_query($query);
while ($row=mysql_fetch_assoc($result))
{
$username = $row['username'];

$query2 = "select * from members where username = '$username'";
$result2 = mysql_query($query2);
$num_rows = mysql_num_rows($result2);
if ($num_rows>0)
{

}
else
{
echo $username;
echo '<br>';

}
};
u can try other short ways as well like arran's!

[omoutop]

u can always use some more queries in order to get more results for statistics purposes and more....I have an example based exactly on ur needs(created identical Database and tables and stuff..)

full code is here:

<?
$query3 = "select count(username) as username from users";
$result3 = mysql_query($query3);
$res=mysql_fetch_assoc($result3);
$resusers = $res['username'];


$query4 = "select count(username) as username from members";
$result4 = mysql_query($query4);
$res2=mysql_fetch_assoc($result4);
$resmembers = $res2['username'];

$resnotmembers = ($resusers-$resmembers);




?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>

<p align="left"><strong><font color="#CCCCCC" size="+3">Statistics...</font></strong></p>
<table width="27%" height="209" border="0" cellpadding="0" cellspacing="0">
<tr>
<td height="209"><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="38%" bgcolor="#00CCFF"><p><strong>All Users: <br>(Total: <? echo $resusers;?>)</strong></p>
</td>
<td width="62%" bgcolor="#CCCCCC">
<?
$query2 = "select * from users";
$result2 = mysql_query($query2);
while ($row2=mysql_fetch_assoc($result2))
{
$username = $row2['username'];
echo $username;
echo '<br>';
};
?>
</td>
</tr>
</table>
<p>&nbsp;</p>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="38%" bgcolor="#00CCFF"> <p><strong>Members: <br>(Total: <? echo $resmembers;?>)</strong></p>
</td>
<td width="62%" bgcolor="#CCCCCC">
<?
$query3 = "select * from members";
$result3 = mysql_query($query3);
while ($row3=mysql_fetch_assoc($result3))
{
$username = $row3['username'];
echo $username;
echo '<br>';
};
?>
</td>
</tr>
</table>
<p>&nbsp;</p>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="38%" bgcolor="#00CCFF"><p><strong>Not Members: <br>(Total: <? echo $resnotmembers;?>)</strong></p>
</td>
<td width="62%" bgcolor="#CCCCCC">
<?php
$query = "select * from users";

$result = mysql_query($query);
while ($row=mysql_fetch_assoc($result))
{
$username = $row['username'];

$query2 = "select * from members where username = '$username'";
$result2 = mysql_query($query2);
$num_rows = mysql_num_rows($result2);
if ($num_rows>0)
{

}
else
{
echo $username;
echo '<br>';

}
};

?>
</td>
</tr>
</table>
<p>&nbsp;</p></td>
</tr>
</table>
</body>
</html>

u might need it anyway...

[edited by: jatar_k at 5:09 pm (utc) on Aug. 26, 2005]
[edit reason] no urls thanks [/edit]

[Jaunty Edward]

Hi Guys,

I am so sorry I could not come on the net yesterday. Thanks for putting so much of your time,

arran thanks for the help but your query uses subqueries and I have mysql 4.0.25 on the server. While locally I tried it it returned 14xx results( it should be around 400).

omoutop, you've done so much but as I tried few of your scripts so far they have not worked. I'll do one thing let me setup a demo DB online on one of my website and you can try your ideas.

I'll just send you a sticky with the user pass.

Thanks again.
Bye

[Jaunty Edward]

Hi,

let me currect my self, aaran your query works fine with mysql 4.1

I just realised that the result 14xx is the currect number. I don't know where is the actual problem but it seems there are a few members who do not have a entry in users tbl.

I will work on that.

At the same time omoutop has come up with his php made code that works well on mysql without subqueries support.

Here is the wonderful code:

$query = "select * from users";

$result = mysql_query($query);
while ($row=mysql_fetch_assoc($result))
{
$username = $row['username'];

$query2 = "select * from members where username = '$username'";
$result2 = mysql_query($query2);
$num_rows = mysql_num_rows($result2);
if ($num_rows>0)
{

}
else
{
echo $username;
echo '<br>';

}
};

Both of you thank you very much once again.

Bye

[omoutop]

u r welcome...does it work now as expected? I know that my code is far bigger than aaran's but it should get the results for u!r u finally getting the results?
hope yes! :)