Can't figure this out

[Archadian]

echo "<table class=\"gena\" align=\"center\" valign=\"top\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\" width=\"480\">";

eh();

$user = mysql_query("SELECT uname FROM users") or die("Users.");
$chk = mysql_fetch_array($user);

while($chkuser = $chk['uname']) {

	roster();

	$roster = mysql_query("SELECT name, note FROM roster_members WHERE name NOT LIKE '$chkuser' AND note NOT LIKE '%alt%'") or die("Roster.");

		while($for = mysql_fetch_array($roster)) {

			$ros = $for['name'];

			echo "<tr>";
			echo "<td align=\"center\" valign=\"top\" width=\"448\" height=\"27\">" . $ros . "</td>";
			echo "</tr>";

	}

}

echo "</table>";

 

I have been playing around with this and can't seem to get this to work at all. I am trying to get a list of names from the "roster DB" and compare it to the "user DB" and make a list of all the names in the Roster DB that aren't listed in the user DB. Confused? I know what im trying to do but i just can't seem to get it to work. If anyone has any ideas please let me know. This is driving me crazy. Thanks in advance

[skali]

while($chk = mysql_fetch_array($user)) {
$chkuser = $chk['uname'];

[Archadian]

Thx skali, i tried that and it printed everything out from the roster DB twice. I did this:

 

echo "<table class=\"gena\" align=\"center\" valign=\"top\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\" width=\"480\">";

eh();

$user = mysql_query("SELECT uname FROM users") or die("Users.");
$chk = mysql_fetch_array($user);

$chkuser = $chk['uname'];

	roster();

	$roster = mysql_query("SELECT name, note FROM roster_members WHERE name NOT LIKE '$chkuser' AND note NOT LIKE '%alt%'") or die("Roster.");

		while($for = mysql_fetch_array($roster)) {

			$ros = $for['name'];

			echo "<tr>";
			echo "<td align=\"center\" valign=\"top\" width=\"448\" height=\"27\">" . $ros . "</td>";
			echo "</tr>";

	}

echo "</table>";

 

But now its printing everything out from the roster DB only once but its not comparing the names to the user DB and leaving those names out.

[skali]

Use this single query

"SELECT DISTINCT(a.name) FROM roster_members a, users b 
WHERE a.name != b.uname";

[Archadian]

Tried it and it didn't work. the names of the tables are roster_members (roster DB) and eh_users (ehforums DB). Im trying to compare the names in both DB's and return the names from the roster DB that does not match the names in the ehforums DB. Trying to make a list of who hasn't registered for the forums yet. Hopefully it clears this up. Thanks

 

Don't mind the actual names of the tables in my code, im pulling the info from a different table now. what i explained above is what im trying to do.

[Archadian]

*bump* Anyone know how the code should be written in order to list the names in the roster DB that do not match the ones in the ehforums DB? Thanks

[benjaminbeazy]

pretty inefficient method, but if you're not gonna do it all the time and you NEED it done

you could use a loop for all roster A and inside run query to check against B

 

i.e.

$a_query = mysql_query("SELECT name from roster_a");

while($row = mysql_fetch_object($a_query)){

$a_name = $row->name;

$b_query = mysql_query("SELECT name from roser_b WHERE `name`='$a_name'");
if(mysql_num_rows($b_query) = 1)

etc...

[benjaminbeazy]

it's sad, i know... 

[btherl]

This sounds like a job for left join!

 

SELECT name
FROM roster_members
LEFT JOIN users
ON (roster_members.name = users.name)
WHERE users.name IS NULL

 

Why does it work?  A left join will keep rows from the left table even when there is no match in the right table.  It will fill the right table with NULL for every column for the rows that don't match.  So to check for "no match", you just check if any column from the right table is null.  This won't work if that column really can be null of course, but I assume users.name is never null.