Posted by Sandman on 09/26/05 21:22

In article <1127753556.675100.86050@g44g2000cwa.googlegroups.com>,
"Ian N" <iannorton@gmail.com> wrote:

> 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.

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]

[Back to original message]