In article <11*********************@g44g2000cwa.googlegroups. com>,
"Ian N" <ia*******@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]