By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,550 Members | 2,725 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,550 IT Pros & Developers. It's quick & easy.

How to filter queries using PHP

P: n/a
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
Jul 17 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Hi Phil!

On Mon, 27 Oct 2003 22:42:12 -0500, "Phil Powell" <so*****@erols.com>
wrote:
http://www.php.net/array_filter

I went there at first for my information on filtering mySQL query results
using PHP, to no
avail.
do it in the database.
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.
A so-called "inner join"

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.
called a WHERE statement

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

Thanx
Phil


--
Jochen Daum - CANS Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.
http://sourceforge.net/projects/phpdbedittk/
Jul 17 '05 #2

P: n/a
"Phil Powell" <so*****@erols.com> wrote in message
news:<5Clnb.104966$0Z5.88767@lakeread03>...

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.


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.somefield = 'SomeValue';

Cheers,
NC
Jul 17 '05 #3

P: n/a
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:32**************************@posting.google.c om...
"Phil Powell" <so*****@erols.com> wrote in message
news:<5Clnb.104966$0Z5.88767@lakeread03>...

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.


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.somefield = 'SomeValue';

Cheers,
NC

Jul 17 '05 #4

P: n/a
Phil Powell wrote:
I cannot honestly think of simple solutions to anything. I'm dead serious,
I never saw that solution at all!


Phil, that is a "shocker" ;)
.... we all have 'em ...
SELECT table1.this, table2.that
FROM table1 LEFT JOIN table2 ON table1.iref = table2.iref
WHERE table1.somefield = 'SomeValue';


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

Jul 17 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.