Set group_concat_max_len and Select through PHP PDO

Hi experts,

I have the following PHP code
    static public function get_locations_for_reserv_details(){
        
         $query = 'SET SESSION group_concat_max_len=3423543543; '
                . 'SELECT locs.id, name, default_time, default_vehicle, '
                     . 'GROUP_CONCAT(DISTINCT assoc.agent_id '
                            . 'ORDER BY agents.fName, agents.lName) AS agent_ids '
                . 'FROM locations AS locs '
                    . 'INNER JOIN location_agent_assoc AS assoc '
                        . 'ON locs.id = assoc.location_id '
                    . 'INNER JOIN agents '
                        . 'ON assoc.agent_id = agents.id '    
                . 'WHERE locs.archived = 0 AND agents.archived = 0 '
                . 'GROUP BY locs.id, name, default_time, default_vehicle '
                . 'ORDER BY locs.name';
         
        $db = Database::getDB();
        
        echo '<br>' . $query . '<br>';
        
        $statement = $db->prepare($query);
        $statement->execute();
        $result = $statement->fetchAll();
        $statement->closeCursor();
        
        return $result;
        
    }

Open in new window


However I keep getting
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error' in C:\inetpub\wwwroot\CSS_Reservations\addedit\locations\locations.php:112 Stack trace: #0 C:\inetpub\wwwroot\CSS_Reservations\addedit\locations\locations.php(112): PDOStatement->fetchAll() #1 C:\inetpub\wwwroot\CSS_Reservations\reservations\reservations.php(834): Locations::get_locations_for_reserv_details() #2 C:\inetpub\wwwroot\CSS_Reservations\reservations\reservations.php(667): Reservations::get_booking_dependancies(Array, Array, Array, NULL, NULL, NULL, NULL, NULL, 'CSST') #3 C:\inetpub\wwwroot\CSS_Reservations\reservations\index.php(47): Reservations::display_booking_details(Array, 'existing') #4 {main} thrown in C:\inetpub\wwwroot\CSS_Reservations\addedit\locations\locations.php on line 112

Open in new window


But my $query string contains the following
SET SESSION group_concat_max_len=3423543543; SELECT locs.id, name, default_time, default_vehicle, GROUP_CONCAT(DISTINCT assoc.agent_id ORDER BY agents.fName, agents.lName) AS agent_ids FROM locations AS locs INNER JOIN location_agent_assoc AS assoc ON locs.id = assoc.location_id INNER JOIN agents ON assoc.agent_id = agents.id WHERE locs.archived = 0 AND agents.archived = 0 GROUP BY locs.id, name, default_time, default_vehicle ORDER BY locs.name

Open in new window


and when I run this under phpMyAdmin under the same user, it orks.  So why doesn't this work in PHP?
APD TorontoSoftware DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
Suggest you use try / catch and visualization of the exception to see what is going wrong.  The try / catch examples are given in this article.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
hieloCommented:
You are probably connecting to the server using a syntax similar to:
$dbh = new PDO($dsn, $user, $password);

Instead of three parameters, try using the fourth parameter and execute the SET command:
$dbh = new PDO($dsn, $user, $password, Array(PDO::MYSQL_ATTR_INIT_COMMAND =>'SET SESSION group_concat_max_len=3423543543') );

You will of course need to modify $query so that it includes only the SELECT statement.

FYI: The options you can pass as the fourth parameter are specific to the db driver you are using.  Since you are using mysql, you'll need to look at:
http://php.net/manual/en/ref.pdo-mysql.php

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.