Dorinn

Thanks man. You make my day. This is how I use your code, maybe someone else will be in a similar situation using Vtiger (of course, some css could be added for a better looking): <?php //created by PFMaBiSmAd on http://www.phpfreaks.com $con = mysql_connect("host","user","password"); mysql_select_db("vtiger", $con) or die; $sql = "SELECT * FROM view_tickets"; // view created by joining vtiger_troubletickets and vtiger_crmentity on ticketid=crmid $result1 = mysql_query($sql) or die(mysql_error()); // call back function for array_filter function find_closed($string) { $find = "Status Changed to Close"; $pos = strpos($string, $find); return ($pos === false) ? false : true ; } echo "<table border='1'>"; echo "<th>Ticket no.</th>"; echo "<th>Created date</th>"; echo "<th>Title</th>"; echo "<th>Description</th>"; echo "<th>Closed date</th>"; echo "<th>Diff time</th>"; echo "</tr>"; while ($row = mysql_fetch_array($result1)) { echo "<tr>"; echo "<td>" .$row['ticket_no'] . "</td>"; echo "<td>" .$row['createdtime'] . "</td>"; echo "<td>" .$row['title'] . "</td>"; echo "<td>" .$row['description'] . "</td>"; echo "<td>"; $string = $row['update_log']; $start = $row['createdtime']; $parts = explode('--//--',$string); $result = array_filter($parts, 'find_closed'); // find only the closed status if(empty($result)){ echo "Ticket is not closed </td>"; } else { // is closed, get the date list(,$closed) = explode('\. -- ',current($result)); echo "Closed date:" . $closed . "</td>"; list(,$day,$month,$year,$time,$ampm,,) = explode(' ',$closed); $datetime2 = new DateTime("$day $month $year $time $ampm"); //$start = $row['dataCreare']; // your created date ----------------------------------------------------------- $datetime1 = new DateTime($start); $interval = $datetime1->diff($datetime2); $array = array("y"=>"Year","m"=>"Month","d"=>"Day","h"=>"Hour","i"=>"Minute","s"=>"Second"); echo "<td>"; foreach($array as $key=>$value){ if($interval->$key > 0){ $plural = $interval->$key > 1 ? 's' : ''; echo " {$interval->$key} $value$plural "; } } echo "</td>"; } echo "</tr>"; } echo "</table>"; ?>

I if think twice, it's better to make the difference between closed date and created date because a ticket could be closed without being in progress. So I need to parse the log and take only the date for the "Status changed in Closed" event and the display it and the difference between it and the created date. The created date is other table's column which type is Datetime (2009-07-23 14:00:06). Thanks again

Hello, Thanks for your response. The part of converting the dates and making the differences works great. The only problem I have is that the text in the 'update_log' column may vary: it could have other info in it or "Status Changed to In Progress" could miss. e.g. Ticket created. Assigned to user Gabriel -- Monday 12th December 2011 12:21:58 PM by Admin--//-- Status Changed to In Progress\. -- Monday 12th December 2011 01:40:57 PM by Gabriel--//-- Priority Changed to High\. -- Monday 12th December 2011 04:41:17 PM by Gabriel--//-- Category Changed to Other\. -- Monday 12th December 2011 04:41:34 PM by Gabriel--//-- Status Changed to Closed\. -- Monday 12th December 2011 04:42:58 PM by Gabriel--//--

Any idea?

Hello, I use Vtiger and I want to make a report page for tickets. The mysql table for the report has columns like: id, title, status, update_log. The data in update_log column looks like this: " Status Changed to In Progress\. -- Tuesday 15th September 2009 04:12:40 PM by Operator--//-- Status Changed to Closed\. -- Friday 18th September 2009 10:39:22 AM by Admin--//-- " I want to display a table with the following columns: TicketID | Title | Status | Status Changed In Progress Date | Status Changed In Closed Date | Time elapse from In Progress to Closed How could I achieve this? Parsing data is not my best. Thanks

Hello, I have a database with the following tables: students groups exams examDay relation master detail ======= ===== ===== ======= ====== ====== ===== idStudent idGroup idExam idExDay idRelation idMaster idDetail idGroup groupName examName exDate idGroup idGroup idMaster studName idExDay idRelation idExam idExam idStudent coursesAtt idExDay I am trying to make a report from the Master and Detail tables that will have the table header like this: Group | Examination Date | Student Name | Exam1 | Exam2| .... | Exam n in which Exam1, Exam2,... are the exams names and they had to take the values from coursesAtt column. Is this possible? I could make a view (doing joins and group by) by the results will be displayed on row. That's why I ask for your help. Thanks

The contents of the tables are something like this: ------------ ----------------- ---------------------------- Probes | Name | Results ------------ ----------------- ---------------------------- 1 | Probe1 | 1 | Name1 | 1 | 1 | 1 ------------- ------------------ --------------------- 2 | Probe2 | 2 | Name2 | 2 | 1 | 1 ------------- ----------------- --------------------- 3 | Probe3 | 3 | Name3 | 3 | 1 | 2 ------------- ---------------- ------------------- 4 | Probe4 | etc. | 4 | 2 | 3 --------------- ------------------- etc. | 5 | 2 | 3 ------------------- | etc. So the table results can hold combinations of tables \'Probes\' and \'Name\'. I wrote a select like this: ---------------------------------------------------------------------------------- select t1.id_name, count(t2.id_probe) as A1, count(t3.id_probe) as A2, count(t4.id_probe) as A3, count(t5.id_probe) as B, count(t6.id_probe) as C, count(t7.id_probe) as D, count(t8.id_probe) as F from results t1 left join results t2 on t1.id_club=t2.id_club and t2.id_probe=1 left join results t3 on t1.id_club=t3.id_club and t3.id_probe=2 left join results t4 on t1.id_club=t4.id_club and t4.id_probe=5 left join results t5 on t1.id_club=t5.id_club and t5.id_probe=3 left join results t6 on t1.id_club=t6.id_club and t6.id_probe=4 left join results t7 on t1.id_club=t7.id_club and t7.id_probe=6 left join results t8 on t1.id_club=t8.id_club and t8.id_probe=7 group by id_name order by id_name; ------------------------------------------------------------------------------------- but the problem is that I want to have the names on first column not the id\'s and to put a condition to return all the values where id_name is not null. Many thanks

Hello, I have 3 tables in a database: |------------| |-----------| |------------| |Results | | Name | | Probes | |------------| |-----------| |------------| |id_results | |id_name | |id_probes | |id_name | |name | |probes | |id_probe | I want to make a select on these that will have an ouput like that: -------------------------------------------------------------------------------- Name | Probe1 | Probe2 ........ --------------------------------------------------------------------------------- Name1 | count of probe1 for Name1 | count of probe2 for Name1 Name2 | count of probe1 for Name2 | count of probe2 for Name2 . . How could I do this from php? Many thanks....