<?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;