Multiple search criteria with empty field option

[bstanic]

Hi guys! I know I am being dumb here but running out of ideas. I am a newbie to
php and mysql and progressing quite well. However, I am currently going round
in circles re the following; I have created a simple search form with two
search boxes which sends the variables to a different page. When the Search
boxes are both completed and submitted, the results page works as it should and
returns the correct results. However, when only one box is used for the search,
the results page returns no values. By leaving the second box blank, the system
must still be interpretating this as a value of 'no value' and therefore is
saying that there are no results for box one criteria with this null value. The
question is; how do I set the Search Boxes to have default empty values or a
label such as --All--? Can anyone please point me in the right direction?

[-Rb]

"bstanic" <webforumsuser@macromedia.com> wrote in message
news:d2gkjj$73r$1@forums.macromedia.com...

> Hi guys! I know I am being dumb here but running out of ideas. I am a newbie
> to
> php and mysql and progressing quite well. However, I am currently going
> round
> in circles re the following; I have created a simple search form with two
> search boxes which sends the variables to a different page. When the Search
> boxes are both completed and submitted, the results page works as it should
> and
> returns the correct results. However, when only one box is used for the
> search,
> the results page returns no values. By leaving the second box blank, the
> system
> must still be interpretating this as a value of 'no value' and therefore is
> saying that there are no results for box one criteria with this null value.
> The
> question is; how do I set the Search Boxes to have default empty values or a
> label such as --All--? Can anyone please point me in the right direction?
>

Validate your form data before you create the query.
Generate the query based on the available form data.

HTH

-Rb

[bstanic]

Rb, thanks for your response. Not quite sure what you meant. Here is some more
information (if you do not mind). I have created 2 test pages.:The first is a
Form with two boxes with TextField labels, style and country. The Form action
is the name of the results page (page 2) i.e. searchlist.php. There is no
recordset or anything else on this page apart from the Form. Page 2 has a
simple table, listing table attributes (5 columns). The recodset query is
SELECT * FROM artist WHERE artist.artist_style = 'colname' AND
artist.country_id = 'colname2' The first Variables is; Name: colname, Default
value: 1, Run-time value: $HTTP_GET_VARS['style'] The second variable is:
Name: colname2, Default value: 1, Run-time value: $HTTP_GET_VARS['country']
'style' and 'country' are the name of the TextField labels in page 1 (Form
page) I tried to validate the Form as per your instructions (my
interpretation) by applying a Validate behavior from the Behavior Tab on the
Design panel but you have to validate at least one of the boxes i.e. you can't
create a validation where both fields can be left blank. However, to test if it
would work, i validated one of the fields to force a value and left the other
one not requiring input. Alas, still the same problem. It works OK with both
fields completed but returns no results when only one field is entered.
Regards B Stanic

[-Rb]

"bstanic" <webforumsuser@macromedia.com> wrote in message
news:d2jgq7$fsc$1@forums.macromedia.com...

> Rb, thanks for your response. Not quite sure what you meant. Here is some
> more
> information (if you do not mind). I have created 2 test pages.:The first is
> a
> Form with two boxes with TextField labels, style and country. The Form
> action
> is the name of the results page (page 2) i.e. searchlist.php. There is no
> recordset or anything else on this page apart from the Form. Page 2 has a
> simple table, listing table attributes (5 columns). The recodset query is
> SELECT * FROM artist WHERE artist.artist_style = 'colname' AND
> artist.country_id = 'colname2' The first Variables is; Name: colname,
> Default
> value: 1, Run-time value: $HTTP_GET_VARS['style'] The second variable is:
> Name: colname2, Default value: 1, Run-time value: $HTTP_GET_VARS['country']
> 'style' and 'country' are the name of the TextField labels in page 1 (Form
> page) I tried to validate the Form as per your instructions (my
> interpretation) by applying a Validate behavior from the Behavior Tab on the
> Design panel but you have to validate at least one of the boxes i.e. you
> can't
> create a validation where both fields can be left blank. However, to test if
> it
> would work, i validated one of the fields to force a value and left the
> other
> one not requiring input. Alas, still the same problem. It works OK with both
> fields completed but returns no results when only one field is entered.
> Regards B Stanic
>

I was referring to validating the variables on the second page, to check to
see if there is a value. Then creating the sql query.

Example:

if (isset($HTTP_GET_VARS['style']) && isset($HTTP_GET_VARS['country'])){
// write the sql query
} elseif (isset($HTTP_GET_VARS['style']) &&
!isset($HTTP_GET_VARS['country'])){
// write the sql query
} else (!isset($HTTP_GET_VARS['style']) && isset($HTTP_GET_VARS['country'])){
// write the sql query
}

This would require writing some code by hand. Sorry, I do not use the built-in
DW behaviors. Possibly some familiar with the DW server behaviors can offer
another suggestion.

Also, this does not include any security checking on these variables.

HTH

-Rb

[bstanic]

Rb, thanks once again. Unfortunately, although i am trying to pick up php as I
progress, I am not quite proficient enough to insert the code you sent. I
understand the logic and tried to insert it into my existing code but with no
luck. As a final attempt, I have sent you the code for the results page. Would
it be possible for you to insert the code you sent, then I could try and run it
again. It would also give me the opportunity to learn from it and will be
useful for helping me solve my own problems in the future. Thank you, once
again, for your help. Much appreciated. Regards

<?php require_once('Connections/THP.php'); ?>
<?php
$maxRows_Recordset1 = 10;
$pageNum_Recordset1 = 0;
if (isset($HTTP_GET_VARS['pageNum_Recordset1'])) {
$pageNum_Recordset1 = $HTTP_GET_VARS['pageNum_Recordset1'];
}
$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;

$colname_Recordset1 = "1";
if (isset($HTTP_GET_VARS['style'])) {
$colname_Recordset1 = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['style'] :
addslashes($HTTP_GET_VARS['style']);
}
$colname2_Recordset1 = "1";
if (isset($HTTP_GET_VARS['country'])) {
$colname2_Recordset1 = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['country']
: addslashes($HTTP_GET_VARS['country']);
}
mysql_select_db($database_THP, $THP);
$query_Recordset1 = sprintf("SELECT * FROM artist, categories, countries WHERE
artist.style_id = categories.style_id AND artist.country_id =
countries.country_id AND artist.style_id = '%s' AND artist.country_id = '%s'",
$colname_Recordset1,$colname2_Recordset1);
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1,
$startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $THP) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);

if (isset($HTTP_GET_VARS['totalRows_Recordset1'])) {
$totalRows_Recordset1 = $HTTP_GET_VARS['totalRows_Recordset1'];
} else {
$all_Recordset1 = mysql_query($query_Recordset1);
$totalRows_Recordset1 = mysql_num_rows($all_Recordset1);
}
$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;

$colname_Recordset2 = "1";
if (isset($HTTP_GET_VARS['style'])) {
$colname_Recordset2 = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['style'] :
addslashes($HTTP_GET_VARS['style']);
}
mysql_select_db($database_THP, $THP);
$query_Recordset2 = sprintf("SELECT * FROM artist, categories, countries WHERE
artist.style_id = categories.style_id AND artist.country_id =
countries.country_id AND artist.style_id = '%s'", $colname_Recordset2);
$Recordset2 = mysql_query($query_Recordset2, $THP) or die(mysql_error());
$row_Recordset2 = mysql_fetch_assoc($Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);

$colname_Recordset3 = "1";
if (isset($HTTP_GET_VARS['country'])) {
$colname_Recordset3 = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['country'] :
addslashes($HTTP_GET_VARS['country']);
}
mysql_select_db($database_THP, $THP);
$query_Recordset3 = sprintf("SELECT * FROM artist, categories, countries WHERE
artist.style_id = categories.style_id AND artist.country_id =
countries.country_id AND artist.country_id = '%s'", $colname_Recordset3);
$Recordset3 = mysql_query($query_Recordset3, $THP) or die(mysql_error());
$row_Recordset3 = mysql_fetch_assoc($Recordset3);
$totalRows_Recordset3 = mysql_num_rows($Recordset3);
?>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<table width="80%" border="1" cellspacing="2" cellpadding="2">
<tr>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
<?php do { ?>
<tr>
<td><?php echo $row_Recordset1['artistgroupname']; ?></td>
<td><?php echo $row_Recordset1['artist_style']; ?></td>
<td><?php echo $row_Recordset1['artistnames']; ?></td>
<td><?php echo $row_Recordset1['country_desc']; ?></td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
<?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
</table>
</body>
</html>
<?php
mysql_free_result($Recordset1);

mysql_free_result($Recordset2);

mysql_free_result($Recordset3);
?>

[bstanic]

RB, forgot to mention, Recordset1 is the query for both style and country.
Recordset2 is the query just for style
and Recordset3 is the query just for country.

[bstanic]

Finally fixed the problem and it was to do with the database query. In the
result page, I was using = when matching a field with the colname variable.
When I changed the query to LIKE and surrounded colname with % %, it worked
like a gem. Also, made it work with menu lists by changing the database
attributes of the menu items to be able to accept NULL values. Many thanks to
Rb for stimulating the 'grey cells' which allowed me to apply a bit more
lateral thinking.