jwer1978

I realize it isn't exactly yours that I posted, I used yours as a starting point so to speak is what I meant.

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

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.

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.

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

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

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...

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)

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

Thanks for the quick reply. Here is what I am working with right now Dump20170106-3.txt

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.