Jump to content


Photo

Need Help!! Search Form Not Returning NULL Fields!


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

#1 nsstudio

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

#2 obsidian

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.

<?php
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