Using PDO format in php

[BosstonKUKU]

I have existing query which successfully output  a dynamic table using pivot. But the problem is i have no idea how to apply this in PDO format in php. Any help please.

 

 

 

SELECT GROUP_CONCAT(DISTINCT CONCAT('SUM(IF(DateTransact = ''',DateTransact,''', TransactionCount, NULL))' ))

                      

                    INTO @sql 

                    FROM dummy_transaction;

 

                    SET @sql = CONCAT('SELECT TransactionType, ', @sql, ' FROM dummy_transaction GROUP BY TransactionType  ');

 

                    PREPARE stmt FROM @sql;

                    EXECUTE stmt;

[Barand]

http://uk1.php.net/manual/en/class.pdo.php

[ginerjm]

Alright - I'll bite.

 

Pardon me if I am showing my ignorance here but just what is that text that you posted? Is it some form of sql that I have not seen. I don't venture out of my 'space' much (cause I don't have to) so it may be some variant used by whatever your current database is, but I have to ask in order to find out.

 

If it was meant to be a pseudo-code to help us understand what you wanted to do, all I can offer is this:

 

Read the chapter that Barand pointed you to. It will not only explain things but will give you many examples of how to create a query statement (using substitute parms for a "prepare query") as well as doing a prepare call and finally binding your data to the substitute parms. Then you do the execute and process the results much like you probably have done many times before.

 

PS - imho go for the :parm syntax for your substitute parms. Then you simply build an array of parms and values without having to worry about setting up "bound parameters" types.

 

$q = "select fielda, fieldb, fieldc from mytablename
where id = :idval";
$qst = $pdo->prepare($q);
$parms = array(
':idval'=>$id_from_user
);
if (!$qst->execute($parms))
.... handle error
else
... continue processing results

 

where $pdo is the handle you get after establishing your db connection.

[BosstonKUKU]

Sorry sir for not being more specific. What i am building now is a dynamic report in php using PDO format and i am using MySQL Multi Column cross Tabulation AKA pivot table. But the problem is how i can apply this query in PHP PDO format.

 

This is my current progress.

try{
    $handler = new PDO('mysql:host=localhost;dbname=DatabaseName','Username','Password');
    $handler ->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

 }catch(PDOException $e){
     die($e->getMessage());
}

 $query = $handler->query (

"Select TransactionType,TransactionCount From dummy_transaction"

 

);

    while($row = $query->fetch()){
       echo $row['TransactionType'], echo $row['TransactionCount'], '<br>';


}

But i don't have any idea on how to deal with this query and apply in PHP PDO format. Something like i am dealing in multi-query. Any advice sir well appreciated. And thank you for the reply. 

Edited February 26, 2017 by BosstonKUKU

[BosstonKUKU]

Sorry for miss arrangement i can't edit.    

[Jacques1]

The whole approach of building the table with dynamic query gymnastics is bad, so you shouldn't try to replicate this with PDO at all.

 

I know this is hard to understand, but SQL is not Excel. SQL is supposed to store and provide data, not render pretty tables. That's what your application is for.

 

So use your database to retrieve the data (i. e. the sum per date and type), and then write code to produce your pivot layout. So the query is simply

SELECT
    datetransact,
    transactiontype,
    SUM(transactioncount) AS transactions
FROM
    dummy_transaction
GROUP BY
    datetransact,
    transactiontype   
;