ical to mysql - hair pulling stuff!

[SetToLoki]

Hey all

I am trying to take calendar info from an ical sheet and store it into a database.

 

I have done this to a certain extent but it is causing me many issues.

I started by getting each event in the ical sheet and stored them in an array

 

Example Array

Array
(
    [begin] => VEVENT
    [prodid] => -//Kerio Technologies//Kerio MailServer//EN
    [method] => PUBLISH
    [version] => 2.0
    [x-version-kms] => 6.2.0
    [x-calendarserver-access] => PUBLIC
    [tzid] => Dublin, Edinburgh, Lisbon, London
    [dtstart] => 19810329T010000
    [tzoffsetto] => +0100
    [tzoffsetfrom] => +0000
    [rrule] => FREQ=YEARLY-BYMONTH=3-BYDAY=-1SU
    [end] => VCALENDAR
    [dtstamp] => 20090703T091907Z
    [uid] => 7d593fe6-0a7d-4a82-bd1a-11a6f9276e4c
    [sequence] => 0
    [summary] => Problems Log
    [location] => Intranet
    [dtstart;tzid="dublin, edinburgh, lisbon, london"] => 20090703T103000
    [dtend;tzid="dublin, edinburgh, lisbon, london"] => 20090703T110000
    [class] => PUBLIC
    [priority] => 5
    [transp] => OPAQUE
    [x-microsoft-cdo-busystatus] => BUSY
    [x-label] => 0
    [description] => If event is deleted in Kerio it doesnt get deleted from 
    [ database.\n\nideas for fix] =>  \\nCron
    [ ntries against the event array.\nif row exists in table but not in array dele] => 
    [ te the row. \nwill need to loop through each row and check it against array. ] => 
    [ this could be a long process, test speeds.] => 
)

 

then I tried to enter each section of the array into a field in my database.

 

Code

foreach ( $events as $k => $event ) { //Loop through event
		$sEvent = $event['data'];
		$BlowUp = explode("\n", $sEvent);
		$e = array();

		foreach($BlowUp as $Data)  //Loop Through Event
		{
			$str = array(";");
			$rpl = array("-");
			$sRoffle = explode(":", $Data);
			$key = strtolower($sRoffle[0]);
			$data = mysql_escape_string(str_replace($str, $rpl, $sRoffle[1]));
			//$TempArray = array($key => $data);
			$e[$key] = $data;
		}
		// check data for empty fields
		if (!isset($e['uid'])){$e['uid'] = "null";}
		if (!isset($e['rrule'])){$e['rrule'] = "null";}
		if (!isset($e['summary'])){$e['summary'] = "null";}
		if (!isset($e['class'])){$e['class'] = "null";}
		if (!isset($e['description'])){$e['description'] = "null";}
		if (!isset($e['priority'])){$e['priority'] = "null";}
		if (!isset($e['x-microsoft-cdo-busystatus'])){$e['x-microsoft-cdo-busystatus'] = "null";}
		if (!isset($e['x-label'])){$e['x-label'] = "null";}
		if (!isset($e['dtstart;tzid="dublin, edinburgh, lisbon, london"'])){$e['dtstart;tzid="dublin, edinburgh, lisbon, london"'] = "null";}
		if (!isset($e['dtend;tzid="dublin, edinburgh, lisbon, london"'])){$e['dtend;tzid="dublin, edinburgh, lisbon, london"'] = "null";}
		if (!isset($e['dtstamp'])){$e['dtstamp'] = "null";}
		if (!isset($e['tzid'])){$e['tzid'] = "null";}
		if (!isset($e['location'])){$e['location'] = "null";}

		if(isset($e['dtstart;value=date'])){
			$e['dtstart;tzid="dublin, edinburgh, lisbon, london"'] = $e['dtstart;value=date'];
			$e['dtend;tzid="dublin, edinburgh, lisbon, london"'] = $e['dtend;value=date'];
		}
		//print_r($e);
		//$fields = "UID, rrule, summary, class, description, priority, xmicrosoftcdobusystatus, xlabel, dtstart, dtend, dtstamp, tzid, location";

		$values[] = "('".$e['uid']."','"
		.$e['rrule']."','"
		.$e['summary']."','"
		.$e['class']."','"
		.$e['description']."','"
		.$e['priority']."','"
		.$e['x-microsoft-cdo-busystatus']."','"
		.$e['x-label']."','"
		.$e['dtstart;tzid="dublin, edinburgh, lisbon, london"']."','"
		.$e['dtend;tzid="dublin, edinburgh, lisbon, london"']."','"
		.$e['dtstamp']."','"
		.$e['tzid']."','"
		.$e['location']."','1')";

				//Insert data into database
		$db1 = new MySQL($db_host, $db_user, $db_pass, $db_name);

		$sql1  = "INSERT INTO cal_events ";  
		$sql1 .= "(class, description, dtend, dtstamp, dtstart, id, location, priority, rrule, summary, tzid, UID, xlabel, xmicrosoftcdobusystatus) ";
		$sql1 .= "VALUES ";
		$sql1 .= "('".$e['class']."','".$e['description']."','".$e['dtend;tzid="dublin, edinburgh, lisbon, london"']."','".$e['dtstamp']."','".$e['dtstart;tzid="dublin, edinburgh, lisbon, london"']."','".$account['id']."','".$e['location']."','".$e['priority']."','".$e['rrule']."','".$e['summary']."','".$e['tzid']."','".$e['uid']."','".$e['x-label']."','".$e['x-microsoft-cdo-busystatus']."')";
		$sql1 .= "ON DUPLICATE KEY UPDATE ";
		$sql1 .= "class=VALUES(class), description=VALUES(description), dtend=VALUES(dtend), dtstamp=VALUES(dtstamp), dtstart=VALUES(dtstart), id=VALUES(id), location=VALUES(location), priority=VALUES(priority), rrule=VALUES(rrule), summary=VALUES(summary), tzid=VALUES(tzid), xlabel=VALUES(xlabel), xmicrosoftcdobusystatus=VALUES(xmicrosoftcdobusystatus)";

		if($results = $db1->query($sql1))
		{
			$string = date("d/m/Y H:i s")." :: calsync.php :: Affected Rows ".$db1->affected_rows($results)." \r\n";
			$File = "cron.txt"; 
			$Handle = fopen($File, 'a+');
			fwrite($Handle, $string); 
			fclose($Handle);
		}
		else
		{
			$string = date("d/m/Y H:i s")." :: calsync.php :: ERROR ".$db1->error." \r\n";
			$File = "cron.txt"; 
			$Handle = fopen($File, 'a+');
			fwrite($Handle, $string); 
			fclose($Handle);					
		}

 

now this work fine, though long winded

the problem I am having is when you come to events like description

 

[description] => If event is deleted in Kerio it doesnt get deleted from 
    [ database.\n\nideas for fix] =>  \\nCron
    [ ntries against the event array.\nif row exists in table but not in array dele] => 
    [ te the row. \nwill need to loop through each row and check it against array. ] => 
    [ this could be a long process, test speeds.] => 

 

As you can see each new line of the description is put into a new block how can I find and restore this info and put it into database, also if anyone can  be bothered reading and giving me examples of how to optimize the code that reads the array and stores it to database, I will reward you with a cookie!

 

[Adam]

How are you populating the array?

[SetToLoki]

How are you populating the array?

 

/**
  * Given XML for a calendar query, return an array of the events (/todos) in the
  * response.  Each event in the array will have a 'href', 'etag' and '$response_type'
  * part, where the 'href' is relative to the calendar and the '$response_type' contains the
  * definition of the calendar data in iCalendar format.
  *
  * @param string $filter XML fragment which is the <filter> element of a calendar-query
  * @param string $relative_url The URL relative to the base_url specified when the calendar was opened.  Default ''.
  * @param string $report_type Used as a name for the array element containing the calendar data. @deprecated
  *
  * @return array An array of the relative URLs, etags, and events from the server.  Each element of the array will
  *               be an array with 'href', 'etag' and 'data' elements, corresponding to the URL, the server-supplied
  *               etag (which only varies when the data changes) and the calendar data in iCalendar format.
  */
  function DoCalendarQuery( $filter, $relative_url = '' ) {

    $xml = <<<EOXML
<?xml version="1.0" encoding="utf-8" ?>
<C:calendar-query xmlns:D="DAV:" xmlns:C="urn:ietf:params:xml:ns:caldav">
  <D:prop>
    <C:calendar-data/>
    <D:getetag/>
  </D:prop>$filter
</C:calendar-query>
EOXML;

    $this->DoXMLRequest( 'REPORT', $xml, $relative_url );
    $xml_parser = xml_parser_create_ns('UTF-8');
    $this->xml_tags = array();
    xml_parser_set_option ( $xml_parser, XML_OPTION_SKIP_WHITE, 1 );
    xml_parse_into_struct( $xml_parser, $this->xmlResponse, $this->xml_tags );
    xml_parser_free($xml_parser);

    $report = array();
    foreach( $this->xml_tags as $k => $v ) {
      switch( $v['tag'] ) {
        case 'DAV::RESPONSE':
          if ( $v['type'] == 'open' ) {
            $response = array();
          }
          elseif ( $v['type'] == 'close' ) {
            $report[] = $response;
          }
          break;
        case 'DAV::HREF':
          $response['href'] = basename( $v['value'] );
          break;
        case 'DAV::GETETAG':
          $response['etag'] = preg_replace('/^"?([^"]+)"?/', '$1', $v['value']);
          break;
        case 'URN:IETF:PARAMS:XML:NS:CALDAV:CALENDAR-DATA':
          $response['data'] = $v['value'];
          break;
      }
    }
    return $report;
  }


  /**
  * Get the events in a range from $start to $finish.  The dates should be in the
  * format yyyymmddThhmmssZ and should be in GMT.  The events are returned as an
  * array of event arrays.  Each event array will have a 'href', 'etag' and 'event'
  * part, where the 'href' is relative to the calendar and the event contains the
  * definition of the event in iCalendar format.
  *
  * @param timestamp $start The start time for the period
  * @param timestamp $finish The finish time for the period
  * @param string    $relative_url The URL relative to the base_url specified when the calendar was opened.  Default ''.
  *
  * @return array An array of the relative URLs, etags, and events, returned from DoCalendarQuery() @see DoCalendarQuery()
  */
  function GetEvents( $start = null, $finish = null, $relative_url = '' ) {
    $filter = "";
    if ( isset($start) && isset($finish) )
        $range = "<C:time-range start=\"$start\" end=\"$finish\"/>";
    else
        $range = '';

    $filter = <<<EOFILTER
  <C:filter>
    <C:comp-filter name="VCALENDAR">
      <C:comp-filter name="VEVENT">
        $range
      </C:comp-filter>
    </C:comp-filter>
  </C:filter>
EOFILTER;

    return $this->DoCalendarQuery($filter, $relative_url);
  }

* @subpackage   caldav
* @author Andrew McMillan <debian@mcmillan.net.nz>
* @copyright Andrew McMillan
* @license   http://gnu.org/copyleft/gpl.html GNU GPL v2

These are the functions I am using, it is part of the Davical source code. (on a side note I spoke to the author and gained permission to use it for what I am doing)