Execute mysql statement via mysql_query

Question

Please help me.

I have mysql query :

SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(mulai = ''',mulai,''', talenta, NULL)) AS ', QUOTE(mulai)) ORDER BY MULAI ASC ) INTO @sql FROM history_talenta; 
SET @sql = CONCAT('SELECT nip, nama, DATE_FORMAT(tgl_grade_terakhir,''%d-%m-%Y'') as tgl_grade_terakhir, ', @sql, ' FROM history_talenta GROUP BY nip'); 
PREPARE stmt FROM @sql; 
EXECUTE stmt;

I would like to run it from PHP mysql_query() but nothing appear but when I tried it via phpmyadmin it works and have returned value.

Thank you

Do not use `mysql_*` API it is deprecated. Use `mysqli_*` or `PDO` — Jens, Jul 20 '16 at 07:40
Your first query is putting info into `@sql`. Your second stmt is building upon that, using that var in the middle of the `concat()` . Not that you don't know that. Check out my answer [here](http://stackoverflow.com/a/32145480) under Revision1 for `mysqli_` showing a `heredoc` (or whatever you call those blocks) that safely allows vars to be be used in `mysqli_` . Also, look into buffer overflow problem by setting the size with a stmt like `SET SESSION group_concat_max_len = 10000;` or some value — Drew, Jul 20 '16 at 07:51

score 0 · Accepted Answer · answered Jul 20 '16 at 07:46

Split into 2 queries:

First query:

SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(mulai = ''',mulai,''', talenta, NULL)) AS ', QUOTE(mulai)) ORDER BY MULAI ASC ) FROM history_talenta;

Get result of this query from mysql_query into $sql1 variable ,then construct another SQL

$sql2 = "SELECT nip, nama, DATE_FORMAT(tgl_grade_terakhir,'%d-%m-%Y') as tgl_grade_terakhir, " . $sql1 . ' FROM history_talenta GROUP BY nip';

Then run mysql_query( $sql2 ) .

Note: mysql_query along with all mysql_* functions is deprecated , I suggest to use mysqli_ functions.

Execute mysql statement via mysql_query

1 Answers1