cant send null results

form sends results but wont search on all when null

[rogerg1967]

I hope you can help with this, I have a search form with 6 drop down menus. each menu has several options including an all lable with a null value.
this form sends to a recordset query which i need to sort by each menu or if "all" display all records within the other filters.

ie
field 1 "all"
field 2 "county"
field 3 "district"

I can set the filters to each value but when i enter "all" i get no results.

I hope this makes sence.
This is being used in a property search web page and the user may want to search by any or all of the filters.

Thanks for your time.

Roger

[justageek]

Can you post the query?

JAG

[rogerg1967]

Hi Jag,
i have only put in 2 querys as i wanted to see if i could get it to work and then put the rest in using the same information. I also use dreamweaver to enter this into my web page.

$fmdistrict_main_search = "-1";
if (isset($_POST['district'])) {
$fmdistrict_main_search = (get_magic_quotes_gpc())? $_POST['district'] : addslashes($_POST['district']);
}
$fmlocation_main_search = "-1";
if (isset($_POST['location'])) {
$fmlocation_main_search = (get_magic_quotes_gpc())? $_POST['location'] : addslashes($_POST['location']);
}
mysql_select_db($database_abruzzo, $abruzzo);
$query_main_search = sprintf("SELECT location, district FROM property WHERE location = '%s' AND district = '%s'", $fmlocation_main_search,$fmdistrict_main_search);
$main_search = mysql_query($query_main_search, $abruzzo) or die(mysql_error());
$row_main_search = mysql_fetch_assoc($main_search);
$totalRows_main_search = mysql_num_rows($main_search);

I hope this is what you want.

Roger

[justageek]

Can you echo out just the '$query_main_search' variable and let's look at just what the contents of that var is?

I'm guessing it's just a hosed value in the where clause in the query but I need to see it as it is sent to the db to know for sure.

JAG

[rogerg1967]

I have run this echo in my result page, as this is the only way i know how to!

<p><?php echo $_POST['location'];?></p>
<p><?php echo $_POST['district'];?></p>

when i send "all" (no value) i get no result, when i select from one of the drop down menus i get the corresponding result.

Is this what you mean, if not could you tell me how to echo the result you want.

Thanks again, sorry for my limited knowledge your help is very appreciated.

[justageek]

No worries...

Try this which is just what you posted before with just the echo of the query so we can see it. When it echos the value to the screen copy and paste it back (just the echo'd value) so we can take a look. Run it twice and grab the output from both. One when you say it works and one where you say it doesn't work:

$fmdistrict_main_search = "-1";
if (isset($_POST['district'])) {
$fmdistrict_main_search = (get_magic_quotes_gpc())? $_POST['district'] : addslashes($_POST['district']);
}
$fmlocation_main_search = "-1";
if (isset($_POST['location'])) {
$fmlocation_main_search = (get_magic_quotes_gpc())? $_POST['location'] : addslashes($_POST['location']);
}
mysql_select_db($database_abruzzo, $abruzzo);
$query_main_search = sprintf("SELECT location, district FROM property WHERE location = '%s' AND district = '%s'", $fmlocation_main_search,$fmdistrict_main_search);
echo "What do we have here?:: '".$query_main_search"'<br>";
$main_search = mysql_query($query_main_search, $abruzzo) or die(mysql_error());
$row_main_search = mysql_fetch_assoc($main_search);
$totalRows_main_search = mysql_num_rows($main_search);

JAG

[rogerg1967]

I just got this

Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING, expecting ',' or ';' in C:\wamp\www\abruzzo_dreams\all_results_page.php on line 13

line 13 is

echo "What do we have here?:: '".$query_main_search"'<br>";

[justageek]

Oops. Sorry. Need one more period(.).

Like so:

echo "What do we have here?:: '".$query_main_search."'<br>";

JAG

[rogerg1967]

ok with "all" selected

What do we have here?:: 'SELECT location, district FROM property WHERE location = '' AND district = '''

with location and district selected.

What do we have here?:: 'SELECT location, district FROM property WHERE location = 'Coastal' AND district = 'Chieti''

[justageek]

Ahhh. OK. So if you want to get ALL of the results then your query has to be something like:

'SELECT location, district FROM property'

The empty strings '' in the select is what is causing the problem. Nothing in the table matches the empty string so the query is doing just what you asked it to do. By removing the where clause you will get everything.

You mentioned null in a field1. If you are using that as a value to indicate ALL then use the following query:

'SELECT location, district FROM property where field1 is null'

Notice I used 'is' and not '=' when checking for null.

JAG

[rogerg1967]

OK i have put that line in, it now reads..

$fmdistrict_main_search = "-1";
if (isset($_POST['district'])) {
$fmdistrict_main_search = (get_magic_quotes_gpc())? $_POST['district'] : addslashes($_POST['district']);
}
$fmlocation_main_search = "-1";
if (isset($_POST['location'])) {
$fmlocation_main_search = (get_magic_quotes_gpc())? $_POST['location'] : addslashes($_POST['location']);
}
mysql_select_db($database_abruzzo, $abruzzo);
$query_main_search = sprintf("SELECT location, district FROM property WHERE location is null", $fmlocation_main_search,$fmdistrict_main_search);
echo "What do we have here?:: '".$query_main_search."'<br>";
$main_search = mysql_query($query_main_search, $abruzzo) or die(mysql_error());
$row_main_search = mysql_fetch_assoc($main_search);
$totalRows_main_search = mysql_num_rows($main_search);

Now if i select all on either of the 2 menus i get nothing and if i select coasal or chieti on either of the menus I get nothing!

Roger

[justageek]

Which field(s) has null values? The thread says "can't send null results" so I'm assuming you have a field with null values somewhere? Also...what does the query show in the echo?

I think you want this result set though:

'SELECT location, district FROM property'

That will return everything.

JAG

[rogerg1967]

Hi Jag i think i may have confused what i am trying to achieve. I have 6 drop down menus with several options in each menu.I want the filter to be able to search by any of the entered values. As long as there is a selection form each of the menus it works fine. But if i want them to be able to search on all the options in each menu option i get nothing. I have set in each menu an "all" label which has a null value. I was hoping that if the record had a null value it would send all the results within that field. So someone could select "all" from the menu district and lets say "Cheite" from the "location" menu and the result would be to show all the districts with the location of "cheite". I have 6 menus for this filter to work with so i limited my filter to just the 2 ("district" and "location") to try and keep i simple for me!

I hope this makes it a bit clearer as to my end result.

Roger

[justageek]

Ahhh. OK...I think I understand now.

So there are a couple ways to do it. You could string all the locations together with or statements like:

...WHERE (location = 'Coastal' OR location = 'Inland' OR location = 'Underwater') AND district = 'Chieti'

But that could get long. You could also do something like:

...WHERE location <> '' AND district = 'Chieti'

Both ways would give you ALL the locations in the district 'Chieti' with the second option above assuming there are no empty entries in the table.

Unfortunately you will have to build the query string with the possible combinations in it. So to get any location and any district your code could put together a where clause like:

...WHERE location <> '' AND district <> ''

That would give you any location and any district. Just keep adding the other options you have in the drop down and you should get what you want.

JAG

[rogerg1967]

OK ,now i always get the same result, so if i now change the menus to show location "hills" and district to "Pascara" I still get the "chieti" and "coastal" coming up in the list when they shouldn't!

this is the new record.

mysql_select_db($database_abruzzo, $abruzzo);
$query_main_search = "SELECT location, district FROM property WHERE location <> '' AND district = 'Chieti'";
$main_search = mysql_query($query_main_search, $abruzzo) or die(mysql_error());
$row_main_search = mysql_fetch_assoc($main_search);
$totalRows_main_search = mysql_num_rows($main_search);

I can give you the link to my testing server if that might help?

Roger

[justageek]

OK ,now i always get the same result, so if i now change the menus to show location "hills" and district to "Pascara" I still get the "chieti" and "coastal" coming up in the list when they shouldn't!

Assuming the database is OK then it has to be the way the query is put together in the php script. Check the query and make absolutely sure that the location and district (and everything else) are correct.

I'm guessing that you're not putting the condition in the php code to handle the queries. If you are not then you would get the same results back.

JAG

[rogerg1967]

I am really sorry about this but I am not getting this. I have tried putting in the code as you have said but i am still not getting any result when i have all the menus set to "all".

What could be wrong with the database that may cause this issue?
When you ask about conditions are you talking about variables i need to set in the query?

This is the query i have in at the moment.

$query_main_search = sprintf("SELECT location, district, FROM property WHERE location <> '' AND district = '%s'",

As i am so new to this I think i may be out of my depth. do you have any facilaties to sort this for me for a fee? If not are you happy to continue to help me with this till i get a resolution, I will be donating as you have put so much effort in.

[justageek]

do you have any facilaties to sort this for me for a fee?

Nah...the nice thing about this forum is we all just like to help :-)

Here is a little change to your code that should get you in the right direction. I code a little different than you do so I did it the way I would do it. Feel free to change.

$main_search = "select location, district from property where ";
if (isset($_POST['district'])) {
// If someone didn't choose 'all' then do the if
if($_POST['district'] <> 'ALL'){
$fmdistrict_main_search = "district = '".mysql_real_escape_string($_POST['district'])."'";
}else{
// Someone chose 'all' so do the else
$fmdistrict_main_search = "district <> ''";
}
}
$main_search = $main_search.$fmdistrict_main_search;

//So now add the location info

if (isset($_POST['location'])) {
// There is a location so add the 'and' to the condition
$main_search = $main_search." and ";
if($_POST['location'] <> "ALL"){
$fmlocation_main_search = "location = '".mysql_real_escape_string($_POST['location']."'";
}else{
$fmlocation_main_search = "location <> ''"
}
}
$main_search = $main_search.$fmlocation_main_search;

Now just send the $main_search query to the db and all should be fine. I didn't try the code so you may have to fix any typos.

JAG

[rogerg1967]

OK i have got most of it now i think, but as this is slightly different than the dreamweaver code i am not sure how you would echo this. I have used
"<?php echo $main_search ['location'];?>

When i do this i get "s"!

How do you echo your query

[rogerg1967]

I have been playing with the code and have come unstuck with the query. Can you tell me how to do the query please.

[justageek]

You'll have to post the latest code so I can see what is hosed exactly. You could sticky mail if it is to big to post.

JAG

[rogerg1967]

OK i sent them to you, I hope they are what you need.

Roger

[rogerg1967]

Hi JAG,
Please ignore all the last sticky mails i sent. I did manage to figure out what was happening and fix the problems. Thank you again for all your help you are just amazing as well as very patient.

I do however have a question which is unrelated to this issue but still a PHP code issue. If you are happy to help me again i would realy appreciate it.

Thanks again

Roger