$Query is repeating my output...

medar

I have a query in a page that looks at two tables - matches their information, and outputs a name. The variable $cookiename determines who the user is on the page, and it matches users.username.

$query = "
SELECT members.mem_number, members.mem_name, members.mem_realname, users.username
FROM members, users
WHERE (members.mem_realname = '$cookiename')";

$sth = $dbh -> do_query($query);
while (list($Memnumber,$Memname,$Memreal,$named) = $dbh -> fetch_array($sth))
{
echo "<a href=\"showinfo.php?User=$Memreal\">$Memreal ($Memname)</a>, ";
}

The above matches a real person in a membership database to his various pseudonyms (I did three for my example). So:

user table (username only):
1. Bob
2. Fred

member table (name, realname):
1. Joe Cool - Bob
2. Susie - Bob
3. Jonny - Fred
4. Betty - Bob

So if I am BOB - and I go to my private page, it should show me the following:

"Joe Cool, Susie, Betty"

Now THIS DOES work great - however it REPEATS the query over and over...ie - it shows the three names again and again and again.

See anything wrong with my query that would cause it to repeat like that?

Thanks for the help.

Anon

try this for your sql statment
SELECT members.mem_number, members.mem_name, members.mem_realname, users.username
FROM members, users
WHERE ( members.memrealname= users.username and members.mem_realname = '$cookiename')";

You need to tell the query how the two tables are linked. What field joins the two tables in this case it seems that username is the Pkey, and Fkey (primarykey and forignkey) this will only work if two people can't have the same username. So you can't have two Bob users.

techek

What you really need is to have a closer look at the SELECT-command at the MySQL-website. There you'll see some small and basic examples on how to join (using AND is a join too!) two or more tables.

The problem you have is that all records from the first table is joined with all records from the second table (only restricted by one WHERE-clause on one table). So if you have eg. 10 records in table one and 100 in table two you'll get 1000 records back in the worst case.

Summary : Look at SELECTs using more than 1 table and how to join them.