Jump to content


Need Help!! Search Form Not Returning NULL Fields!

  • This topic is locked This topic is locked
1 reply to this topic

#1 nsstudio

  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 03 August 2005 - 11:19 PM

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'); ?>
$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'); ?>

if (strlen($_POST['address']) <> "")
$varaddress = "address LIKE " + $_POST['address'];
$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:

<!--#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_numRows = 0

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

#2 obsidian

  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 03 August 2005 - 11:26 PM

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.
You can't win, you can't lose, you can't break even... you can't even get out of the game.

while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users