/**
* @param int $ID ID of the section to get.
* @return OnpubSection An {@link OnpubSection} object. NULL if the section does not exist in the database.
*/
public function get($ID, OnpubQueryOptions $queryOptions = NULL)
{
if ($queryOptions === NULL) {
$queryOptions = new OnpubQueryOptions();
}
$query = $this->getQuery($ID, $queryOptions);
$result = $this->pdo->query($query);
OnpubDatabase::verifyQuery($this->pdo, $result, FALSE);
if (!($rows = $result->fetchAll(PDO::FETCH_ASSOC))) {
return NULL;
}
$row = $rows[0];
$section = new OnpubSection();
$section->ID = $row["ID"];
$section->imageID = $row["imageID"];
$section->websiteID = $row["websiteID"];
$section->parentID = $row["parentID"];
$section->name = $row["name"];
$section->url = $row["url"];
$section->setCreated(new DateTime($row["created"]));
$section->setModified(new DateTime($row["modified"]));
if (!$queryOptions->includeArticles) {
return $section;
}
if ($row["imageID"]) {
$image = new OnpubImage();
$image->ID = $row["imageID"];
$image->websiteID = $row["websiteID"];
$image->fileName = $row["fileName"];
$image->description = $row["description"];
$image->setCreated(new DateTime($row["imageCreated"]));
$image->setModified(new DateTime($row["imageModified"]));
$section->image = $image;
}
$articlesassoc = array();
foreach ($rows as $row) {
$articleID = $row["articleID"];
if ($articleID) {
if ($queryOptions->includeContent) {
$content = $row["content"];
} else {
$content = "";
}
$article = new OnpubArticle();
$article->ID = $articleID;
$article->imageID = $row["articleImageID"];
$article->title = $row["title"];
$article->content = $content;
$article->url = $row["articleURL"];
$article->setCreated(new DateTime($row["articleCreated"]));
$article->setModified(new DateTime($row["articleModified"]));
if (isset($articlesassoc["{$articleID}"])) {
$article = $articlesassoc["{$articleID}"];
} else {
$articlesassoc["{$articleID}"] = $article;
}
if ($row["articleImageID"]) {
$image = new OnpubImage();
$image->ID = $row["articleImageID"];
$image->websiteID = $row["articleImageWebsiteID"];
$image->fileName = $row["articleImageFileName"];
$image->description = $row["articleImageDescription"];
$image->url = $row["articleImageURL"];
$image->setCreated(new DateTime($row["articleImageCreated"]));
$image->setModified(new DateTime($row["articleImageModified"]));
$article->image = $image;
}
$articles = $section->articles;
$exists = FALSE;
foreach ($articles as $a) {
if ($a->ID == $article->ID) {
$exists = TRUE;
}
}
if ($exists == FALSE) {
$articles[] = $article;
$section->articles = $articles;
}
}
}
$authorsassoc = array();
reset($rows);
foreach ($rows as $row) {
$authorID = $row["authorID"];
$articleID = $row["articleID"];
if ($authorID && $articleID) {
$author = new OnpubAuthor();
$author->ID = $authorID;
$author->imageID = $row["authorImageID"];
$author->givenNames = $row["givenNames"];
$author->familyName = $row["familyName"];
$author->displayAs = $row["displayAs"];
$author->setCreated(new DateTime($row["authorCreated"]));
$author->setModified(new DateTime($row["authorModified"]));
if (isset($authorsassoc["{$authorID}"])) {
$author = $authorsassoc["{$authorID}"];
} else {
$authorsassoc["{$authorID}"] = $author;
}
if ($row["authorImageID"]) {
$image = new OnpubImage();
$image->ID = $row["authorImageID"];
$image->websiteID = $row["authorImageWebsiteID"];
$image->fileName = $row["authorImageFileName"];
$image->description = $row["authorImageDescription"];
$image->setCreated(new DateTime($row["authorImageCreated"]));
$image->setModified(new DateTime($row["authorImageModified"]));
$author->image = $image;
}
$article = $articlesassoc["{$articleID}"];
$authors = $article->authors;
$exists = FALSE;
foreach ($authors as $a) {
if ($a->ID == $author->ID) {
$exists = TRUE;
}
}
if ($exists == FALSE) {
$authors[] = $author;
$article->authors = $authors;
}
}
}
$result->closeCursor();
return $section;
}
/**
* @param string $keywords Keyword(s) to search for.
* @return array All the images which were found as an array of {@link OnpubImage} objects.
*/
public function search($keywords, OnpubQueryOptions $queryOptions = NULL)
{
if ($queryOptions === NULL) {
$queryOptions = new OnpubQueryOptions();
}
$query = $this->searchQuery($keywords, $queryOptions);
$result = $this->pdo->query($query);
OnpubDatabase::verifyQuery($this->pdo, $result, FALSE);
$rows = $result->fetchAll(PDO::FETCH_ASSOC);
$images = array();
if ($rows) {
foreach ($rows as $row) {
$image = new OnpubImage();
$image->ID = $row["ID"];
$image->websiteID = $row["websiteID"];
$image->fileName = $row["fileName"];
$image->description = $row["description"];
$image->url = $row["url"];
$image->setCreated(new DateTime($row["created"]));
$image->setModified(new DateTime($row["modified"]));
$images[] = $image;
}
}
$result->closeCursor();
return $images;
}
/**
* @param OnpubQueryOptions $queryOptions Database query options.
* @return array An array of {@link OnpubSAMap} objects.
*/
public function select(OnpubQueryOptions $queryOptions = NULL, $sectionID = NULL, $articleID = NULL)
{
if ($queryOptions === NULL) {
$queryOptions = new OnpubQueryOptions();
}
$query = $this->selectQuery($queryOptions, $sectionID, $articleID);
$result = $this->pdo->query($query);
OnpubDatabase::verifyQuery($this->pdo, $result, FALSE);
$rows = $result->fetchAll(PDO::FETCH_ASSOC);
$samaps = array();
if ($rows) {
foreach ($rows as $row) {
$samap = new OnpubSAMap();
$samap->ID = $row["ID"];
$samap->sectionID = $row["sectionID"];
$samap->articleID = $row["articleID"];
$samap->setCreated(new DateTime($row["created"]));
$samap->setModified(new DateTime($row["modified"]));
$samaps[] = $samap;
}
}
$result->closeCursor();
return $samaps;
}
/**
* Get multiple articles from the database.
*
* @param OnpubQueryOptions $queryOptions Database query options.
* @return array An array of {@link OnpubArticle} objects.
*/
public function select(OnpubQueryOptions $queryOptions = NULL, $sectionID = NULL, $websiteID = NULL)
{
if ($queryOptions === NULL) {
$queryOptions = new OnpubQueryOptions();
}
$query = $this->selectQuery($queryOptions, $sectionID, $websiteID);
$result = $this->pdo->query($query);
OnpubDatabase::verifyQuery($this->pdo, $result, FALSE);
$rows = $result->fetchAll(PDO::FETCH_ASSOC);
$articles = array();
if ($rows) {
$lastID = null;
$article = new OnpubArticle();
foreach ($rows as $row) {
if ($lastID != $row["ID"]) {
$article = new OnpubArticle();
}
if ($queryOptions->includeContent) {
$content = $row["content"];
} else {
$content = "";
}
$article->ID = $row["ID"];
$article->imageID = $row["imageID"];
$article->title = $row["title"];
$article->content = $content;
$article->url = $row["url"];
$article->setCreated(new DateTime($row["created"]));
$article->setModified(new DateTime($row["modified"]));
if ($row["imageID"]) {
$image = new OnpubImage();
$image->ID = $row["imageID"];
$image->websiteID = $row["imageWebsiteID"];
$image->fileName = $row["imageFileName"];
$image->description = $row["imageDescription"];
$image->setCreated(new DateTime($row["imageCreated"]));
$image->setModified(new DateTime($row["imageModified"]));
$article->image = $image;
}
if ($sectionID || $websiteID) {
$article->sectionIDs[] = $row["sectionID"];
}
if ($lastID != $row["ID"]) {
$articles[] = $article;
}
$lastID = $article->ID;
}
}
$result->closeCursor();
return $articles;
}
/**
* Gets a list of MySQL databases that the logged-in user has access to.
* System database names are excluded from the list.
*
* @return array Array will be empty if user has no database access.
* Otherwise array will contain the names of the MySQL databases she has
* access to.
*/
public function listDBs()
{
$result = $this->pdo->query('SHOW DATABASES');
OnpubDatabase::verifyQuery($this->pdo, $result, FALSE);
$rows = $result->fetchAll(PDO::FETCH_ASSOC);
$excludes = array('mysql', 'performance_schema', 'information_schema');
$dbs = array();
if ($rows) {
foreach ($rows as $row) {
if (!in_array($row['Database'], $excludes)) {
$dbs[] = $row['Database'];
}
}
}
$result->closeCursor();
return $dbs;
}
/**
* @param OnpubQueryOptions $queryOptions Database query options.
* @return array An array of {@link OnpubAuthor} objects.
*/
public function select(OnpubQueryOptions $queryOptions = NULL)
{
if ($queryOptions === NULL) {
$queryOptions = new OnpubQueryOptions();
}
$query = $this->selectQuery($queryOptions);
$result = $this->pdo->query($query);
OnpubDatabase::verifyQuery($this->pdo, $result, FALSE);
$rows = $result->fetchAll(PDO::FETCH_ASSOC);
$authors = array();
if ($rows) {
foreach ($rows as $row) {
$author = new OnpubAuthor();
$author->ID = $row["ID"];
$author->imageID = $row["imageID"];
$author->givenNames = $row["givenNames"];
$author->familyName = $row["familyName"];
$author->displayAs = $row["displayAs"];
$author->url = $row["url"];
$author->setCreated(new DateTime($row["created"]));
$author->setModified(new DateTime($row["modified"]));
$authors[] = $author;
}
}
$result->closeCursor();
return $authors;
}
/**
* @param int $ID ID of the website to get.
* @return OnpubWebsite An {@link OnpubWebsite} object. NULL if the website does not exist in the database.
*/
public function get($ID, OnpubQueryOptions $queryOptions = NULL, $flatArticleList = FALSE)
{
if ($queryOptions === NULL) {
$queryOptions = new OnpubQueryOptions();
}
$query = $this->getQuery($ID, $queryOptions);
$result = $this->pdo->query($query);
OnpubDatabase::verifyQuery($this->pdo, $result, FALSE);
if (!($rows = $result->fetchAll(PDO::FETCH_ASSOC))) {
return NULL;
}
$row = $rows[0];
$website = new OnpubWebsite();
$website->ID = $row["ID"];
$website->imageID = $row["imageID"];
$website->name = $row["name"];
$website->url = $row["url"];
$website->imagesURL = $row["imagesURL"];
$website->imagesDirectory = $row["imagesDirectory"];
$website->setCreated(new DateTime($row["created"]));
$website->setModified(new DateTime($row["modified"]));
if ($row["imageID"]) {
$image = new OnpubImage();
$image->ID = $row["imageID"];
$image->websiteID = $row["ID"];
$image->fileName = $row["fileName"];
$image->description = $row["description"];
$image->setCreated(new DateTime($row["imageCreated"]));
$image->setModified(new DateTime($row["imageModified"]));
$website->image = $image;
}
if (!$queryOptions->includeSections) {
return $website;
}
$osections = new OnpubSections($this->pdo);
$sections = array();
$sectionsassoc = array();
foreach ($rows as $row) {
$sectionID = $row["sectionID"];
$ID = $row["ID"];
if ($sectionID) {
$section = new OnpubSection();
$section->ID = $sectionID;
$section->imageID = $row["sectionImageID"];
$section->websiteID = $row["sectionWebsiteID"];
$section->parentID = $row["sectionParentID"];
$section->name = $row["sectionName"];
$section->url = $row["sectionURL"];
$section->setCreated(new DateTime($row["sectionCreated"]));
$section->setModified(new DateTime($row["sectionModified"]));
if (isset($sectionsassoc["{$sectionID}"])) {
$section = $sectionsassoc["{$sectionID}"];
} else {
$sectionsassoc["{$sectionID}"] = $section;
}
if ($row["sectionImageID"]) {
$image = new OnpubImage();
$image->ID = $row["sectionImageID"];
$image->websiteID = $row["sectionImageWebsiteID"];
$image->fileName = $row["sectionImageFileName"];
$image->description = $row["sectionImageDescription"];
$image->setCreated(new DateTime($row["sectionImageCreated"]));
$image->setModified(new DateTime($row["sectionImageModified"]));
$section->image = $image;
}
$sections = $website->sections;
$exists = FALSE;
foreach ($sections as $s) {
if ($s->ID == $section->ID) {
$exists = TRUE;
}
}
if ($exists == FALSE) {
$sections[] = $section;
$website->sections = $sections;
}
}
}
// An array to track the subsections indexes in the sections array.
$subsections = array();
// Loop through all sections and link subsections to their parents and
// vice-versa.
for ($i = 0; $i < sizeof($sections); $i++) {
$section = $sections[$i];
if ($section->parentID) {
$parentID = $section->parentID;
if (isset($sectionsassoc["{$parentID}"])) {
$parentSection = $sectionsassoc["{$parentID}"];
$parentSection->sections[] = $section;
$section->parent = $parentSection;
}
$subsections[] = $i;
}
}
// Unset subsections from the original sections array now that they are
// linked to their parent sections.
foreach ($subsections as $subsection) {
unset($sections[$subsection]);
}
// Array now might not be offset from 0 for sections. Re-index from 0.
$website->sections = array_values($sections);
if (!$queryOptions->includeArticles) {
return $website;
}
$articlesassoc = array();
reset($rows);
foreach ($rows as $row) {
$articleID = $row["articleID"];
$sectionID = $row["sectionID"];
if ($articleID && $sectionID) {
if ($queryOptions->includeContent) {
$content = $row["content"];
} else {
$content = "";
}
$article = new OnpubArticle();
$article->ID = $articleID;
$article->imageID = $row["articleImageID"];
$article->title = $row["title"];
$article->content = $content;
$article->url = $row["articleURL"];
$article->setCreated(new DateTime($row["articleCreated"]));
$article->setModified(new DateTime($row["articleModified"]));
if (isset($articlesassoc["{$articleID}"])) {
$article = $articlesassoc["{$articleID}"];
} else {
$article->sectionIDs[] = $sectionID;
$articlesassoc["{$articleID}"] = $article;
}
if ($row["articleImageID"]) {
$image = new OnpubImage();
$image->ID = $row["articleImageID"];
$image->websiteID = $row["articleImageWebsiteID"];
$image->fileName = $row["articleImageFileName"];
$image->description = $row["articleImageDescription"];
$image->url = $row["articleImageURL"];
$image->setCreated(new DateTime($row["articleImageCreated"]));
$image->setModified(new DateTime($row["articleImageModified"]));
$article->image = $image;
}
$section = $sectionsassoc["{$sectionID}"];
$articles = $section->articles;
$exists = FALSE;
foreach ($articles as $a) {
if ($a->ID == $article->ID) {
$exists = TRUE;
}
}
if ($exists == FALSE) {
$articles[] = $article;
$section->articles = $articles;
}
}
}
if ($flatArticleList) {
$articles = array();
foreach ($articlesassoc as $article) {
$articles[] = $article;
}
$website->articles = $articles;
}
$authorsassoc = array();
reset($rows);
foreach ($rows as $row) {
$authorID = $row["authorID"];
$articleID = $row["articleID"];
if ($authorID && $articleID) {
$author = new OnpubAuthor();
$author->ID = $authorID;
$author->imageID = $row["authorImageID"];
$author->givenNames = $row["givenNames"];
$author->familyName = $row["familyName"];
$author->displayAs = $row["displayAs"];
$author->setCreated(new DateTime($row["authorCreated"]));
$author->setModified(new DateTime($row["authorModified"]));
if (isset($authorsassoc["{$authorID}"])) {
$author = $authorsassoc["{$authorID}"];
} else {
$authorsassoc["{$authorID}"] = $author;
}
if ($row["authorImageID"]) {
$image = new OnpubImage();
$image->ID = $row["authorImageID"];
$image->websiteID = $row["authorImageWebsiteID"];
$image->fileName = $row["authorImageFileName"];
$image->description = $row["authorImageDescription"];
$image->setCreated(new DateTime($row["authorImageCreated"]));
$image->setModified(new DateTime($row["authorImageModified"]));
$author->image = $image;
}
$article = $articlesassoc["{$articleID}"];
$authors = $article->authors;
$exists = FALSE;
foreach ($authors as $a) {
if ($a->ID == $author->ID) {
$exists = TRUE;
}
}
if ($exists == FALSE) {
$authors[] = $author;
$article->authors = $authors;
}
}
}
$result->closeCursor();
return $website;
}