How to filter queries using PHP

[Phil Powell]

http://www.php.net/array_filter

I went there at first for my information on filtering mySQL query results
using PHP, to no
avail.

This is more of a Vignette construct (my native environment) so bear with
me. I decided the
easiest course of action to tackle a perplexing problem involving two
queries and having to
display them in a certain customized manner:

Query 1
1 - 1 billion rows (who knows!), with field 'iref'

Query 2
1 - [infinity] rows (who knows!), with field 'iref'

the queries are based upon the concept that Query1.iref = Query2.iref and
that Query1 has
a one-to-many relationship with Query2, based on iref.

I am tasked with displaying in a report one row from Query1 and all
subsequent rows from
Query2 (which are "grouped" by iref for ease sake) where Query1.iref =
Query2.iref.

Problem is, I am having to use embedded WHILE loops to parse through the
queries and produce
my results, which is a horrific performance problem and the client is
way-not-happy about
that. Furthermore, I would have to do individual Query2 queries solely
based upon iref, which
means countless hundreds of db entry calls on a single query, still having
to parse inside
embedded WHILE loops. WORSE performance hog.

So I remember from my Vignette days a TCL proc written by VPS professionals
called FILTER
which would filter through the query resultset and only produce the
rows/cols where a col
field value = $fieldValue and that would make life a breeze.

But PHP alas has nothing like that, unless someone else can point me to a
direction where
I can find just that. I tried writing my own and I don't even know the
logic as to how to
imagine how to create my own FILTER.

Thanx
Phil

[Jochen Daum]

Re: How to filter queries using PHP

Hi Phil!

On Mon, 27 Oct 2003 22:42:12 -0500, "Phil Powell" <soazine@erols. com>
wrote:
[color=blue]
>http://www.php.net/array_filter
>
>I went there at first for my information on filtering mySQL query results
>using PHP, to no
>avail.[/color]

do it in the database.[color=blue]
>
>This is more of a Vignette construct (my native environment) so bear with
>me. I decided the
>easiest course of action to tackle a perplexing problem involving two
>queries and having to
>display them in a certain customized manner:
>
>Query 1
>1 - 1 billion rows (who knows!), with field 'iref'
>
>Query 2
>1 - [infinity] rows (who knows!), with field 'iref'
>
>the queries are based upon the concept that Query1.iref = Query2.iref and
>that Query1 has
>a one-to-many relationship with Query2, based on iref.
>[/color]
A so-called "inner join"

[color=blue]
>I am tasked with displaying in a report one row from Query1 and all
>subsequent rows from
>Query2 (which are "grouped" by iref for ease sake) where Query1.iref =
>Query2.iref.
>
>Problem is, I am having to use embedded WHILE loops to parse through the
>queries and produce
>my results, which is a horrific performance problem and the client is
>way-not-happy about
>that. Furthermore, I would have to do individual Query2 queries solely
>based upon iref, which
>means countless hundreds of db entry calls on a single query, still having
>to parse inside
>embedded WHILE loops. WORSE performance hog.
>
>So I remember from my Vignette days a TCL proc written by VPS professionals
>called FILTER
>which would filter through the query resultset and only produce the
>rows/cols where a col
>field value = $fieldValue and that would make life a breeze.[/color]

called a WHERE statement
[color=blue]
>
>But PHP alas has nothing like that, unless someone else can point me to a
>direction where
>I can find just that. I tried writing my own and I don't even know the
>logic as to how to
>imagine how to create my own FILTER.[/color]

Thats because PHP is more a web based swiss army knife and does not
integrate a database environment from start (by hiding this dependency
from you)


Look up

-select statement
- inner join
- where

on www.mysql.com

and look up in google how to display MySQL queries in tables in PHP.

Mosty probably you'll also need

- limit
- order by


HTH, Jochen


[color=blue]
>
>Thanx
>Phil
>[/color]

--
Jochen Daum - CANS Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.

http://sourceforge.net/projects/phpdbedittk/

[Nikolai Chuvakhin]

Re: How to filter queries using PHP

"Phil Powell" <soazine@erols. com> wrote in message
news:<5Clnb.104 966$0Z5.88767@l akeread03>...[color=blue]
>
> I decided the easiest course of action to tackle a perplexing
> problem involving two queries and having to display them in
> a certain customized manner:
>
> Query 1
> 1 - 1 billion rows (who knows!), with field 'iref'
>
> Query 2
> 1 - [infinity] rows (who knows!), with field 'iref'
>
> the queries are based upon the concept that Query1.iref = Query2.iref
> and that Query1 has a one-to-many relationship with Query2, based
> on iref.[/color]

Note that you are needlessly complicating your life. Reduce
two queries to one. Then there's no need to filter anything;
you will get right to the dataset you need. Something like:

SELECT table1.this, table2.that
FROM table1 LEFT JOIN table2 ON table1.iref = table2.iref
WHERE table1.somefiel d = 'SomeValue';

Cheers,
NC

[Phil Powell]

Re: How to filter queries using PHP

I cannot honestly think of simple solutions to anything. I'm dead serious,
I never saw that solution at all!

Phil

"Nikolai Chuvakhin" <nc@iname.com > wrote in message
news:32d7a63c.0 310272337.65e60 dab@posting.goo gle.com...[color=blue]
> "Phil Powell" <soazine@erols. com> wrote in message
> news:<5Clnb.104 966$0Z5.88767@l akeread03>...[color=green]
> >
> > I decided the easiest course of action to tackle a perplexing
> > problem involving two queries and having to display them in
> > a certain customized manner:
> >
> > Query 1
> > 1 - 1 billion rows (who knows!), with field 'iref'
> >
> > Query 2
> > 1 - [infinity] rows (who knows!), with field 'iref'
> >
> > the queries are based upon the concept that Query1.iref = Query2.iref
> > and that Query1 has a one-to-many relationship with Query2, based
> > on iref.[/color]
>
> Note that you are needlessly complicating your life. Reduce
> two queries to one. Then there's no need to filter anything;
> you will get right to the dataset you need. Something like:
>
> SELECT table1.this, table2.that
> FROM table1 LEFT JOIN table2 ON table1.iref = table2.iref
> WHERE table1.somefiel d = 'SomeValue';
>
> Cheers,
> NC[/color]

[Terence]

Re: How to filter queries using PHP

Phil Powell wrote:
[color=blue]
> I cannot honestly think of simple solutions to anything. I'm dead serious,
> I never saw that solution at all![/color]

Phil, that is a "shocker" ;)
.... we all have 'em ...
[color=blue][color=green]
>>SELECT table1.this, table2.that
>> FROM table1 LEFT JOIN table2 ON table1.iref = table2.iref
>> WHERE table1.somefiel d = 'SomeValue';[/color][/color]

If this query is heavily used or has a lot of data, make sure you create
indexes on "both" columns in the join (one from each table). If you
created one as the primary key, then it should already have a UNIQUE
index automatically created for it. You also want to index the column
you're filtering on ie. "table1.somefie ld" as per above example. If you
plan on using an ORDER BY clause, then create an index that spans the
columns in the order-by list (if they are from the same table).

Too many indexes impact on your INSERT performance, but I'm guessing
that you are doing a lot of reading. Database optimisation is probably a
black art, I don't know that much about it, but I think the above should
be good advice. Too many indexes are bad for other reasons to but I
forgotten them presently :)
Any experts out there with some DB optimisation advice???

good luck Phil.