function removeLikeToTable($dbconn, $userId, $msgId)
{
pg_prepare($dbconn, "deleteLikes", "DELETE FROM yakLikes WHERE userID = \$1 AND msgID = \$2");
$sql = pg_execute($dbconn, "deleteLikes", array($userId, $msgId));
pg_prepare($dbconn, "updateLikes", "UPDATE yak SET likes=(likes-1) WHERE id = \$1");
$sql = pg_execute($dbconn, "updateLikes", array($msgId));
}
public function execQry()
{
$recordString = false;
switch ($this->connectorStructure['dbType']) {
case "ORA":
$connectorString = '(DESCRIPTION = (CONNECT_TIMEOUT=5) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =' . $this->connectorStructure['dbIP'] . ')(PORT = ' . $this->connectorStructure['dbPort'] . ')))(CONNECT_DATA=(SID= ' . $this->connectorStructure['dbName'] . ')))';
$dbConnector = oci_connect($this->connectorStructure['dbUser'], $this->connectorStructure['dbPassword'], $connectorString);
if (!$dbConnector) {
echo 'Failed connection.......';
} else {
$oraQuery = oci_parse($dbConnector, $this->queryStructure['dbQuery']);
}
oci_execute($oraQuery);
$rowsnum = oci_fetch_all($oraQuery, $recordString);
if ($rowsnum == 0) {
$recordString = array();
} else {
if (!(substr($this->queryStructure['dbQuery'], 1, 6) == 'select')) {
$oraQuery = oci_parse($dbConnector, 'commit');
oci_execute($oraQuery);
$rowsnum = oci_fetch_all($oraQuery, $recordString);
}
}
oci_close($dbConnector);
break;
case "PGS":
$connectorString = "host=" . $this->connectorStructure['dbIP'] . " port=" . $this->connectorStructure['dbPort'] . " dbname=" . $this->connectorStructure['dbName'] . " user="******" password=" . $this->connectorStructure['dbPassword'];
$dbConnector = pg_connect($connectorString);
if (!$dbConnector) {
echo 'Failed connection.......';
} else {
pg_prepare($dbConnector, $this->queryStructure['qryName'], $this->queryStructure['dbQuery']);
}
$queryResult = pg_execute($dbConnector, $this->queryStructure['qryName'], $this->queryStructure['qryParameters']);
if (!$queryResult) {
$recordString = array();
} else {
$recordString = pg_fetch_all($queryResult);
if (!(substr($this->queryStructure['dbQuery'], 1, 6) == 'select')) {
pg_prepare($dbConnector, 'commit', 'commit');
pg_exec($dbConnector, 'commit');
}
}
pg_close($dbConnector);
break;
default:
$recordString = false;
break;
}
return $recordString;
}
function populate_prepare_queries($dbconn)
{
// INSERT IN COMMANDES
pg_prepare($dbconn, "insert_commandes", 'INSERT INTO commandes (per_id,com_date,com_date_expedition,com_date_facturation,com_statut,com_statut_facturation)' . ' VALUES ($1,$2,$3,$4,$5,$6) RETURNING com_id');
// INSERT IN LIGNES_COMMANDE
pg_prepare($dbconn, "insert_lignes_commande", 'INSERT INTO lignes_commande (lic_quantite,pro_id,com_id,lic_est_reduction,lic_prix_unitaire)' . ' VALUES ($1,$2,$3,$4,$5) RETURNING lic_id');
}
/** Luo HTML sivustolaajuinen tagilistam\"{a}\"{a}r\"{a}t kysymykselle
* @param $question_id integer
*/
function create_global_tag_count_box_for_a_question($question_id)
{
/* $result resource
* $tags_array_summary array
* $figure array
*/
$tags_array_summary = get_tags_for_a_question($question_id);
$dbconn = pg_connect("host=localhost port=5432 dbname=noaa user=noaa password=123");
// to get the amout of tags Globally
$result = pg_prepare($dbconn, "query_tag_amount", 'SELECT count(tag)
FROM tags
WHERE tag = $1');
echo "<div class='tags_summary'>";
echo "<p>tagged</p>";
for ($i = 0; $i < count($tags_array_summary); $i++) {
echo "<div id='one_tag_line'>";
$result = pg_execute($dbconn, "query_tag_amount", array($tags_array_summary[$i]['tag']));
$figure = pg_fetch_all($result);
for ($j = 0; $j < count($figure); $j++) {
create_tags($tags_array_summary[$i]);
echo "<span id='multiplier'> × " . $figure[$j]['count'] . "</span>";
}
echo "</div>";
}
echo "</div>";
}
function lookup_user($usr_id, $cur_time)
{
// check if user exists (if not, create user)
// check if url exists (if not, create url)
// add vote
if ($con = connect_db('../auth.txt')) {
$result = pg_prepare($con, "check_user", 'SELECT * FROM users where id = $1');
$result = pg_execute($con, "check_user", array($usr_id));
$usr_entry = pg_fetch_array($result);
pg_free_result($result);
if (!$usr_entry) {
$result = pg_prepare($con, "reg_user", 'INSERT INTO users VALUES ($1, $2, $3)');
$result = pg_execute($con, "reg_user", array($usr_id, 0, (int) $cur_time));
pg_free_result($result);
$id = $usr_id;
$spent = 0;
$reg = $cur_time;
} else {
$id = $usr_entry[0];
$spent = $usr_entry[1];
$reg = $usr_entry[2];
}
echo json_encode(array("usr" => $id, "spent" => $spent, "reg" => $reg));
} else {
echo json_encode(array("f**k" => "nuts"));
}
}
function printUserLog()
{
//db connection
$conn = pg_connect(HOST . " " . DBNAME . " " . USERNAME . " " . PASSWORD) or die('Could not connect: ' . pg_last_error());
//query the database
$result = pg_prepare($conn, "getLog", "SELECT * FROM lab8.log\n\t\t\tWHERE username LIKE \$1") or die("getLog prepare fail: " . pg_last_error());
$result = pg_execute($conn, "getLog", array($_SESSION['user'])) or die("getLog execute fail: " . pg_last_error());
//Printing results in HTML
echo "<br>There where <em>" . pg_num_rows($result) . "</em> rows returned<br><br>\n";
echo "<table class='tablestuff' border='1'>";
//account for added form row
echo "<tr>";
//checking the number of fields return to populate header
$numFields = pg_num_fields($result);
//populating the header
for ($i = 0; $i < $numFields; $i++) {
$fieldName = pg_field_name($result, $i);
echo "<th width=\"135\">" . $fieldName . "</th>\n";
}
echo "</tr>";
//populating table with the results
while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
echo "\t<tr>\n";
foreach ($line as $col_value) {
echo "\t\t<td>{$col_value}</td>\n";
}
echo "\t</tr>\n";
}
echo "</table>\n";
// Free resultset
pg_free_result($result);
//close connection
pg_close($conn);
}
/**
* Prepares a query.
*
* @param $name The name of the statement.
* @param $sql The SQL in the statement.
*
* @return Whether the statement preparation was successful.
*/
public function prepare($name, $sql)
{
$result = pg_prepare($this->connection, $name, $sql);
$this->setError($result);
$this->checkDie($result);
return $result ? TRUE : FALSE;
}
/**
* Execute a query and return the result
*
* Executes a query with optional parameters (which are automatically
* escaped). If a name is provided, then the query will be prepared
* and saved if necessary (or simply executed if it was already
* prepared). Unnamed queries are prepared and executed each time they
* are called. Note that the second and later times a named statement
* is called, the query string is disregarded (as the statement is
* already prepared). There is no way to reallocate a name for a
* query.
*
* @param string $str The query string to execute
* @param array $params The query parameters
* @param string $name The name of the query
* @throws DatabaseException
* @return resource
*/
public static function query($str, $params = array(), $name = null)
{
$p =& static::$prepared;
if ($name && !array_key_exists($name, $p) || !$name) {
if ($name) {
$p[$name] = true;
} else {
$name = '';
}
error_log("Preparing query ({$name}): {$str}");
$prepped = pg_prepare(Database::connect(), $name, $str);
if ($prepped === false) {
$pgerr = pg_last_error();
if ($name) {
$msg = "Could not prepare query {$name}: {$pgerr}";
} else {
$msg = "Could not prepare query: {$pgerr}";
}
throw new DatabaseException($msg);
}
}
error_log("Executing query {$name}");
$resource = pg_execute(Database::connect(), $name, $params);
if ($resource === false) {
$pgerr = pg_last_error();
if ($name) {
$msg = "Could not execute query {$name}: {$pgerr}";
} else {
$msg = "Could not execute query: {$pgerr}";
}
throw new DatabaseException($msg);
}
return $resource;
}
/**
* prepare query
* */
public function execStatement($sql, $params)
{
$this->connect();
$result = pg_prepare($this->connection, 'myStmt', $sql);
pg_execute($this->connection, 'myStmt', $params);
$this->disconnect();
return $result;
}
function inside_allowed_area_check($wkt)
{
pg_prepare("", "SELECT ST_Within(ST_GeometryFromText(\$1, 25833), klarschiff.klarschiff_stadtgrenze_hro.the_geom) FROM klarschiff.klarschiff_stadtgrenze_hro");
$result = pg_execute("", array($wkt));
if ($row = pg_fetch_assoc($result)) {
return $row['st_within'] === 't';
}
return false;
}
/**
* @param $sql
*/
protected function executeQuery($sql)
{
$id = 9;
$name = "BMW";
$price = 36000;
$sql = "INSERT INTO cars VALUES(\$1, \$2, \$3)";
pg_prepare($this->connection, "prepare1", $sql) or die("Cannot prepare statement\n");
pg_execute($this->connection, "prepare1", array($id, $name, $price)) or die("Cannot execute statement\n");
}
function getState($state)
{
$ret = array();
$rs = pg_prepare($this->dbconn, "STATESELECT", sprintf("select *,\n ST_x(s.geom) as x, ST_y(s.geom) as y, valid at time zone '%s' as lvalid,\n max_gust_ts at time zone '%s' as lmax_gust_ts,\n max_sknt_ts at time zone '%s' as lmax_sknt_ts,\n \t\t\tvalid at time zone 'UTC' as utc_valid,\n s.name as sname from\n current c2, summary_%s c, stations s WHERE\n s.state = \$1 and (s.network ~* 'RWIS' or s.network ~* 'ASOS' or\n \t\t\ts.network in ('KCCI','KELO','KIMT') or network = 'AWOS') \n and c.day = 'TODAY'\n and c2.valid > 'TODAY' and c2.iemid = c.iemid and c.iemid = s.iemid", $this->tzname, $this->tzname, $this->tzname, date("Y")));
$rs = pg_execute($this->dbconn, "STATESELECT", array($state));
for ($i = 0; $row = @pg_fetch_array($rs, $i); $i++) {
$ret[$row["id"]] = new IEMAccessOb($row);
}
return $ret;
}
private function executeUpdate($query, $params)
{
$result = pg_prepare($this->connection, "query", $query);
if ($params) {
$resultSet = pg_execute($this->connection, "query", $params);
} else {
$resultSet = pg_execute($this->connection, "query");
}
return pg_affected_rows($resultSet);
}
function get_iemprop($propname)
{
$dbconn = iemdb("mesosite");
$rs = pg_prepare($dbconn, "SELECT321" . $propname, "SELECT * from properties where\n propname = \$1");
$rs = pg_execute($dbconn, "SELECT321" . $propname, array($propname));
if (pg_num_rows($rs) < 1) {
return null;
}
$row = pg_fetch_array($rs, 0);
return $row["propvalue"];
}
public function prepare($query)
{
$id = (string) mktime();
$res = pg_prepare($this->_connection, $id, $query);
if ($res) {
$rs = new pgsqlDbResultSet($res, $id, $this->_connection);
} else {
throw new jException('jelix~db.error.query.bad', pg_last_error($this->_connection) . '(' . $query . ')');
}
return $rs;
}
function neighbors($station, $lat, $lon)
{
$con = iemdb("mesosite");
$rs = pg_prepare($con, "_SELECT", "SELECT *,\n ST_distance(ST_transform(geom,3857), \n ST_transform(ST_GeomFromEWKT('SRID=4326;POINT(" . $lon . " " . $lat . ")'), 3857)) /1000.0 as dist from stations \n WHERE ST_point_inside_circle(geom, " . $lon . ", " . $lat . ", 0.25) \n and id != \$1 ORDER by dist ASC");
$result = pg_execute($con, "_SELECT", array($station));
$s = "<table class=\"table table-striped\">\n <thead><tr><th>Distance [km]</th><th>Network</th><th>Station Name</th></tr></thead>";
for ($i = 0; $row = @pg_fetch_assoc($result, $i); $i++) {
$s .= sprintf("<tr><td>%.3f</td><td><a href=\"locate.php?network=%s\">%s</a></td><td><a href=\"site.php?station=%s&network=%s\">%s</a></td></tr>", $row["dist"], $row["network"], $row["network"], $row["id"], $row["network"], $row["name"]);
}
$s .= "</table>";
return $s;
}
public function prepare($query)
{
list($newQuery, $parameterNames) = $this->findParameters($query, '$%');
$id = (string) time();
$res = pg_prepare($this->_connection, $id, $newQuery);
if ($res) {
$rs = new pgsqlDbResultSet($res, $id, $this->_connection, $parameterNames);
} else {
throw new jException('jelix~db.error.query.bad', pg_last_error($this->_connection) . '(' . $query . ')');
}
return $rs;
}
function getGlobalDefaults($dbconn)
{
$defaults = array("ms_tol" => 6, "ms2_tol" => 20, "ms_tol_unit" => "ppm", "ms2_tol_unit" => "ppm", "missed_cleavages" => 4, "notes" => "", "customsettings" => "", "acquisitions" => array(), "sequences" => array());
$getMultiOptions = array("enzyme" => "SELECT id FROM enzyme WHERE is_default = TRUE", "ions" => "SELECT id FROM ion WHERE is_default = TRUE", "crosslinkers" => "SELECT id FROM crosslinker WHERE is_default = TRUE", "losses" => "SELECT id FROM loss WHERE is_default = TRUE", "fixedMods" => "SELECT id FROM modification WHERE is_default_fixed = TRUE", "varMods" => "SELECT id FROM modification WHERE is_default_var = TRUE");
foreach ($getMultiOptions as $key => $value) {
pg_prepare($dbconn, $key, $value);
$result = pg_execute($dbconn, $key, array());
$defaults[$key] = resultsAsArray($result);
}
//error_log (print_r($defaults, TRUE));
return $defaults;
}
function p($query=null, $name='') { // prepare
l('Prepared query = ['.$query.']', LLDEBUG);
if($query === null) throw new Exception('Query is null');
// Replace the '?' in '$?' with a number
for($i=1; ($pos = strpos($query, '$?')) !== false; $i++) {
$query = substr_replace($query, $i, $pos+1, 1);
}
// Prepare query
$this->result = pg_prepare($this->connection, $name, $query);
if(false === $this->result) throw new Exception('Could not prepare query');
return $this;
}
function get_iemapps_tags($tagname)
{
// Get a html list for this tagname
$pgconn = iemdb("mesosite", TRUE, TRUE);
$rs = pg_prepare($pgconn, "TAGSELECT", "SELECT name, description, url from iemapps WHERE " . "appid in (SELECT appid from iemapps_tags WHERE tag = \$1) " . "ORDER by name ASC");
$rs = pg_execute($pgconn, "TAGSELECT", array($tagname));
$s = "<ul>";
for ($i = 0; $row = @pg_fetch_assoc($rs, $i); $i++) {
$s .= sprintf("<li><a href=\"%s\">%s</a><br />%s</li>\n", $row["url"], $row["name"], $row["description"]);
}
$s .= "</ul>";
return $s;
}
function login()
{
$schema = "feedmati_system";
$conn = dbconnect();
$user = $_POST['username'];
$loginQuery = "SELECT * FROM {$GLOBALS['schema']}.authentication WHERE user_email = \$1";
$stmt = pg_prepare($conn, "login", $loginQuery);
echo pg_result_error($stmt);
if (!$stmt) {
echo "Error, can't prepare login query<br>";
return;
}
$result = pg_execute($conn, "login", array($user));
if (pg_num_rows($result) == 0) {
echo "Username or Password is incorrect";
return;
}
$saltQuery = "SELECT salt FROM {$GLOBALS['schema']}.authentication WHERE user_email = \$1";
$saltPrepare = pg_prepare($conn, "salt", $saltQuery);
if (!$saltPrepare) {
echo "Could not sanitize query for salt";
return;
}
$saltResult = pg_execute($conn, "salt", array($user));
if (pg_num_rows($saltResult) > 0) {
$saltRow = pg_fetch_assoc($saltResult);
$salt = $saltRow['salt'];
$salt = trim($salt);
$saltedpw = sha1($_POST['password'] . $salt);
}
$userPassPrepare = pg_prepare($conn, "pwquery", "SELECT * FROM {$GLOBALS['schema']}.authentication WHERE user_email = \$1 AND password_hash = \$2");
if (!$userPassPrepare) {
echo "Error, can't prepare username password query<br>";
}
$user = $_POST['username'];
$pwq = array($user, $saltedpw);
$userPassResult = pg_execute($conn, "pwquery", $pwq);
$numrows = pg_num_rows($userPassResult);
//if there is a match it logs the person in and adds an entry to the log
if ($numrows == 1) {
$action = "login";
$_SESSION['user'] = $user;
$userPassRow = pg_fetch_assoc($userPassResult);
$fname = $userPassRow['fname'];
$_SESSION['fname'] = $fname;
header("Location: home.php");
} else {
echo "Username or Password is incorrect";
}
pg_close($conn);
}
function get_rss_data($id)
{
$connection_string = sprintf("host=%s dbname=%s user=%s password=%s", KS_DBSERVER, KS_DB, KS_OWNER, KS_PW);
$connection = pg_connect($connection_string);
// Retrieve all data from georss_polygone
$data = array();
pg_prepare("", "SELECT g.ideen, g.ideen_kategorien, g.probleme, g.probleme_kategorien, ST_AsText(g.the_geom) AS wkt FROM klarschiff.klarschiff_geo_rss g WHERE md5(g.id::varchar)=\$1");
$result = pg_execute("", array($id));
if ($row = pg_fetch_assoc($result)) {
$data = $row;
}
pg_close($connection);
return $data;
}
function validate_meldung_in_aoi($point)
{
$connection_string = sprintf("host=%s dbname=%s user=%s password=%s", KS_DBSERVER, KS_DB, KS_OWNER, KS_PW);
$connection = pg_connect($connection_string);
// Retrieve all data from georss_polygone
$data = array();
pg_prepare("", "SELECT ST_Within(ST_GeometryFromText(\$1, 25833), klarschiff.klarschiff_stadtgrenze_hro.the_geom) FROM klarschiff.klarschiff_stadtgrenze_hro");
$result = pg_execute("", array($point));
if ($row = pg_fetch_assoc($result)) {
return $row["st_within"] == 't';
}
pg_close($connection);
return false;
}
function getUserRights($dbconn, $userID)
{
pg_prepare($dbconn, "user_rights", "SELECT * FROM users WHERE id = \$1");
$result = pg_execute($dbconn, "user_rights", [$userID]);
$row = pg_fetch_assoc($result);
//error_log (print_r ($row, true));
$canSeeAll = !isset($row["see_all"]) || $row["see_all"] === 't';
// 1 if see_all flag is true or if that flag doesn't exist in the database
$canAddNewSearch = !isset($row["can_add_search"]) || $row["can_add_search"] === 't';
// 1 if can_add_search flag is true or if that flag doesn't exist in the database
$isSuperUser = isset($row["super_user"]) && $row["super_user"] === 't';
// 1 if super_user flag is present AND true
return array("canSeeAll" => $canSeeAll, "canAddNewSearch" => $canAddNewSearch, "isSuperUser" => $isSuperUser);
}
public function storeEvent(EventEnvelope $event, $seriesName)
{
$seriesNumber = $this->createSeriesIfNeeded($seriesName);
$this->ensureConnection();
$prepareRes = pg_prepare($this->connection, 'storeEvent', 'insert into ' . $this->config->get('postgres.prefix', true) . 'events values (now(), $1, $2, $3)');
if ($prepareRes === false) {
throw new \RuntimeException(pg_errormessage($this->connection));
}
$res = pg_execute($this->connection, 'storeEvent', array($seriesNumber, $event->value, json_encode($event->payload)));
if ($res === false) {
throw new \RuntimeException(pg_errormessage($this->connection));
}
return true;
}
/**
* Constructor.
*
* @param Connection $connection Reference to the connection object.
* @param string $query SQL query to prepare.
* @param array $types Types information, used to convert input params.
* @throws exceptions\InvalidQueryException
*/
public function __construct(Connection $connection, $query, array $types = array())
{
$this->_connection = $connection;
$this->_queryId = 'statement' . ++self::$statementIdx;
if (!@pg_prepare($this->_connection->getResource(), $this->_queryId, $query)) {
throw new exceptions\InvalidQueryException(pg_last_error($this->_connection->getResource()));
}
foreach ($types as $key => $type) {
if ($type instanceof TypeConverter) {
$this->_converters[$key] = $type;
} elseif (null !== $type) {
$this->_converters[$key] = $this->_connection->getTypeConverter($type);
}
}
}
function trashmail_check($config, $email)
{
$db_config = $config['database']['frontend'];
$connection = pg_connect("host=" . $db_config['host'] . " port=" . $db_config['port'] . " dbname=" . $db_config['dbname'] . " user="******" password="******"");
$blacklist_this = false;
if ($connection) {
pg_prepare('', "SELECT COUNT(*) FROM klarschiff.klarschiff_trashmail_blacklist WHERE \$1 LIKE '%@' || pattern OR \$2 LIKE '%.' || pattern");
$result = pg_execute('', array($_REQUEST['email'], $_REQUEST['email']));
if ($row = pg_fetch_assoc($result)) {
$blacklist_this = $row["count"] !== '0';
}
pg_close($connection);
}
return $blacklist_this ? '1001#1001#' . $config['labels']['errors']['mail_on_blacklist'] : false;
}
function trashmail_check($email)
{
$connection_string = sprintf("host=%s dbname=%s user=%s password=%s", KS_DBSERVER, KS_DB, KS_OWNER, KS_PW);
$connection = pg_connect($connection_string);
$blacklist_this = false;
if ($connection) {
pg_prepare('', "SELECT COUNT(*) FROM klarschiff.klarschiff_trashmail_blacklist WHERE \$1 LIKE '%@' || pattern OR \$2 LIKE '%.' || pattern");
$result = pg_execute('', array($_REQUEST['email'], $_REQUEST['email']));
if ($row = pg_fetch_assoc($result)) {
$blacklist_this = $row["count"] !== '0';
}
pg_close($connection);
}
return $blacklist_this ? '1001#1001#Ihre E-Mail-Adresse wird nicht akzeptiert, da sie auf unserer Trashmail-Blacklist steht.' : false;
}
function StationData($a, $n = "")
{
$this->table = array();
$this->dbconn = iemdb("mesosite");
$rs = pg_prepare($this->dbconn, "SELECT ST1", "SELECT *, " . "ST_x(geom) as lon, ST_y(geom) as lat from stations " . "WHERE id = \$1 and network = \$2");
$rs = pg_prepare($this->dbconn, "SELECT ST2", "SELECT *, " . "ST_x(geom) as lon, ST_y(geom) as lat from stations " . "WHERE id = \$1");
if (is_string($a)) {
$this->load_station($a, $n);
} else {
if (is_array($a)) {
foreach ($a as $id) {
$this->load_station($id, $n);
}
}
}
}
function NetworkTable($a)
{
$this->table = array();
$this->dbconn = iemdb("mesosite", PGSQL_CONNECT_FORCE_NEW);
$rs = pg_prepare($this->dbconn, "SELECT", "SELECT *, ST_x(geom) as lon, \n \tST_y(geom) as lat from stations WHERE network = \$1 ORDER by name ASC");
$rs = pg_prepare($this->dbconn, "SELECTST", "SELECT *, ST_x(geom) as lon, \n \tST_y(geom) as lat from stations WHERE id = \$1");
if (is_string($a)) {
$this->load_network($a);
} else {
if (is_array($a)) {
foreach ($a as $network) {
$this->load_network($network);
}
}
}
}
