Need Help!! Search Form Not Returning NULL Fields!

[nsstudio]

PLEASE HELP! This is sooo frustrating... I've created a search form that allows users to search a variety of fields - the more crietria they type in the narrower the results get. Dreamweaver MX has created the code, and it works, HOWEVER it won't return any results that have a NULL value for any of the query variables.

 

For example, let's say I have a simple mySQL table that has the fields "name_first", "name_last", and "address". And I have 3 records - the first two have values for all three fields, and the third record only has first & last name but the address is NULL. If I do any kind of search, with or without search criteria, that third record doesn't show up.

 

Here's my Dreamweaver-created code for the RESULTS page (only relevant code is included below):

 

<?php require_once('../Connections/bhleadmin.php'); ?>
<?php
$colnamefirst_rsStaff = "%";
if (isset($HTTP_POST_VARS['name_first'])) {
 $colnamefirst_rsStaff = (get_magic_quotes_gpc()) ? $HTTP_POST_VARS['name_first'] : addslashes($HTTP_POST_VARS['name_first']);
}
$colnamelast_rsStaff = "%";
if (isset($HTTP_POST_VARS['name_last'])) {
 $colnamelast_rsStaff = (get_magic_quotes_gpc()) ? $HTTP_POST_VARS['name_last'] : addslashes($HTTP_POST_VARS['name_last']);
}
$coladdress_rsStaff = "%";
if (isset($HTTP_POST_VARS['address'])) {
 $coladdress_rsStaff = (get_magic_quotes_gpc()) ? $HTTP_POST_VARS['address'] : addslashes($HTTP_POST_VARS['address']);
}
mysql_select_db($database_bhleadmin, $bhleadmin);
$query_rsStaff = sprintf("SELECT * FROM staff WHERE name_first LIKE '%%%s%%' AND name_last LIKE '%%%s%%' AND address LIKE '%%%s%%' ORDER BY id ASC", $colnamefirst_rsStaff,$colnamelast_rsStaff,$coladdress_rsStaff);
$rsStaff = mysql_query($query_rsStaff, $bhleadmin) or die(mysql_error());
$row_rsStaff = mysql_fetch_assoc($rsStaff);
$totalRows_rsStaff = mysql_num_rows($rsStaff);

 

 

I've been experimenting with variations of "IS NULL" so that if no criteria is submitted for that field in the search form, then it will return both % and NULL fields, but I can't seem to get it to work. Here is one of the many variations I tried (I threw out all of the Dreamweaver code) - in this case the records with NULL fields DID show up, but then my search criteria had no effect - all the records showed up no matter what I searched for.

 

<?php require_once('../Connections/bhleadmin.php'); ?>
<?php

if (strlen($_POST['address']) <> "")
$varaddress = "address LIKE " + $_POST['address'];
else
$varaddress = "(address LIKE '%' OR address IS NULL)"; 

$customquery = "$varaddress";

mysql_select_db($database_bhleadmin, $bhleadmin);
$query_rsStaff = ("SELECT * FROM staff WHERE $customquery ORDER BY id ASC");
$rsStaff = mysql_query($query_rsStaff, $bhleadmin) or die(mysql_error());
$row_rsStaff = mysql_fetch_assoc($rsStaff);
$totalRows_rsStaff = mysql_num_rows($rsStaff);

 

This seems like the most basic function - can anyone out there help? I've gotten this to work in the past using ASP:

 

<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="../Connections/clients.asp" -->
<%
Set rsResults = Server.CreateObject("ADODB.Recordset")
rsResults.ActiveConnection = MM_clients_STRING
rsResults.Source = "SELECT * FROM Clients"
dim appendSymbol
appendSymbol = " WHERE "

if (Request.Form("Name") <> "") then
 rsResults.Source = rsResults.Source + appendSymbol + "Name LIKE '%" + Request.Form("Name") + "%'"
 if (appendSymbol = " WHERE ") then appendSymbol = " AND " 
end if

if (Request.Form("Sex") <> "") then
 rsResults.Source = rsResults.Source + appendSymbol + "Sex LIKE '" + Request.Form("Sex") + "'"
 if (appendSymbol = " WHERE ") then appendSymbol = " AND " 
end if

rsResults.CursorType = 0
rsResults.CursorLocation = 2
rsResults.LockType = 1
rsResults.Open()

rsResults_numRows = 0
%>

 

 

Any help is sincerely appreciated, as I'm stuck on this project until I can figure this out! Thanks in advance!

[obsidian]

please refrain from double posting. it is against our forum guidelines. if you don't receive a response from someone at your first post after a little time, feel free to *bump* the post to get more recognition, but please try to avoid duplicate entries. i am closing this topic. please focus your attention on your other post.