Jump to content

mastubbs

Members
  • Posts

    52
  • Joined

  • Last visited

Profile Information

  • Gender
    Not Telling

mastubbs's Achievements

Member

Member (2/5)

1

Reputation

  1. Thanks very much for the help. Unfortunately it didn't work and i cant really figure out why. The dates are in the correct format but it only deleted 6 rows but there are thousands that should match. The only thing i can think to add is that although slot_id is sequential in booking_slots, it is not in booking_reservation. Ie, in booking_slots, booking_id numbers are 1,2,3,4 etc but in booking_reservation there may not be a 'reservation' with booking_id=2 (for example). In context, there are hundreds of booking slots for each day, but they are not always filled. Only filled (reserved) slots end up in booking_reservation with the slot_id number for that slot - if that makes sense? I'm not sure if that would cause a problem with the query though?
  2. Hi guys, I’m really hoping someone can help with this query. I'm sure it must use join somehow but i cant work out exactly how to do it. I have two tables (‘booking_slots’ and ‘booking_reservation’). ‘booking_slots’ has ‘slot_date’ and ‘slot_id’ fields. ‘booking_reservation’ has a number of fields including ‘slot_id’ but not ‘slot_date’. I want to run a query to delete all records between a certain date range in BOTH tables (say for example 01 Jan 2012 to 01 Jan 2013). To do this I want to find and delete all records using ‘slot_date’ in ‘booking_slots’ and use the corresponding ‘slot_id’ of the deleted records to delete the records with the same ‘slot_id’ in ‘booking_reservation’. Any help with this would be very greatly appreciated. Thanks
  3. Hi all, So i am passing a data from a form, in the format yyyy-mm-dd. I want to convert that to dd/mm/yyyy but for some reason i cant get it to work. Can anybody tell me what im doing wrong? $date = $_GET['date']; $date_uk = $date->format('d/m/Y'); Error is: Fatal error: Call to a member function format() on a non-object Thanks in advance for any help, Matt
  4. Sorry, i meant this. Still doesn't work though :-( $Find_Query2 = mysql_query("SELECT patients.*, addobs.*, DATE_FORMAT(addobs.datetime, '%d/%m/%Y %H:%i:%s') as dti1, (SELECT DATE_FORMAT(MIN(addobs.datetime), '%m/%d/%Y %H:%i:%s') FROM addobs WHERE addobs.mrn = patients.mrn AND addobs.datetime >= lastOkPar.lastDatetime AND addobs.par >= $par AND addobs.hidden != 'yes') as dti2 FROM addobs ORDER by addobs.par ASC, dti2 ASC INNER JOIN patients ON addobs.MRN = patients.MRN LEFT JOIN ( SELECT addobs.MRN , MAX(addobs.datetime) as lastDatetime FROM addobs WHERE addobs.par < $par AND addobs.hidden != 'yes' GROUP BY addobs.MRN ) lastOkPar ON lastOkPar.MRN = addobs.MRN WHERE addobs.datetime = (SELECT MAX(OLAST.datetime) FROM addobs AS OLAST WHERE OLAST.MRN = patients.MRN) AND addobs.par >= $par AND NOT addobs.hidden = 'yes' AND COALESCE(patients.ward,'') != 'dc' "); Thanks Matt
  5. Amazing thank you. So the fact that some of the dti2 results were for the correct patients was by chance? I have been playing around trying to order the results by par and then dti2, but no joy. I would have thought that this would work? $Find_Query2 = mysql_query("(SELECT patients.*, addobs.*, DATE_FORMAT(addobs.datetime, '%d/%m/%Y %H:%i:%s') as dti1, (SELECT DATE_FORMAT(MIN(addobs.datetime), '%m/%d/%Y %H:%i:%s') FROM addobs WHERE addobs.mrn = patients.mrn AND addobs.datetime >= lastOkPar.lastDatetime AND addobs.par >= $par AND addobs.hidden != 'yes') as dti2 FROM addobs ORDER by patient.par ASC, dti2 ASC INNER JOIN patients ON addobs.MRN = patients.MRN LEFT JOIN ( SELECT addobs.MRN , MAX(addobs.datetime) as lastDatetime FROM addobs WHERE addobs.par < $par AND addobs.hidden != 'yes' GROUP BY addobs.MRN ) lastOkPar ON lastOkPar.MRN = addobs.MRN WHERE addobs.datetime = (SELECT MAX(OLAST.datetime) FROM addobs AS OLAST WHERE OLAST.MRN = patients.MRN) AND addobs.par >= $par AND NOT addobs.hidden = 'yes' AND COALESCE(patients.ward,'') != 'dc' ) order by addobs.par ASC "); Any idea how i can get the order right? Thanks again for the help with this. Matt
  6. Ok so ive been playing about with this to get it working and i've made a few minor changes (eg rather than par cut-off being set at 6, it's now a variable $par so that the user can change the cut-off), other than that its pretty much the same query as kicken suggested. However i am getting some very odd results from it: 1) The returned results aren't ordered by ascending par. I cant actually work out any obvious logic as to how they are ordered. Interestingly, reordering by DESC does change the order of the results, but does not order them by desc either. 2) Oddly, the dti2 results are mostly right however for the first row the dti2 result returned corresponds to the wrong mrn. All the other columns in the result do correctly correspond to the mrn displayed (eg most recent value for par, most recent par date/time, other corresponding columns from the table [like patients name]) but not the dti2. It is displaying a result that does exist in the table, but is associated with a different mrn. Odd because as far as i can tell it only happens in the first row returned, and then the dti2 results for the following rows correspond to the correct patients. Im not sure if anyone better than me at php/sql has any ideas about why this is happening? I've been messing around with it for a weeks but havn't figured it out! Thanks in advance for any help with this. Matt $Find_Query2 = mysql_query("SELECT patients.*, addobs.*, DATE_FORMAT(addobs.datetime, '%d/%m/%Y %H:%i:%s') as dti1, (SELECT DATE_FORMAT(MIN(addobs.datetime), '%m/%d/%Y %H:%i:%s') FROM addobs WHERE addobs.datetime >= lastOkPar.lastDatetime AND addobs.par >= $par AND addobs.hidden != 'yes') as dti2 FROM addobs INNER JOIN patients ON addobs.MRN = patients.MRN LEFT JOIN ( SELECT addobs.MRN , MAX(addobs.datetime) as lastDatetime FROM addobs WHERE addobs.par < $par AND addobs.hidden != 'yes' GROUP BY addobs.MRN ) lastOkPar ON lastOkPar.MRN = addobs.MRN WHERE addobs.datetime = (SELECT MAX(OLAST.datetime) FROM addobs AS OLAST WHERE OLAST.MRN = patients.MRN) AND addobs.par >= $par AND NOT addobs.hidden = 'yes' AND COALESCE(patients.ward,'') != 'dc' order by addobs.par ASC");
  7. Ah ok i see what you mean. Will try to get into using prepared statements. Thanks for the help. Matt
  8. Ah... i have noticed a problem here. Sometimes the value of 'par1' will be 0. The problem with the methods above is that they seem to assume that par1 = 0 is the same as empty. Is there a way to get it to recognise 0 as a number, but still default to '4' if empty ? Thanks all for the help, Matt
  9. Hi all, Im trying to populate a drop down list from a php table but for some reason im getting stuck. The table structure is: Table name: Par1AddPatients_dynlist_items Columns: ID, listid, name, value I am trying to populate the list with names and corresponding values from the table, where the list id is ‘1’. <select name="ward_list"> <?php $connect = mysql_connect("localhost","username ","password "); if (!$connect) { die("MySQL could not connect!"); } $DB = mysql_select_db('jasperss_par1pats'); if(!$DB) { die("MySQL could not select Database!"); } $ward_list = mysql_query("SELECT name AND value FROM Par1AddPatients_dynlist_items WHERE listid = '1'"); while ($row = mysql_fetch_array($ward_list)){ echo '<option value="'. $row['value'] .'">'. $row['name'] .'</option>'; } ?> </select> At the moment I’m just getting an empty drop-down box. Any ideas where i'm going wrong? Thanks in advance for any help with this, Matt
  10. Whops yep i forgot the 'else', thanks. Thanks also for introduction to ternary operator. Matt
  11. Hi All, I have a script that relies on a numeric variable from GET. I am trying to figure out how to have a 'default' value if no GET value is passed in the url. I have been trying to get it to work with if() but no joy: $par2 = $_GET['par1']; if(empty($par2)) { $par = 4; } $par = $par2; So if a value for 'par1' is passed in the url then $par will be whatever that value is. However if no value is passed then the default value for $par should be 4. Unfortunately that code doesn't seem to work. If i pass no 'par1' value in the url i jest get a php error. Any tips on where im going wrong here would be much appreciated. Thanks in advance for any help, Matt
  12. Hi all, im using the following sql query to select data from two tables where the most recent value for 'par' is > 6. SELECT patients.*, addobs.*, DATE_FORMAT(addobs.datetime, '%d/%m/%Y %H:%i:%s') as dti1 FROM addobs INNER JOIN patients ON addobs.MRN = patients.MRN WHERE addobs.datetime = (SELECT MAX(OLAST.datetime) FROM addobs AS OLAST WHERE OLAST.MRN = patients.MRN) AND addobs.par > 6 AND NOT addobs.hidden = 'yes' AND COALESCE(patients.ward,'') != 'dc' order by addobs.par ASC I would like to extend this query to also return as 'dti2', the datetime value that par > 6 LAST BECAME true. To clarify, if i have 5 values as follows: datetime par 06-18-2013 05:00:00 7 06-18-2013 04:00:00 8 06-18-2013 03:00:00 7 06-18-2013 02:00:00 2 06-18-2013 01:00:00 10 Then the datetime to be returned by my query as dti2 would be 06-18-2013 03:00:00, as this is the time that par>6 LAST BECAME true (and it has STAYED true since this time) This is to be distinguished from 06-18-2013 01:00:00 (which is when PAR>6 FIRST became true) and also to be distinguished from 06-18-2013 05:00:0 (which is when PAR>6 was LAST true) To give some context for further clarity: PAR is a measure of how unwell a patient is (larger numbers = more unwell). So i am trying to create a list of unwell patients. I want to return patients who have a PAR score of >6 (which is what my query currently achieves) and the amount of time they have had CONSECUTIVE par scores >6. From my limited knowledge of sql i would have thought that this could be done somehow with coalesce but im not sure exactly how. i have tried a few different things with no luck. I hope that all makes sense, i get a little confused even explaining it! Thanks for any help anyone can give in advance, Best, Matt
  13. Hi All, So I am using this query to return some values.: $Find_Query2 = mysql_query("SELECT patients.*, addobs.* FROM addobs INNER JOIN patients ON addobs.MRN = patients.MRN WHERE addobs.datetime = (SELECT MAX(OLAST.datetime) FROM addobs AS OLAST WHERE OLAST.MRN = patients.MRN) AND addobs.par > 6 AND NOT addobs.hidden = 'yes' AND COALESCE(patients.ward,'') != 'dc' order by addobs.par ASC"); However, i one of the values ('datetime') is in the php datetime format yyyy-mm-dd hh:ii:ss. I want to return this in a different format dd/mm/yyyy hh:ii:ss. I tried using DATE_FORMAT as thats what i usually use to do this but i think my syntax is wrong as it is returning no results for 'datetime' in the way i am using it. I think im getting confused with my brackets somewhere. $Find_Query2 = mysql_query("SELECT patients.*, addobs.* FROM addobs INNER JOIN patients ON addobs.MRN = patients.MRN WHERE addobs.datetime = (SELECT DATE_FORMAT(MAX(OLAST.datetime), '%d/%m/%Y %H:%i:%s') FROM addobs AS OLAST WHERE OLAST.MRN = patients.MRN) AND addobs.par > 6 AND NOT addobs.hidden = 'yes' AND COALESCE(patients.ward,'') != 'dc' order by addobs.par ASC"); Can anybody tell me what im doing wrong here? Thanks in advance for any help, Matt
  14. Ahh... i see. yep that worked. Thanks Kicken.
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.