Location: PHPKode > scripts > Lightweight Club Calendar > lc-calendar-0.9.4/drivers/event/mysql.class.php
<?php
/**
* MySQL based EventDriver for LCC
*
* PHP Version 4, 5
*
* @author Greg* <hide@address.com>
* @copyright Copyright (c) 2007, Benedikt Hallinger
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the
* Free Software Foundation, 51 Franklin St, Fifth Floor,
* Boston, MA  02110-1301  USA
*/

/**
* A Driver that is capaple of storing and retrieving Eventdata from a MySQL database.
*
* The configuration is done via a more dimensional associative array (see below).
* This driver suits best the cases where you have a mysql database and no PEAR
* available. If you have a workin PEAR installation, you may consider
* using the PEAR::MDB2 event driver ('pearmbd2').
*
* The database schema is required as dollowing:
* +----+--------+---------+---------------+-----+
* | id | author | created | last_modified | ... |
* +----+--------+---------+---------------+-----+
* Please be sure to provide at least ALL meta event fields
* LCC requires. These are all default fields mentioned in
* the default configuration (see below).
*
* Possible configuration:
* 'db' (array)      Database connection informations.
*                   Default configuration:
*                   'host' => "localhost",
*                   'user' => "user",
*                   'pwd' => "secret",
*                   'dbname' => "lcc"
*
* 'table' (string)  The table name of the event table
*                   Default: 'events'
*
* 'fields' (array)  User can specify his own db col names for fields
*                   syntax is internal_key => user_key
*                   Default configuration:
*                   'id'                   => "id",
*                   'author'               => "author",
*                   'created'              => "created",
*                   'last_modified'        => "last_modified",
*                   'last_modified_author' => "last_modified_author",
*                   'event_start'          => "event_start",
*                   'event_end'            => "event_end",
*                   'title'                => "title",
*                   'description'          => "description",
*                   'color'                => "color"
*/
class LCC_EventDriver_MySQL extends LCC_EventDriver
{

	/**
	* Default config of the driver.
	*
	* This gets overwritten by the core calling setConfig()
	* Setting default values.
	*
	* @var array
	*/
	var $config = array(
	                    'db' => array(
	                                  'host'   => "localhost",
	                                  'user'   => "user",
	                                  'pwd'    => "secret",
	                                  'dbname' => "lcc"
	                             ),

	                    'fields' => array(
	                                      'id'                   => "id",
	                                      'author'               => "author",
	                                      'created'              => "created",
	                                      'last_modified'        => "last_modified",
	                                      'last_modified_author' => "last_modified_author",
	                                      'event_start'          => "event_start",
	                                      'event_end'            => "event_end",
	                                      'title'                => "title",
	                                      'description'          => "description",
	                                      'color'                => "color"
	                                 ),
	                    'table' => "events"
	);

	/**
	* The link-id for the connection.
	*
	* @access private
	*/
	var $_link = false;

	/**
	* A cache that holds all the events in a month.
	*
	* @access private
	*/
	var $_monthEvents = null;

	/**
	* Initiates connection to the database
	*/
	function configure()
	{
		$this->_link = mysql_connect($this->config['db']['host'], $this->config['db']['user'], $this->config['db']['pwd']);
		if ( $this->_link !== false ) {
			if ( mysql_select_db($this->config['db']['dbname'], $this->_link) === false ) {
				$this->_error("DB can't be found !");
			}
		} else {
			$this->_error("Can't connect to the MySQL Server.");
		}
	}

	/**
	* Returns event-IDs for a given month.
	* If the passed timestamp corresponds to a day within the range [1-15], then, we will look for events of this month. Otherwise, it will be
	* events of the next month.
	*
	* @param int $timestamp  UNIX timestamp
	* @return array          Event-IDs
	*/
	function getEventsForMonth($timestamp)
	{
		$this->_monthEvents = array();

		if ( $this->_link ) {
			$month = date("m", $timestamp);
			$year = date("Y", $timestamp);

			// Make the timestamp of the start day of the month.
			// 'j' <=> day without leading zero
			if (date("j", $timestamp) <= 15) {
				$start = mktime(0,0,0,$month, 1, $year);
			} else {
				$start = mktime(0,0,0, $month + 1, 1, $year);
			}

			// End of month.
			$end = mktime(23,59,59, date("m", $start) + 1, 0, date("Y", $start));

			// generates the month array.
			// There, we create an array for each day, to be sure we return something.
			for ($day = date("j", $start); $day <= $stop; $day++) {
				$this->_monthEvents[ $day ] = array();
			}

			$fields =& $this->config['fields']; // shortcut
			// SELECT id, event_start AS start, event_end AS end FROM events WHERE (event_start BETWEEN $start AND $end OR event_end BETWEEN $start AND $end) ORDER BY event_start ASC;
			$query = "SELECT " . $fields['id'] . ", " . $fields['event_start'] . " AS start, " . $fields['event_end'] . " AS end FROM " . $this->config['table'] . " WHERE (`" . $fields['event_start'] . "` BETWEEN " . $start . " AND " . $end . " OR `" . $fields['event_end'] . "` BETWEEN " . $start . " AND " . $end . ") ORDER BY " . $fields['event_start'] . " ASC;";
			$rsc = mysql_query($query, $this->_link);

			if ( $rsc ) {
				while ($event = mysql_fetch_assoc($rsc)) {
					// More common values.
					$from = date("j", $event['start']);
					$to = date("j", $event['end']);
					
					// Oh oh : this event spreads on multiple months.
					if ( date("m", $event['start']) != date("m", $event['end']) ) {
						// If it starts before the current month, then make it start at the 1st of the current month.
						if (date("m", $event['start']) != date("m", $start)) {
							$from = date("j", $start);
						}

						// Same with the end of the event.
						if (date("m", $event['end']) != date("m", $end)) {
							$to = date("j", $end);
						}
					}

					// Then, fetch the events in days. 
					for ( $day = $from; $day <= $to; $day++ ) {
						$this->_monthEvents[ $day ][] = $event['id'];
					}
				}	

				mysql_free_result($rsc);
			}
		}
	}



	/**
	* Returns event-IDs of events for a given day.
	*
	* @param int $timestamp     UNIX timestamp for the day
	* @return false|array       Indexed array containing event-IDs.
	*/
	function getEventsForDate($timestamp)
	{
		$events = false;
		if ( $this->_link ) {
			// Builds the month cache.
			if ( is_null($this->_monthEvents) ) {
				$this->getEventsForMonth($timestamp);
			}


			if ( array_key_exists(date("j", $timestamp), $this->_monthEvents) ) {
				$events =& $this->_monthEvents[ date("j", $timestamp) ];
			} else {
				$events = array();
			}
		}

		return $events;
	}

	/**
	* Storing data in the DB.
	*
	* @param int $id      The event-id to be updated. If empty, then a new event is inserted.
	* @param array $data  The event data.
	* @return int|false
	*/
	function storeData($id, $data)
	{
		if ($this->_link) {
			// By default, we assume a new event is to be inserted.
			$query_head = "INSERT INTO ";
			$query_body = "";
			$query_foot = ";";

			if ($id) { // Update an existing event
				// build the UPDATE query
				$query_head = "UPDATE ";
				$query_foot = "WHERE `" . $this->config['fields']['id'] . "`=" . $id . " LIMIT 1;"; 
			}

			$query_head .= $this->config['table'] . " SET";

			foreach ($data as $field => $value) {
				if ( array_key_exists($field, $this->config['fields'])) {
					$query_body .= " `" . $this->config['fields'][$field] . "`=" . (is_string($value) ? "\"":"") . $value . (is_string($value) ? "\"":"") . ",";
				}
			}

			if (strlen($query_body)) {
				$query_body[ strlen($query_body) - 1 ] = " ";
			}

			$query = $query_head . $query_body . $query_foot;

			$success = mysql_query($query, $this->_link);
		}

		if ( $success ) {
			if ( empty($id) ) {
				$id = mysql_insert_id($this->_link);
			}
		}

		return $id;
	}

	/**
	* Used by the core to fetch all data from a given event-id.
	*
	* Return value is an associative array.
	* If there is no event, return false.
	*
	* @return array|false
	*/
	function getData($id)
	{
		$data = false;
		if ( $this->_link ) {
			$rsc = mysql_query("SELECT * FROM " . $this->config['table'] . " WHERE `" . $this->config['fields']['id'] . "`=" . $id . " LIMIT 1;");
			if ($rsc) {
				// Raw data : fields name are those defined by the user.
				// They have to be replaced by internal names (see loop below).
				$raw_data = mysql_fetch_assoc($rsc);
				// builds the correct data array.
				$data = array();
				if ( $raw_data ) {
					foreach ($this->config['fields'] as $internal_key => $foreign_key) {
						if ( !empty($raw_data[ $foreign_key ])) {
							$data[$internal_key] = $raw_data[ $foreign_key ];
						}
					}

					// Computes start_date and end_date
					$data['start_date'] = date("Y-m-d", $data['event_start']);
					$data['end_date'] = date("Y-m-d", $data['event_end']);
					// Computes time_start and time_end
					$data['start_time'] = date("H:i", $data['event_start']);
					$data['end_time'] = date("H:i", $data['event_end']);

				}
				mysql_free_result($rsc);
			}
		}

		return $data;
	}

	/**
	* Deletes an event.
	*
	* @param int $id	event-id to be deleted.
	* @return true|false
	*/
	function deleteEvent($id)
	{
		$del_event = false;
		if ( $this->_link ) {
			$del_event = mysql_query("DELETE FROM " . $this->config['table'] . " WHERE `" . $this->config['fields']['id'] . "`=" . $id . " LIMIT 1;");
		}

		return $del_event;
	}

	/**
	* Print an error message.
	*
	* @param string $message	The error message to be displayed.
	*/
	function _error($message)
	{
		die('<b>' . get_class($this) . '</b>:' . $message . '<br />');
	}
}
?>
Return current item: Lightweight Club Calendar