function load_market(mysqli $conn) { clear_market($conn); //calculate elementary price of each goods //for each places $places = PLACES_COUNT; for ($i = 1; $i <= $places; $i++) { // INSERT INTO `market`(`id`, `name`, `price`, `place_id`) VALUES ([value-1],[value-2],[value-3],[value-4],[value-5]) $res_goods_here = mysqli_query($conn, "SELECT * FROM " . GOODS_TABLE . " WHERE place_id = {$i}"); //print_sql("SELECT * FROM ".GOODS_TABLE." WHERE place_id = $i"); //print_query_result("place(id = $i) goods list", $res_goods_here); $goods_here_count = mysqli_num_rows($res_goods_here); for ($j = 0; $j < $goods_here_count; $j++) { $goods_here = mysqli_fetch_array($res_goods_here); $price = round($goods_here['price']); $name = $goods_here['name']; $id = $goods_here['goods_id']; $min_price = $price * $goods_here['fluctu_min']; $max_price = $price * $goods_here['fluctu_max']; $price = mt_rand($min_price, $max_price); mysqli_query($conn, "INSERT INTO `market`(`id`, `name`, `price`, `place_id`, `goods_id`) VALUES ({$j} + {$i}, '{$name}', {$price}, {$i}, {$id} )"); print_var("name", $name); print_sql("INSERT INTO `market`(`id`, `name`, `price`, `place_id`) VALUES ({$j} + {$i}, '{$name}', {$price}, {$i} )"); } } }
function dbQuery($sql, $parameters = array()) { global $fullSql, $debug; $fullSql = dbMakeQuery($sql, $parameters); if ($debug) { if ($GLOBALS['cli']) { print_message("\nSQL[%y" . $fullSql . "%n] ", 'color'); } else { print_sql($fullSql); } } if ($GLOBALS['config']['profile_sql']) { $time_start = microtime(true); } $result = mysql_query($fullSql); // sets $this->result if ($GLOBALS['config']['profile_sql']) { $time_end = microtime(true); #fwrite($this->logFile, date('Y-m-d H:i:s') . "\n" . $fullSql . "\n" . number_format($time_end - $time_start, 8) . " seconds\n\n"); $GLOBALS['sql_profile'][] = array('sql' => $fullSql, 'time' => number_format($time_end - $time_start, 8)); } if ($result === false && error_reporting() & 1) { // aye. this gets triggers on duplicate Contact insert //trigger_error('QDB - Error in query: ' . $fullSql . ' : ' . mysql_error(), E_USER_WARNING); } return $result; }
function dbQuery($sql, $parameters = array()) { global $fullSql; $fullSql = dbMakeQuery($sql, $parameters); if (OBS_DEBUG) { // Pre query debug output if (is_cli()) { print_message(PHP_EOL . 'SQL[%y' . $fullSql . '%n]', 'console', FALSE); } else { print_sql($fullSql); } } if (OBS_DEBUG || $GLOBALS['config']['profile_sql']) { $time_start = microtime(true); } $result = mysql_query($fullSql); // sets $this->result if (OBS_DEBUG || $GLOBALS['config']['profile_sql']) { $runtime = number_format(microtime(true) - $time_start, 8); $debug_msg .= 'RUNTIME[' . ($runtime > 0.05 ? '%r' : '%g') . $runtime . 's%n]'; if ($GLOBALS['config']['profile_sql']) { #fwrite($this->logFile, date('Y-m-d H:i:s') . "\n" . $fullSql . "\n" . number_format($time_end - $time_start, 8) . " seconds\n\n"); $GLOBALS['sql_profile'][] = array('sql' => $fullSql, 'time' => $runtime); } } if (OBS_DEBUG) { if ($result === FALSE && error_reporting() & 1) { // aye. this gets triggers on duplicate Contact insert //trigger_error('QDB - Error in query: ' . $fullSql . ' : ' . mysql_error(), E_USER_WARNING); $error_msg = 'Error in query: (' . mysql_errno() . ') ' . mysql_error(); $debug_msg .= PHP_EOL . 'ERROR[%r' . $error_msg . '%n]'; } if (is_cli()) { if (OBS_DEBUG > 1) { $rows = mysql_affected_rows(); $debug_msg = 'ROWS[' . ($rows < 1 ? '%r' : '%g') . $rows . '%n]' . PHP_EOL . $debug_msg; } // After query debug output for cli print_message($debug_msg, 'console', FALSE); } else { print_error($error_msg); } } return $result; }
$i++; } ################################################################ # Check what we want to sort data on ################################################################ if ($_GET['sortby'] == "date") { $sql = "SELECT `flmevent_date`,`flmevent_user`,MAX(`flmevent_feature`),count(*) FROM `flexlm_events` WHERE `flmevent_type`='OUT' GROUP BY `flmevent_date`,`flmevent_user` ORDER BY `flmevent_date`,`flmevent_user`,`flmevent_feature` DESC;"; } else { if ($_GET['sortby'] == "user") { $sql = "SELECT `flmevent_date`,`flmevent_user`,MAX(`flmevent_feature`),count(*) FROM `flexlm_events` WHERE `flmevent_type`='OUT' GROUP BY `flmevent_user`,`flmevent_date` ORDER BY `flmevent_user`,`flmevent_date`,`flmevent_feature` DESC;"; } else { $sql = "SELECT `flmevent_date`,MAX(flmevent_user),`flmevent_feature`,count(*) FROM `flexlm_events` WHERE `flmevent_type`='OUT' GROUP BY `flmevent_feature`,`flmevent_date` ORDER BY `flmevent_feature`,`flmevent_date`,`flmevent_user` DESC;"; } } if (isset($debug) && $debug == 1) { print_sql($sql); } $recordset = $db->query($sql); if (DB::isError($recordset)) { die($recordset->getMessage()); } while ($row = $recordset->fetchRow()) { $table->AddRow($row, "style=\"background: " . $features_color[$row[2]] . ";\""); } $table->updateColAttributes(3, "align=\"center\""); $recordset->free(); $db->disconnect(); ################################################################ # Right align the 3 column ################################################################ $table->updateColAttributes(2, "align=\"right\"");
function test_dictionary($dbType) { echo "<h3>{$dbType}</h3><p>"; $db = NewADOConnection($dbType, "pear"); $dict = NewDataDictionary($db); if (!$dict) { continue; } $dict->debug = 1; $opts = array('REPLACE', 'mysql' => 'TYPE=INNODB', 'oci8' => 'TABLESPACE USERS'); if (mt_rand(1, 10000) < 5000) { $flds = array(array('id', 'I', 'AUTO', 'KEY'), array('name' => 'firstname', 'type' => 'varchar', 'size' => 30, 'DEFAULT' => 'Joan'), array('lastname', 'varchar', 28, 'DEFAULT' => 'Chen', 'key'), array('averylonglongfieldname', 'X', 1024, 'NOTNULL', 'default' => 'test'), array('price', 'N', '7.2', 'NOTNULL', 'default' => '0.00'), array('MYDATE', 'D', 'DEFDATE'), array('TS', 'T', 'DEFTIMESTAMP')); } else { $flds = "\nID I AUTO KEY,\nFIRSTNAME VARCHAR(30) DEFAULT 'Joan',\nLASTNAME VARCHAR(28) DEFAULT 'Chen' key,\naverylonglongfieldname X(1024) DEFAULT 'test',\nprice N(7.2) DEFAULT '0.00',\nMYDATE D DEFDATE,\nBIGFELLOW X NOTNULL,\nTS T DEFTIMESTAMP"; } $sqla = $dict->CreateDatabase('ADOdbLite', array('postgres' => "LOCATION='/u01/postdata'")); print_sql($dbType, $sqla, "CreateDatabase"); $dict->SetSchema('ADOdbLite'); if (substr($dbType, 0, 8) != 'postgres') { $sqli = $dict->CreateTableSQL('testtable', $flds, $opts); print_sql($dbType, $sqli, "CreateTableSQL"); } $sqli = $dict->CreateIndexSQL('idx', 'testtable', 'firstname,lastname', array('BITMAP', 'FULLTEXT', 'CLUSTERED', 'HASH')); print_sql($dbType, $sqli, "CreateIndexSQL"); $sqli = $dict->CreateIndexSQL('idx2', 'testtable', 'price,lastname'); //,array('BITMAP','FULLTEXT','CLUSTERED')); print_sql($dbType, $sqli, "CreateIndexSQL"); $addflds = array(array('height', 'F'), array('weight', 'F')); $sqli = $dict->AddColumnSQL('testtable', $addflds); print_sql($dbType, $sqli, "AddColumnSQL"); $addflds = array(array('height', 'F', 'NOTNULL'), array('weight', 'F', 'NOTNULL')); $sqli = $dict->AlterColumnSQL('testtable', $addflds); print_sql($dbType, $sqli, "AlterColumnSQL"); if ($dbType == 'mysql') { $db->Connect($_POST['dbhost'], $_POST['dbusername'], $_POST['dbpassword'], $_POST['databasename']); $dict->SetSchema(''); $sqla2 = $dict->CreateTableSQL('adoxyz', $flds, $opts); if ($_POST['create_test']) { $debug_query = $dict->ExecuteSQLArray($sqla2); } if ($sqla2) { print_sql($dbType, $sqla2, "CreateTableSQL"); } print_sql($dbType, $dict->ChangeTableSQL('adoxyz', 'LASTNAME varchar(32)'), "ChangeTableSQL"); } print_sql($dbType, $dict->DropColumnSQL('table', array('my col', '`col2_with_Quotes`', 'A_col3', 'col3(10)')), "DropColumnSQL"); unset($db, $dict); }
<?php try { require_once "./db.php"; $REQUEST = get_request_data(); $uid = null; if (array_key_exists('userid', $REQUEST)) { $uid = $REQUEST['userid']; } elseif (isset($_COOKIE) && isset($_COOKIE['user'])) { $uid = $_COOKIE['user']['id']; } else { throw new Exception('Must supply userid'); } $dbh = open_db(); $cohortstring = ''; if (array_key_exists('cohortid', $REQUEST)) { $cohortstring = "AND cohortid={$REQUEST["cohortid"]}"; } print_sql($sql = "SELECT balance.*, cohort.name AS cohort_name, ufrom.name AS from_name, ufrom.nick AS from_nick, uto.name AS to_name, uto.nick AS to_nick FROM balance LEFT JOIN user AS ufrom ON userid_from=ufrom.userid LEFT JOIN user AS uto ON userid_to=uto.userid LEFT JOIN cohort USING(cohortid) WHERE amount <> 0 AND (userid_from={$uid} or userid_to={$uid}) {$cohortstring}"); if (($res = $dbh->query($sql, PDO::FETCH_ASSOC)) == false) { throw new Exception('Error selecting from balance table:' . implode($dbh->errorInfo(), ' ')); } $data = array(); foreach ($res as $row) { array_push($data, $row); } echo json_response('success', null, $data); } catch (Exception $e) { echo json_response('error', $e->getMessage(), null); }
?> <li class="dropdown"> <a href="<?php echo generate_url(array('page' => 'overview')); ?> " class="dropdown-toggle" data-hover="dropdown" data-toggle="dropdown"> <i class="oicon-databases"></i> <b class="caret"></b></a> <div class="dropdown-menu" style="padding: 10px 10px 0px 10px; width: 1150px; height: 700px; z-index: 2000; overflow: scroll;"> <table class="table table-condensed-more table-striped"> <?php $sql_profile = array_sort($sql_profile, 'time', 'SORT_DESC'); $sql_profile = array_slice($sql_profile, 0, 15); foreach ($sql_profile as $sql_query) { echo '<tr><td>', $sql_query['time'], '</td><td>'; print_sql($sql_query['sql']); echo '</td></tr>'; } ?> </table> </div> </li> <?php } // End profile_sql ?> </ul> </div> </div> </div>
$userid_payer = $balance['userid_to']; $userid_payee = $balance['userid_from']; } else { throw new Exception("No balance to settle"); } $amount = abs($amount); print_sql($balance); print_sql($sql = "INSERT INTO purchase SET description=\"{$REQUEST["desc"]}\", amount={$amount}, userid={$userid_enterer}, userid_payer={$userid_payer}, date_of=\"{$datestring}\", date_created=NOW()"); if (($nrows = $dbh->exec($sql)) != 1) { throw new Exception("Inserted {$nrows} rows into purchase table, expected 1..."); } $purchaseId = $dbh->lastInsertId(); print_sql($sql = "INSERT INTO iou SET purchaseid={$purchaseId}, cohortid={$cohortid}, userid_payer={$userid_payer}, userid_payee={$userid_payee}, amount={$amount}, date_updated=NOW()"); if (($nrows = $dbh->exec($sql)) != 1) { throw new Exception("Inserted {$nrows} rows into purchasedetail table, expected 1..."); } print_sql($sql = "UPDATE balance SET amount=0.00 WHERE userid_from={$userid1} AND userid_to={$userid2} AND cohortid={$cohortid}"); if (($nrows = $dbh->exec($sql)) != 1) { throw new Exception("Updated {$nrows} rows in balance table, expected 1..."); } //////////////////////////////////////////////////////////////////////////////// // commit the transaction on success // if ($debug) echo "<p>Everything was successful -- committing the transaction!</p>"; $dbh->commit(); echo json_response('success', null, null); } catch (Exception $e) { //////////////////////////////////////////////////////////////////////////////// // roll back the transaction on any error echo json_response('error', $e->getMessage(), null); $dbh->rollBack(); }
// if ($REQUEST['whopaid'] == $iou['userid']) continue; if ($iou['amount'] == "") { continue; } print_sql($sql = "INSERT INTO iou SET purchaseid={$purchaseId}, cohortid={$REQUEST["cohortid"]}, userid_payer={$REQUEST["whopaid"]}, userid_payee={$iou["userid"]}, amount={$iou["amount"]}, date_updated=NOW()"); if (($nrows = $dbh->exec($sql)) != 1) { throw new Exception("Inserted {$nrows} rows into purchasedetail table, expected 1..."); } // HOWEVER, we do NOT store self-balances if ($REQUEST['whopaid'] == $iou['userid']) { continue; } if ($REQUEST['whopaid'] < $iou['userid']) { print_sql($sql = "UPDATE balance SET amount=amount-{$iou["amount"]} WHERE userid_from={$REQUEST["whopaid"]} AND userid_to={$iou["userid"]} AND cohortid={$REQUEST["cohortid"]}"); } else { print_sql($sql = "UPDATE balance SET amount=amount+{$iou["amount"]} WHERE userid_to={$REQUEST["whopaid"]} AND userid_from={$iou["userid"]} AND cohortid={$REQUEST["cohortid"]}"); } if (($nrows = $dbh->exec($sql)) != 1) { throw new Exception("Updated {$nrows} rows in balance table, expected 1..."); } } //////////////////////////////////////////////////////////////////////////////// // commit the transaction on success // if ($debug) echo "<p>Everything was successful -- committing the transaction!</p>"; $dbh->commit(); echo json_response('success', null, null); } catch (Exception $e) { //////////////////////////////////////////////////////////////////////////////// // roll back the transaction on any error echo json_response('error', $e->getMessage(), null); $dbh->rollBack();
?> <li class="dropdown"> <a href="<?php echo generate_url(array('page' => 'overview')); ?> " class="dropdown-toggle" data-hover="dropdown" data-toggle="dropdown"> <i class="oicon-databases"></i> <b class="caret"></b></a> <div class="dropdown-menu" style="padding: 10px; width: 1150px;"> <table class="table table-bordered table-condensed-more table-rounded table-striped"> <?php $sql_profile = array_sort($sql_profile, 'time', 'SORT_DESC'); $sql_profile = array_slice($sql_profile, 0, 15); foreach ($sql_profile as $sql_query) { echo '<tr><td>', $sql_query['time'], '</td><td>', print_sql($sql_query['sql']), '</td></tr>'; #echo '<tr><td>', $sql_query['time'], '</td><td>' ,$sql_query['sql'], '</td></tr>'; } ?> </table> </div> </li> <?php } // End profile_sql ?> </ul> </div> </div> </div>
<?php try { require "./db.php"; $REQUEST = get_request_data(); $dbh = open_db(); if (!array_key_exists('name', $REQUEST)) { throw new Exception('Did not supply name'); } print_sql($sql = "SELECT * FROM location WHERE name LIKE '{$REQUEST["name"]}%'"); if (($res = $dbh->query($sql, PDO::FETCH_ASSOC)) == false) { throw new Exception("Could not select recent purchase ids"); } $locations = $res->fetchAll(); if (!$locations) { echo json_encode(array('status' => 'success', 'message' => 'No locations found', 'data' => null)); } else { echo json_response('success', null, $locations); } } catch (Exception $e) { echo json_response('error', $e->getMessage(), null); }
function muffleAlerts($host) { require_once "DB.php"; global $dsn; global $notify_resend; $mysqldate = date('Y-m-d H:i:s'); $alertdate = date('Y-m-d H:i:s', strtotime("-{$notify_resend} minutes")); $db = DB::connect($dsn, true); if (DB::isError($db)) { die($db->getMessage()); } # check for records $sql = "SELECT pkid from alert_events where type = '" . $host['type'] . "' and hostname = '" . $host['hostname'] . "' and datetime > '" . $alertdate . "' and datetime < '" . $mysqldate . "'"; $recordset = $db->query($sql); if (DB::isError($recordset)) { print_sql($sql); die($recordset->getMessage()); } if ($recordset->numRows() == 0) { #nothing found $sql = "INSERT into alert_events(datetime,type,hostname) values('" . $mysqldate . "','" . $host['type'] . "','" . $host['hostname'] . "')"; $recordset = $db->query($sql); if (DB::isError($recordset)) { print_sql($sql); die($recordset->getMessage()); } return 1; } else { # we found a match! # muffle alerts return 0; } }
throw new Exception("Could not connect to location table"); } if (($nrows = $res->fetchColumn()) != 1) { throw new Exception("Found {$nrows} locations with locationid {$REQUEST["locationid"]}, expecting 1..."); } if (array_key_exists('lat', $REQUEST)) { $valueString .= "lat={$REQUEST["lat"]} "; } if (array_key_exists('lon', $REQUEST)) { $valueString .= "lon={$REQUEST["lon"]} "; } if (array_key_exists('addr', $REQUEST)) { $valueString .= "addr=\"{$REQUEST["addr"]}\" "; } if ($valueString == "") { throw new Exception("No values were supplied to update the database"); } print_sql($sql = "UPDATE location SET {$valueString} WHERE locationid={$REQUEST["locationid"]}"); if (($nrows = $dbh->exec($sql)) > 1) { throw new Exception("Updated {$nrows} locations; expected 0 or 1..."); } elseif ($nrows == 0) { throw new Exception("Updated {$nrows} locations; the supplied data were already in the database"); } $dbh->commit(); echo json_response('success', null, null); } catch (Exception $e) { if ($dbh) { $dbh->rollBack(); } echo json_response('error', $e->getMessage(), null); }
function dbQuery($sql, $parameters = array()) { global $fullSql; $fullSql = dbMakeQuery($sql, $parameters); if (OBS_DEBUG > 0) { // Pre query debug output if (is_cli()) { $debug_sql = explode(PHP_EOL, $fullSql); print_message(PHP_EOL . 'SQL[%y' . implode('%n' . PHP_EOL . '%y', $debug_sql) . '%n]', 'console', FALSE); } else { print_sql($fullSql); } } if (OBS_DEBUG > 0 || $GLOBALS['config']['profile_sql']) { $time_start = microtime(true); } $result = dbCallQuery($fullSql); // sets $this->result if (OBS_DEBUG > 0 || $GLOBALS['config']['profile_sql']) { $runtime = number_format(microtime(true) - $time_start, 8); $debug_msg .= 'SQL RUNTIME[' . ($runtime > 0.05 ? '%r' : '%g') . $runtime . 's%n]'; if ($GLOBALS['config']['profile_sql']) { #fwrite($this->logFile, date('Y-m-d H:i:s') . "\n" . $fullSql . "\n" . number_format($time_end - $time_start, 8) . " seconds\n\n"); $GLOBALS['sql_profile'][] = array('sql' => $fullSql, 'time' => $runtime); } } if (OBS_DEBUG > 0) { if ($result === FALSE && error_reporting() & 1) { $error_msg = 'Error in query: (' . dbError() . ') ' . dbErrorNo(); $debug_msg .= PHP_EOL . 'ERROR[%r' . $error_msg . '%n]'; } if (is_cli()) { if (OBS_DEBUG > 1) { $rows = dbAffectedRows(); $debug_msg = 'ROWS[' . ($rows < 1 ? '%r' : '%g') . $rows . '%n]' . PHP_EOL . $debug_msg; } // After query debug output for cli print_message($debug_msg, 'console', FALSE); } else { print_error($error_msg); } } if ($result === FALSE && isset($GLOBALS['config']['db']['debug']) && $GLOBALS['config']['db']['debug']) { logfile('db.log', 'Failed dbQuery (#' . dbErrorNo() . ' - ' . dbError() . '), Query: ' . $fullSql); } return $result; }