*
verticalSlice
1. For an array of assoc rays, return an array of values for a particular key
2. if $keyfield is given, same as above but use that hash key as the key in new array
public static verticalSlice ( $array, $field, $keyfield = null ) |
function displayAllMainPageCategoryItems() { $category_ids = DBHelper::verticalSlice(Category::getCategories(), "id"); $html = ""; for ($i = 0; $i < count($category_ids); $i++) { $html .= sprintf('<div data-tab="%d" class="category-items hidden">', $i + 1); $html .= displayMainPageCategoryItems($category_ids[$i]); $html .= '</div>'; } return $html; }
function test_4_null() { DB::query("UPDATE accounts SET password = NULL WHERE username=%s", 'Bart'); $all = DB::query("SELECT * FROM accounts ORDER BY id ASC"); $ages = DBHelper::verticalSlice($all, 'age', 'password'); $this->assert(count($ages) === 5); $this->assert($ages[''] === '15'); $passwords = DBHelper::reIndex($all, 'password'); $this->assert(count($passwords) === 5); $this->assert($passwords['']['username'] === 'Bart'); $this->assert($passwords['']['password'] === NULL); }
public function getItems($use_1d) { if (count($this->items) != 0) { if ($use_1d) { return $this->items_1d; } return $this->items; } $data = DB::query("SELECT * FROM category_items WHERE category_id=%d", $this->category_id); $ids = DBHelper::verticalSlice($data, "id"); foreach ($ids as $id) { $item = new categoryItem($this->category_id, $id); $values = array($this->category_id, $id, $item->name, $item->image); array_push($this->items_1d, $values); array_push($this->items, $item); } if ($use_1d) { return $this->items_1d; } return $this->items; }
die; } $email_bb = ''; foreach ($current_events as $event) { $description = $event["description"]; if (empty(trim($description))) { $description = "[no description]"; } $email_bb .= "[subheading][i]{$event["title"]}[/i] on {$event["date"]}[/subheading]{$description}\n\n"; } $email_bb = <<<HEREDOC Hi captains! This is a reminder that [b]{$count}[/b] events are coming up within a couple of weeks: {$email_bb} Thanks! Your friendly LHS Math Club Mailbot P.S. An event reminder will be sent every Sunday. HEREDOC; if ($count > 1) { $subject = $count . " Events coming up"; } else { $subject = "'" . $current_events[0]["title"] . "' coming up"; } $status = send_email(array("*****@*****.**"), $subject, $email_bb, "*****@*****.**", "[LHS Math Club Captains]", NULL); if ($status !== true) { die("Error: {$status}"); } $ids = DBHelper::verticalSlice($current_events, 'event_id'); DB::update('events', array('auto_remind' => DB::sqleval('auto_remind + 1')), 'event_id in %li', $ids);
$sid = $_GET["sid"]; if ($user === 0) { header("Location: /index.php"); exit; } if ($user->data["permission"] != 4) { if ($user->data["service_id"] != $sid && $user->data["permission"] == 3) { echo "Invalid permissions"; // return; } } $id = intval($id); if ($id !== -1) { $item = DB::queryOneRow("SELECT * FROM menu_items WHERE id=%d", $id); $side_link = DB::query("SELECT * FROM menu_sides_item_link WHERE item_id=%d", $id); $used = DBHelper::verticalSlice($side_link, "sides_id"); if (count($used) == 0) { $used = array(-1); } $osides = DB::query('SELECT * FROM menu_sides WHERE id NOT IN (' . implode(',', array_map('intval', $used)) . ') AND service_id=%d', $sid); } else { $side_link = array(); $osides = DB::query("SELECT * FROM menu_sides WHERE service_id=%d", $sid); } $iname = $id == -1 ? "Name" : "Name: " . $item["name"]; $iprice = $id == -1 ? "Price" : "Price: " . $item["price"]; $idesc = $id == -1 ? "Description" : "Description: " . $item["desc"]; $checked = $id == -1 ? "" : ($item["has_side"] === "1" ? "checked" : ""); if ($id === -1) { $id = intval(DB::queryOneRow("SELECT * FROM settings WHERE name='lastitem'")["value"]) + 1; DB::update("settings", array("value" => $id), "name=%s", "lastitem");
function get_bcc_list() { static $list = 0; //Caching, for efficiency. if ($list === 0) { $result = DB::query('SELECT name, email FROM users WHERE mailings="1" AND permissions!="T" AND email_verification="1"'); //Doesn't have to be approved. Includes you. $list = DBHelper::verticalSlice($result, 'name', 'email'); } return $list; }
function show_scores() { /* //What's the difference between inner, right, left, and full? //NO IDEA STILL. SELECT * FROM ( SELECT score_id, tests.test_id FROM (test_scores INNER JOIN tests ON tests.test_id = test_scores.test_id) UNION ALL SELECT score_id, tests.test_id FROM (test_scores RIGHT JOIN tests ON tests.test_id = test_scores.test_id) ) tbl GROUP BY score_id HAVING COUNT(*) =1 */ /* //Valiant attempt: SELECT tests.test_id, tests.name, tests.total_points, (test_scores.score - stats.average) / stats.st AS zvalue FROM (test_scores INNER JOIN tests ON test_scores.test_id = tests.test_id CROSS JOIN (SELECT STDDEV(test_scores.score) AS st, AVG(test_scores.score) AS average) stats) WHERE test_scores.test_id IN (1,2,3,4,5,6,7,8) GROUP BY test_scores.test_id ORDER BY tests.date DESC */ $teststats = DB::query('SELECT tests.test_id as test_id, tests.name as name, tests.total_points as total_points, STDDEV(test_scores.score) AS stddev, AVG(test_scores.score) AS avg FROM (test_scores INNER JOIN tests ON test_scores.test_id = tests.test_id) WHERE test_scores.test_id IN %li GROUP BY test_scores.test_id ORDER BY tests.date DESC', $_GET['Test']); $test_ids = DBHelper::verticalSlice($teststats, 'test_id'); $test_names = DBHelper::verticalSlice($teststats, 'name', 'test_id'); $avgs = DBHelper::verticalSlice($teststats, 'avg', 'test_id'); $stddevs = DBHelper::verticalSlice($teststats, 'stddev', 'test_id'); /* Original, readable one: select users.name, sum(case tests.test_id when 248 then score/12 end) as t1, sum(case tests.test_id when 249 then score/16 end) as t2, sum(case tests.test_id when 248 then score/12 when 249 then score/16 end) as total from test_scores inner join tests on test_scores.test_id = tests.test_id inner join users on test_scores.user_id = users.id where tests.test_id in (248, 249) group by test_scores.user_id order by sum(test_scores.score) desc, users.name asc */ //Generating HTML in my SQL. LOLZ. $query = "select users.name as name, "; $sums = ""; $total = "sum(case tests.test_id "; /*if(isSet($$$))//add a select for the CanGo $sums .= ",GROUP_CONCAT( case tests.test_id when {$test_id} then CONCAT('<div style=\'height:4em;\' onmouseover=\"$(this).find(\'small\').css({display:\'inline\'});\"><b>',score,'</b> <small style=\"float:right;display:none;\">(z: ',round((score-{$avgs[$test_id]})/{$stddevs[$test_id]},3),')</small></div>') else '' end SEPARATOR '') as t{$test_id} ";*/ foreach ($test_ids as $test_id) { $sums .= ",GROUP_CONCAT(\n\t\t\t\tcase tests.test_id\n\t\t\t\twhen {$test_id} then CONCAT('<div style=\\'height:4em;\\' onmouseover=\"\$(this).find(\\'small\\').css({display:\\'inline\\'});\"><b>',score,'</b> <small style=\"float:right;display:none;\">(z: ',round((score-{$avgs[$test_id]})/{$stddevs[$test_id]},3),')</small></div>')\n\t\t\t\telse '' end SEPARATOR '') as t{$test_id} "; $total .= "when {$test_id} then (score-{$avgs[$test_id]})/{$stddevs[$test_id]} "; } $total .= "end ) as zsum,\n\t\t\tsum(case when score is null then 0 else 1 end) as count,\n\t\t\tsum(score) as sum"; //zsum is bonkers $query .= "{$total} {$sums} from test_scores\n\tinner join tests\n\t on test_scores.test_id = tests.test_id\n inner join users\n on test_scores.user_id = users.id\n where tests.test_id in %li\n\tgroup by test_scores.user_id"; foreach ($test_ids as $id) { $test_names['t' . $id] = $test_names[$id]; unset($test_names[$id]); $avgs['t' . $id] = round($avgs[$id], 3); unset($avgs[$id]); $stddevs['t' . $id] = round($stddevs[$id], 3); unset($stddevs[$id]); } //Sets each assoc array key from ID to tID, just like in the SQL above. $headers = array('name' => 'Name', 'total' => 'Total') + $test_names; //Assoc array with key test_id and value test name $headdata = array(); $headdata[] = array('name' => 'Average', 'total' => '') + $avgs; $headdata[] = array('name' => 'Pop Std Dev', 'total' => '') + $stddevs; $headdata[] = array(); $querydata = DB::query($query, $test_ids); //var_dump($querydata); foreach ($querydata as &$row) { //The extra addition creates a significant bias toward people who have taken more tests. $row['finalscore'] = $row['zsum'] / $row['count'] + $row['count'] / 2.5; $row['total'] = "score: <b>" . round($row['finalscore'], 3) . "</b>" . "<br>Σz: " . round($row['zsum'], 3) . "<br>Σx: " . $row['sum']; } unset($row); //php, why do I actually have to do this? usort($querydata, function ($r1, $r2) { if ($r1['finalscore'] != $r2['finalscore']) { //First in order of z-average return $r1['finalscore'] < $r2['finalscore'] ? 1 : -1; } else { //Then in random order. return mt_rand(-1000, 1000); } }); $data = array_merge($headdata, $querydata); $namestr = '<div><b>Tests:</b> ' . implode(', ', $test_names) . '</div><br>'; foreach ($querydata as $row) { $namestr .= $row['name'] . '<br>'; } echo <<<HEREDOC <h1>View Scores</h1> \t \t <h3>TL;DR</h3> \t <div>{$namestr}</div> \t <br> \t \t <h3>Stats</h3> <p>Z-scores are a very good way of standardizing scores. It is sorted by a (slightly modified) z-average, which is the average of the <i>available</i> z-scores only. The final sorted-by score has a bit of weight placed toward attending more tryouts. Ties, as always, are broken in random order.</p> \t <a href="Tests">< Back</a> HEREDOC . make_table('contrasting', $headers, $data); }
function lmt_send_coaches_email($subject, $bb_body) { $result = DB::query('SELECT name, email FROM coaches WHERE email != "" AND deleted="0"'); $list = DBHelper::verticalSlice($result, 'email', 'name'); return lmt_send_list_email($list, $subject, $bb_body, 'coaches'); }