mastubbs
-
Posts
52 -
Joined
-
Last visited
Everything posted by mastubbs
-
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?
-
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
-
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
-
qsl : datetime that X last became true (and stayed true)
mastubbs replied to mastubbs's topic in PHP Coding Help
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 -
qsl : datetime that X last became true (and stayed true)
mastubbs replied to mastubbs's topic in PHP Coding Help
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 -
Perfect, thanks.
-
qsl : datetime that X last became true (and stayed true)
mastubbs replied to mastubbs's topic in PHP Coding Help
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"); -
Ah ok i see what you mean. Will try to get into using prepared statements. Thanks for the help. Matt
-
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
-
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
-
Whops yep i forgot the 'else', thanks. Thanks also for introduction to ternary operator. Matt
-
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
-
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
-
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
-
Ahh... i see. yep that worked. Thanks Kicken.
-
Hi All, So i have two tables, 'addobs' and 'patients'. In patients one variable is 'ward. I am using an SQL query as follows to extract data from both tables: $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 NOT patients.ward = 'dc' order by addobs.par ASC"); However, there is a problem somewhere with this and i get no results returned (although there are records that fit the query). However, if i remove AND NOT patients.ward = 'dc' then it works (except of course i get all records, whether ward is 'dc' or not) - but i want to only display records where ward is not 'dc'. Interestingly, if i change that line to AND patients.ward = 'dc' then it return all of the results where ward='dc'. So why does 'NOT' not work? Maybe something to do with the fact that that some of the values are NULL? Any ideas would be much appreciated. Thanks Matt
-
Hi all, So I have a page which uses mysql_query to select some variables from a table, and then produces an array in order to plot this information on a graph (data used for this depends on the unique id which is obtained by $_GET [‘mrn’]. This is all working fine. However, what I want to do now is include multiple copies of these graphs on a single page (graphs for different mrn’s). I tried to do this by including the files like this: <php $_GET[mrn] = "1"; include('graph.php'); ?> //some html <php $_GET[mrn] = "2"; include('graph.php'); ?> The problem with this method has been that although it works once, the second you include a second graph it stops working (presumably because I have tried to assign two different values to the same variable by effectively defining it twice?) My question is therefore, is there some way to include a file, get its output (draw its graph), and then stop including it, so that I can start including the next file (drawing next graph). My reading so far has led me to believe there probably isn’t a way? Am I correct about that? Does anyone have any ideas about how I might get this working using a different technique? Thanks in advance for any help, Matt
-
lol sorry kicken, slip of the finger.
-
Hi Both, Thanks for that kitchen, i couldn't have explained it better myself (clearly, lol). Thanks very much to both of you for your help with this. Matt
-
Thanks all for the help, DavidAM's worked the best, thanks for that. Mikosiko's idea did almost work, but in fact only showed the last par > 6 for a unique mrn, rather than if the last par score was >6, if that makes sense. so any record with a par>6 showed up even if that was not the most recent par score for that mrn. Thanks again, Matt
-
OK time for a very tricky mysql query (well, tricky for me anyway)… I have two tables: ‘patients’ and ‘obs’ Variables in patients: MRN, name, others1 Variables in obs: MRN, par (numeric value), time_recorded (datetime value), others2 Each time a new par value is added to obs: MRN, datetime, others 2 are recorded also (there are no null values). Multiple par values are therefore added over time for the same MRN number (as different rows in the table) I want to make a query that will select * from patients and * from obs using inner join using MRN only when the most recent par value for each unique MRN in obs was >6 For some context: MRNs are hospital numbers of patients and par is a score of how unwell they are. I am trying to produce a list of all the patients who’s most recent par value recorded in obs is >6 and print their details. I think I have managed to work out the inner join part: SELECT * from obs INNER JOIN patients ON obs.MRN = patient.MRN. What I just can’t figure out however is how to select only records where the most recent par for that particular MRN (ie patient) was > 6. I suppose one way to do this is to select all data from both tables using inner join MRN where obs.par >6, and then sort by mrn, the sort by datetime decending, and then only print the top returned result for each unique mrn, but im not really sure if that last part is possible? I hope all that makes sense? Any guidance or ideas anyone has about how to achieve this would be very helpful. Thanks all in advance for any replies, Matt
-
Good idea. I will do that. You will be pleased to hear my tables are no longer named "table_" on your advice by they way.
-
Ah ok, thanks.
-
Kicken you legend that did the trick. For future reference what was wrong with my IF ELSE syntax? Im guessing from Jessica it should have been "SELECT IF datetime_man='null' DATE_FORMAT(datetime,'%Y/%m/%d %H:%i:%s') AS datetime2 ELSE DATE_FORMAT(datetime_man,'%Y/%m/%d %H:%i:%s') AS datetime2
-
Hi all, So im quite new to sql queries and ive been reading how to use if else but i ccant figure out what im getting wrong in this one. Can anyone help? I want to use a datetime variable (which is an automatic timestamp 'datetime') but only if the user has not manually over-ridden this by entering their own datetime ('datetime_man). I have tried to use IF ELSE but im getting it wrong somewhere. Is the syntax right? $Find_Query1 = mysql_query("SELECT DATE_FORMAT(datetime,'%Y/%m/%d %H:%i:%s') AS datetime2 IF datetime_man='null' ELSE DATE_FORMAT(datetime_man,'%Y/%m/%d %H:%i:%s') AS datetime2, DATE_FORMAT(datetime,'%H:%i on %d/%m/%y') AS datetime3 IF datetime_man='null' ELSE DATE_FORMAT(datetime,'%H:%i on %d/%m/%y') AS datetime3, SBP, SBP_R, SBP_B, DBP, DBP_R, DBP_B, HR, HR_R, HR_B, RR, RR_R, RR_B, SpO2, SpO2_R, SpO2_B, O2flow, O2flow_R, O2flow_B, O2device, O2deviceOther, TEMP, TEMP_R, TEMP_B, AVPU FROM addobs WHERE mrn='$Search' AND DATE(datetime)='$Date' AND NOT hidden = 'yes' order by datetime2 ASC"); Thanks in advance for the help, Matt