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

What code are you using to read the company from the database ?

Hi Tomt,

welcome back..

i'm currently using the coding below (Which I know it wrong) but I have tried adjusting it and if I do the code stops working.

Any help on this would be great if possible guys..

Been racking my brains all day trying to get it to work.. :(

Any help on this would be great if possible guys..

Been racking my brains all day trying to get it to work.. :(

On closer inspection it is saving the ID Field for the Company and not the 'CompanyName' information I give it but i'm not to sure how to change that.

"<?=$selected?>><?=$projectName?>

Your saving: value="<?=$projectId?>" to the database, you'll either need to change it to value="<?=$projectName?>" so it writes the name to the database, or amend your query to select projectName from YourTable Where id = $projectID

Hi Tomt,

Many thanks for your help. To be honest I have played around with the code and as its has been written for something different. I have just tried to ram it into my setup and tweak it but its only done half a job.

I could really do with trying to make it a little simpler.

I believe the 1st part of the code is correct?

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

<?

$query ='SELECT * '.

'FROM category '.

'ORDER BY category ASC';

Now I need the part of code that saves the 'Catagory' field from the table called 'Catagory' into the database. so when you view the ticket it shows the 'Catagory' fields information and not the 'ID' does that make sense?

many thanks for any advice and help. I am learning a lot as we go through this.

Looking at your previous post :

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

This should be writing the value of $projectId in to and $projectName should be the name being shown on the dropdown menu.

Try changing "> to "

Does that do what you want ?

Hi Tomt,

Many thanks for that. I changed that around and now it works! Wh00!

right.. next think to do is somehow look out and see if you can figure this one out.. dont worry i'm going to also be wracking my brains over it also.

First of all need to Change the Email Address field to a drop down menu.

Secondly, The new 'Company Field'. Once the user has selected the company.

The email field will be populated with emails that are linked to that company name via ID's in the table.

this is the mission and i'm going to give it a shot! any advice from anyone of course always welcome. we all learn together

Write a Reply...