fenway Posted May 14, 2007 Share Posted May 14, 2007 If you're always using IS NOT NULL, why not just INNER JOIN them/ Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/page/2/#findComment-253069 Share on other sites More sharing options...
bubblegum.anarchy Posted May 15, 2007 Share Posted May 15, 2007 From my already limited understanding, at least one of the three joins needs to be available. Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/page/2/#findComment-253184 Share on other sites More sharing options...
Barand Posted May 16, 2007 Share Posted May 16, 2007 Yes, his thread was double-posted in php help forum. I asked for a dump of some test data to be attached (so I could load and test for myself. Also I wanted to see how the encounter ids hang together for the different tests.) All I got was this pig---------10000000 ------------- from the $tb_find_chemlab /pig---------10000000 /pig---------10000000 /pig---------10000000 /kib---13---Bacteriological Test---10000005 ----- from the $tb_find_baclabor /test---14---Bacteriological Test---10000006 /lolgah---------10000008----------------------- from $tb_find_patho At which point I shook my head and gave up. Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/page/2/#findComment-254042 Share on other sites More sharing options...
maliary Posted May 16, 2007 Author Share Posted May 16, 2007 I didn't understand what you had asked for.But here are the sql dumps. Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/page/2/#findComment-254253 Share on other sites More sharing options...
maliary Posted May 16, 2007 Author Share Posted May 16, 2007 I have attached sql dumps for 4 of the tables involved. [attachment deleted by admin] Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/page/2/#findComment-254268 Share on other sites More sharing options...
Barand Posted May 16, 2007 Share Posted May 16, 2007 Just going off to the office now - I'll have a look this evening when i get home Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/page/2/#findComment-254271 Share on other sites More sharing options...
maliary Posted May 16, 2007 Author Share Posted May 16, 2007 The last table. [attachment deleted by admin] Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/page/2/#findComment-254281 Share on other sites More sharing options...
bubblegum.anarchy Posted May 16, 2007 Share Posted May 16, 2007 maliary... I'd suggest that Barand would appreciate more than one tables worth of information.. more like all the tables that are described in the query and some test data for each table. Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/page/2/#findComment-254283 Share on other sites More sharing options...
maliary Posted May 16, 2007 Author Share Posted May 16, 2007 I actually thought i had. Anyway I will repeat the attachments for all the 5 tables. Please remove the earlier ones i had made. Find the fifth below.l [attachment deleted by admin] Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/page/2/#findComment-254285 Share on other sites More sharing options...
maliary Posted May 16, 2007 Author Share Posted May 16, 2007 The fifth table. [attachment deleted by admin] Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/page/2/#findComment-254287 Share on other sites More sharing options...
bubblegum.anarchy Posted May 16, 2007 Share Posted May 16, 2007 The following query: SELECT person.pid , person.name_first , baclabor.* , patho.* , chemlab.* FROM care_encounter AS encounter INNER JOIN care_person AS person ON encounter.pid = person.pid LEFT JOIN care_test_findings_baclabor AS baclabor ON encounter.encounter_nr = baclabor.encounter_nr LEFT JOIN care_test_findings_patho AS patho ON encounter.encounter_nr = patho.encounter_nr LEFT JOIN care_test_findings_chemlab AS chemlab ON encounter.encounter_nr = chemlab.encounter_nr WHERE baclabor.encounter_nr IS NOT NULL OR patho.encounter_nr IS NOT NULL OR chemlab.encounter_nr IS NOT NULL Produces the following result: pid name_first batch_nr encounter_n room_nr dept_nr notes findings_in findings_cu findings_fi type entry_nr rec_date doctor_id findings_da findings_ti status modify_id modify_time create_id create_time batch_nr encounter_n room_nr dept_nr type doctor_id findings_da findings_ti status modify_id modify_time create_id create_time batch_nr encounter_n job_id test_date test_time group_id type validator validate_dt status modify_id modify_time create_id create_time 10000000 pig 1 1 10000001 2007-03-13 10:26:50 priority Chemical Test 0000-00-00 00:00:00 admin 2007-05-05 10:44:52 admin 2007-03-13 10:26:50 10000000 pig 3 9 10000002 2007-04-04 09:57:49 priority Chemical Test 0000-00-00 00:00:00 hidden 2007-05-05 11:02:22 admin 2007-04-04 09:57:49 10000000 pig 5 9 10000002 2007-04-04 14:38:21 priority Chemical Test 0000-00-00 00:00:00 admin 2007-04-13 14:38:24 admin 2007-04-13 14:38:21 10000000 pig 6 12 10000001 2007-05-06 00:00:00 priority Chemical Test 0000-00-00 00:00:00 2007-05-03 14:35:59 0000-00-00 00:00:00 10000005 kib 4 13 0 0 0 0 Bacteriological Test 0000-00-00 0000-00-00 00:00:00 2007-05-05 11:00:01 0000-00-00 00:00:00 10000006 test 4 14 10000003 2007-04-11 16:59:25 priority Chemical Test 0000-00-00 00:00:00 2007-05-05 12:07:20 admin 2007-04-11 16:59:25 10000005 kib 1 15 0 Pathological Test 2007-05-18 00:00:00 2007-05-07 16:10:01 0000-00-00 00:00:00 10000007 benson 5 16 25 dftf 0 0 0 Bacteriological Test 30000001 2007-04-16 2007-04-16 09:57:00 done admin 2007-05-11 08:54:12 admin 2007-04-16 09:57:04 If the above results are not what is required then what is required??????? Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/page/2/#findComment-254313 Share on other sites More sharing options...
maliary Posted May 16, 2007 Author Share Posted May 16, 2007 Ok, It sure does. But why is it that when i display the results some don't show up? like the 'type' details only show up for one table baclabor. what's wrong with this statement? This is what am using to retrive from the database. while ($rows = mysql_fetch_assoc($result)) { echo '<tr>'; echo '<td>'; echo $rows['name_first'] .'---'. $rows['encounter_nr'].'---'.$rows['type'].'---'.$rows['pid'].'---'.$rows['findings_date'].'---'.$rows['test_date']; echo '<br/>'; echo '</td>'; echo '/<tr>'; } Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/page/2/#findComment-254316 Share on other sites More sharing options...
bubblegum.anarchy Posted May 16, 2007 Share Posted May 16, 2007 Aliases will be required to distinguish identical column names, something like this: SELECT person.pid , person.name_first , baclabor.encounter_nr AS baclabor_encounter_nr , patho.encounter_nr AS patho_encounter_nr , chemlab.encounter_nr AS chemlab_encounter_nr , .... etc FROM care_encounter AS encounter... And then in PHP: <?php echo $rows['chemlab_encounter_nr']; ?> Consider trimming the selected values to only those that are required. Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/page/2/#findComment-254346 Share on other sites More sharing options...
maliary Posted May 16, 2007 Author Share Posted May 16, 2007 Thanks a lot, but This works $rows['baclabor_type'] but this dosen't $rows['chemlab_type'] and $rows['patho_type'] . Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/page/2/#findComment-254467 Share on other sites More sharing options...
maliary Posted May 16, 2007 Author Share Posted May 16, 2007 But, All the encounter numbers are displayed work well. Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/page/2/#findComment-254526 Share on other sites More sharing options...
Barand Posted May 16, 2007 Share Posted May 16, 2007 Do you want something like this? [pre] pid | name | encounter | test ---------+---------+--------------+------------------------ 10000000 | pig | 1 | Chemical Test 10000000 | pig | 9 | Chemical Test 10000000 | pig | 12 | Chemical Test 10000005 | kib | 13 | Bacteriological Test 10000005 | kib | 15 | Pathological Test 10000006 | test | 14 | Chemical Test 10000007 | benson | 16 | Bacteriological Test [/pre] SELECT p.pid, p.name_first, f.encounter_nr, f.type 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 UNION SELECT p.pid, p.name_first, g.encounter_nr, g.type FROM care_person AS p INNER JOIN care_encounter AS e ON p.pid = e.pid INNER JOIN care_test_findings_patho AS g ON e.encounter_nr = g.encounter_nr UNION SELECT p.pid, p.name_first, h.encounter_nr, h.type FROM care_person AS p INNER JOIN care_encounter AS e ON p.pid = e.pid INNER JOIN care_test_findings_baclabor AS h ON e.encounter_nr = h.encounter_nr ORDER BY pid, encounter_nr Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/page/2/#findComment-254831 Share on other sites More sharing options...
maliary Posted May 17, 2007 Author Share Posted May 17, 2007 That's it! It worked. Thanks. Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/page/2/#findComment-255146 Share on other sites More sharing options...
maliary Posted May 17, 2007 Author Share Posted May 17, 2007 We are still there, How would I display $row[test_date] and $row[findings_date], in this scenario. $row[test_date] found in the care_test_findings_chemlab $row[findings_date] found both care_test_findings_patho and care_test_findings_baclabor. Two diffrent but required columns. Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/page/2/#findComment-255501 Share on other sites More sharing options...
Barand Posted May 17, 2007 Share Posted May 17, 2007 Just give them all the same column alias in the queries SELECT ..., test_date as thedate, ... SELECT ..., findings_date as thedate, ... Then echo $row['thedate'] Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/page/2/#findComment-255735 Share on other sites More sharing options...
maliary Posted May 18, 2007 Author Share Posted May 18, 2007 The query above is part of a search script. It works well without the search variable being passed,where it displays all the data. How would I pass variables for a search. If I wanted to search for specifics? Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/page/2/#findComment-256292 Share on other sites More sharing options...
maliary Posted May 18, 2007 Author Share Posted May 18, 2007 To search using the first name,encounter number ,test date or findings date. Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/page/2/#findComment-256307 Share on other sites More sharing options...
Barand Posted May 18, 2007 Share Posted May 18, 2007 example <?php $searchdate = '2007-05-17'; $sql = "SELECT p.pid, p.name_first, f.encounter_nr, f.type, f.test_date as thedate 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 f.test_date = '$searchdate' UNION SELECT p.pid, p.name_first, g.encounter_nr, g.type, g.findings_date as thedate FROM care_person AS p INNER JOIN care_encounter AS e ON p.pid = e.pid INNER JOIN care_test_findings_patho AS g ON e.encounter_nr = g.encounter_nr WHERE g.findings_date = '$searchdate' UNION SELECT p.pid, p.name_first, h.encounter_nr, h.type, h.findings_date as thedate FROM care_person AS p INNER JOIN care_encounter AS e ON p.pid = e.pid INNER JOIN care_test_findings_baclabor AS h ON e.encounter_nr = h.encounter_nr WHERE h.findings_date = '$searchdate' ORDER BY pid, encounter_nr "; ?> Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/page/2/#findComment-256321 Share on other sites More sharing options...
maliary Posted May 19, 2007 Author Share Posted May 19, 2007 Hi, I have made some modification to search from a date range and a number which works. It should also be able to search using the names or pid. It dosen't work however with names or pid.What's the error here? $this->sql="SELECT f.encounter_nr,f.findings_date, e.encounter_class_nr, p.pid, p.name_last, p.name_first, p.date_birth, p.sex FROM care_person AS p INNER JOIN care_encounter AS e ON p.pid = e.pid INNER JOIN care_test_findings_patho AS f ON e.encounter_nr = f.encounter_nr WHERE e.encounter_nr = $key AND findings_date BETWEEN '".$pn."' AND '".$cw."' AND (e.encounter_nr $sql_LIKE '$key%' OR p.pid $sql_LIKE '$key%' OR p.name_last $sql_LIKE '$key%' OR p.name_first $sql_LIKE '$key%' ) UNION SELECT f.encounter_nr,f.test_date, e.encounter_class_nr, p.pid, p.name_last, p.name_first, p.date_birth, p.sex 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 e.encounter_nr = $key AND test_date BETWEEN '".$pn."' AND '".$cw."' AND (e.encounter_nr $sql_LIKE '$key%' OR p.pid $sql_LIKE '$key%' OR p.name_last $sql_LIKE '$key%' OR p.name_first $sql_LIKE '$key%' ) UNION SELECT f.encounter_nr,f.findings_date, e.encounter_class_nr, p.pid, p.name_last, p.name_first, p.date_birth, p.sex FROM care_person AS p INNER JOIN care_encounter AS e ON p.pid = e.pid INNER JOIN care_test_findings_baclabor AS f ON e.encounter_nr = f.encounter_nr WHERE e.encounter_nr = $key AND findings_date BETWEEN '".$pn."' AND '".$cw."' AND (e.encounter_nr $sql_LIKE '$key%' OR p.pid $sql_LIKE '$key%' OR p.name_last $sql_LIKE '$key%' OR p.name_first $sql_LIKE '$key%' ) "; Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/page/2/#findComment-256924 Share on other sites More sharing options...
Barand Posted May 19, 2007 Share Posted May 19, 2007 What is "$sql_LIKE" ? Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/page/2/#findComment-256976 Share on other sites More sharing options...
maliary Posted May 20, 2007 Author Share Posted May 20, 2007 "$sql_LIKE" the equivalent of "LIKE". You may change it to "LIKE". It works the same. Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/page/2/#findComment-257463 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.