Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion

osTicket v1.10 (stable) and Maintenance Release v1.9.15 are now available! Go get it now

Adding custom status to ticket page title

Initial post edited for forum mod at posters request:

I can't explain the sql stuff, but I get what it's doing.  Here is the short and sweet of it:


The goal is to pull select data from the database and populate it into the browser's title page.  The default page title is "Ticket # and $ticket->getId()

The solution is to find the table and value, then put a sql query into the /include/staff/header.inc.php  Here is an example.  ost_form_entry_values and ost_form_entry are tables in the database.  Replace the <title> tage with the following:

 <title>
<?php
if(isset($ticket)){
  $sql ="SELECT value FROM ost_form_entry_values a INNER JOIN ost_form_entry e ON e.object_id=".$ticket->getId()." AND e.object_type='T' AND e.id=a.entry_id WHERE field_id=13";  // The query itself
  $res = db_query($sql);
  list($val) = db_fetch_row($res);
  echo $ticket->getStatus() . " & " . $val . " & " . $ticket->getId();  // This is what is displayed on the title page.  getStatus() will tell if the ticket is open or closed, etc..  $val is the custom value, getId() is the ticket number
}else{
  echo ($ost && ($title=$ost->getPageTitle()))?$title:'osTicket :: Staff Control Panel';  // If not a ticket, then display this title page
}
?>
    </title>

Might be easier to copy all this into your favorite text editor so it's not crowded and you can read the comments etc..


I followed the directions here http://www.osticket.com/forum/discussion/4122/mod-change-page-title-to-ticket-subject-when-ticket-open-staff  but it looks like things are different with the newer version.  I have a custom list and custom forms in tickets now (awesome! ) .  My minions will need to know what is populated in one of those fields while they have multiple tickets open in each tab.  What I'd like for them to see is:  <custom form value> & <status> & <ticket #>

I surely don't mind doing the footwork, just would someone point me in the right direction.  (I'm a php tard but am learning).  

Thanks,
«1

Comments

  • I found out where to do it!  Just having trouble doing it now..

    /upload/scp/tickets.php

    The line here:  $ost->setPageTitle('Ticket #'.$ticket->getNumber());

    Just need to know how to get the value from the custom form I put into the ticket to display in the page title now.  Any ideas greatly appreciated.

    Thanks,

  • I also can't see how each ticket is keeping track of the custom form value.  I'm pulling information from a custom list that populates the dropdown.   Any ideas?

    Thanks,
  • Hey waynesupport,

    Custom form values are stored in the ost_form_entry_values (also known as the FORM_ANSWER_TABLE).  If you wanted to have this information displayed in the title, you would need to pull it with a sql statement and store it as a variable to use in that function.

    Hope that this helps!
  • Assuming I know little about php and mysql - where would you suggest I go to start doing this?  And thanks a million for the help.  Greatly appreciate it.
  • OK - I see.  It appears that the entry_id is the ticket and field_id 13 is the value I'm looking for.  This is awesome.  Now to figure how to tell sql to get it and store it and use in the function.  

    Any examples I can use for this.. *look online to see how to do this*

    Thanks again,
  • Assuming you know very little about php and mysql, it will be a little challenging. 

    The first thing you need to do is get a couple of variables:
    1. the list-id where your custom data is stored (ost_list)
    2. the field-id which is where your data is stored (ost_form_field)
    With those, we can go about creating a custom SQL statement to grab the desired information.  I would recommend that you go into your sql database and go to those two tables I listed and grab those ids.  For the list-id, look at the name and it should match the name you setup when you created your custom list.  For the field-id, do the same thing.  Look at the Label (which will match the label you set in the ticket system) and the name will match the variable you set in the field.

    Hopefully that makes sense.  Get me those two numbers and I can help setup the sql statement.
  • ost_list  list-id would be 1  (there's only one entry, 1)

    ost_form_field  I don't see a field-id there  I see a form_id with the correct label and it's form_id is  2

    ost_form_entry_values shows field_id of 13


  • edited March 2014
    Sorry scratch the part about form_id .  Lots of other things are using form_id 2.. 

    Apologies if I didn't follow correctly.

    ost_form_entry_values - field_id 13 corresponds to ost_form_field id 13

    entry_id must be the ticket.   Sweet.

    I believe that is what I'm looking for?
  • yea, thats what I am looking for.  Let me play with it for a second.
  • Okay, try putting this in your header.inc.php file.

    I haven't tested it but this will be similar to what you are looking for.

    $sql = 'SELECT value FROM ost_form_entry_values a LEFT JOIN ost_form_entry e ON e.object_id='.$ticket->getId().' AND e.object_type='T' AND e.id=a.entry_id WHERE field_id=13';
    $res = db_query($sql);
    list($val) = db_fetch_row($res);

    Andy
  • edited March 2014
    I put it right under the <!DOCTYPE HTML... and did it like this:

    <?php

    $sql = 'SELECT value FROM ost_form_entry_values a LEFT JOIN ost_form_entry e ON e.object_id='.$ticket->getId().' AND e.object_type='T' AND e.id=a.entry_id WHERE field_id=13';
    $res = db_query($sql);
    list($val) = db_fetch_row($res);

    ?>

    Tickets come up with blank page when I click on them now.
  • Yeah, you would need to place it inside a php tag ( <?php ?> ), otherwise it will display as plain text.
  • I got that, but now all I get is a blank page when clicking the link and viewing the ticket.
  • There is an error then.  I would check your php error log to see where the error is occuring.
  • PHP Parse error:  syntax error, unexpected 'value' (T_STRING) in /var/www/upload/include/staff/header.inc.php on line 122, referer: http://myosticketserver.org/upload/scp/login.php

    Trying to figure it out.
  • Line 122 is:
    $sql ='SELECT value FROM ost_form_entry_values a LEFT JOIN ost_form_entry e ON e.object_id='.$ticket->getId().' AND e.object_type='T' AND e.id=a.entry_id WHERE field_id=13';

  • edited March 2014
    I can run that query in mysql workbench by first selecting the db (use osticket; ) and running without the ' on each end.

    Like this:

    use osticket;
    SELECT value FROM ost_form_entry_values a LEFT JOIN ost_form_entry e ON e.object_id='.$ticket->getId().' AND e.object_type='T' AND e.id=a.entry_id WHERE field_id=13;
  • ohh, I see my issue: change the ' to " on everything except the one for e.object_type='T'  so that it looks like this:

    $sql ="SELECT value FROM ost_form_entry_values a LEFT JOIN ost_form_entry e ON e.object_id=".$ticket->getId()." AND e.object_type='T' AND e.id=a.entry_id WHERE field_id=13";
  • Cool, thanks for the time!  Now,  would this be the finished product?

    <?php
    $sql ="SELECT value FROM ost_form_entry_values a LEFT JOIN ost_form_entry e ON e.object_id=".$ticket->getId()." AND e.object_type='T' AND e.id=a.entry_id WHERE field_id=13";
    $res = db_query($sql);
    list($val) = db_fetch_row($res);
    ?>
  • edited March 2014
    I don't get a blank page anymore when I log in - I get the dashboard | tickets | Knowledgebase 
    Open | Answered | Overdue | Closed Tickets | New Ticket

    but the links don't do anything (the URL does change though in address bar)

    PHP Fatal error:  Call to a member function getId() on a non-object in /var/www/upload/include/staff/header.inc.php on line 121, referer: http://myostserver.org/upload/scp/

    I know next to nothing but it looks like it doesn't like the php call inside the sql query?  

  • Let me take a look at my code real quick.
  • hey wayne,

    Try this -> replace your <title> tag with the following section of code, which is what I believe you want.

        <title>
    <?php
    if(isset($ticket)){
      $sql ="SELECT value FROM ost_form_entry_values a INNER JOIN ost_form_entry e ON e.object_id=".$ticket->getId()." AND e.object_type='T' AND e.id=a.entry_id WHERE field_id=13";
      $res = db_query($sql);
      list($val) = db_fetch_row($res);
      echo $ticket->getStatus() . " & " . $val . " & " . $ticket->getId();
    }else{
      echo ($ost && ($title=$ost->getPageTitle()))?$title:'osTicket :: Staff Control Panel';
    }
    ?>
        </title>


    I added an if statement incase you are not actually looking at a ticket so you don't get an error.
  • Which php file would I put this in?  
  • This goes in /include/staff/header.inc.php
  • edited March 2014
    Rowland, I love you.

    It worked!  I only now need to figure out how to insert "Ticket #" after "&" and I'm golden.  You should post this up as a HOWTO here now!  I bet it would be really, REALLY popular :-)
  • Well, I am assuming it worked.  Glad to hear it!  Ntozier can close the thread then!  Let me know if you have any other questions!
  • If it's OK - can we do a quick sum up HOWTO that I can modify my first post here for anyone else coming to the page?  
  • Unfortunately (unless someone knows something different than I do), you aren't able to edit a post after 1 hour has past.  They will have to scroll through to find the past a couple above on how to change it.
  • edited March 2014
    I bet one of the mods would be happy to change it for you if you wrote up what you want it to say. :)
  • Based on what rowlandan25 has given me I can now figure out how to pull any information from the database and put in my page title.  I just need to figure how to convey that in an intelligent manner for others.  :-)
Sign In or Register to comment.