Jump to content

Archived

This topic is now archived and is closed to further replies.

nsstudio

Need Help!! Search Form Not Returning NULL Fields!

Recommended Posts

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!

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
Guest
This topic is now closed to further replies.

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.