Raw Expression in simple php and mysql

hey guys,

i wirte this code in laravel before :


DB::table('survey')
                ->where('date_time', '>=' , $from[$i] )
        ->where('date_time' , '<=' , $to[$i] )
                ->groupBy('survey_location')
                ->select( 'survey_location'   ,
         DB::raw('SUM(survey_value) as total_survey , 
        AVG(survey_value) as average_survey , 
COUNT(agent_number) as count_survey' ))
                ->get();

but now i want to write it in simple php and mysql , can anybody help me?

D9705996

7 years ago

Level 51

You can use DB::connection()->enableQueryLog(); then execute your query builder version in you post before running DB::getQueryLog(); to get the raw SQL (you could co this in php artisan tinker).

You can then use pdo to run your query (change the settings to match you environment) e.g.

$host = '127.0.0.1';
$db   = 'test';
$user = 'root';
$pass = '';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
try {
     $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
     throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

$stmt = $pdo->query('YOUR SQL QUERY');
while ($row = $stmt->fetch())
{
    // do something
}

1 like

Please or to participate in this conversation.