function writeTable($qualifier, $subject_id = '', $description_search = 0) { global $IconPath; global $proxyURL; $db = new Querier(); // sanitize submission switch ($qualifier) { case "Num": $connection = $db->getConnection(); $statement = $connection->prepare("SELECT distinct left(t.title,1) as initial, t.title as newtitle, t.description, location, access_restrictions, t.title_id as this_record,eres_display, display_note, pre, citation_guide, ctags, helpguide\n \tFROM title as t\n \tINNER JOIN location_title as lt\n \tON t.title_id = lt.title_id\n \tINNER JOIN location as l\n \tON lt.location_id = l.location_id\n \tINNER JOIN restrictions as r\n \tON l.access_restrictions = r.restrictions_id\n \tINNER JOIN rank as rk\n \tON rk.title_id = t.title_id\n \tINNER JOIN source as s\n \tON rk.source_id = s.source_id\n \tWHERE left(title, 1) REGEXP '[[:digit:]]+'\n \tAND eres_display = 'Y'\n \tORDER BY newtitle"); $statement->execute(); $results = $statement->fetchAll(); break; case "All": $connection = $db->getConnection(); $statement = $connection->prepare("\n SELECT distinct left(t.title,1) as initial, t.title as newtitle, t.description, location, access_restrictions, t.title_id as this_record,eres_display, display_note, pre, citation_guide, ctags, helpguide, alternate_title\n FROM title as t\n INNER JOIN location_title as lt\n ON t.title_id = lt.title_id\n INNER JOIN location as l\n ON lt.location_id = l.location_id\n INNER JOIN restrictions as r\n ON l.access_restrictions = r.restrictions_id\n INNER JOIN rank as rk\n ON rk.title_id = t.title_id\n INNER JOIN source as s\n ON rk.source_id = s.source_id\n WHERE title != ''\n AND eres_display = 'Y'\n \t\tORDER BY newtitle"); $statement->execute(); $results = $statement->fetchAll(); break; case "bysub": if (isset($subject_id)) { // get title ids in pluslets' resource token connected to subject // I don't know the purpose of this, so I zeroed it out below by resetting the $lobjTitleIds array --agd $lobjGuide = new Guide($subject_id); $lobjTitleIds = $lobjGuide->getRelatedTitles(); $lobjTitleIds = array(); if (count($lobjTitleIds) > 0) { $connection = $db->getConnection(); $statement = $connection->prepare("\n \tSELECT distinct left(t.title,1) as initial, t.title as newtitle, t.description, location, access_restrictions, t.title_id as this_record,eres_display, display_note, pre, citation_guide, ctags, helpguide, alternate_title\n \tFrom title as t\n \tINNER JOIN location_title as lt\n \tON t.title_id = lt.title_id\n \tINNER JOIN location as l\n \tON lt.location_id = l.location_id\n \tINNER JOIN restrictions as r\n \tON l.access_restrictions = r.restrictions_id\n \tINNER JOIN rank as rk\n \tON rk.title_id = t.title_id\n \tINNER JOIN source as s\n \tON rk.source_id = s.source_id\n WHERE title LIKE :qualifer\n \tAND eres_display = 'Y'\n ORDER BY newtitle"); /* $condition1 = "WHERE (subject_id = $subject_id"; $condition1 .= count ( $lobjTitleIds ) > 0 ? "\nOR t.title_id IN (" . implode ( ',', $lobjTitleIds ) . ")" : ""; $condition1 .= ")"; */ $letter = "%" . $qualifier . "%"; $statement->bindParam(":qualifer", $letter); $statement->execute(); $results = $statement->fetchAll(); } else { $connection = $db->getConnection(); $statement = $connection->prepare("\n \tSELECT distinct left(t.title,1) as initial, t.title as newtitle, t.description, location, access_restrictions, t.title_id as this_record,eres_display, display_note, pre, citation_guide, ctags, helpguide, alternate_title\n \tFROM title as t\n \tINNER JOIN location_title as lt\n \tON t.title_id = lt.title_id\n \tINNER JOIN location as l\n \tON lt.location_id = l.location_id\n \tINNER JOIN restrictions as r\n \tON l.access_restrictions = r.restrictions_id\n \tINNER JOIN rank as rk\n \tON rk.title_id = t.title_id\n \tINNER JOIN source as s\n \tON rk.source_id = s.source_id\n WHERE subject_id = :subject_id\n \tAND eres_display = 'Y'\n ORDER BY newtitle"); $statement->bindParam(":subject_id", $subject_id); $statement->execute(); $results = $statement->fetchAll(); } } else { $connection = $db->getConnection(); $statement = $connection->prepare("\n \tSELECT distinct left(t.title,1) as initial, t.title as newtitle, t.description, location, access_restrictions, t.title_id as this_record,eres_display, display_note, pre, citation_guide, ctags, helpguide\n \tFROM title as t\n \tINNER JOIN location_title as lt\n \tON t.title_id = lt.title_id\n \tINNER JOIN location as l\n \tON lt.location_id = l.location_id\n \tINNER JOIN restrictions as r\n \tON l.access_restrictions = r.restrictions_id\n \tINNER JOIN rank as rk\n \tON rk.title_id = t.title_id\n \tINNER JOIN source as s\n \tON rk.source_id = s.source_id\n WHERE title LIKE :qualifer\n \tAND eres_display = 'Y'\n ORDER BY newtitle"); $letter = "%" . $qualifier . "%"; $statement->bindParam(":qualifer", $letter); $statement->execute(); $results = $statement->fetchAll(); } break; case "bytype": if (isset($_GET["type"])) { $type = scrubData("%" . $_GET["type"] . "%"); $connection = $db->getConnection(); $statement = $connection->prepare("\n \t\tSELECT distinct left(t.title,1) as initial, t.title as newtitle, t.description, location, access_restrictions, t.title_id as this_record,eres_display, display_note, pre, citation_guide, ctags, helpguide\n FROM title as t\n INNER JOIN location_title as lt\n ON t.title_id = lt.title_id\n INNER JOIN location as l\n ON lt.location_id = l.location_id\n INNER JOIN restrictions as r\n ON l.access_restrictions = r.restrictions_id\n INNER JOIN rank as rk\n ON rk.title_id = t.title_id\n INNER JOIN source as s\n ON rk.source_id = s.source_id\n WHERE ctags LIKE :type\n AND eres_display = 'Y'\n ORDER BY newtitle"); $statement->bindParam(":type", $type); $statement->execute(); $results = $statement->fetchAll(); } break; case "search": // If you uncomment the next line, it will search description field $connection = $db->getConnection(); $statement = $db->prepare("\n \tSELECT distinct left(t.title,1) as initial, t.title as newtitle, t.description, location, access_restrictions, t.title_id as this_record,eres_display, display_note, pre, citation_guide, ctags, helpguide\n \tFROM title as t\n \tINNER JOIN location_title as lt\n \tON t.title_id = lt.title_id\n \tINNER JOIN location as l\n \tON lt.location_id = l.location_id\n \tINNER JOIN restrictions as r\n \tON l.access_restrictions = r.restrictions_id\n \tINNER JOIN rank as rk\n \tON rk.title_id = t.title_id\n \tINNER JOIN source as s\n \tON rk.source_id = s.source_id\n WHERE (title LIKE :qualifier OR description LIKE :qualifier)\n \tAND eres_display = 'Y'\n ORDER BY newtitle"); $qualifier = "%" . $qualifier . "%"; $statement->execute(); $results = $statement->fetchAll(); break; default: // This is the simple output by letter and also the search if (strlen($qualifier) == 1) { // Is like the first letter $connection = $db->getConnection(); $statement = $connection->prepare("\n\tSELECT DISTINCT LEFT(t.title,1) as initial, t.title as newtitle, t.description, location, access_restrictions, t.title_id as this_record,eres_display, display_note, pre, citation_guide, ctags, helpguide,alternate_title\n \tFROM title as t\n \tINNER JOIN location_title as lt\n \tON t.title_id = lt.title_id\n \tINNER JOIN location as l\n \tON lt.location_id = l.location_id\n \tINNER JOIN restrictions as r\n \tON l.access_restrictions = r.restrictions_id\n \tINNER JOIN rank as rk\n \tON rk.title_id = t.title_id\n \tINNER JOIN source as s\n \tON rk.source_id = s.source_id\n WHERE left(t.title, 1) LIKE :qualifer\n \tAND eres_display = 'Y'\n\n UNION\n \tSELECT DISTINCT LEFT(alternate_title,1) as initial, alternate_title as newtitle, t.description, location, access_restrictions, t.title_id as this_record,eres_display, display_note, pre, citation_guide, ctags, helpguide,alternate_title\n FROM title as t\n \tINNER JOIN location_title as lt\n \tON t.title_id = lt.title_id\n \tINNER JOIN location as l\n \tON lt.location_id = l.location_id\n \tINNER JOIN restrictions as r\n \tON l.access_restrictions = r.restrictions_id\n \tINNER JOIN rank as rk\n \tON rk.title_id = t.title_id\n \tINNER JOIN source as s\n \tON rk.source_id = s.source_id\n WHERE left(alternate_title, 1) LIKE :qualifer\n \tAND eres_display = 'Y'\nORDER BY newtitle\n\n"); $letter = $qualifier . "%"; $statement->bindParam(":qualifer", $letter); $statement->execute(); $results = $statement->fetchAll(); } else { $connection = $db->getConnection(); $statement = $connection->prepare("\n \tSELECT distinct left(t.title,1) as initial, t.title as newtitle, t.description, location, access_restrictions, t.title_id as this_record,eres_display, display_note, pre, citation_guide, ctags, helpguide\n \tFROM title as t\n \tINNER JOIN location_title as lt\n \tON t.title_id = lt.title_id\n \tINNER JOIN location as l\n \tON lt.location_id = l.location_id\n \tINNER JOIN restrictions as r\n \tON l.access_restrictions = r.restrictions_id\n \tINNER JOIN rank as rk\n \tON rk.title_id = t.title_id\n \tINNER JOIN source as s\n \tON rk.source_id = s.source_id\n WHERE title LIKE :qualifer\n \tAND eres_display = 'Y'\n ORDER BY newtitle"); $letter = "%" . $qualifier . "%"; $statement->bindParam(":qualifer", $letter); $statement->execute(); $results = $statement->fetchAll(); $condition1 = "WHERE title LIKE " . $db->quote("%" . $qualifier . "%"); } if ($description_search == 1) { $connection = $db->getConnection(); $statement = $connection->prepare("\n \tSELECT distinct left(t.title,1) as initial, t.title as newtitle, t.description, location, access_restrictions, t.title_id as this_record,eres_display, display_note, pre, citation_guide, ctags, helpguide\n \tFROM title as t\n \tINNER JOIN location_title as lt\n \tON t.title_id = lt.title_id\n \tINNER JOIN location as l\n \tON lt.location_id = l.location_id\n \tINNER JOIN restrictions as r\n \tON l.access_restrictions = r.restrictions_id\n \tINNER JOIN rank as rk\n \tON rk.title_id = t.title_id\n \tINNER JOIN source as s\n \tON rk.source_id = s.source_id\n WHERE (title LIKE :qualifer OR description LIKE :qualifer1)\n \tAND eres_display = 'Y'\n ORDER BY newtitle"); $qualifier = "%" . $qualifier . "%"; $statement->bindParam(":qualifer", $qualifier); $statement->bindParam(":qualifer1", $qualifier); $statement->execute(); $results = $statement->fetchAll(); } } $num_rows = count($results); if ($num_rows == 0) { return "<div class=\"no_results\">" . _("Sorry, there are no results at this time.") . "</div>"; } // prepare header $items = "<table width=\"98%\" class=\"item_listing trackContainer\">"; $row_count = 0; $colour1 = "oddrow"; $colour2 = "evenrow"; foreach ($results as $myrow) { $row_colour = $row_count % 2 ? $colour1 : $colour2; $patterns = "/'|\"/"; $replacements = ""; $item_title = $myrow[1]; if ($myrow["pre"] != "") { $item_title = $myrow["pre"] . " " . $item_title; } $safe_title = trim(preg_replace($patterns, $replacements, $item_title)); $blurb = $myrow["description"]; $bib_id = $myrow[5]; // / CHECK RESTRICTIONS /// if ($myrow['4'] == 2 or $myrow['4'] == 3) { $url = $proxyURL . $myrow[3]; $rest_icons = "restricted"; } elseif ($myrow['4'] == 4) { $url = $myrow[3]; $rest_icons = "restricted"; } else { $url = $myrow[3]; $rest_icons = ""; // if you want the unlocked icon to show, enter "unrestricted" here } $current_ctags = explode("|", $myrow["ctags"]); // add our $rest_icons info to this array at the beginning array_unshift($current_ctags, $rest_icons); $icons = showIcons($current_ctags); // / Check for Help Guide /// if ($myrow["helpguide"] != "") { $helpguide = " <a href=\"" . $myrow["helpguide"] . "\"><img src=\"{$IconPath}/help.gif\" border=\"0\" alt=\"" . _("Help Guide") . "\" title=\"" . _("Help Guide") . "\" /></a>"; } else { $helpguide = ""; } // Check if there is a display note if ($myrow["display_note"] == NULL) { $display_note_text = ""; } else { $display_note_text = "<br /><strong>" . _("Note:") . " </strong>" . $myrow['display_note']; } $bonus = "{$blurb}<br />"; if ($blurb != "") { $information1 = "<span id=\"bib-{$bib_id}\" class=\"toggleLink curse_me\"><img src=\"{$IconPath}/information.png\" border=\"0\" alt=\"" . _("more information") . "\" title=\"" . _("more information") . "\" /></span>"; // This is new details link; you can use the one above if you prefer $information = "<span id=\"bib-{$bib_id}\" class=\"toggleLink curse_me\">" . _("about") . "</span>"; } else { $information = ""; $information1 = ""; } $target = targetBlanker(); $items .= self::generateLayout($row_colour, $url, $target, $item_title, $information, $information1, $icons, $helpguide, $display_note_text, $bonus); $row_count++; } $items .= "</table>"; return $items; }