Initial commit

1 files changed, 151 insertions, 0 deletions

diff --git a/db.php b/db.php
new file mode 100644
index 0000000..0f0c2d7
--- /dev/null
+++ b/db.php
@@ -0,0 +1,151 @@
+<?php
+require_once ('config.php');
+
+$pdo=new PDO ('mysql:host='.DB_HOST.';dbname='.DB_NAME,DB_USER,DB_PASS);
+
+function add_event ($info)
+{
+	global $pdo;
+	$st=$pdo->prepare ('insert into `events` (`title`,`location`,`start_date`,`end_date`,`source`,`added_by`,`description`) values (?,?,?,?,?,?,?)');
+	$ok=$st->execute ([
+		$info['title'],
+		$info['location'],
+		$info['start_date'],
+		$info['end_date'],
+		$info['source'],
+		$info['added_by'],
+		$info['description']
+	]);
+
+	if (!$ok){
+		$err=$st->errorInfo();
+		throw new Exception ('Error ('.$err[0].', '.$err[1].'): '.$err[2]);
+	}
+
+	$id=$pdo->lastInsertId();
+
+	$st=$pdo->prepare ('insert into `event_keyword` (`event`,`keyword`) values (?,?)');
+	foreach ($info['keywords'] as $keyword_id)
+		$st->execute ([$id,$keyword_id]);
+
+	return $id;
+}
+
+function get_events ($keywords=[])
+{
+	global $pdo;
+
+	if (count ($keywords) > 0){
+		$keywords=$pdo->query (
+			'select `id` from `keywords` ' .
+			'where `keyword` in ('.implode (',',array_map ([$pdo,'quote'],$keywords)).')'
+		);
+		$keywords=array_map ('array_shift',$keywords->fetchAll (PDO::FETCH_NUM));
+	}
+
+	$sql='select `events`.*,`sources`.`source` as `source_name` ' .
+		'from `events` inner join `sources` on `events`.`source`=`sources`.`id` ';
+	if (count ($keywords) > 0){
+		$sql.='where exists (select * from `event_keyword` ' .
+			'where `event`=`events`.`id` and `keyword` in ('.implode (',',$keywords).')) ';
+	}
+	$sql.='order by `start_date` asc,`end_date` asc,`title` asc';
+
+	$st=$pdo->prepare ($sql);
+	$st->execute();
+	$events=$st->fetchAll (PDO::FETCH_ASSOC);
+
+	$get_keywords_st=$pdo->prepare (
+		'select `keywords`.`keyword` ' .
+		'from `event_keyword` inner join `keywords` on `keywords`.`id`=`event_keyword`.`keyword` ' .
+		'where `event_keyword`.`event`=? ' .
+		'order by `keywords`.`keyword` asc'
+	);
+	foreach ($events as $i => $event){
+		$get_keywords_st->execute ([$event['id']]);
+		$keywords=$get_keywords_st->fetchAll (PDO::FETCH_NUM);
+		$events[$i]['keywords']=array_map ('array_shift',$keywords);
+	}
+
+	return $events;
+}
+
+function get_keyword ($keyword)
+{
+	global $pdo;
+	$st=$pdo->prepare ('select `id` from `keywords` where `keyword`=?');
+	$st->execute ([$keyword]);
+
+	if ($st->rowCount() > 0){
+		$row=$st->fetch();
+		return $row['id'];
+	}
+
+	return null;
+}
+
+function get_keywords ()
+{
+	global $pdo;
+	$keywords=$pdo->query (
+		'select `keywords`.`keyword`, count(`event_keyword`.`event`) as `count` ' .
+		'from `event_keyword` inner join `keywords` on `event_keyword`.`keyword`=`keywords`.`id` ' .
+		'group by `event_keyword`.`keyword` ' .
+		'order by `keywords`.`keyword` asc'
+	);
+	return $keywords->fetchAll (PDO::FETCH_ASSOC);
+}
+
+function get_or_add_keyword ($keyword)
+{
+	global $pdo;
+	$id=get_keyword ($keyword);
+
+	if (is_null ($id)){
+		$st=$pdo->prepare ('insert into `keywords` (`keyword`) values (?)');
+		$st->execute ([$keyword]);
+		$id=$pdo->lastInsertId();
+	}
+
+	return $id;
+}
+
+function get_source ($source)
+{
+	global $pdo;
+	$st=$pdo->prepare ('select `id` from `sources` where `source`=?');
+	$st->execute ([$source]);
+
+	if ($st->rowCount() > 0){
+		$row=$st->fetch();
+		return $row['id'];
+	}
+
+	return null;
+}
+
+function get_or_add_source ($source)
+{
+	global $pdo;
+	$id=get_source ($source);
+
+	if (is_null ($id)){
+		$st=$pdo->prepare ('insert into `sources` (`source`) values (?)');
+		$st->execute ([$source]);
+		$id=$pdo->lastInsertId();
+	}
+
+	return $id;
+}
+
+function get_user_by_email ($email)
+{
+	global $pdo;
+	$st=$pdo->prepare ('select * from `users` where `email`=?');
+	$st->execute ([$email]);
+
+	if ($st->rowCount() > 0)
+		return $st->fetch (PDO::FETCH_ASSOC);
+	else
+		throw new Exception ('no user with email '.$email);
+}