Some PHP and SQL questions... My first project

[mastubbs]

Hi All!

 

So I’ve never really used SQL or PHP before, this is my first project. The idea is that nurses can search for patients in one table of a hospital database (patients table), and enter new records of their heart rate and blood pressure in a separate database table (observations table). I have managed to set up a database and the tables, and made a search form (searching using MRN, which is the patient's hospital number) which returns the patient details (name, dob, etc...)

 

<?php

//STEP 1 Connect To Database
$connect = mysql_connect("localhost","jasperss_par1","k_dD6JsB");
if (!$connect)
{
die("MySQL could not connect!");
}

$DB = mysql_select_db('jasperss_par1pats');

if(!$DB)
{
die("MySQL could not select Database!");
}

//STEP 2 Check Valid Information
if(isset($_GET['search']))
{
//STEP 3 Declair Variables
$Search = $_GET['search'];
$Find_Query1 = mysql_query("SELECT * FROM patients WHERE mrn LIKE '%$Search%' ");

if(!$Find_Query1)
{
die(mysql_error());
}

while($row = mysql_fetch_assoc($Find_Query1))
{
    echo '<br/> MRN: '.$row['mrn'];
    echo '<br/> First Name: '.$row['fname'];
    echo '<br/> Last Name: '.$row['lname'];
    echo '<br/> Date of Birth: '.$row['dob'];
	echo '<br/> <a href="http://test.com/nextpage.php?mrn= ' .$row['mrn']>Click here</a>;
}
echo 'no patients were found';
}

?>

There’s a few things I just can’t figure out how to do, although I should imagine it’s fairly straight forward when you know how! Any help or ideas anyone has would be very much appreciated.

 

1)      The database will not record a number which starts with 0! (eg if I save a MRN number as 0001, it just saves as 1). I have tried various field types but can’t seem to do it. Does anyone know if there is a field type that will accept numbers starting with 0?

 

2)      I only want my search to find the record if the exactly correct MRN number is entered. However, if I replace the ‘like’ condition with =, I get no returned results, even if I search for exactly the correct number, I can’t figure out why.

 

3)      Because I only want to display one result, how do I echo ‘multiple results found’ if there is more than one record with the same mrn.

 

4)      I want to link onto the next page, is there some way I can ‘post’ forward some of the data on this page (in order so i can autofill part of the form on the linked page with MRN and name, so the nurses can just fill in heart rate and blood pressure for that patient and submit it to observations table). You can see I have tried to do it by parsing the MRN in the link, but I must have the syntax wrong somewhere because its not working :-(.

 

Thanks very much in advance for any help anyone can give me with this.

 

Matt

[ignace]

1) The database will not record a number which starts with 0! (eg if I save a MRN number as 0001, it just saves as 1). I have tried various field types but can’t seem to do it. Does anyone know if there is a field type that will accept numbers starting with 0?

Leave the number stored in your database as 1 and use str_pad to pad the number with the leading zero's when you display it. And if you really need it change your data type to varchar or if it has a fixed length char.

 

I only want my search to find the record if the exactly correct MRN number is entered. However, if I replace the ‘like’ condition with =, I get no returned results, even if I search for exactly the correct number, I can’t figure out why.

Why would you let your customers go through the extra trouble of padding with 0's?

 

Because I only want to display one result, how do I echo ‘multiple results found’ if there is more than one record with the same mrn.

mysql_num_rows > 1

 

I want to link onto the next page, is there some way I can ‘post’ forward some of the data on this page (in order so i can autofill part of the form on the linked page with MRN and name, so the nurses can just fill in heart rate and blood pressure for that patient and submit it to observations table). You can see I have tried to do it by parsing the MRN in the link, but I must have the syntax wrong somewhere because its not working

Pass them as a query string with an incremental page number:

 

search.php?page=2&mrn=1

In your query then you need to convert the page to an offset. For example if you limit the results to 20 per page.

 

function page_to_offset($page, $count) {
  return ($page - 1) * $count;
}

$count = 20;
$offset = page_to_offset($_GET['page'] ?: 1, $count);

$sql .= ' LIMIT ' . $count . ' OFFSET ' . $offset;

For the first page this will look like:

 

Page 1: LIMIT 20 OFFSET 0

Page 2: LIMIT 20 OFFSET 20

Edited May 19, 2013 by ignace

[mastubbs]

Hi Ignace, Thanks for your reply.

 

 

Leave the number stored in your database as 1 and use str_pad() to pad the number with the leading zero's when you display it. And if you really need it change your data type to varchar or if it has a fixed length char.

 

great, thanks.

 

 

Why would you let your customers go through the extra trouble of padding with 0's?

 

Its because the patient's MRN numbers are unique numbers which sometimes start with a variable number of leading 0s. It is quite important that this is an exact search as i only want to return a single result, which is the correct patient for that number. In our current system the nurses are used to entering the leading 0s so i would like to keep them to avid confusion. I would have thought that replacing LIKE with = would do it, but it returns nothing even if the MRN number is exactly correct. Any idea why?

 

 

mysql_num_rows() > 1

 

where is the code does this go? I tried it as below but no joy. If there are multiple results i don't want to return any of them, i just want to echo 'multiple results', and a link back to the search.html page to try again.

if(!$Find_Query1)
{
die(mysql_error());
}

if(mysql_num_rows() > 1) echo 'multiple results, check MRN and try again';
while($row = mysql_fetch_assoc($Find_Query1))
{
    echo '<br/> MRN: '.$row['mrn'];
    echo '<br/> First Name: '.$row['fname'];
    echo '<br/> Last Name: '.$row['lname'];
    echo '<br/> Date of Birth: '.$row['dob'];
}
}

?>

I also realised that 'echo 'no patients were found'; is returned whether or not there are patients found, so i have obviously used that wrongly! I guess you could use mysql_num_rows() = 0? How would i use both, im not sure how to do an if else statement in php.

 

Thanks again for your help with this.

 

Matt

[ignace]

In our current system the nurses are used to entering the leading 0s so i would like to keep them to avid confusion.

Great so when you receive their input you intval it and use that to query your database.

 

if(mysql_num_rows() > 1) echo 'multiple results, check MRN and try again';

As you can see my answer contained a link, follow it and read what the page says.

[mastubbs]

As you can see my answer contained a link, follow it and read what the page says.

 

Sadly this link does not really answer the question. I see how to return that there are multiple results, i don't see how you can return ONLY this, so that none of the results are displayed if there are multiple, which is what i want it to do - i just want to tell them to try again if there is not a single result. It also says that this extension is depreciated and will be removed soon, another reason not to use it?

 

I don't suppose anyone know the answer to why '=' doesn't find anything but 'like' does, still cant get my head around that :-/ 

[mastubbs]

I don't suppose anyone know the answer to why '=' doesn't find anything but 'like' does, still cant get my head around that :-/ 

 

Just to help others who may have this question: the answer was i had missed that there was % (wildcard) before and after Search so it was performing a LIKE type search even if = was selected due to that - took me a lot of fiddling but i figured it out eventually!

Edited May 21, 2013 by mastubbs

[Barand]

If you had posted that version of the code it might have saved you a lot of head-scratching