Hallo!

I'm having difficulties doing queries with more than one search parameter using UD4 and PHAkt 1.0.1. To illustrate my problem I'm using the "Define a recordset for the advanced results page"-example in the UD4-Tutorial. The search page uses a text field (txtLastName) and a list object (All - %, Administration - Administration, Operations - Operations, etc.) to enter the search parameters. As long as I enter something in the text field everything works fine, but as soon as I leave the text field blank - to display all employees of a selected department for example - no search results are displayed.

The Url with the search parameters looks like this: localhost/compass/Compass%20Intranet/ResultsAdv.php?txtLastName=&mnuDept=Administration&Submit=Search

What I found out so far is that when I add "%" as search parameter for txtLastName (txtLastName=%) in the url the correct results are displayed. My question now is, how do I get that "%" into the Url?
Maybe it has something to do with my settings in the advanced recordset menu: default-value and runtime-value for my variables are set to "%" (for default) and "$txtLastName" (for runtime). Can anybody tell me how to correctly set the values for the variables? Or what else could be the problem?

I'm using UD4, PHAkt 1.0.1, Omnicron HTTPd-Server 2.08, Win98SE and PHP
4.06.

<?php $Results__varLastName = "%";
if (isset($txtLastName))
{$Results__varLastName = $txtLastName;}?>
<?php $Results__varDept = "%";
if (isset($mnuDept))
{$Results__varDept = $mnuDept;}?>
<?php $Results=$conn_compass->Execute("SELECT EMPLOYEEID, FIRSTNAME,
LASTNAME, DEPARTMENT, EXTENSION FROM employees WHERE LASTNAME like '" .
($Results__varLastName) . "' and DEPARTMENT like '" . ($Results__varDept) .
"'" or DIE($conn_compass->ErrorMsg());


Can anybody give me a hint on what I'm doing wrong? Any help would be highly appreciated!

Thanks,
Jose

Copy the following and replace your current SQL statement :-

SELECT EMPLOYEEID, FIRSTNAME,
LASTNAME, DEPARTMENT, EXTENSION FROM employees WHERE LASTNAME like '" .
($Results__varLastName) . "'":" 1=1 and DEPARTMENT like '" . ($Results__varDept) .
"'":" 1=1"

Tim Green

Extension & PHP TalkZone Manager

------------------------------------------
www.UDzone.com : A dynamic Dreamweaver,
Ultradev and Fireworks site for developers
by developers.
------------------------------------------


Edited by - rawveg on 07/31/2001 02:11:11

Thanks a lot for the quick reply. I changed my SQL statement to the following, but get a parse error in the part highlighted in green now:

$Recordset1=$conn_compass->Execute("SELECT EMPLOYEEID, FIRSTNAME,
LASTNAME, DEPARTMENT, EXTENSION FROM employees WHERE LASTNAME like '" .
($Recordset1__varLastName) . <font color=green>"'":" 1=1 </font id=green>and DEPARTMENT like '" . ($Recordset1__varDept) .
"'":" 1=1" or DIE($conn_compass-&gt;ErrorMsg());

Any ideas? Thanks in advance...

Jose

Sorry, my fault. I retrieved the answer to this from my archive of posts in various lists, and I didn't at all implement it correctly.

Use this code instead. Hopefully it will sort the problem out for you.

&lt;?php $Results__varLastName = "%";
if (isset($txtLastName))
{$Results__varLastName = $txtLastName;}?&gt;
&lt;?php $Results__varDept = "%";
if (isset($mnuDept))
{$Results__varDept = $mnuDept;}?&gt;
&lt;?php
$rsWhere=isset($txtLastName)?"$txtLastName LIKE '".$Results__varLastName."'":" 1=1";
$rsWhere.=isset($mnuDept)?"$mnuDept LIKE '".$Results__varDept."'":" 1=1";
?&gt;
&lt;?php $Results=$conn_compass-&gt;Execute("SELECT EMPLOYEEID, FIRSTNAME,
LASTNAME, DEPARTMENT, EXTENSION FROM employees WHERE ".($rsWhere)."" or DIE($conn_compass-&gt;ErrorMsg());

Tim Green

Extension & PHP TalkZone Manager
<font size=1>-------------------------------------------
<i>Please read the Forum FAQ before posting
a question to this TalkZone.</i>
-------------------------------------------
www.UDzone.com : A dynamic Dreamweaver,
Ultradev and Fireworks site for developers
by developers.
-------------------------------------------</font id=size1>

Edited by - rawveg on 07/31/2001 16:12:43