Comparing two mysql tables, please help!

[Ian N]

I have a website which puts customers into different groups, depending
on the referrer they can see certain products. So for example if the
referrer code = 1, they'll only be able to see the products in group
1.

To achieve this I've used 2 database tables, one for all of the
products, and one for the groups (AGProds) which contains 2 colums
(GroupID and ProdID) the system works well and I've been happy with
it.

My problem is updating the products at the moment, they have to be done
one by one, what I'd really like is a page that displayed all the
products on one page, if the product was in the particular group, it
would put a tick in the tickbox, if not it would be left blank.

What I'm really struggling with is thinking of an efficient SQL call
to the 2 tables, I basically want to compare two tables, displaying all
of the products and flagging them if they're in another table.

Could someone help? Sorry for the long winded question.

Regards

Ian

[Sandman]

Re: Comparing two mysql tables, please help!

In article <1127753556.675 100.86050@g44g2 000cwa.googlegr oups.com>,
"Ian N" <iannorton@gmai l.com> wrote:
[color=blue]
> I have a website which puts customers into different groups, depending
> on the referrer they can see certain products. So for example if the
> referrer code = 1, they'll only be able to see the products in group
> 1.
>
> To achieve this I've used 2 database tables, one for all of the
> products, and one for the groups (AGProds) which contains 2 colums
> (GroupID and ProdID) the system works well and I've been happy with
> it.
>
> My problem is updating the products at the moment, they have to be done
> one by one, what I'd really like is a page that displayed all the
> products on one page, if the product was in the particular group, it
> would put a tick in the tickbox, if not it would be left blank.
>
> What I'm really struggling with is thinking of an efficient SQL call
> to the 2 tables, I basically want to compare two tables, displaying all
> of the products and flagging them if they're in another table.
>
> Could someone help? Sorry for the long winded question.[/color]

I would solve it with two SQL queries, like this:

<?
$q=mysql_query( "select * from AGProds") or print mysql_error();
while ($r=mysql_fetch _array($q)){
$groups[$r["ProdID"]][] = $r["GroupID"];
}

$q=mysql_query( "select * from Products") or print mysql_error();
while ($r=mysql_fetch _array($q)){
$g1 = in_array(1, $groups[$r["id"]]) ? "[X]" : "[ ]";
$g2 = in_array(2, $groups[$r["id"]]) ? "[X]" : "[ ]";
print "$r[name] - $g1 - $g2\n";
}
?>

It's dirty, but you may get the general idea. That would output something like:

Hair gel [X] [ ]
Shave gel [ ] [X]

And so on. You should adjust it to fit your output of course.

Now, if you want to select all products that only exist in group 2 - you do it
like this:

<?
$q=mysql_query( "select * from Products,AGProd where AGProd.ProdID =
Product.id and AGProd.GroupID = 2") or print mysql_error();
while ($r=mysql_fetch _array($q)){
print "$r[name] - $r[GroupID]\n";
}
?>

--
Sandman[.net]

[Ian N]

Re: Comparing two mysql tables, please help!

Thanks Sandman, i'll try and implement this now.

Cheers,

Ian