Query Question

[cover]

I have a form with drop down menus to query for name, month, and year
to capture activity accordingly by an individual for a given month and
given year. But I'd like to also be able to query ALL individiduals
for a given month and year OR an individual for the whole year for
example.

My question, is there any way to have blank (not filled in) fields of
a query input ignored when left blank? I'm currently doing very
successfully what I described in the para above but it's taking me 3
queries (which I have on the same sheet for simplicity) to do it when
it would look a lot neater to have a single query and leave any drop
down menu items that I don't want to include on the query, blank and
ignored. Any ideas? TIA

[lesperancer]

so do you have an 'all' option in your drop down ?
what do the three existing queries look like ?

[Joseph Melnick]

Hello Cover,

$query = "select * from table where 1 = 1 ";
if($name != "") $query .= "and name = '".$name."'" ;
if($month != "") $query .= "and month = '".$month."' ";
if($year != "") $query .= "and year = '".$year."'" ;

Joseph Melnick
JM Web Consultants
http://www.jphp.com

"cover" <coverland914 @ yahoo.com> wrote in message
news:1117203234 .674c2d5cd748bf 86b45f941e0b1cb 1fb@teranews...

I have a form with drop down menus to query for name, month, and year
to capture activity accordingly by an individual for a given month and
given year. But I'd like to also be able to query ALL individiduals
for a given month and year OR an individual for the whole year for
example.

My question, is there any way to have blank (not filled in) fields of
a query input ignored when left blank? I'm currently doing very
successfully what I described in the para above but it's taking me 3
queries (which I have on the same sheet for simplicity) to do it when
it would look a lot neater to have a single query and leave any drop
down menu items that I don't want to include on the query, blank and
ignored. Any ideas? TIA

[jedihawk]

I don't see anything wrong with that code. Because you're appending to
the $query var, it's just one single SQL query. But your perl code
(I'm assuming you're using perl) is controlling the query structure.

BTW, I think your code would be more efficient if you used this syntax:

if( $name != '' ) $query .= "and name = '$name'";

or even:

( $name != '' ) && ( $query .= "and name = '$name'" );

Hope this helps.
-Hawk

[jedihawk]

I don't see anything wrong with that code. Because you're appending to
the $query var, it's just one single SQL query.

BTW, I think your code would be slightly more efficient if you used
this syntax:

if( $name != '' ) $query .= "and name = '$name'";

[Kimmo Laine]

"jedihawk" <je******@gmail .com> kirjoitti
viestissä:11*** *************** ***@g47g2000cwa .googlegroups.c om...

I don't see anything wrong with that code. Because you're appending to
the $query var, it's just one single SQL query.

BTW, I think your code would be slightly more efficient if you used
this syntax:

if( $name != '' ) $query .= "and name = '$name'";

Forgetting spaces here...

Something more like
if( $name != '' )
$query .= " and name = '$name' "; // padding string with some spaces

So the final query won't be
"select * from table where 1 = 1 and name = '$name'and month = '$month'and
year = '$year'";

but rather
"select * from table where 1 = 1 and name = '$name' and month = '$month'
and year = '$year' ";

--
"I am pro death penalty. That way people learn
their lesson for the next time." -- Britney Spears

et************* ***@5P4Mgmail.c om

[Chris]

What wonderful code - talk about slick... :-) don't quite understand
what the "from table where 1 = 1" is doing (the business with the '1'
but it sure works great - thanks so much...

A second question since this bit of magic happened so fast and if you
have a moment... On a database with name, month, year and so on is
days (as in days taken off). The query runs very nicely but I'd love
to have a total at the bottom of the query page.

name, date, days, notes pretty much comprise it with 1, 2, 3, and so
on being entered into a mysql database varchar field for the numbers.

Is adding the totalizer relatively easy on a query? I have what I
think is a pretty good book on PHP & mysql but haven't been able to
find any answer there.

thanks,
Chris (cover)
just changed my newserver

On Fri, 27 May 2005 11:56:16 -0400, "Joseph Melnick"
<jm******@jphp. com> wrote:

Hello Cover,

$query = "select * from table where 1 = 1 ";
if($name != "") $query .= "and name = '".$name."'" ;
if($month != "") $query .= "and month = '".$month."' ";
if($year != "") $query .= "and year = '".$year."'" ;

Joseph Melnick
JM Web Consultants
http://www.jphp.com

[Malcolm Dew-Jones]

Chris (co**********@y ahoo.com) wrote:
: What wonderful code - talk about slick... :-) don't quite understand
: what the "from table where 1 = 1" is doing (the business with the '1'
: but it sure works great - thanks so much...
WHERE 1 = 1 (i.e. where one equals one)

is always true.

It is the same as having no where clause, except that you can append
something like

" AND name='fred' "

and the statement will still work.

WHERE 1 = 1 AND name='fred'
Otherwise you need to worry about adding the WHERE clause and when to add
the AND.

It is a useful idiom, nothing more.

--

This space not for rent.

[Chris]

I really do appreciate ALL of your replies, guys - thanks so much...
I've gained from all of them as I learn more about PHP.

Chris

[Joseph Melnick]

Hello Kimmo,

You are correct. missing spaces are another trap.

Adding white space between clauses is important.

Thank you for noticing.

Joseph Melnick

"Kimmo Laine" <et************ *******@Mgmail. com> wrote in message
news:d7******** **@phys-news1.kolumbus. fi...

"jedihawk" <je******@gmail .com> kirjoitti
viestissä:11*** *************** ***@g47g2000cwa .googlegroups.c om...

I don't see anything wrong with that code. Because you're appending to
the $query var, it's just one single SQL query.

BTW, I think your code would be slightly more efficient if you used
this syntax:

if( $name != '' ) $query .= "and name = '$name'";

Forgetting spaces here...

Something more like
if( $name != '' )
$query .= " and name = '$name' "; // padding string with some spaces

So the final query won't be
"select * from table where 1 = 1 and name = '$name'and month =
'$month'and year = '$year'";

but rather
"select * from table where 1 = 1 and name = '$name' and month = '$month'
and year = '$year' ";

--
"I am pro death penalty. That way people learn
their lesson for the next time." -- Britney Spears

et************* ***@5P4Mgmail.c om