MySQL joining tables and selecting dynamic rows as column

Question

I tried to use a prepare statement, but got 500 error.

I have 2 tables:

1.Sales Record (table_sales_record)

    +----------+------------+----------------+
    | sales_id | product_id | shipping_status|
    +----------+------------+----------------+
    |    s1    |    1010    |     shipping   |
    |    s1    |    1011    |     arrived    |
    |    s2    |    1012    |     arrived    |
    |    s2    |    1013    |     pending    |
    |    s1    |    1014    |     pending    |
    |    s2    |    1015    |     pending    |
    +----------+------------+----------------+

Sales Details (table_sales_details)

+------------+------------+
|  sales_id  |    name    | 
+------------+------------+
|    s1      |   Sales A  |
|    s2      |   Sales B  |
|    s3      |   Sales C  |
+------------+------------+

What I need is

   +----------+------------+----------+---------+---------+----------+---------+
   | sales_id |    name    |   1015   |  1014   |  1013   |   1012   |  1011   |
   |    s1    |   Sales A  |   NULL   | pending |   NULL  |   NULL   | arrived |
   |    s2    |   Sales B  | pending  |   NULL  | pending |  arrived |   NULL  |
   +----------+------------+----------+---------+---------+----------+---------+

The below SQL is what I have tried:

SELECT CONCAT(
  'SELECT `table_sales_record`.sales_id', 
GROUP_CONCAT('
 , `t_', REPLACE(product_id, '`', '``'), '`.`shipping_status`
     AS `', REPLACE(product_id, '`', '``'), '`'
 SEPARATOR ''),
' FROM `table_sales_record` ', GROUP_CONCAT('
 LEFT JOIN `table_sales_record`   AS `t_', 
REPLACE(product_id, '`', '``'), '`
        ON `table_sales_record`.sales_id = `t_', 
REPLACE(sales_id, '`', '``'), '`.sales_id
       AND `t_', REPLACE(sales_id, '`', '``'), '`.sales_id = ', 
QUOTE(sales_id)
 SEPARATOR ''),
' GROUP BY `table_sales_record`.sales_id'
) INTO @qry FROM (SELECT distinct sales_id FROM 
`table_sales_record`) t;

PREPARE stmt FROM @qry;
EXECUTE stmt;

I can just finish some part and not yet join table_sales_details, but I get the 500 error when execute this SQL query.

Do I follow the right direction? Can anyone help, please?

*"but return 500 error"* - Check your php logs, turn on error reporting, etc. You need to get the actual error message before you can correct it. — David, Sep 10 '19 at 16:23
Possible duplicate of [How can I get useful error messages in PHP?](https://stackoverflow.com/questions/845021/how-can-i-get-useful-error-messages-in-php) — David, Sep 10 '19 at 16:23
I just running the sql statement in phpmyadmin but not the PHP — joker, Sep 10 '19 at 16:25
Can you clarify how *"I tried to use prepare statement"* is being applied in phpmyadmin? How are you using a prepared statement there? — David, Sep 10 '19 at 16:26
um.... It can't be running the above prepared statement ? may be that's the reason why I got the 500 error .... — joker, Sep 10 '19 at 16:29
But I have no idea how to get the above result neither..... Can help in creating the SQL statement? — joker, Sep 10 '19 at 16:30
I could just be unfamiliar with the terminology in this case. Perhaps phpmyadmin has error logs? Either way, "a 500 error" just means that something failed on the server. It does not itself tell you what failed or why. — David, Sep 10 '19 at 16:31
This code has to be inside a procedure, you can't run it as an ordinary query. — Barmar, Sep 10 '19 at 16:32
It reports Error Text: Internal Server Error. However, it works fine when I run a simple select statement. Therefore it could not be related to the server? — joker, Sep 10 '19 at 16:33
@joker No, I mean it need to be in a MySQL stored procedure. — Barmar, Sep 10 '19 at 16:44
Alternatively, consider handling isssued of data display in application code. — Strawberry, Sep 10 '19 at 17:07

MySQL joining tables and selecting dynamic rows as column

0 Answers0