SQL conversion

[Barand]

Define "doesn't work".

 

What are the values in the search variables when it "doesn't work"?

[maliary]

 

Sorry for being so vague.

 

Well, what i mean is when i try searching using either the last name,  first name  or pid with the date range it doesn't give any results, when it actually should be.

 

Searching using a number with the date range -- for the encounter_nr -- produces the correct search results.

 

Here is the form that does the search,if it helps

 

<FORM action="<?php echo $thisfile; ?>" method="post" name="sform" onSubmit="return checkForm(sform.keyword)">

<B><?php echo 'Enter Date Range' ?></B></font><p>
From:<font size=3>
<INPUT  READONLY  type="text" name="from_date" size="20" maxlength="40" value=<?php echo '"'.$sBdayBuffer.'" '.$sDateJSfrom.' '?> </font>
<p></p>
To:    <font size=3>
<INPUT  READONLY   type="text" name="to_date" size="20" maxlength="40" value=<?php echo '"'.$sBdayBuffer.'" '.$sDateJSto.''?> </font>
<p></p>
<INPUT type="text" name="keyword" size="20" maxlength="40" value="<?php echo $keyword ?>"></font>
<input type=hidden name="search" value=1>  
<input type=hidden name="from" value="<?php echo $from_date ?>">
<input type=hidden name="to" value="<?php echo $to_date ?>"> 
<input type=submit name="submit" value=submit> 

</FORM>

[Barand]

have you tried

 

echo $this->sql;

 

when it doesn't work so you can see the actual submitted query?

[maliary]

 

Yes I have,

 

The search key is passed through all the variables. But it still doesn't work.

I broke down the query into 3 parts and tested the individual tables.

 

this produces the required results when a name is passed through as the search key.

 

$SQL="SELECT p.name_last,p.name_first, f.*,e.*
        FROM   care_person AS p
        INNER JOIN care_encounter AS e  ON p.pid = e.pid
        INNER JOIN care_test_findings_chemlab AS f ON e.encounter_nr = f.encounter_nr WHERE  test_date  BETWEEN '".$pn."' AND '".$cw."' OR  p.name_last LIKE '$key%'  ";   
                                                                                                                         

 

when I try searching using both encounter number and search key. I find that it can only search using the encounter key but the search using the name stops working.

 

 

$SQL="SELECT p.name_last,p.name_first, f.*,e.*
        FROM   care_person AS p
        INNER JOIN care_encounter AS e  ON p.pid = e.pid
        INNER JOIN care_test_findings_chemlab AS f ON e.encounter_nr = f.encounter_nr WHERE  test_date  BETWEEN '".$pn."' AND '".$cw."' AND  (p.name_last LIKE '$key%' OR e.encounter_nr=$key) ";   
                                                                                                                         

[fenway]

I see single quotes and PHP variables...?

[maliary]

Finally Got it!

 

The problem is the search key should have this %key% instead of a single key% as in encounter_nr =  %key%.

 

But now I have to trim the values. How do I do this?

[bubblegum.anarchy]

Use the php trim function:

 

trim($key)

[gregor171]

I think he was right from the start, as funny as it seems.

Sub-query seems as a good idea for this. But since it's working now it was just an idea.