[REQ] Custom Report using PHP

[webcarvallo]

Hi. Sam.
I´m a newbie.   I could not get your "PDF output" to work yet...

But I really need something a little different, and I Think it will be simple for you.

I just need to access data directly from MySQL (just like you) and display them on a page (and ideally the option to print it), according to some criteria such as could be the ticket ID or such a group or other criteria (custom fields)
I need to show the tickets belonging to this criteria and show at the bottom the sum of some variables that I have already in other custom fields (labour hours, cost of materials, etc ) to get a customized report, totaling some values below.  (get it?)

I prefer your aproach to work directly from de DB by PHP pages, totally independent of TTX.   

I think you've done pretty much here, but I don't know how to display results of a query as a table in a page and sum or calculate some fields of costs at the end of the list.

Please, Can you help me?  PLEASE, PLEASE.

[Sparky]

Hi. Sam.
I´m a newbie.   I could not get your "PDF output" to work yet...

But I really need something a little different, and I Think it will be simple for you.

I just need to access data directly from MySQL (just like you) and display them on a page (and ideally the option to print it), according to some criteria such as could be the ticket ID or such a group or other criteria (custom fields)
I need to show the tickets belonging to this criteria and show at the bottom the sum of some variables that I have already in other custom fields (labour hours, cost of materials, etc ) to get a customized report, totaling some values below.  (get it?)

I prefer your aproach to work directly from de DB by PHP pages, totally independent of TTX.   

I think you've done pretty much here, but I don't know how to display results of a query as a table in a page and sum or calculate some fields of costs at the end of the list.

Please, Can you help me?  PLEASE, PLEASE.

What you want is very custom and perhaps not as simple as you describe.  It also seems more related to a custom report than PDF generation so I'll be splitting this off as a new topic.

Begging for help is generally not welcome and I doubt anyone would donate the time to write this customization from scratch... but if you want to start working on it yourself and come back with some questions, that would be best.

Otherwise, if you don't even know where to start, you should probably hire a professional.  I know Alex does TTX customizations for a reasonable hourly rate.  You might want to contact him at his helpdesk.

[arvin]

Hello,

I had to do the same thing ... the solution to my issue was to create a date range PHP script that had a predefined criteria and export it to a downloadable excel file ... then I created a macro in excel to make my pivot table reports....

this solution is totally independent of TTX ....

I'd be more then happy to share it with you ...if you still need something ..

Take care

Arv

[Sparky]

Hello,

I had to do the same thing ... the solution to my issue was to create a date range PHP script that had a predefined criteria and export it to a downloadable excel file ... then I created a macro in excel to make my pivot table reports....

this solution is totally independent of TTX ....

I'd be more then happy to share it with you ...if you still need something ..

Take care

Arv

That's very generous of you.  Please feel free to share it in this thread if that's something you're willing to do.

[arvin]

Hi Sparky,

Will do ... I will add comments/instructions to all of the scripts today so that users can easily make edits.  once I'm done I will upload the scripts ...

Cheers!   

[arvin]

Okie Dokie .... here it is folks!

A complete php script with date range selection capabilities and export to excel (csv format)....

All you need to do unzip the file and ...

1 - Save it locally
2 - Update ttx_sql_output.php with you server login info (Lines 10 thru 14)
3 - Upload all files to somewhere in your public directory (I  suggest creating a new folder/dir)
4 - Point your browser to www.yourdomain.com/newdir/ttxrptgen.html

And have fun! 

Yes ...its been tested and it works 

Click here to download http://www.mediafire.com/?swpbk8o309k07et

[webcarvallo]

Thank you very much Arvin, I´ve gone for a while and now I come back.
I will test your script and comment it later.

Sparky:
Sorry if I importunate you ("Begging for help is generally not welcome...") , but I´m a newbie here, english isn´t my mothertongue and maybe I'm not fully understand this Forum rules.     
By the way, in my country, people often selflessly and generously help others, as Arvin and yourself does.   So for me it isn´t so strange ask for help (even starting from zero in some matter).  Therefore, I'll be more carefull from now.

You are very nice people, thanks again.

[OldWarrior]

Okie Dokie .... here it is folks!

A complete php script with date range selection capabilities and export to excel (csv format)....

2 - Update ttx_sql_output.php with you server login info (Lines 10 thru 14)

Hi arvin, thanks a lot for sharing.

I've some doubts_


In this line
$link = mysql_connect("SQL Server Name here:Port here","your userID here","your password here");
I don't understand which values to use
same in this line
mysql_select_db("your Db Name", $link);

• 1.- SQL Server Name here
• 2.- Port here
• 3.- your password here
• 4.- your Db Name

using http://MYDOMAIN.COM/sqlsetup.cgi I can see these data:

MySQL Module Setup
MySQL database access info. Please contact your hosting provider or system admin if not sure.
MySQL Host:     localhost
MySQL Port:     3306
Database ID:     xxxx_TTE
MySQL User:     xxxx_tte
MySQL Password:   xxxxpw 

using this lines
$link = mysql_connect("localhost:3306","xxxx_tte","xxxxpw");
mysql_select_db("xxxx_TTE", $link);
reports the error
Could not connect: Access denied for user 'xxxx_tte'@'domain.com' (using password: YES)

"1.-" is the domain url? or is the database? or it'is correct "localhost"?
"2.-" for this value I've used the 3306 as show in sqlsetup.cgi
"3.-" the password is the pw of the db? or the password of domain/admin?
"4.-" is the "Database ID" as show in sqlsetup? or some as xxxx_TTE.tickets or xxxx_TTE.messages

I've tested with several differences but no luck.

Thanks very much.[/list]

[arvin]

Hi OldWarrior !

I hope this helps ....

Code:

$link = mysql_connect("sqlxxx.yoursqlservername.com:3306","yourUID","yourPWD");
if (!$link) {
        die('Could not connect: ' . mysql_error());
}
mysql_select_db("nameofyouDB", $link);
extract($_POST);

I believe the reason you are having connection issues is because of "Localhost"

[OldWarrior]

Hi OldWarrior !

I hope this helps ....

Code:

$link = mysql_connect("sqlxxx.yoursqlservername.com:3306","yourUID","yourPWD");
if (!$link) {
        die('Could not connect: ' . mysql_error());
}
mysql_select_db("nameofyouDB", $link);
extract($_POST);

I believe the reason you are having connection issues is because of "Localhost"

Yes, now it works.
Thanks very much.

[arvin]

  anytime OldWarrior !

have fun !