jwer1978 Posted January 6, 2017 Share Posted January 6, 2017 Looking for some suggestions on the best way to get information needed from some tables. I have an incidents table that I pull an id from in order to query a wf_data table. First I need to get the wf_data string and wf_data.text that is not "null" and return to the web page, I also need to get all wf_data.id that are of a certain wf_data.type so that I can use the wf_data.id to query another table to get additional information. Problem is I am returning all the "null" values and I am not sure if there is a way to weed them out. I am also not sure how to work with/access the multiple wf_data.string (or other fields possibly) that can be returned per incidents.id. The next issue will be when there is more than 9 rows per incidents.id but I will cross that bridge when I get there. $sql = "SELECT incidents.id as 'incident_id', from_unixtime(incidents.date_ocurred, '%m-%d-%Y') as 'Date Ocurred', incidents.title as 'title' FROM incidents WHERE category_id='58' ORDER BY incident_id ASC"; $result = mysqli_query($conn,$sql); //printf("<table><tr><td>Date</td><td>Problem</td><td>Incident Manager</td><td>RCA Manager</td><td>Severity</td><td>Description</td><td>SBM/SSM</td><td>Status</td></tr>"); /* Get incidents */ while ($row = mysqli_fetch_array($result,MYSQLI_BOTH)){ printf("ID - %s, DATE - %s, TITLE - %s.\n</br>",$row[0],$row[1],$row[2]); /* Get wf_data field_ids */ $sql_wf_data = "SELECT id as 'id', field_id as 'field_id', type as 'field_type' FROM wf_data where object_id=$row[0] order by field_id ASC"; $result_wf_data = mysqli_query($conn,$sql_wf_data); $row_cnt = mysqli_num_rows($result_wf_data); while ($row_wf_data = mysqli_fetch_array($result_wf_data,MYSQLI_BOTH)){ if ($row_cnt < '10') { printf("ID - %s, FIELD_ID - %s, FIELD_TYPE - %s.\n</br>",$row_wf_data[0],$row_wf_data[1],$row_wf_data[2]); //printf("<tr><td>Date- %s</td><td>Problem- %s</td><td>Incident Manager- %s</td><td>RCA Manager- %s</td><td>Severity- %s</td><td>Description- %s</td><td>SSM- %s</td><td>Status- %s</td></tr>\n",$row[3],$row[2]); /* Get additional refrences */ $sql_field_data = "SELECT v_text as 'description' FROM wf_data where id=$row_wf_data[0]"; $result_field_data = mysqli_query($conn,$sql_field_data); while ($row_field_data = mysqli_fetch_array($result_field_data,MYSQLI_BOTH)){ printf("DESCRIPTION - %s \n</br>",strip_tags($row_field_data[0])); }//Closes additional refrence loop } else { printf("%s %s Result set has $row_cnt rows.\n</br>",$row_wf_data[0],$row_wf_data[1]); /* Get correct field IDs */ } }/* closes wf_data field_ids */ }/* closes incidents */ I haven't used php/mysql in over 15 years so I am sure I don't know all the new tricks, any suggestions would be greatly appreciated though. Quote Link to comment https://forums.phpfreaks.com/topic/302883-unsure-of-best-way-for-set-of-queries/ Share on other sites More sharing options...
Barand Posted January 6, 2017 Share Posted January 6, 2017 Can you let me have a copy of your software that lets you load a database table from an image. I can't find one. Then I could recreate your data on my pc to rewrite your queries. Alternatively, can you provide SQL dump of those tables with some sample data? 1 Quote Link to comment https://forums.phpfreaks.com/topic/302883-unsure-of-best-way-for-set-of-queries/#findComment-1541112 Share on other sites More sharing options...
jwer1978 Posted January 6, 2017 Author Share Posted January 6, 2017 Thanks for the quick reply. Here is what I am working with right now Dump20170106-3.txt Quote Link to comment https://forums.phpfreaks.com/topic/302883-unsure-of-best-way-for-set-of-queries/#findComment-1541118 Share on other sites More sharing options...
benanamen Posted January 6, 2017 Share Posted January 6, 2017 (edited) Can you let me have a copy of your software that lets you load a database table from an image. I can't find one. Then I could recreate your data on my pc to rewrite your queries. LMFAO! Edited January 6, 2017 by benanamen Quote Link to comment https://forums.phpfreaks.com/topic/302883-unsure-of-best-way-for-set-of-queries/#findComment-1541122 Share on other sites More sharing options...
Barand Posted January 6, 2017 Share Posted January 6, 2017 That made my life easier!. Don't run queries inside loops, use joins to get the data with a single query. It's far more efficient. You can use coalesce to show the non-null value from the record. This will eliminate most of the nulls. If "object_id" is related to incident is filed, why is it confusingly called "object_id"? Example SELECT i.id as incident , i.title , i.date_ocurred , w.id as wid , w.field_id , w.type , CONVERT(COALESCE(v_boolean,v_integer,v_float,v_string,v_text,''), CHAR(50)) as value FROM incidents i JOIN wf_data w ON w.object_id = i.id ORDER BY i.id, w.field_id; Results +----------+------------------------------------------------------+--------------+------+----------+------+-------------------------------------------------+ | incident | title | date_ocurred | wid | field_id | type | value | +----------+------------------------------------------------------+--------------+------+----------+------+-------------------------------------------------+ | 206 | Root Cause Analysis | 1481719620 | 1606 | 2659 | 102 | | | 206 | Root Cause Analysis | 1481719620 | 1607 | 2660 | 2 | 206 | | 206 | Root Cause Analysis | 1481719620 | 1599 | 2661 | 6 | 1481414400 | | 206 | Root Cause Analysis | 1481719620 | 1600 | 2662 | 102 | | | 206 | Root Cause Analysis | 1481719620 | 1601 | 2663 | 102 | | | 206 | Root Cause Analysis | 1481719620 | 1602 | 2664 | 104 | | | 206 | Root Cause Analysis | 1481719620 | 1603 | 2665 | 5 | <p>Warehouse internet circuit down</p> | | 206 | Root Cause Analysis | 1481719620 | 1604 | 2666 | 4 | umm | | 206 | Root Cause Analysis | 1481719620 | 1605 | 2667 | 4 | resolved | | 231 | Lacey warehouse primary circuits down - Title - Test | 1482241980 | 1678 | 2659 | 102 | | | 231 | Lacey warehouse primary circuits down - Title - Test | 1482241980 | 1679 | 2660 | 2 | 231 | | 231 | Lacey warehouse primary circuits down - Title - Test | 1482241980 | 1671 | 2661 | 6 | 1482192000 | | 231 | Lacey warehouse primary circuits down - Title - Test | 1482241980 | 1672 | 2662 | 102 | | | 231 | Lacey warehouse primary circuits down - Title - Test | 1482241980 | 1673 | 2663 | 102 | | | 231 | Lacey warehouse primary circuits down - Title - Test | 1482241980 | 1674 | 2664 | 104 | | | 231 | Lacey warehouse primary circuits down - Title - Test | 1482241980 | 1675 | 2665 | 5 | <p>Lacey warehouse primary circuits down</p> | | 231 | Lacey warehouse primary circuits down - Title - Test | 1482241980 | 1676 | 2666 | 4 | SBM/SSM entry | | 231 | Lacey warehouse primary circuits down - Title - Test | 1482241980 | 1677 | 2667 | 4 | RCA Status Entry | | 232 | Testing RCA Workflow | 1482245760 | 1686 | 2668 | 102 | | | 232 | Testing RCA Workflow | 1482245760 | 1687 | 2669 | 2 | 232 | | 232 | Testing RCA Workflow | 1482245760 | 1690 | 2670 | 102 | | | 232 | Testing RCA Workflow | 1482245760 | 1680 | 2670 | 102 | | | 232 | Testing RCA Workflow | 1482245760 | 1691 | 2671 | 102 | | | 232 | Testing RCA Workflow | 1482245760 | 1681 | 2671 | 102 | | | 232 | Testing RCA Workflow | 1482245760 | 1682 | 2672 | 104 | | | 232 | Testing RCA Workflow | 1482245760 | 1692 | 2672 | 104 | | | 232 | Testing RCA Workflow | 1482245760 | 1693 | 2673 | 5 | <p>Testing RCA workflow - this is updatedp> | | 232 | Testing RCA Workflow | 1482245760 | 1683 | 2673 | 5 | <p>Testing RCA workflow</p> | | 232 | Testing RCA Workflow | 1482245760 | 1694 | 2674 | 4 | I dont know SBM/SSM - still dont know | | 232 | Testing RCA Workflow | 1482245760 | 1684 | 2674 | 4 | I dont know SBM/SSM | | 232 | Testing RCA Workflow | 1482245760 | 1695 | 2675 | 4 | Resolved | | 232 | Testing RCA Workflow | 1482245760 | 1685 | 2675 | 4 | Resolved | | 232 | Testing RCA Workflow | 1482245760 | 1688 | 2676 | 1 | 0 | | 232 | Testing RCA Workflow | 1482245760 | 1689 | 2677 | 4 | Update details as needed | | 232 | Testing RCA Workflow | 1482245760 | 1696 | 2678 | 1 | 1 | | 233 | Root Cause Analysis | 1482252180 | 1703 | 2690 | 102 | | | 233 | Root Cause Analysis | 1482252180 | 1704 | 2691 | 2 | 233 | | 233 | Root Cause Analysis | 1482252180 | 1697 | 2692 | 102 | | | 233 | Root Cause Analysis | 1482252180 | 1698 | 2693 | 102 | | | 233 | Root Cause Analysis | 1482252180 | 1699 | 2694 | 104 | | | 233 | Root Cause Analysis | 1482252180 | 1700 | 2695 | 5 | <p>new RCA</p> | | 233 | Root Cause Analysis | 1482252180 | 1701 | 2696 | 4 | SSM | | 233 | Root Cause Analysis | 1482252180 | 1702 | 2697 | 4 | Confirmed | | 233 | Root Cause Analysis | 1482252180 | 1705 | 2698 | 1 | 1 | | 234 | Root Cause Analysis | 1482323640 | 1712 | 2690 | 102 | | | 234 | Root Cause Analysis | 1482323640 | 1713 | 2691 | 2 | 234 | | 234 | Root Cause Analysis | 1482323640 | 1706 | 2692 | 102 | | | 234 | Root Cause Analysis | 1482323640 | 1707 | 2693 | 102 | | | 234 | Root Cause Analysis | 1482323640 | 1708 | 2694 | 104 | | | 234 | Root Cause Analysis | 1482323640 | 1709 | 2695 | 5 | <p>test description</p> | | 234 | Root Cause Analysis | 1482323640 | 1710 | 2696 | 4 | test ssm | | 234 | Root Cause Analysis | 1482323640 | 1711 | 2697 | 4 | test status | | 234 | Root Cause Analysis | 1482323640 | 1714 | 2698 | 1 | 1 | | 259 | This is the title | 1483468620 | 1721 | 2690 | 102 | | | 259 | This is the title | 1483468620 | 1722 | 2691 | 2 | 259 | | 259 | This is the title | 1483468620 | 1715 | 2692 | 102 | | | 259 | This is the title | 1483468620 | 1716 | 2693 | 102 | | | 259 | This is the title | 1483468620 | 1717 | 2694 | 104 | | | 259 | This is the title | 1483468620 | 1718 | 2695 | 5 | <p>this is a description</p> | | 259 | This is the title | 1483468620 | 1719 | 2696 | 4 | ssm | | 259 | This is the title | 1483468620 | 1720 | 2697 | 4 | status | | 259 | This is the title | 1483468620 | 1723 | 2698 | 1 | 1 | +----------+------------------------------------------------------+--------------+------+----------+------+-------------------------------------------------+ Quote Link to comment https://forums.phpfreaks.com/topic/302883-unsure-of-best-way-for-set-of-queries/#findComment-1541123 Share on other sites More sharing options...
jwer1978 Posted January 6, 2017 Author Share Posted January 6, 2017 (edited) Thanks! I was looking at using joins as well, problem I ran into was accessing the correct fields as multiple rows are returned per object_id, really I run into this the current way I was attempting too. The final result I am trying to achieve is a table of incidents. Also I did not create the base script. I am just working with the tables that it came with Date Title Description SSM Status 1481719620 root cause analysis warehouse internet circuit down umm resolved Edited January 6, 2017 by jwer1978 Quote Link to comment https://forums.phpfreaks.com/topic/302883-unsure-of-best-way-for-set-of-queries/#findComment-1541126 Share on other sites More sharing options...
Barand Posted January 6, 2017 Share Posted January 6, 2017 Your desired output only shows 3 fields from wf_data table although your data has 9 for each object. So which of the 9 are you really interested in? For example, the description text in your sample above is from the v_text field of of field_id 2665. Your incident table has a status_id field - where does "resolved" text come from? What is SSM and where is it from? Some field ids are duplicated for incident 232 - do you only want the latest entry for a required field id? (Tables of this format are a real PIA) Quote Link to comment https://forums.phpfreaks.com/topic/302883-unsure-of-best-way-for-set-of-queries/#findComment-1541128 Share on other sites More sharing options...
jwer1978 Posted January 6, 2017 Author Share Posted January 6, 2017 The web form causes 9 rows to be created. When sorted by w.id those rows will always be in the same order from what I have seen. The SSM comes from the v_string of field_id 2666 and the resolved come from the v_string of field_id 2667. The web form can be "rejected" causing the person who originally submitted it to provide updates which is what causes the duplicate field_id's in 232 so ideally it would be the latest entry for the field ID as you said that we are looking to get. You aren't kidding, I have been messing with this for 2 weeks now lol. The type 102 field is actually a username field. the w.id from that field must be referenced in another table to get the numerical userid which is then referenced in another table to get the persons actual name (which will become part of this but that shouldn't be as tough for me)... We have already made several suggestions to the company, unfortunately the source code is protected so we couldn't even modify it if we wanted to (which would probably be easier) Quote Link to comment https://forums.phpfreaks.com/topic/302883-unsure-of-best-way-for-set-of-queries/#findComment-1541136 Share on other sites More sharing options...
Barand Posted January 6, 2017 Share Posted January 6, 2017 (edited) It's 11pm (GMT) here so I'll have another look at it tomorrow morning. Edited January 6, 2017 by Barand Quote Link to comment https://forums.phpfreaks.com/topic/302883-unsure-of-best-way-for-set-of-queries/#findComment-1541137 Share on other sites More sharing options...
Psycho Posted January 7, 2017 Share Posted January 7, 2017 (edited) If I am understanding you and the data correctly, this should work. It is a hack, but I don't see a really elegant solution with what you have to work with. SELECT i.id as incident, i.title, i.date_ocurred, w.id as wid, w.field_id, w.type, CONVERT(COALESCE(v_boolean,v_integer,v_float,v_string,v_text,''), CHAR(50)) as value FROM incidents i JOIN ( SELECT object_id, MAX(id)-2 as filter_id FROM wf_data WHERE type = 4 GROUP BY object_id ) filter ON i.id = filter.object_id JOIN wf_data w ON w.object_id = i.id AND w.id >= filter.filter_id WHERE w.type IN (4, 5) ORDER BY i.id, w.field_id; This will return three records for each incident as follows: incide|title |date_ocurre|wid |field_|typ|value 206|Root Cause Analysis | 1481719620|1603| 2665| 5|<p>Warehouse internet circuit down</p> 206|Root Cause Analysis | 1481719620|1604| 2666| 4|umm 206|Root Cause Analysis | 1481719620|1605| 2667| 4|resolved 231|Lacey warehouse primary circuits down - Tit| 1482241980|1675| 2665| 5|<p>Lacey warehouse primary circuits down</p> 231|Lacey warehouse primary circuits down - Tit| 1482241980|1676| 2666| 4|SBM/SSM entry 231|Lacey warehouse primary circuits down - Tit| 1482241980|1677| 2667| 4|RCA Status Entry 232|Testing RCA Workflow | 1482245760|1693| 2673| 5|<p>Testing RCA workflow - this is updated</p> 232|Testing RCA Workflow | 1482245760|1694| 2674| 4|I dont know SBM/SSM - still dont know 232|Testing RCA Workflow | 1482245760|1695| 2675| 4|Resolved 233|Root Cause Analysis | 1482252180|1700| 2695| 5|<p>new RCA</p> 233|Root Cause Analysis | 1482252180|1701| 2696| 4|SSM 233|Root Cause Analysis | 1482252180|1702| 2697| 4|Confirmed 234|Root Cause Analysis | 1482323640|1709| 2695| 5|<p>test description</p> 234|Root Cause Analysis | 1482323640|1710| 2696| 4|test ssm 234|Root Cause Analysis | 1482323640|1711| 2697| 4|test status 259|This is the title | 1483468620|1718| 2695| 5|<p>this is a description</p> 259|This is the title | 1483468620|1719| 2696| 4|ssm 259|This is the title | 1483468620|1720| 2697| 4|status The value of the first record is the description, the second is the SSM, and the third is the status. you can then handle those in the code like this (not tested): $data = array(); while ($row = mysqli_fetch_array($result, MYSQLI_BOTH)) { if(!isset($data[$row['incident']])) { $data[$row[incident]] = array( 'title' = $row['title'], 'date' = $row['date_ocurred'], 'description' = $row['value']); } elseif(!isset($data[$row['incident']['ssm']])) { $data[$row[incident]]['ssm'] = $row['value']); } else { $data[$row[incident]]['status'] = $row['value']); } } That should create an array in this format array( '206' => array ( 'title' => 'Root Cause Analysis', 'date' => '1481719620', 'description' => '<p>Warehouse internet circuit down</p>', 'ssm' => 'umm', 'status' => 'resolved', ), '231' => array ( 'title' => 'Lacey warehouse primary circuits down - Title', 'date' => '1482241980', 'description' => '<p>Lacey warehouse primary circuits down</p>', 'ssm' => 'SBM/SSM entry', 'status' => 'RCA Status Entry', ) . . . etc. ) Edited January 7, 2017 by Psycho Quote Link to comment https://forums.phpfreaks.com/topic/302883-unsure-of-best-way-for-set-of-queries/#findComment-1541138 Share on other sites More sharing options...
Barand Posted January 7, 2017 Share Posted January 7, 2017 Incident #232 (and others) doesn't have field_ids of 2665, 2666 and 2667 so that cannot be used. I would have expected the same for always to output the same - apparently not. I guess more understanding of the convoluted data structure is required. Quote Link to comment https://forums.phpfreaks.com/topic/302883-unsure-of-best-way-for-set-of-queries/#findComment-1541147 Share on other sites More sharing options...
jwer1978 Posted January 7, 2017 Author Share Posted January 7, 2017 Thank you psycho! I will work with this some more today. Barand, I did some testing and found that if the form is changed and "re-deployed" it will cause the field_id's to change to a new set of #'s. So as long as the form is created and not messed with again I suppose it should be ok... Quote Link to comment https://forums.phpfreaks.com/topic/302883-unsure-of-best-way-for-set-of-queries/#findComment-1541149 Share on other sites More sharing options...
Barand Posted January 7, 2017 Share Posted January 7, 2017 The attached table image show the incidence of the various field_ids for the incidents. For the purpose of the report you are attempting to produce, which field ids are you interested in? Quote Link to comment https://forums.phpfreaks.com/topic/302883-unsure-of-best-way-for-set-of-queries/#findComment-1541150 Share on other sites More sharing options...
jwer1978 Posted January 7, 2017 Author Share Posted January 7, 2017 Barand, I included a few files here which may make it even easier to explain. I updated the sql dump so that you are referencing what has become the "final" web form yesterday, the field_id's should not change any more. Thanks again for all the help Dump20170107-1.txt Quote Link to comment https://forums.phpfreaks.com/topic/302883-unsure-of-best-way-for-set-of-queries/#findComment-1541153 Share on other sites More sharing options...
Barand Posted January 7, 2017 Share Posted January 7, 2017 Yesterday I thought it was a PIA. Now I have seen that RCA Manager is the third type 102 and ssm and status are the second and third type 4 records my view is that working with this database is a short path to insanity, and I am not paid enough for that. There should be an identifier to specify which field is which, not rely on a count, but neither the type nor the field id fulfill that function. (I hoped the metadata table might define the fields but no such luck). Ether there is something inherently wrong with the structure or the implementation is at fault. Anything you do with that data is going to be a hack. Good luck. Quote Link to comment https://forums.phpfreaks.com/topic/302883-unsure-of-best-way-for-set-of-queries/#findComment-1541156 Share on other sites More sharing options...
benanamen Posted January 7, 2017 Share Posted January 7, 2017 (edited) Problem is I am returning all the "null" values and I am not sure if there is a way to weed them out. Oh no, no, no, your problem is the entire database structure. Seriously, stop what you are doing right now and go back to the drawing board and start working on a sensible DB Schema. We will be happy to help you get going. What you have is an XY problem to the extreme. Start with a project summary of what this application is and what it should do then create a requirements document and post it. Once we know what you want and need we can offer the correct direction. Edited January 7, 2017 by benanamen Quote Link to comment https://forums.phpfreaks.com/topic/302883-unsure-of-best-way-for-set-of-queries/#findComment-1541157 Share on other sites More sharing options...
jwer1978 Posted January 7, 2017 Author Share Posted January 7, 2017 Barand, thank you for taking time and trying to help. I am glad it isn't all me lol. I believe this may be a project that has to wait until (if) the company updates their database per our suggestions, because it really is terrible how pretty much everything is stored in it. I would actually love to see the source code one day... In the meantime maybe we will just create our own form/database Quote Link to comment https://forums.phpfreaks.com/topic/302883-unsure-of-best-way-for-set-of-queries/#findComment-1541158 Share on other sites More sharing options...
jwer1978 Posted January 7, 2017 Author Share Posted January 7, 2017 Oh no, no, no, your problem is the entire database structure. Seriously, stop what you are doing right now and go back to the drawing board and start working on a sensible DB Schema. We will be happy to help you get going. What you have is an XY problem to the extreme. Start with a project summary of what this application is and what it should do then create a requirements document and post it. Once we know what you want and need we can offer the correct direction. Yea, what this was is, we have a helpdesk solution being implemented. We were looking to use the customer forms/workflows to track root cause analysis (we do this on a spreadsheet currently), since the data was being stored we were hoping to be able to access it, which is not going to be the case. So new plan is to build my own seperate from the other software. Quote Link to comment https://forums.phpfreaks.com/topic/302883-unsure-of-best-way-for-set-of-queries/#findComment-1541159 Share on other sites More sharing options...
Psycho Posted January 7, 2017 Share Posted January 7, 2017 (edited) Did you look at the results returned by the query I provided? I'm pretty sure it works even if the form is reset. Edited January 7, 2017 by Psycho Quote Link to comment https://forums.phpfreaks.com/topic/302883-unsure-of-best-way-for-set-of-queries/#findComment-1541162 Share on other sites More sharing options...
jwer1978 Posted January 7, 2017 Author Share Posted January 7, 2017 yes I did psycho and it worked well, thank you for taking the time to provide it. The problem I think will be that the 2 tables you worked with were only a part of the problem. There would be much more hacking needed in order to get what we are looking for. Quote Link to comment https://forums.phpfreaks.com/topic/302883-unsure-of-best-way-for-set-of-queries/#findComment-1541163 Share on other sites More sharing options...
Barand Posted January 8, 2017 Share Posted January 8, 2017 Oh no, no, no, your problem is the entire database structure. Seriously, stop what you are doing right now and go back to the drawing board and start working on a sensible DB Schema. We will be happy to help you get going. What you have is an XY problem to the extreme. A schema of this type (EAV model) is perfectly valid if implemented correctly. It is particularly useful for a products database where, say, a retailer sells washing machines and PCs. The sets of attributes for these items are very different (although some, like manufacturer and price, are common to all). It is also common in CMS systems so entities and attributes can easily be added on the fly. For the application that you have here, a straightforward relational model would be my choice. Quote Link to comment https://forums.phpfreaks.com/topic/302883-unsure-of-best-way-for-set-of-queries/#findComment-1541172 Share on other sites More sharing options...
jwer1978 Posted January 8, 2017 Author Share Posted January 8, 2017 yes I did psycho and it worked well, thank you for taking the time to provide it. The problem I think will be that the 2 tables you worked with were only a part of the problem. There would be much more hacking needed in order to get what we are looking for. psycho, I was playing around a little more with yours and I believe its pretty close to what I need if I work with this database. Problem is I dont quite understand the filter part enough to make it work with all the tables/data needed SELECT i.id, i.title, from_unixtime(i.date_ocurred, '%m-%d-%Y %h:%i') as date_ocurred, w.v_string, w.v_text, w.type AS fieldtype, w.field_id, w.id AS wid, wdc.v_integer AS userid, CASE wdc.v_string WHEN '9EC4BAF6' THEN 'OPEN' WHEN '3BE38FD7' THEN 'CLOSED' END as status, (SELECT name FROM users WHERE users.id = userid) AS fname, (SELECT lastname FROM users WHERE users.id = userid) AS lname, CONVERT(COALESCE(w.v_boolean,w.v_integer,w.v_float,w.v_string,w.v_text,''), CHAR(50)) as 'values' FROM incidents i INNER JOIN wf_data w ON w.object_id = i.id LEFT OUTER JOIN wf_data_collection wdc ON w.id = wdc.data_id AND i.id = wdc.object_id WHERE i.category_id = 58 ORDER BY i.id ASC, w.field_id ASC Quote Link to comment https://forums.phpfreaks.com/topic/302883-unsure-of-best-way-for-set-of-queries/#findComment-1541184 Share on other sites More sharing options...
Psycho Posted January 8, 2017 Share Posted January 8, 2017 I'm not sure what that last query is that you provided. It certainly isn't the one I provided. SELECT i.id as incident, i.title, i.date_ocurred, w.id as wid, w.field_id, w.type, CONVERT(COALESCE(v_boolean,v_integer,v_float,v_string,v_text,''), CHAR(50)) as value FROM incidents i JOIN ( SELECT object_id, MAX(id)-2 as filter_id FROM wf_data WHERE type = 4 GROUP BY object_id ) filter ON i.id = filter.object_id JOIN wf_data w ON w.object_id = i.id AND w.id >= filter.filter_id WHERE w.type IN (4, 5) ORDER BY i.id, w.field_id; When analyzing the raw data. it appeared to me that the three records associated with an incident ID that you needed always had the type of 5, 4, 4. Further, I saw that some incidents had multiple instances of three records with those types. So, my approach was to find the last three records (sorted by field_id) with the type 5 or 4. The "filter" is a dynamic table which will return the MAX(id)-2 from the wf_data table for each unique object_id (i.e. incident id). Then using that dynamic data, I query the incidents table and the wf_data table only selecting the data from the wf_data table that are of the type 4 or 5 AND where the wf_data.id is greater than or equal to the filter id value from the sub-query. Quote Link to comment https://forums.phpfreaks.com/topic/302883-unsure-of-best-way-for-set-of-queries/#findComment-1541199 Share on other sites More sharing options...
jwer1978 Posted January 9, 2017 Author Share Posted January 9, 2017 I realize it isn't exactly yours that I posted, I used yours as a starting point so to speak is what I meant. Quote Link to comment https://forums.phpfreaks.com/topic/302883-unsure-of-best-way-for-set-of-queries/#findComment-1541215 Share on other sites More sharing options...
Psycho Posted January 9, 2017 Share Posted January 9, 2017 I realize it isn't exactly yours that I posted, I used yours as a starting point so to speak is what I meant. But, it didn't include the "secret sauce" that made mine work for what you needed. If you need additional data returned, I would start with the one I provided. however, if what you need is substantially different than what you asked, I'm not sure what to tell you. I already took my own time to review the data independently and devise a solution - albeit a hack due to the structure of the data. So, if what you really need will require a rewrite I doubt I will be able to help. Quote Link to comment https://forums.phpfreaks.com/topic/302883-unsure-of-best-way-for-set-of-queries/#findComment-1541227 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.