473,324 Members | 2,239 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Query Question

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
Jul 17 '05 #1
16 2699
so do you have an 'all' option in your drop down ?
what do the three existing queries look like ?

Jul 17 '05 #2
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.674c2d5cd748bf86b45f941e0b1cb1fb@t eranews...
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

Jul 17 '05 #3
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

Jul 17 '05 #4
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'";

Jul 17 '05 #5
"jedihawk" <je******@gmail.com> kirjoitti
viestissä:11*********************@g47g2000cwa.goog legroups.com...
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.com
Jul 17 '05 #6
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


Jul 17 '05 #7
Chris (co**********@yahoo.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.
Jul 17 '05 #8
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

Jul 17 '05 #9
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.goog legroups.com...
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.com

Jul 17 '05 #10
Hi Chris

Funny thing is I have been working on a site (see all my recent posts)
where I have had to do something very similar, and I know how complicated
is gets very quickly.

Just for reference, you may want to look at adding some JavaScript to
your form that does some of the hard work for you, the form I have
been working on has something like 50 different queries that could be
made depending on what the user has sleeted, I found by using JavaScript
I could say if the user selects X then set Y to whatever. This way you
are only passing the info you really need.

Brian
Jul 17 '05 #11
I noticed that Message-ID: <6J***************@newsfe5-gui.ntli.net> from
Brian contained the following:
Just for reference, you may want to look at adding some JavaScript to
your form that does some of the hard work for you, the form I have
been working on has something like 50 different queries that could be
made depending on what the user has sleeted, I found by using JavaScript
I could say if the user selects X then set Y to whatever. This way you
are only passing the info you really need.


But you really should not rely on Javascript.

--
Geoff Berrow 0110001001101100010000000110
001101101011011001000110111101100111001011
100110001101101111001011100111010101101011
Jul 17 '05 #12
Hello Geoff,

This is definitely true.

JavaScript should only be used for the benefit of the client to provide
valid input.

Expect the unexpected. Any hacker can sent garbage data to your application.

Check input for length, type, range, format and remove garbage.

Get to understand Regular Expressions, They are your friend.

Write functions once that you can reuse to minimize your effort.
Joseph Melnick
JM Web Consultants
www.jphp.com


"Geoff Berrow" <bl******@ckdog.co.uk> wrote in message
news:gj********************************@4ax.com...
I noticed that Message-ID: <6J***************@newsfe5-gui.ntli.net> from
Brian contained the following:
Just for reference, you may want to look at adding some JavaScript to
your form that does some of the hard work for you, the form I have
been working on has something like 50 different queries that could be
made depending on what the user has sleeted, I found by using JavaScript
I could say if the user selects X then set Y to whatever. This way you
are only passing the info you really need.


But you really should not rely on Javascript.

--
Geoff Berrow 0110001001101100010000000110
001101101011011001000110111101100111001011
100110001101101111001011100111010101101011

Jul 17 '05 #13

"Joseph Melnick" <jm******@jphp.com> wrote in message
news:x_********************@rogers.com...
Hello Geoff,

This is definitely true.

JavaScript should only be used for the benefit of the client to provide
valid input.

Expect the unexpected. Any hacker can sent garbage data to your
application.

Check input for length, type, range, format and remove garbage.

Get to understand Regular Expressions, They are your friend.

Write functions once that you can reuse to minimize your effort.
Joseph Melnick
JM Web Consultants
www.jphp.com

I agree with what your saying, but for example the site I have been
working on recently required the user to select a 'store' or 'all stores'
My client asked for a tick box for all stores, and 2 drop down lists of
the store number or location.
By using JavaScript, if the 'all' box is ticked then set the 2 drop down
lists
to blank, if say the store location is selected then set the store number
and
tick box to blank, and so on.

This way when the form is submitted I can run various tests to see what
has been set, what to look for and what variables to use. The reason I have
done it this way is so I don't have to build a un-friendly user interface
that
would confuses the user.by select this if you want this, and that if you
want that
and so on.

I'm not saying that once the form has been submitted you should run Regular
Expressions on the incoming data, but this way you no what to run on what.
Brian

Jul 17 '05 #14
Hello Brian,

Yes you are correct. Using JavaScript for usability is valid.

You could have simplified this task with a single mullti-select without the
need for a checkbox.

<select name="locations" multiple>
<option value="99999" selected>All Locations</option>
<option value="89837">89837 - Smallville</option>
....
<option value="98374">98374 - Largerville</option>
</select>

Would simplify validation and reduce page real-estate.

Just a idea.

Have a great day.

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

I agree with what your saying, but for example the site I have been
working on recently required the user to select a 'store' or 'all stores'
My client asked for a tick box for all stores, and 2 drop down lists of
the store number or location.
By using JavaScript, if the 'all' box is ticked then set the 2 drop down
lists
to blank, if say the store location is selected then set the store number
and
tick box to blank, and so on.

This way when the form is submitted I can run various tests to see what
has been set, what to look for and what variables to use. The reason I
have
done it this way is so I don't have to build a un-friendly user interface
that
would confuses the user.by select this if you want this, and that if you
want that
and so on.

I'm not saying that once the form has been submitted you should run
Regular
Expressions on the incoming data, but this way you no what to run on what.
Brian

Jul 17 '05 #15
Built on machine and works fine:
PHP 5.0.4
Apache 2.0.53
MySQL Client API version 4.1.7

Running on a server with the code below hanging up unless it's
'remmed' out...:
PHP 4.1.1
Apache 1.3.23
MySQL Client API version 3.23.39

mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to
database"); // make connection to database
mysql_select_db($DBName) or die("Unable to select database $DBName");
// select database
// if (mysqli_connect_errno())
// {
// echo 'Error: Could not connect to database. Please report this
problem.';
// exit;
// }
Everything on my form worked fine on my development machine with the
newer versions of PHP, MySQL, and Apache. When I ran it on the server
with older versions, my query hung up.

QUESTION: The part that specifically hangs is "if
(mysqli_connect_errno())" of which I have to think something is there
that wasn't in the older versions so... Does anyone know an
equivalent line that will work on the older PHP ??? TIA


Jul 23 '05 #16
Chris (Chris <coverland914 @ yahoo.com>) decided we needed to hear...
Built on machine and works fine:
PHP 5.0.4
Apache 2.0.53
MySQL Client API version 4.1.7

Running on a server with the code below hanging up unless it's
'remmed' out...:
PHP 4.1.1
Apache 1.3.23
MySQL Client API version 3.23.39

mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to
database"); // make connection to database
mysql_select_db($DBName) or die("Unable to select database $DBName");
You don't need the code below in any case, since you have already
tested if your connection and database selection worked or not in
the lines above. However, you might wish to add the return value from
mysql_error() to the strings you output from die - that will
report the cause of any error more effectively.
// select database
// if (mysqli_connect_errno())
// {
// echo 'Error: Could not connect to database. Please report this
problem.';
// exit;
// }
Everything on my form worked fine on my development machine with the
newer versions of PHP, MySQL, and Apache. When I ran it on the server
with older versions, my query hung up.

QUESTION: The part that specifically hangs is "if
(mysqli_connect_errno())" of which I have to think something is there
that wasn't in the older versions so... Does anyone know an
equivalent line that will work on the older PHP ??? TIA


The mysqli library is seperate from the mysql library and provides
different (improved) functions - so you probably shouldn't be mixing
up using both sets of functions anyway - use one or the other, but
not both together. The reason the call doesn't work in the older
version of PHP is probably that its not compiled in or enabled.

--
Dave <da**@REMOVEbundook.com>
(Remove REMOVE for email address)
Jul 23 '05 #17

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: majsen | last post by:
Hi, I have problem running this query. It will time out for me... My database are small just about 200 members. I have a site for swaping appartments (rental). my query should look for match in...
8
by: Együd Csaba | last post by:
Hi All, how can I improve the query performance in the following situation: I have a big (4.5+ million rows) table. One query takes approx. 9 sec to finish resulting ~10000 rows. But if I run...
3
by: John Ortt | last post by:
> I have a table of dates in ascending order but with varying intervals. I > would like to create a query to pull out the date (in field 1) and then pull > the date from the subsequent record...
3
by: Ekqvist Marko | last post by:
Hi, I have one Access database table including questions and answers. Now I need to give answer id automatically to questionID column. But I don't know how it is best (fastest) to do? table...
7
by: serge | last post by:
How can I run a single SP by asking multiple sales question either by using the logical operator AND for all the questions; or using the logical operator OR for all the questions. So it's always...
6
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID ...
2
by: mmitchell_houston | last post by:
I'm working on a .NET project and I need a single query to return a result set from three related tables in Access 2003, and I'm having trouble getting the results I want. The details: ...
22
by: Stan | last post by:
I am working with Access 2003 on a computer running XP. I am new at using Access. I have a Db with a date field stored as mm/dd/yyyy. I need a Query that will prompt for the month, ie. 6 for...
3
by: Richard Hollenbeck | last post by:
I am very sorry about the (almost) re-post, but you will see that my first question wasn't very clear; I have another question I posted this morning called, "in DAO: Run time error 3061 Too few...
16
by: ARC | last post by:
Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.