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

Query Question

P: n/a
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
Share this Question
Share on Google+
16 Replies


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

Jul 17 '05 #2

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
"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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a

"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

P: n/a
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

P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.