<?php
require __DIR__ . '/vendor/autoload.php';
require __DIR__ . '/config/config.php';
$dbConn = new \Simplon\Mysql\Mysql($config['db']['host'], $config['db']['user'], $config['db']['password'], $config['db']['database']);
$result = ['gender' => [], 'age' => [], 'top5_interest' => []];
$gender_distribution = $dbConn->fetchRowMany('SELECT sex, COUNT(*) as count FROM members GROUP BY sex ORDER BY count DESC;');
$gender_id_to_string = [1 => 'female', 2 => 'male', 0 => '?'];
foreach ($gender_distribution as $gender) {
$result['gender'][$gender_id_to_string[$gender['sex']]] = $gender['count'];
}
$date_format = 'Y-m-d';
$column_name = '`birth_date`';
$age_dates = ['<=10' => "{column} >= '" . date($date_format, strtotime("-10 years", time())) . "'", '11-20' => "{column} >= '" . date($date_format, strtotime("-20 years", time())) . "' AND {column} < '" . date($date_format, strtotime("-10 years", time())) . "'", '21-30' => "{column} >= '" . date($date_format, strtotime("-30 years", time())) . "' AND {column} < '" . date($date_format, strtotime("-20 years", time())) . "'", '>31' => "{column} < '" . date($date_format, strtotime("-30 years", time())) . "'", '?' => "{column} IS NULL"];
$total = 0;
foreach ($age_dates as $name => $condition) {
$query = str_ireplace('{column}', $column_name, "SELECT COUNT(*) as count FROM `members` WHERE " . $condition);
$age_distribution = $dbConn->fetchRow($query);
$result['age'][$name] = $age_distribution['count'];
}
$interests_distribution = $dbConn->fetchRowMany('SELECT interest, COUNT( * ) AS count FROM interests GROUP BY interest ORDER BY count DESC LIMIT 0 , 6');
$interests = [];
foreach ($interests_distribution as $interest) {
if ($interest['interest'] == 'music') {
continue;
}
$interests[] = $interest['interest'];
}
$result['top5_interest'] = $interests;
print_r(json_encode($result, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT));
echo '<h4>total rows: ' . $dbh->getRowCount() . '</h4>';
var_dump($results);
// ############################################
echo '<h3>fetchValueManyCursor</h3>';
$counter = 0;
foreach ($dbh->fetchColumnManyCursor($query, $conds) as $result) {
echo '<h4>#' . ++$counter . ' cursor</h4>';
var_dump($result);
}
// ############################################
echo '<h3>fetch</h3>';
$results = $dbh->fetchRow($query, $conds);
var_dump($results);
// ############################################
echo '<h3>fetchMany</h3>';
$results = $dbh->fetchRowMany($query, $conds);
var_dump($results);
// ############################################
echo '<h3>fetchManyCursor</h3>';
$counter = 0;
foreach ($dbh->fetchRowManyCursor($query, $conds) as $result) {
echo '<h4>#' . ++$counter . ' cursor</h4>';
var_dump($result);
}
// ############################################
echo '<h3>execute sql: truncate</h3>';
$response = $dbh->executeSql('TRUNCATE import_dump');
var_dump($response);
// ############################################
echo '<h3>insert</h3>';
echo '<h4>with ID response</h4>';
<?php require __DIR__ . '/../vendor/autoload.php'; $config = ['server' => 'localhost', 'username' => 'rootuser', 'password' => 'rootuser', 'database' => 'beatguide_devel_service']; $dbh = new \Simplon\Mysql\Mysql($config['server'], $config['username'], $config['password'], $config['database']); // ############################################ echo "<h1>IN with integers</h1>"; $conds = ['ids' => [1, 2, 3, 4, 5]]; $query = 'SELECT id, email FROM users WHERE id IN (:ids)'; var_dump($dbh->fetchRowMany($query, $conds)); // ############################################ echo "<h1>IN with strings</h1>"; $conds = ['emails' => ['*****@*****.**', '*****@*****.**']]; $query = 'SELECT id, email FROM users WHERE email IN (:emails)'; var_dump($dbh->fetchRowMany($query, $conds));
$birth_date = count($bdateParts) == 3 ? implode('-', array_reverse($bdateParts)) : null;
}
$putMemeber = ['vk_id' => $member['id'], 'sex' => $member['sex'], 'birth_date' => $birth_date];
try {
$dbConn->insert('members', $putMemeber);
} catch (Exception $e) {
}
}
}
$dbConn->executeSql('TRUNCATE interests;');
$request_count = 200;
$members_count = $dbConn->fetchRow('SELECT count(*) as c from members;')['c'];
$iterations_count = $members_count / $request_count;
for ($i = 0; $i < $iterations_count; $i++) {
echo 'Fetching users ' . $request_count * $i . ' - ' . ($request_count * $i + $request_count) . PHP_EOL;
$members = $dbConn->fetchRowMany('SELECT vk_id from members ORDER by vk_id LIMIT :skip, :limit;', ['skip' => $request_count * $i, 'limit' => $request_count]);
$vk_ids = [];
foreach ($members as $member) {
$vk_ids[] = $member['vk_id'];
}
$vk_ids_string = implode(',', $vk_ids);
$users = $vk->api('users.get', ['user_ids' => $vk_ids_string, 'fields' => 'interests']);
if (!count($users)) {
echo 'error 1 ';
var_dump($users);
var_dump($vk_ids_string);
exit;
}
foreach ($users as $user) {
if (!isset($user['interests'])) {
continue;
