Comparing two mysql tables, please help!

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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

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

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.



Re: Comparing two mysql tables, please help!

Quoted text here. Click to load it

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 =  and AGProd.GroupID = 2") or print mysql_error();
    while ($r=mysql_fetch_array($q)){
        print "$r[name] - $r[GroupID]\n";


Re: Comparing two mysql tables, please help!

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



Site Timeline