SQL query select between 2 input fields

[UD2006]

I have 2 fields on my form for zipcode.

I want to make a selecting, for example to display records with zipcodes between the input of zipcode field1 and the input of zipcode field2.

I tried to following:

if(!empty($_POST)) {
$nbs_zip = $_POST['nbs_zip'];
$nbs_zip2 = $_POST['nbs_zip2'];
$nbs_contact_year = $_POST['nbs_contact_year'];
$nbs_year = $_POST['nbs_year'];
$nbs_relativeto = $_POST['nbs_relativeto'];
}

$sqlAND = ""; //edited to initiate before isset (if null doesnt work try $sqlAND ="";
if (isset($_POST['nbs_vmb'])) {
foreach ($_POST['nbs_vmb'] as $option) {
$sqlAND .="AND ". $option ." = '1' ";
}
}

$csv_terminated = "\n";
$csv_separator = ";";
$csv_enclosed = '"';
$csv_escaped = "\\";
$sql_query = "SELECT * FROM nbs_contacts JOIN nbs_events ON nbs_contacts.nbs_contact_id = nbs_events.nbs_contact_id WHERE nbs_zip BETWEEN $nbs_zip AND $nbs_zip2 AND nbs_contact_year LIKE \"%$nbs_contact_year%\" AND nbs_year LIKE \"%$nbs_year%\" AND nbs_relativeto LIKE \"%$nbs_relativeto%\" $sqlAND";


It does seem to work, but I when I make a selection for the zipcode between 1000 and 2000, I only get 6 records, while I know I have 8 records with the zipcode between 1000 and 2000.

Any idea why?

[UD2006]

Ok, this problem is solved, I overlooked something.

I had made a second table and made a join on the two tables, but the 2 records who are not in the selection don't have their id in the second table, that is why they were not selected.

But there is a small question I need to ask.

If I want to make a selection on the zipcode (as above), but also on the same way between years, I know I can't put 2 between statements into 1 query, but is there a way to solve this, so I can make the 2 selection (betweens zipcodes as well as between years)?


Edit:I've been able to add the second between for the years also, but when I run the query (with an echo so I can see what the query looks like), I see that the first between has '' (because I left those fields empty) and the second between has the items in it I typed.

How can I make the code so that it sees when a between is empty (the fields), then it should not be used in the query to get records.

[Old Pedant]

I know I can't put 2 between statements into 1 query

You "know" wrong.

Of course you can.


$sql = "SELECT [NEVER USE * in our SELECT!!!!] "
. " FROM nbs_contacts INNER JOIN nbs_events "
. " ON nbs_contacts.nbs_contact_id = nbs_events.nbs_contact_id "
. " WHERE nbs_zip BETWEEN $nbs_zip AND $nbs_zip2 "
. " AND nbs_contact_year BETWEEN $first_year AND $last_year "
. " AND nbs_relativeto LIKE '%$nbs_relativeto%' $sqlAND";

No idea where you heard you can only have one BETWEEN. That would be like saying you can only have one AND.

[Old Pedant]

And if you don need a given BETWEEN, just don't put it into the query:

$sql = "SELECT [NEVER USE * in our SELECT!!!!] "
. " FROM nbs_contacts INNER JOIN nbs_events "
. " ON nbs_contacts.nbs_contact_id = nbs_events.nbs_contact_id "
. " WHERE 1 = 1 ";
if ( ... need the zip code test ... )
{
$sql .= " AND nbs_zip BETWEEN $nbs_zip AND $nbs_zip2 ";
}
if ( ... need the year test ... )
{
$sql .= " AND nbs_contact_year BETWEEN $first_year AND $last_year ";
}
$sql .= " AND nbs_relativeto LIKE '%$nbs_relativeto%' $sqlAND";
...

[UD2006]

And if you don need a given BETWEEN, just don't put it into the query:

$sql = "SELECT [NEVER USE * in our SELECT!!!!] "
. " FROM nbs_contacts INNER JOIN nbs_events "
. " ON nbs_contacts.nbs_contact_id = nbs_events.nbs_contact_id "
. " WHERE 1 = 1 ";
if ( ... need the zip code test ... )
{
$sql .= " AND nbs_zip BETWEEN $nbs_zip AND $nbs_zip2 ";
}
if ( ... need the year test ... )
{
$sql .= " AND nbs_contact_year BETWEEN $first_year AND $last_year ";
}
$sql .= " AND nbs_relativeto LIKE '%$nbs_relativeto%' $sqlAND";
...


Thanks for the reply.

Dont know where I heard I could only use 1 between in a query thanks for clearing that up.

I was looking for a code that checks that when the nbs_zip and nbs_zip2 or nbs_contact_year and nbs_contact_year2 arent filled they shouldnt be in the query, because this is now the point. am I right that the part I quoted is for that?

Thanks again

[UD2006]

Can anyone help me with my last question (in my last post)?

[Old Pedant]

I'm not a PHP person, but I vaguely recall seeing an isset( ) or maybe it was $_ISSET( ) method for testing if a post or query string value is or is not present.

Oh, w.t.h. It's dirt easy to RTFM for this.

http://us.php.net/manual/en/function.isset.php

So:

$sql = "SELECT [NEVER USE * in our SELECT!!!!] "
. " FROM nbs_contacts INNER JOIN nbs_events "
. " ON nbs_contacts.nbs_contact_id = nbs_events.nbs_contact_id "
. " WHERE 1 = 1 ";
if ( isset($nbs_zip) && isset($nbs_zip2) ) /* you can do it this way... */
{
$sql .= " AND nbs_zip BETWEEN $nbs_zip AND $nbs_zip2 ";
}
if ( isset($first_year, $last_year) ) /* ...or this way, per the docs */
{
$sql .= " AND nbs_contact_year BETWEEN $first_year AND $last_year ";
}
$sql .= " AND nbs_relativeto LIKE '%$nbs_relativeto%' $sqlAND";
...


Online docs are really handy for answering questions like this.

[UD2006]

Thanks for the reply, but still I don't get the results I want.

I get an error message (used echo sql_query to see the query working).

And found out that both the between are in the query eventhough I haven't filled them.

So they shouldn't be put into the query when they are not filled.

[UD2006]

Problem solved, I played around with the code and made several if(!empty statements with else, to see if fields (for the between are empty or not) and it works the way I want it.

Thanks for your help.

[Old Pedant]

So isset( ) is not the right PHP function to use?

Sorry. Said I'm not a PHP person. I was just going by what the docs seemed to say.

[UD2006]

So isset( ) is not the right PHP function to use?

Sorry. Said I'm not a PHP person. I was just going by what the docs seemed to say.

Dont worry. Thanks for your help anyway.

[UD2006]

Problem comes back. I thought I solved the issue (it looked that way), but now the problem is back and don't know why.

I've created 2 betweens, 1 for zipcode and 1 for birthday year.

I've made the code the way (I thought I did), that when the second fields (zipcode2 and year2) are empty I just get a regular LIKE for the zipcode and year (first fields).

But if zipcode2 is also filled but year2 not (year and year2 aren't filled, but zipcode and zipcode2 are), it should create a query for the between on the zipcode en leave the other out of the query.

btw: when I filled zipcode and zipcode2 (should get a between), the query shows the following:

SELECT * FROM nbs_contacts JOIN nbs_events ON nbs_contacts.nbs_contact_id = nbs_events.nbs_contact_id WHERE nbs_relativeto LIKE '%%' AND nbs_contact_year LIKE '%%'



This is the code I have for now, don't know if there are any error:

if(!empty($nbs_zip2) && empty($nbs_contact_year2))
{
$sql_query = "SELECT * "
. " FROM nbs_contacts JOIN nbs_events "
. " ON nbs_contacts.nbs_contact_id = nbs_events.nbs_contact_id "
. " WHERE nbs_relativeto LIKE '%$nbs_relativeto%' ";
{
$sql_query .= " AND nbs_zip BETWEEN $nbs_zip AND $nbs_zip2 ";
}
$sql_query .= " $sqlAND";
}
else
{
$sql_query = "SELECT * "
. " FROM nbs_contacts JOIN nbs_events "
. " ON nbs_contacts.nbs_contact_id = nbs_events.nbs_contact_id "
. " WHERE nbs_relativeto LIKE '%$nbs_relativeto%' AND nbs_zip LIKE '%$nbs_zip%' $sqlAND";
}
if(!empty($nbs_contact_year2) && empty($nbs_zip2))
{
$sql_query = "SELECT * "
. " FROM nbs_contacts JOIN nbs_events "
. " ON nbs_contacts.nbs_contact_id = nbs_events.nbs_contact_id "
. " WHERE nbs_relativeto LIKE '%$nbs_relativeto%' ";
{
$sql_query .= " AND nbs_contact_year BETWEEN $nbs_contact_year AND $nbs_contact_year2 ";
}
$sql_query .= " $sqlAND";
}
else
{
$sql_query = "SELECT * "
. " FROM nbs_contacts JOIN nbs_events "
. " ON nbs_contacts.nbs_contact_id = nbs_events.nbs_contact_id "
. " WHERE nbs_relativeto LIKE '%$nbs_relativeto%' AND nbs_contact_year LIKE '%$nbs_contact_year%' $sqlAND";
}
if(!empty($nbs_zip2) && !empty($nbs_contact_year2))
{
$sql_query = "SELECT * "
. " FROM nbs_contacts JOIN nbs_events "
. " ON nbs_contacts.nbs_contact_id = nbs_events.nbs_contact_id "
. " WHERE nbs_relativeto LIKE '%$nbs_relativeto%' ";
{
$sql_query .= " AND nbs_zip BETWEEN $nbs_zip AND $nbs_zip2 ";
}
$sql_query .= " AND nbs_contact_year BETWEEN $nbs_contact_year AND $nbs_contact_year2 ";
{
$sql_query .= " $sqlAND";
}
}

echo $sql_query;

Hope someone can help me with this.

[Fumigator]

You're making this way too hard. Just build the query one brick at a time. Start with the basic query, then append conditions onto your WHERE clause if the user input calls for it. No need for a big hairy complex series of "if" statements.

Also I would make sure my variables that define the begin and end range values have default settings, i.e. the begin range is set to 0 and the end range is set to 999999999, THEN apply the input from the html form (or wherever the values are coming from).


//basic query
$sql_query = "
SELECT *
FROM nbs_contacts
JOIN nbs_events
ON nbs_contacts.nbs_contact_id = nbs_events.nbs_contact_id
WHERE nbs_relativeto LIKE '%$nbs_relativeto%'";

//check the zipcode input, append to query if a value exists
if(!empty($nbs_zip2)) {
$sql_query .= " AND (nbs_zip BETWEEN $nbs_zip AND $nbs_zip2)";
}

//check the contact year input, append to query if a value exists
if(!empty($nbs_zip2)) {
$sql_query .= " AND (nbs_contact_year BETWEEN $nbs_contact_year AND $nbs_contact_year2)";
}


Oh and I'm curious-- what is the $sqlAND variable for? What's in it?

p.s. You should be aware the empty() function will evaluate to TRUE if the variable has a value of zero. So if your variable can contain a value of zero and it's still valid, you'll need to account for that. I usually avoid the function because of this unfortunate fact.

[Old Pedant]

What I don't understand is why you would check *only* the zip2 and year2 values???

Surely if the first zip is missing you wouldn't want to try to do
BETWEEN [nothing here] AND 2008

I *REALLY* think you should use the kind of "if" tests I showed you in post #7, just using empty() in place of isset():

$sql = "SELECT [NEVER USE * in our SELECT!!!!] "
. " FROM nbs_contacts INNER JOIN nbs_events "
. " ON nbs_contacts.nbs_contact_id = nbs_events.nbs_contact_id "
. " WHERE AND nbs_relativeto LIKE '%$nbs_relativeto%' ";
if ( ! empty($nbs_zip) && ! empty($nbs_zip2) )
{
$sql .= " AND nbs_zip BETWEEN $nbs_zip AND $nbs_zip2 ";
}
if ( ! empty($nbs_contact_year) && ! empty($nbs_contact_year2) )
{
$sql .= " AND nbs_contact_year BETWEEN $nbs_contact_year AND $nbs_contact_year2 ";
}
$sql .= " $sqlAND";
...


Fumigator: He shows where $sqlAND comes from in his first post.

[Old Pedant]

Fumigator: Can you explain why my answer using isset( ) (post #7) would not have worked????

He uses isset( ) in building $sqlAND and it seems to work:

if (isset($_POST['nbs_vmb'])) {
foreach ($_POST['nbs_vmb'] as $option) {
$sqlAND .="AND ". $option ." = '1' ";
}


Does isset( ) need to be used on the $_POST[...] *before* one assigns that to a variable? Is that the problem???

That is, is there a difference between doing:

if ( isSet($_POST["xxx"]) ) ...

and

$xxx = $_POST["xxx"];
if ( isSet($xxx) ) ...

???

Sorry. Not a PHP user. Don't even have it installed on this machine. (Had it on a machine that crashed in August, but so seldom used it I haven't bothered putting it on this one.)

[UD2006]

//basic query
$sql_query = "
SELECT *
FROM nbs_contacts
JOIN nbs_events
ON nbs_contacts.nbs_contact_id = nbs_events.nbs_contact_id
WHERE nbs_relativeto LIKE '%$nbs_relativeto%'";

//check the zipcode input, append to query if a value exists
if(!empty($nbs_zip2)) {
$sql_query .= " AND (nbs_zip BETWEEN $nbs_zip AND $nbs_zip2)";
}

//check the contact year input, append to query if a value exists
if(!empty($nbs_zip2)) {
$sql_query .= " AND (nbs_contact_year BETWEEN $nbs_contact_year AND $nbs_contact_year2)";
}


I have tested your code and I still get the following message when I echo the query:

SELECT * FROM nbs_contacts JOIN nbs_events ON nbs_contacts.nbs_contact_id = nbs_events.nbs_contact_id WHERE nbs_relativeto LIKE '%%' AND nbs_contact_year LIKE '%%' AND nbs_zip LIKE '%1000%'


I filled both nbs_zip and nbs_zip2 and left contact year and contact year2 empty.

I also played around with the code and found out that when I have the following code:

$sql_query = "SELECT * "
. " FROM nbs_contacts JOIN nbs_events "
. " ON nbs_contacts.nbs_contact_id = nbs_events.nbs_contact_id "
. " WHERE nbs_relativeto LIKE '%$nbs_relativeto%'";
if (!empty($nbs_zip) && !empty($nbs_zip2) && empty($nbs_contact_year) && empty($nbs_contact_year2))
{
$sql_query .= " AND nbs_zip BETWEEN $nbs_zip AND $nbs_zip2 $sqlAND";
}
else {
$sql_query = "SELECT * "
. " FROM nbs_contacts JOIN nbs_events "
. " ON nbs_contacts.nbs_contact_id = nbs_events.nbs_contact_id "
. " WHERE nbs_relativeto LIKE '%$nbs_relativeto%' AND nbs_contact_year LIKE '%$nbs_contact_year%' AND nbs_zip LIKE '%$nbs_zip%' $sqlAND";
}
if (!empty($nbs_contact_year) && !empty($nbs_contact_year2) && empty($nbs_zip) && empty($nbs_zip2))
{
$sql_query .= " AND nbs_contact_year BETWEEN $nbs_contact_year AND $nbs_contact_year2 $sqlAND";
}
else
{
$sql_query = "SELECT * "
. " FROM nbs_contacts JOIN nbs_events "
. " ON nbs_contacts.nbs_contact_id = nbs_events.nbs_contact_id "
. " WHERE nbs_relativeto LIKE '%$nbs_relativeto%' AND nbs_contact_year LIKE '%$nbs_contact_year%' AND nbs_zip LIKE '%$nbs_zip%' $sqlAND";
}

I also get the message as above.
But when I "disable" the second if (for contact_year and contact_year2), the first query works.

What can be wrong?

[Fumigator]

Fumigator: Can you explain why my answer using isset( ) (post #7) would not have worked????

Because the variables $nbs_zip and $nbs_zip2 are both set (defined) in memory, even though the value they are set to is "". The $_POST array will contain the indices from the form even if the form is submitted with blanks in all the fields (with the exception of checkboxes which is an annoying HTTP header standard to not pass along a checkbox object if it's not checked).

Furthermore, empty($_POST) is inappropriate; should be is_array($_POST).

[Fumigator]

I have tested your code and I still get the following message when I echo the query:

SELECT * FROM nbs_contacts JOIN nbs_events ON nbs_contacts.nbs_contact_id = nbs_events.nbs_contact_id WHERE nbs_relativeto LIKE '%%' AND nbs_contact_year LIKE '%%' AND nbs_zip LIKE '%1000%'



That's pretty much impossible, since nowhere do I append AND nbs_zip LIKE to the query string. You seemed to have missed the concept I presented altogether; you shouldn't assume the concept is faulty if example code I post isn't completely bug free and ready to be copy/pasted.

Then concept is, don't rewrite the entire query for several different scenarios; build the query one brick at a time based on isolated conditions.

[Old Pedant]

Fumigator: Thanks for the clarification.

Okay, so it's the same as in ASP coding. (VBScript has an ISEMPTY() function that is virtually identical to isset(), and it can't be use for testing post values for the same reason.)

Which makes sense, becuz it all depends on what the browser sends in the HTTP Request.

In ASP, I just code this:

<%
If Trim( Request("xxx") ) <> "" Then
...
End If
%>

And the Trim() function [which normally is used to trim off leading and trailing spaces] guarantees that it returns a string, even if the thing trimmed is a null or empty value. So it even works for unchecked checkboxes, for example.

Seems like the right answer for PHP would to be do something similar. Create your own trim( ) function that handles null values, empty values, and string values all the same.

And it's handy for validation, in any case, to zap people who enter a string of 10 spaces in a UserName field, for example.

[Old Pedant]

Then concept is, don't rewrite the entire query for several different scenarios; build the query one brick at a time based on isolated conditions.

Which is exactly the same (kind of) answer I gave him in my first response. He seems to be just ignoring all our advice.

[Fumigator]

And the Trim() function [which normally is used to trim off leading and trailing spaces] guarantees that it returns a string, even if the thing trimmed is a null or empty value. So it even works for unchecked checkboxes, for example.

That confuses me since the HTTP request gives you no indication there is even a checkbox on the form if it's unchecked. Am I wrong about that??? In PHP the $_POST array doesn't contain any clue there is a checkbox on the form and in Cold Fusion, same thing. Putting both those pieces of evidence together led me to the conclusion an unchecked checkbox does not exist in the HTTP request. But if you get a value out using ASP.... Hmm! Perhaps it's just the way your condition is phrased, it catches non-existant form elements and handles them.

[Old Pedant]

Exactly.

It *assumes* that you know that there was a checkbox of that name in the <form> that was posted (or GET-ed).

And indeed operates on the basis that a non-value (either blank or empty) indicates that the checkbox was unchecked.

Obviously doesn't work to process <form>s with unknown content, but handles 95% or more of normal usage.

[UD2006]

That's pretty much impossible, since nowhere do I append AND nbs_zip LIKE to the query string. You seemed to have missed the concept I presented altogether; you shouldn't assume the concept is faulty if example code I post isn't completely bug free and ready to be copy/pasted.

Then concept is, don't rewrite the entire query for several different scenarios; build the query one brick at a time based on isolated conditions.

I think you missed my point:
As I told, I tested your code, but got NOT the results I want, so I added a little more things to the code, I've added a else.

See my code above:
But when I "disable" the second if (for contact_year and contact_year2), the first query works.

I've tested it with your code and also added with mine.

[Old Pedant]

You just seem to be fighting the advice both Fumigator and I are giving you.

I'll try ONE MORE TIME, based on the last set of code you posted:

$sql_query =
"SELECT * "
. " FROM nbs_contacts JOIN nbs_events "
. " ON nbs_contacts.nbs_contact_id = nbs_events.nbs_contact_id "
. " WHERE nbs_relativeto LIKE '%$nbs_relativeto%'";

if (!empty($nbs_zip) && !empty($nbs_zip2) )
{
$sql_query .= " AND nbs_zip BETWEEN $nbs_zip AND $nbs_zip2 ";
}
if ( !empty($nbs_contact_year) && !empty($nbs_contact_year2) )
{
$sql_query .= " AND nbs_contact_year BETWEEN $nbs_contact_year AND $nbs_contact_year2 ";
}
$sql_query .= $sqlAND;

echo "DEBUG sql_query: " . $sql_query . "<hr>\n";


I DO NOT understand WHY you think you need to tie together the two SEPARATE tests for the zip range and the contact year range.

Is it not possible that a person might put in values for *ALL FOUR* of those form fields???

Your code would ONLY allow *EITHER* the zip range *OR* the contact year range but NEVER both.

Your code had several other problems, too. Just for one example, if $nbs_zip is *NOT* empty, but all the other values *ARE* empty, then your code would nevertheless try to use the contact year range in building the SQL!

If you say the above code "doesn't work" then TELL US WHY. At a *MINIMUM* show us what the DEBUG statement that I added in there shows you on the web page. And if you get results, but not the ones you want, then tell us *IN DETAIL* how the results differ from what you want. But *STILL* showo us the DEBUG of the SQL query.