How can I convert mysql query (group_concat) in postgresql query

Question

I want to convert this bellow two MySQL query(specially second query) in PostgreSQL query.because I want to use this bellow query for PostgreSQL database.So how can i write this bellow query in PostgreSQL.So that this bellow query support PostgreSQL Database.

            <?php

            $data = [];
            $branchs = [];
           $query=mysqli_query($con,"SELECT DISTINCT branchcode FROM brackinfo2 WHERE event_id BETWEEN 71 AND 75");

           while($values=mysqli_fetch_array($query)){
            $branchs[] = $values['branchcode'];
           }

    $query=mysqli_query($con,"SELECT CONCAT(section,'.', sub_sec_id) AS si, GROUP_CONCAT (CONCAT(branchcode,':',question_point)) as brands, section, sub_sec_id, point as fullmark FROM brackinfo2 WHERE event_id BETWEEN 71 AND 75 GROUP BY branchcode,si");

            while($values=mysqli_fetch_array($query)){
                     $data[$values['si']]['fullmark'] = $values['fullmark'];
                     $data[$values['si']]['section'] = $values['section'];
                    $tmp = explode(",", $values['brands']);
                    foreach ($tmp as $key => $value) {
                        $tmp2 = explode(":", $value);
                        $data[$values['si']]['branchs'][$tmp2[0]] = $tmp2[1];
                    }
                }
                ?>
        <table>
           <thead>
            <tr>
            <th>SI</th>
            <th>Full Marks</th>
            <th>Section & Indicator Name</th>
                <?php 
                foreach ($branchs as $branch) {
                    echo "<th> Branch Name<br>($branch)</th>";
                }?>
            </tr>
            </thead>
            <tbody>
                <?php 
                foreach ($data as $si  => $info)
                {
                    echo "<tr>";
                    echo "<td>$si</td>";
                    echo "<td>{$info['fullmark']} </td>";
                    echo "<td>{$info['section']} </td>";
                    foreach( $branchs as $branch)
                    {
                        if (isset($info['branchs'][$branch])) {
                            echo "<td>{$info['branchs'][$branch]}</td>";
                        } else {
                            echo "<td></td>";
                        }

                    }
                    echo "</tr>";

                }

                ?>
            </tbody>
        </table>

Possible duplicate of [Postgresql GROUP\_CONCAT equivalent?](https://stackoverflow.com/questions/2560946/postgresql-group-concat-equivalent) — Chris White, May 18 '19 at 14:56

A l w a y s S u n n y · Answer 1 · 2019-05-18T08:29:20.510

0

You need to use PHP PDO to support both MYSQL and POSTGRESQL with same query(most of the case) OR use pg_connect(), pg_query() and pg_fetch_array() specifically for postgresql only. See here for more about PHP PDO query examples

Connection example in MYSQL:

<?php
try {
  $dbuser = 'mysql';
  $dbpass = 'abc123';
  $dbhost = 'localhost';
  $dbname='mysql';
  $dbh = new PDO('mysql:host=$dbhost;dbname=$dbname', $dbuser, $dbpass);
}catch (PDOException $e){
  echo "Error!: " . $e->getMessage() . "<br/>";
  die();
}
?>

Connection example in POSTGRESQL:

<?php
try{
  $dbuser = 'postgres';
  $dbpass = 'abc123';
  $dbhost = 'localhost';
  $dbname='postgres';
  $dbh = new PDO("pgsql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
 }catch (PDOException $e){
  echo "Error : " . $e->getMessage() . "<br/>";
  die();
 }
?>

edited May 18 '19 at 08:29

answered May 18 '19 at 08:23

A l w a y s S u n n y

36,497
8
60
103

Always Sunny.If I only use postgresql then how can i write query for postgresql for this above code. – mz4568 May 18 '19 at 10:54
Read my answer properly, I've mentioned about specifically about postgres way – A l w a y s S u n n y May 18 '19 at 12:22
I have read.you just told about connection.but I wanted to know,how can i modify this bellow query in PostgreSQL.if i use pg_query and pg_fetch_array then will this code work.because PostgreSQL do not support group_concat. see query please. $query=mysqli_query($con,"SELECT CONCAT(section,'.', sub_sec_id) AS si, GROUP_CONCAT (CONCAT(branchcode,':',question_point)) as brands, section, sub_sec_id, point as fullmark FROM brackinfo2 WHERE event_id BETWEEN 71 AND 75 GROUP BY branchcode,si"); – mz4568 May 18 '19 at 15:08

How can I convert mysql query (group_concat) in postgresql query

1 Answers1