Jump to content

Using PDO format in php


BosstonKUKU
Go to solution Solved by Jacques1,

Recommended Posts

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;

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by BosstonKUKU
Link to comment
Share on other sites

  • Solution

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   
;
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.