Excluding records from repeat region in PHP

[duncan.lovett@litho.co.uk]

PHP 4
MySQL 4.0.20
DW MW 2004 7.01

This problem is giving me a bit of a headache, and I'm getting nowhere
with it so thought I'd open it up to all, any help would be greatly
appreciated.

OVERVIEW
--------
I am creating an ecommerce site for an IT reseller. Customers must log
in to use the purchasing side of the site.

Individual customers that log in have certain products that they are
not allowed to see but this varies between users, so no two customer
may have the same hidden products.

To set this scenario up, I have a main table containing the whole range
of products the reseller sells, and a table referencing the hidden
products, consisting of a "User ID" column and a "Product ID" column.

In MySQL 4.1 I would have no problem running a subselect query and
pulling just allowed products into a recordset, unfortunately I am
running 4.0 which does not support this and I am not in a position to
upgrade the server.

I am currently getting around the problem by creating an 'Allowed
products' table rather thatn 'hidden' and listing "User ID" and
"Product ID" for all products they are allowed to view. This causes a
bit of a problem as the reseller has about 6500 purchasing customers
and 20000 products, so the 'allowed' table would grow incredibly huge,
thats 130,000,000 records if all are allowed!

GOAL
----
The way I am planning to get around this problem is keep the 'Hidden'
table, create TWO recordsets on a page - one, rsFetchProducts , a
"SELECT * FROM Products" and the other, rsFetchHiddenPr oducts, "SELECT
Product_ID from HiddenProducts where User_ID = 'users id number'".

I then apply a repeat region to rsFetchProducts to list all data from
the recordset.

*************** ***
What I need to do next is to filter this repeat region with PHP,
basically saying - if any of the Product_ID(s) in rsFetchProducts is
equal to any of the Product_ID(s) in rsFetchHiddenPr oducts, DON'T
include them in the repeat region.
*************** ***

This must be possible, but by background on PHP isn't too great, I know
I need an if condition in the repeat region, but am not sure if I need
to filter directly from the recordset or pop it into an array first and
then filter.

If you would like to see my code etc, I can email it to you. I would
appreciate some working code examples too due to lack of knowledge in
PHP.

Thanks guys
Dunc

[Ken Robinson]

Re: Excluding records from repeat region in PHP


duncan.lov...@l itho.co.uk wrote (in part):[color=blue]
> *************** ***
> What I need to do next is to filter this repeat region with PHP,
> basically saying - if any of the Product_ID(s) in rsFetchProducts is
> equal to any of the Product_ID(s) in rsFetchHiddenPr oducts, DON'T
> include them in the repeat region.
> *************** ***[/color]

Have you looked at the array function in PHP, especially
array_intersect () at http://www.php.net/array_intersect

Ken

[Zilla]

Re: Excluding records from repeat region in PHP

duncan.lovett@l itho.co.uk wrote:
[snip][color=blue]
> GOAL
> ----
> The way I am planning to get around this problem is keep the 'Hidden'
> table, create TWO recordsets on a page - one, rsFetchProducts , a
> "SELECT * FROM Products" and the other, rsFetchHiddenPr oducts, "SELECT
> Product_ID from HiddenProducts where User_ID = 'users id number'".
>
> I then apply a repeat region to rsFetchProducts to list all data from
> the recordset.
>
> *************** ***
> What I need to do next is to filter this repeat region with PHP,
> basically saying - if any of the Product_ID(s) in rsFetchProducts is
> equal to any of the Product_ID(s) in rsFetchHiddenPr oducts, DON'T
> include them in the repeat region.
> *************** ***
>
> This must be possible, but by background on PHP isn't too great, I know
> I need an if condition in the repeat region, but am not sure if I need
> to filter directly from the recordset or pop it into an array first and
> then filter.[/color]
[snip]

Try something like this:

<?php
$link = mysql_connect($ mysql_address, $mysql_user, $mysql_password ) or
die("Could not connect : " . mysql_error());
mysql_select_db ($mysql_databas e) or die("Could not select database");
$query = "SELECT Product_ID FROM HiddenProducts WHERE User_ID = 'users
id number'";
$result = mysql_query($qu ery) or die("Query failed : " . mysql_error());
//Fetch results as associative array.
while($row = mysql_fetch_arr ay($result, MYSQL_ASSOC)) {
//Saving user's hidden ids in array $hidden_ids.
$hidden_ids[] = $row[Product_ID];
}
$query = "SELECT * FROM Products";
$result = mysql_query($qu ery) or die("Query failed : " . mysql_error());
//Fetch results as associative array.
while($row = mysql_fetch_arr ay($result, MYSQL_ASSOC)) {
//Looping over $hidden_ids to check if the user is allowed to see
the product
foreach($hidden _ids as $value) {
if($row[Product_ID] == $value) {
$allowed = "no";
} else {
if($allowed != "no") {
$allowed = "yes";
}
}
}
if($allowed == "yes") {
//*************** *************** *************** **
//* Place the code to display the product here. *
//*************** *************** *************** **
}
}
?>

Zilla

--
HUSK: Fjern de store bogstaver i
e-mailen for at skrive til mig

REMEMBER: Remove the capital letters
in my e-mail to write to me.

[coolsti]

Re: Excluding records from repeat region in PHP

On Fri, 29 Apr 2005 02:41:46 -0700, duncan.lovett wrote:
[color=blue]
> PHP 4
> MySQL 4.0.20
> DW MW 2004 7.01
>
> This problem is giving me a bit of a headache, and I'm getting nowhere
> with it so thought I'd open it up to all, any help would be greatly
> appreciated.[/color]

A problem with subselects is that people forget or never realize that many
things can be performed without subselects. In fact, the problem you
describe, if I understand it correctly, is one for a left join.

Consider two tables:

create table products (productid int unsigned not null primary key);
create table products_not_al lowed (userid int unsigned not null,
productid int unsigned not null);

And here I have the product id in the products table as the field
products, and in the products_not_al lowed table, each row identifies a
user by its user id and the product id that this user is not allowed to
see.

The query which will produce what you then want, all the products that a
particular user is not allowed to see, is this:

select products.produc tid from products left join products_not_al lowed on
products.produc tid = products_not_al lowed.productid and userid =
$user where products_not_al lowed.productid is null

Here, $user is the user id if the user for this query.

The left join produces a table where there is a NULL in the rows for both
the user field and the products_not_al lowed.productid field whenever there
is not a match of product id's, that is whenever the product id is not in
the products_not_al lowed table for user = $user. So what you then want is
all the rows where either userid or products_not_al lowed.productid is
null. I used the latter in the above query.

This will work even if there are many more fields in your products table
that you also wish to show.

- steve

[coolsti]

Re: Excluding records from repeat region in PHP

> This will work even if there are many more fields in your products table[color=blue]
> that you also wish to show.
>
> - steve[/color]

Another thought, now that I just read what I sent: If my post above is the
solution you seek, then the important thing here is you can save yourself
an enormous amount of effort and code lines by having the database do as
much as possible for you, rather than the PHP code.

You should do this whenever possible. But don't forget that when things
get complicated with lots of joins, you should analyze the queries to
locate slow queries (joins can produce big intermediate results), and add
the appropriate indexes when needed. Amazing, what difference this makes.

- steve

[duncan.lovett@litho.co.uk]

Re: Excluding records from repeat region in PHP

Guys,

Thanks for all your suggestions, will have a look into all of them and
see which one works for me...

If anybody else has ideas on this please let me know as if I haven't
posted to say I've solved it, assume i'm still working with it!

Thanks again, I'll probably be asking some questions later...

[duncan.lovett@litho.co.uk]

Re: Excluding records from repeat region in PHP

Steve -

your suggestion worked an absolute treat - that's what I originally
wanted few weeks ago but had to come up with the 'Allowed Products'
table as a tempory get around to show a working demo - You don't
reallise how much hassle you've saved me!

Cheers for that - and thanks ken & zilla for taking time to make
suggestions too, go enjoy the weekend!!

Dunc

[coolsti]

Re: Excluding records from repeat region in PHP

On Fri, 29 Apr 2005 08:01:46 -0700, duncan.lovett wrote:
[color=blue]
> Steve -
>
> your suggestion worked an absolute treat - that's what I originally
> wanted few weeks ago but had to come up with the 'Allowed Products'
> table as a tempory get around to show a working demo - You don't
> reallise how much hassle you've saved me!
>
> Cheers for that - and thanks ken & zilla for taking time to make
> suggestions too, go enjoy the weekend!!
>
> Dunc[/color]

You are welcome, Dunc. I try really hard to get the database to do as much
as possible for me. For one thing, this is what it is for. And it is
amazing what it can do. If queries containing many joins become slow, they
can be speeded up by creating the proper indexes.

One situation where it is just about necessary to put everything into one
query is if you would like the database to limit the number rows returned,
for example if you want to show the 3rd set of 25 results, using limit
50,25. This would be rather inefficient if you needed to take out the
first 50 results as well, then do the limit with PHP code.

It pays to ask on a mysql or the php mysql forum for a good way to do the
query. I had someone help me there in a case where I needed to find rows
in one table which had ALL of one field matched in another table - now I
upgraded to mysql 4.1 to take advantage of things like group_concat and
subselects, and my queries are really becoming quite complicated. But they
do the trick!

- steve

[duncan.lovett@litho.co.uk]

Re: Excluding records from repeat region in PHP

Steve, have now run into another problem thats absolutely stumping me -
any ideas whether this one can be achieved with a single query?

post with problem details can be found at:

http://groups.google.co.uk/group/comp.lang.php/browse_thread/thread/8f0193f8d08f5e96/376cc53f6e045596?hl=en#376cc53f6e045596

don't know if you can help but can't hurt asking, as you fixed my last
headache!

Cheers,

Dunc