Following the link I posted, there is an example of getting the data from a MySQL database and populating the dropdown lists.

I tend to try this sort of thing, then embed it into the ticketing software once i know it works.

Hi Tomt,

Excellent find! Many thanks for that.. Will look into this and try to get it working and the have a go an putting it into the new ticket screen..

Would you know which files I would need to input this coding into to make it work with a new ticket (sorry bit of a n00b when it comes to php)

For a client on a new ticket = include/client/open.inc.php

For staff on a new ticket = include/staff/newticket.inc.php

For a client on a new ticket = include/client/open.inc.php

For staff on a new ticket = include/staff/newticket.inc.php

Many thanks for that Tomt,

However, not being very ofay with PHP. i'm not to sure where you would place the javascript if there are not header of body tags?

Just incase someone out there is a PHP guru and have a spare 30mins on their hands.. this is what we are hoping to achieve..

I have attached the Screenshot.

Select Company - Will read off a list in a table from MySQL.

Support Type - Will read off a static list of 2 choices 'Hardware' or 'Software'.

thanks in advance for any help available.

ss.JPG

Hi guys,

As I dont expect everything to be handed to me on a plate, I have managed to figure out and put into place the Drop-down menu's 'Tomt' so kindly found me as you can see from the screen shot.

Now I just need some help/advice on how to get this into the ticket (as seen in previous screen shot)

thanks in advance guys.

ss2.JPG

backup and open \include\client\open.inc.php

FIND:

<tr>

<th width="20%">Full Name:</th>

<td>

<?if ($thisclient && ($name=$thisclient->getName())) {

?>

<input type="hidden" name="name" value="<?=$name?>"><?=$name?>

<?}else {?>

<input type="text" name="name" size="25" value="<?=$info?>">

<?}?>

&nbsp;<font class="error">*&nbsp;<?=$errors?></font>

</td>

</tr>

REPLACE WITH:

<tr>

<th width="20%">Full Name:</th>

<td>

<?if ($thisclient && ($name=$thisclient->getName())) {

?>

<input type="hidden" name="name" value="<?=$name?>"><?=$name?>

<?}else {?>

<input type="text" name="name" size="25" value="<?=$info?>">

<?}?>

&nbsp;<font class="error">*&nbsp;<?=$errors?></font>

</td>

<th width="20%">Select Company:</th>

<td width="20%">

<select size="1" name="D1">

<option>option1</option>

<option>option2</option>

</select>

</td>

</tr>

FIND:

<tr>

<th nowrap >Email Address:</th>

<td>

<?if ($thisclient && ($email=$thisclient->getEmail())) {

?>

<input type="hidden" name="email" size="25" value="<?=$email?>"><?=$email?>

<?}else {?>

<input type="text" name="email" size="25" value="<?=$info?>">

<?}?>

&nbsp;<font class="error">*&nbsp;<?=$errors?></font>

</td>

</tr>

REPLACE WITH:

<tr>

<th nowrap >Email Address:</th>

<td>

<?if ($thisclient && ($email=$thisclient->getEmail())) {

?>

<input type="hidden" name="email" size="25" value="<?=$email?>"><?=$email?>

<?}else {?>

<input type="text" name="email" size="25" value="<?=$info?>">

<?}?>

&nbsp;<font class="error">*&nbsp;<?=$errors?></font>

</td>

<th width="20%">Select Support:</th>

<td width="20%">

<select size="1" name="D1">

<option>option1</option>

<option>option2</option>

</select>

</td>

</tr>

EDIT the dropdown to suit your needs :)

Hi Tomt,

Many thanks! That is excellent and will come in very handy.

However, we need this on the staff side when opeing new tickets as we do not intend to allow the customer to create their own support tickets just yet.

Which files will need to be adjusted to this to show on a Staff created support ticket.. and once save would this information be carried through?

Your a star for helping me out. Thank you very much for this. ^_^

Apply the same idea as above to include/staff/newticket.php

Then you'll need to edit include/class.ticket.php to retrieve your new form fields and write them to the database.

Hi Tomt,

Many thanks for you help on this.

I have manged to put the information in to the Staff ticket which is great.. the next step will be to alter the .class file to write the new information to the table.

My question is, I guess I have to create a new field in the table for 'Support' & 'Company'? If so which table does this need to be written to?

The second step would be to somehow merge the javascript into PHP so the drop down menu pulls through the information.

By default when a new ticket is created, the ticket 'detail' is written to ost_ticket the body (text) of the ticket is written to ost_ticket_message

Both of these tables share similar fields and the data is joined between the two.

Where you write to and how you do it will depend on what you ultimately want to achieve !

For my multi company mod I created a new table called ost_company and created the fields I needed in their. I then added the field comp_id to ost_company, ost_ticket and ost_ticket_message.

My MySQL queries can then pull the correct data using the comp_id fields.

I run a test version of OSticket locally so I can edit and change the code and database to suit quickly. I also use HeidiSQL to look at the database structure.

Hope this helps :)

Hi Tomt,

First of all, many thanks for your help so far. I am not a php or javascript kind of guy so all your help is warmly received.

You asked what I ultimately want to achieve so I decided to take the time and show you in word form (guided with screenshots of where i'm at with this project).

The main goal is to have a ticket on the 'staff' side which would have additional fields that are populated by a MySQL table and this information to be filtered through OSticket to relavent sections. The main field to be added would be the Company list.

As you can see from Screenshot 1, I have managed to place the code in correctly for the staff new ticket screen.

In screenshot 2, Show the code use sent me in use but inScreenshot 3 in the SQL command I am needing to merge to be able to product the pulled through information.

Example of this can be seen working in Screenshot 4 and 5.

Once that has been achieved then I would need the informatio to be filtered through to the saved ticket screen shown in Screeshot 6 and then finally on the 'List ticket view' instead of the heading 'Department' showing.. We would need 'Company' as shown in screenshot 7.

I understand I would also need to edit the .class file but i'm not really too sure where to begin?!

any help would be great. Many thanks in advance for being so kind.

regards

Mark

ss1.JPG

ss2.jpg

ss3.jpg

ss4.jpg

ss5.JPG

P.S...Please find attached 6 & 7 as i'm only allowed to upload 5 images to a post.

ss6.jpg

ss7.JPG

Hi

I'm working away for the rest of this week.. hopefully someone else will jump in !

Hi Tomt,

Many thanks for your help on this... Good news is I've made some progress!

I've managed to pull through the information from the 'New Ticket' and filter it through to saved tickets and even print out using the 'Print' modification.

The only thing I need to know how to do now is get the 'Company' field to read from the table in MySQL.

any other takers out there on this one?

I think i've nearly cracked it.. just need some guidence...

I've found this code on the Mods Forum...

<tr>

<td align="left" ><b>Project:</b></td>

<td>

<select name="project_id">

<option value="" selected>Select Project</option>

<?

$query ='SELECT project_id, project_name '.

'FROM ost_project '.

'WHERE isactive = true '.

'ORDER BY project_name';

$services=db_query($query);

while (list($projectId,$projectName) = db_fetch_row($services)) {

$selected = ($info==$projectId)?'selected':'';?>

<option value="<?=$projectId?>"<?=$selected?>><?=$projectName?>

</option>

<?

}

?>

</select>

</td>

</tr>

Now some how I need to get that merged with my code..

</td>

<th width="20%">Company:</th>

<td width="20%">

<select size="1" name="CompanyName">

<option value="" selected >Select Source</option>

<option>Tradestock</option>

<option>Nutrigold</option>

</select>

</td>

</tr>

the only difference is I need it to read the following from MySql.

SELECT *

FROM `category`

ORDER BY `category`.`category` ASC

maybe you can replace your mysql query like this:

<tr>

<td align="left" ><b>Project:</b></td>

<td>

<select name="project_id">

<option value="" selected>Select Project</option>

<?

$query ='SELECT * '.

'FROM category '.

'ORDER BY category ASC';

$services=db_query($query);

while (list($projectId,$projectName) = db_fetch_row($services)) {

$selected = ($info==$projectId)?'selected':'';?>

<option value="<?=$projectId?>"<?=$selected?>><?=$projectName?>

</option>

<?

}

?>

</select>

</td>

</tr>

hope it can help you.

Hi OZKR,

thanks for your reply...

but the code in question was ripped out of another mod.. and I dont have any other these parts in my setup..

$services=db_query($query);

while (list($projectId,$projectName) = db_fetch_row($services)) {

$selected = ($info==$projectId)?'selected':'';?>

<option value="<?=$projectId?>"<?=$selected?>><?=$projectName?>

</option>

I've tried taking them out but i just get a blank 'new ticket screen.

What I want it to do if for the drop down menu to read the SQL Column in the table called Catergory so the staff member can select it.

ok, replace the while part with something like this:

while ($row = mysql_fetch_array($query, MYSQL_NUM)) {

the $query is your mysql query.

Inside the while you can print anything you want.

you can look at this php(dot)net example:

<?php

mysql_connect("localhost", "mysql_user", "mysql_password") or

die("Could not connect: " . mysql_error());

mysql_select_db("mydb");

$result = mysql_query("SELECT id, name FROM mytable");

while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {

printf ("ID: %s Name: %s", $row, $row);

}

mysql_free_result($result);

?>

hope you can solve your problem.

Nearly Cracked it!

Thanks for your reply ozkr,

I'm 99% there I think!... I've got the drop-down menu now reading from the MySQL field I needed as you can see in Screenshot 1

but....

When I save the ticket and try to view it under the heading 'Company Name' its showing a number 1? ( See Screenshot 2)

at First I thought it was pulling in an ID for each of the companies, but after testing it does not matter which Company I choose, 1, 2 or 3.. it still prints the same number 1 when viewing a saved ticket?!

The data its pulling in from is a new table called category (See Screenshot 3)

and the Script I am running is below.

</td>

<th width="20%">Company:</th>

<td width="20%">

<select name="CompanyName">

<option value="" selected>Select Company</option>

<?

$query ='SELECT * '.

'FROM category '.

'ORDER BY category ASC';

$services=db_query($query);

while (list($projectId,$projectName) = db_fetch_row($services)) {

$selected = ($info==$projectId)?'selected':'';?>

<option value="<?=$projectId?>"<?=$selected?>><?=$projectName?>

</option>

<?

}

?>

Any guidence we be greatly received

ss1.jpg

ss2.jpg

ss3.JPG