Jump to content

Unsure of best way for set of queries


jwer1978

Recommended Posts

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.

post-203215-0-00237300-1483711410_thumb.png

post-203215-0-23601400-1483711410_thumb.png

Link to comment
Share on other sites

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                                               |
+----------+------------------------------------------------------+--------------+------+----------+------+-------------------------------------------------+
Link to comment
Share on other sites

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 by jwer1978
Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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 by Psycho
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by benanamen
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

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