searching

[jeff5656]

I am trying to do a search of my database, but it is not working properly. If I type in the patient name "ddd" (which is the 3rd record), it displays the data from the FIRST record.  If I type in the mrn of "1234" (which belongs to record 2), again, record 1 is displayed.

If I leave all fields blank int he search form, I DON'T get the echo "no results to display".

 

<?php  
include "connectdb.php";
$mrnstring = $_POST['mrn'];
$patientstring= $_POST['patient_name'];
$fellowstring= $_POST['fellow'];

echo '$mrn:' . $mrnstring . '<br/>';
echo '$patientstring:' . $patientstring . '<br/>';
echo '$fellowstring:' . $fellowstring . '<br/>';

$sql = "SELECT * FROM active_consults WHERE mrn LIKE '$mrnstring' or patient_name LIKE '$patientstring' or fellow LIKE '$fellowstring' ORDER BY id_incr DESC";
$query = mysql_query($sql) or die(mysql_error());
$row_sql = mysql_fetch_assoc($query);
$total = mysql_num_rows($query);

if($total>0) {
while ($row_sql = mysql_fetch_assoc($query)) {//echo out the results
echo ''.$row_sql['patient_name'].' '.$row_sql['mrn'].'' .$row_sql['fellow'];
echo '<br/>';
}
} else
{
echo "No results to display";
}

?>

also, if two records have the same mrn, only the first record gets displayed AND then record #1 gets displayed even if the mrn isn't the same!

[hansford]

this would be a problem with the SELECT statement;

 

why are you using LIKE in the statement? if your looking for exact matches then use something like this:

 

SELECT * FROM active_consults WHERE mrn = '$mrnstring' AND patient_name = '$patientstring' AND fellow = '$fellowstring'

 

 

[jeff5656]

I'm using LIKE because after I figure out this, I'm going to work on allowing partial searches (i.e. wildcards) and I read that LIKE is needed.  In any case, when i changed LIKE to =, it still didn't work.

[BlueSkyIS]

remove the first instance of this:

 

$row_sql = mysql_fetch_assoc($query);

 

[jeff5656]

remove the first instance of this:

 

$row_sql = mysql_fetch_assoc($query);

 

I removed that, but after the correct record displays, that first record STILL displays:

 

If I type 111 into the MRN field I get this output (1st 3 lines are the echo commands in the above script) The 3rd line (smnith) is the record that keeps getting displayed.  Note that the mrn is NOT 111, but is 12345:

 

$mrn:111

$patientstring:

$fellowstring:

 

dsad 111 gjj

ddd 111 fgfg

smnith 12345

[T Horton]

This is what I would do (and is taken from my website):

 

$query = "SELECT * FROM active_consults WHERE mrn LIKE '%$mrnstring%' or patient_name LIKE '%$patientstring%' or fellow LIKE '%$fellowstring%' ORDER BY id_incr DESC";
$total = mysql_num_rows($query);

if($total>0) {
while ($row_sql = mysql_fetch_array($query)) {//echo out the results
echo ''.$row_sql['patient_name'].' '.$row_sql['mrn'].'' .$row_sql['fellow'];
echo '<br/>';
}
} else
{
echo "No results to display";
}

 

Let me know how you get on with that.

 

Best Regards

 

 

 

Tom

[hansford]

I'd be interested to see your database table

 

in the SELECT statement your using OR which means match this or match that

so if any of those conditions are true that emtire row will be grabbed

[jeff5656]

I put the exported table here:

-- phpMyAdmin SQL Dump
-- version 2.10.1
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Generation Time: Jun 01, 2008 at 01:50 PM
-- Server version: 5.0.45
-- PHP Version: 5.2.5

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

-- 
-- Database: `consults`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `active_consults`
-- 

CREATE TABLE `active_consults` (
  `id_incr` int(254) NOT NULL auto_increment,
  `patient_name` varchar(20) NOT NULL,
  `mrn` int(12) unsigned NOT NULL,
  `location` varchar(10) NOT NULL,
  `fellow` varchar(20) NOT NULL,
  `rcf_date` varchar(20) NOT NULL,
  `rcf_date2` date NOT NULL,
  `admission` text NOT NULL,
  `consult_reason` text NOT NULL,
  `impression` text NOT NULL,
  `recs` text NOT NULL,
  `comments` text NOT NULL,
  `followup` enum('n','y') NOT NULL,
  `signoff_status` enum('s','a') default NULL,
  PRIMARY KEY  (`id_incr`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=18 ;

-- 
-- Dumping data for table `active_consults`
-- 

INSERT INTO `active_consults` (`id_incr`, `patient_name`, `mrn`, `location`, `fellow`, `rcf_date`, `rcf_date2`, `admission`, `consult_reason`, `impression`, `recs`, `comments`, `followup`, `signoff_status`) VALUES 
(14, 'smnith', 12345, '', '', '06/01/08', '2008-06-01', '', '', '', '', '', 'n', 'a'),
(15, 'bobby', 1234, '', 'fellowbobby', '06/01/08', '2008-06-01', '', '', '', '', '', 'n', 'a'),
(16, 'ddd', 111, '', 'fgfg', '06/01/08', '2008-06-01', '', '', '', '', '', 'n', 'a'),
(17, 'dsad', 111, '', 'gjj', '06/01/08', '2008-06-01', '', '', '', '', '', 'n', 'a');

[jeff5656]

T horton: when I used your code I got "no results to display" even when i typed in a mrn that was there.  However, I had to comment out the

//$total = mysql_num_rows($query);

 

or I got:

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\wamp\www\consults\searchengine.php on line 12

No results to display

[hansford]

you have this in your table

-----------------------------

'smnith', 12345, '06/01/08'

'bobby', 1234, 'fellowbobby'

'ddd', 111, '06/01/08'

'dsad', 111 , '06/01/08'

 

the statement

------------------

"SELECT * FROM active_consults WHERE mrn LIKE '$mrnstring' or patient_name LIKE '$patientstring' or fellow LIKE '$fellowstring'"

 

the variable settings

------------------------

$mrnstring = 111;

$patientstring = "";

$fellowstring = "";

 

return values

-------------------------

'ddd', 111, '06/01/08'

'dsad', 111 , '06/01/08'

 

the variable settings

------------------------

$mrnstring = 111;

$patientstring = "smnith";

$fellowstring = "";

 

return values

------------------

'smnith', 12345, '06/01/08'

'ddd', 111, '06/01/08'

'dsad', 111 , '06/01/08'

[jeff5656]

The search does work, but why is it that after the results are displayed, the 1st record (sminth) is then displayed?  That record doesn't match the search string and yet it is displayed.  Could the answer lie in this part of the code?

 

if($total>0) {
while ($row_sql = mysql_fetch_assoc($query)) {//echo out the results
echo ''.$row_sql['patient_name'].' '.$row_sql['mrn'].' ' .$row_sql['fellow'];
echo '<br/>';

 

Thanks for everyone's help so far!

[hansford]

no -

'smnith', 12345, '06/01/08' will only be displayed if one of your variables contains 'smnith' or 12345 or  '06/01/08'

Make sure you don't have any other echos that are printing for testing purposes

[jeff5656]

no -

'smnith', 12345, '06/01/08' will only be displayed if one of your variables contains 'smnith' or 12345 or  '06/01/08'

 

Then why is it that when I type ddd into the patient_name form do I get the smnith record (which does not contain ddd in the patient_name?

here's the output:

 

$mrn:

$patientstring:ddd

$fellowstring:

 

ddd 111 fgfg

smnith 12345

[hansford]

Ok, I had to run your code to see what you were seeing.

 

'smnith 12345' was indeed the result - in fact - the only result and not what we were expecting.  I'm not a MYSQL expert, but removing 'ORDER BY id_incr DESC' from the query string will produce the correct results.

 

comment out these echo statements as they confuse us as if they are the results

--------------------------------------------------------------

echo '$mrn:' . $mrnstring . '<br/>';

echo '$patientstring:' . $patientstring . '<br/>';

echo '$fellowstring:' . $fellowstring . '<br/>';

--------------------------------------------

when echoing results -put more space between the $row_sql['mrn'] AND $row_sql['fellow'] columns