0

I am using mysql database with tool adminar and I want to convert data from one table to column and get it in json object. I have the table in following format-

----------------------------------------------------
| prn | finding_field | finding_value | finding_no |
----------------------------------------------------
|  3  | Temperature   |   100         |    1       |    
|  3  | Fasting       |   99          |    1       |    
|  3  | Random        |   120         |    1       |     
|  6  | Temperature   |   98          |    2       | 
|  6  | cholesterol   |   200         |    2       |
----------------------------------------------------

and my expected output is as follows-

-------------------------------------------------------------
| finding_no | Temperature | Fasting | Random | cholesterol |
-------------------------------------------------------------
|    1       |    100      |  99     |  120   |   NULL      |
|    2       |    98       |  NULL   |  NULL  |   200       |
-------------------------------------------------------------

I have written the query as follows-

  SET @sql = NULL;
        SELECT
          GROUP_CONCAT(DISTINCT
            CONCAT(
              'GROUP_CONCAT(IF(finding_field = ''',
              finding_field,
              ''',finding_value, NULL)) AS ',
              finding_field
            )
          ) INTO @sql;
        SET @sql = CONCAT('SELECT finding_no                  
                            , ', @sql, ' 
                           FROM cpc_specific_finding 
                           where prn=3 
                           GROUP BY finding_no');

        PREPARE stmt FROM @sql ;
        EXECUTE stmt ;
        DEALLOCATE PREPARE stmt";

This Query working in adminar but giving error in php.

Is there any way to write query without using prepare statement?

Neha Vaidya
  • 79
  • 1
  • 7
  • The `mysql_` database extension will not run multiple queries – RiggsFolly May 30 '17 at 12:41
  • 2
    Every time you use [the `mysql_`](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) database extension in new code **[this happens](https://media.giphy.com/media/kg9t6wEQKV7u8/giphy.gif)** it is deprecated and has been for years and is gone for ever in PHP7. If you are just learning PHP, spend your energies learning the `PDO` or `mysqli` database extensions and prepared statements. [Start here](http://php.net/manual/en/book.pdo.php) – RiggsFolly May 30 '17 at 12:42
  • Yes I read that. But what is alternative for that? Or how the query can be modified in better way? – Neha Vaidya May 30 '17 at 12:46
  • Look up [`mysqli_multi_query`](http://php.net/manual/en/mysqli.multi-query.php) in the manual – RiggsFolly May 30 '17 at 12:47
  • I tried with mysqli_multi_query() too but still remain error to mysql_fetch_assoc() – Neha Vaidya May 30 '17 at 12:53
  • Did you change the `connection.php` code to connect using `mysqli` instead of `mysql` ?? – RiggsFolly May 30 '17 at 12:55
  • yes I used mysql_connect and mysql_select_db functions which I changed to mysqli – Neha Vaidya May 30 '17 at 13:25

0 Answers0