问:
Pivot table columns to rows and use a value in one table if it does not exists in another
I'd like to create a report from tables that don't have an ideal layout. I can create pivot tables in excel but I want to create the reports online for a web based dashboard.  I've looked at
http://datacharmer.org/downloads/pivot_tables_mysql_5.pdf for pivot tables and Select from table if record found in another table for determining if a value exists but I cannot get the desired results. I was able to create a pivot table with a temp table in mysql but the reporting software would not allow data manipulation.

Some answers (qid 742) are in the survey_survey_144477 table but others are in the answers table. So I tried to use the value from survey_survey_144477 if the qid did nto exist in survey_answers.

This is only for one specific report. The survey results are in table survey_survey_144477 and only have a few questions but other surveys may have 20 or 30 questions.  So, I'd liek to make this dynmaic so I won't have to change all of the column heading for every report.  I also looked for parsing the column because part of the columns in survey_survey_144477 have the qid but no luck.  I also treied to determine how I could select all of the colums but disregard some such as token, last page, etc. because I thought it would be helpful if I made it dynamic but also no luck.

I'd appreciate any help you can offer.

The desired output would be:

```<code>| How would you rate ... | Do you think our product ... | One a scale of 1-10 ... | Will you recommend |
+-----------------+------+------------------------------+-------------------------+--------------------+
| Excellent              | Yes                          | 7                       | Yes                |
| Very good              | No                           | 8                       | Yes                |
</code>```

I have tried:

```<code>## Create pivot table
CREATE TEMPORARY TABLE temp
SELECT id,'144477X148X740' AS lid, 740 AS qid, 144477X148X740 AS value FROM survey_survey_144477
UNION ALL
SELECT id, '144477X148X741' AS lid, 741 AS qid, 144477X148X741 AS value FROM survey_survey_144477
UNION ALL
SELECT id, '144477X148X742SQ001' AS lid, 742 AS qid, 144477X148X742SQ001 AS value FROM survey_survey_144477
UNION ALL
SELECT id, '144477X148X745' AS lid, 745 AS qid, 144477X148X745 AS value FROM survey_survey_144477;

## Get the answers.  Table survey_answers has answers from dropdowns and selection boxes.
## Other answers are in the temp table.
select T.id, SQ.question, SA.answer
   from survey_questions SQ
   join survey_answers SA on SA.qid = SQ.qid
   join temp T on T.qid = SQ.qid
   where SQ.sid = 144477
   --   and exists (select SA.answer as answer
   --                      from survey_answers SA
   --                     join survey_questions SQ on SA.qid = SQ.qid
   --                     join temp T on T.qid = SQ.qid)

CREATE TEMPORARY TABLE temp2
select id, qid, value
   from temp
   where not exists (select qid from survey_answers
                           where temp.qid = survey_answers.qid);

    describe survey_survey_144477;
    +---------------------+----------------+------+-----+---------+----------------+
    | Field               | Type           | Null | Key | Default | Extra          |
    +---------------------+----------------+------+-----+---------+----------------+
    | id                  | int(11)        | NO   | PRI | NULL    | auto_increment |
    | token               | varchar(35)    | YES  |     | NULL    |                |
    | submitdate          | datetime       | YES  |     | NULL    |                |
    | lastpage            | int(11)        | YES  |     | NULL    |                |
    | startlanguage       | varchar(20)    | NO   |     | NULL    |                |
    | ipaddr              | text           | YES  |     | NULL    |                |
    | refurl              | text           | YES  |     | NULL    |                |
    | 144477X148X740      | varchar(5)     | YES  |     | NULL    |                |
    | 144477X148X741      | varchar(5)     | YES  |     | NULL    |                |
    | 144477X148X742SQ001 | decimal(30,10) | YES  |     | NULL    |                |
    | 144477X148X745      | varchar(5)     | YES  |     | NULL    |                |
    | 144477X148X748      | text           | YES  |     | NULL    |                |
    +---------------------+----------------+------+-----+---------+----------------+

    describe survey_questions ;
    +----------------+--------------+------+-----+---------+----------------+
    | Field          | Type         | Null | Key | Default | Extra          |
    +----------------+--------------+------+-----+---------+----------------+
    | qid            | int(11)      | NO   | PRI | NULL    | auto_increment |
    | parent_qid     | int(11)      | NO   | MUL | 0       |                |
    | sid            | int(11)      | NO   | MUL | 0       |                |
    | gid            | int(11)      | NO   | MUL | 0       |                |
    | type           | varchar(1)   | NO   | MUL | T       |                |
    | title          | varchar(20)  | NO   |     |         |                |
    | question       | mediumtext   | NO   |     | NULL    |                |
    | preg           | mediumtext   | YES  |     | NULL    |                |
    | help           | mediumtext   | YES  |     | NULL    |                |
    | other          | varchar(1)   | NO   |     | N       |                |
    | mandatory      | varchar(1)   | YES  |     | NULL    |                |
    | question_order | int(11)      | NO   |     | NULL    |                |
    | language       | varchar(20)  | NO   | PRI | en      |                |
    | scale_id       | int(11)      | NO   |     | 0       |                |
    | same_default   | int(11)      | NO   |     | 0       |                |
    | relevance      | mediumtext   | YES  |     | NULL    |                |
    | modulename     | varchar(255) | YES  |     | NULL    |                |
    +----------------+--------------+------+-----+---------+----------------+

    describe survey_answers ;
    +------------------+-------------+------+-----+---------+-------+
    | Field            | Type        | Null | Key | Default | Extra |
    +------------------+-------------+------+-----+---------+-------+
    | qid              | int(11)     | NO   | PRI | 0       |       |
    | code             | varchar(5)  | NO   | PRI |         |       |
    | answer           | mediumtext  | NO   |     | NULL    |       |
    | sortorder        | int(11)     | NO   | MUL | NULL    |       |
    | assessment_value | int(11)     | NO   |     | 0       |       |
    | language         | varchar(20) | NO   | PRI | en      |       |
    | scale_id         | int(11)     | NO   | PRI | 0       |       |
    +------------------+-------------+------+-----+---------+-------+

    select id, 144477X148X740, 144477X148X741, 144477X148X742SQ001, 144477X148X745, 144477X148X748 from survey_survey_144477 limit 10;
    +----+----------------+----------------+---------------------+----------------+----------------+
    | id | 144477X148X740 | 144477X148X741 | 144477X148X742SQ001 | 144477X148X745 | 144477X148X748 |
    +----+----------------+----------------+---------------------+----------------+----------------+
    |  1 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    |  2 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    |  3 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    |  4 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    |  5 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    |  6 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    |  7 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    |  8 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    |  9 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    | 10 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    +----+----------------+----------------+---------------------+----------------+----------------+

select qid, sid, question from survey_questions where sid = 144477;
+-----+--------+-------------------------------------------------------------------------------------------+
| qid | sid    | question                                                                                |
+-----+--------+-------------------------------------------------------------------------------------------+
| 748 | 144477 | Please enter your email address or phone number if you would like someone to contact you. |
| 740 | 144477 | How would you rate our product's quality?                                                 |
| 741 | 144477 | Do you think our product helps your business?                                             |
| 742 | 144477 | One a scale of 1-10, how would you rate the value of our product?                         |
| 743 | 144477 |                                                                                |
| 745 | 144477 | Will you recommend our product?                                                           |
+-----+--------+-------------------------------------------------------------------------------------------+

select * from survey_answers where qid between 740 and 745;
+-----+------+-----------+-----------+------------------+----------+----------+
| qid | code | answer    | sortorder | assessment_value | language | scale_id |
+-----+------+-----------+-----------+------------------+----------+----------+
| 740 | 1    | Excellent |         1 |                1 | en       |        0 |
| 740 | 2    | Very good |         2 |                1 | en       |        0 |
| 740 | 3    | Good      |         3 |                1 | en       |        0 |
| 740 | 4    | Fair      |         4 |                1 | en       |        0 |
| 740 | 5    | Poor      |         5 |                1 | en       |        0 |
| 741 | A1   | Yes       |         1 |                0 | en       |        0 |
| 741 | A2   | No        |         2 |                0 | en       |        0 |
| 745 | A1   | Yes       |         1 |                0 | en       |        0 |
| 745 | A2   | No        |         2 |                0 | en       |        0 |
+-----+------+-----------+-----------+------------------+----------+----------+
</code>```

Update:

I created a table to map values from one table to another. Automating this will be a challenge that I will mention later.

```<code>CREATE TABLE `survey_answers_lookup` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sid` int(11) NOT NULL DEFAULT '0',
  `qid` int(11) NOT NULL DEFAULT '0',
  `survey_table_row_id` int(11) NOT NULL DEFAULT '0' COMMENT 'id that is in the survey_<id> table',
  `answer` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
VALUES (144477,740,1,1);

INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
VALUES (144477,741,1,'A1');

INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
VALUES (144477,742,1,'5.0000000000');

INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
VALUES (144477,745,1,'A1');

INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
VALUES (144477,748,1,'1@email.com');

INSERT INTO survey_answers_lookup(sid,qid,survey_table_row_id,answer)
VALUES (144477,740,2,'2');

INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
VALUES (144477,741,2,'A2');

INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
VALUES (144477,742,2,'8.0000000000');

INSERT INTO survey_answers_lookup(sid,qid,survey_table_row_id,answer)
VALUES (144477,745,2,'A2');

INSERT INTO survey_answers_lookup(sid,qid,survey_table_row_id,answer)
VALUES (144477,748,2,'2@email.com');

INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
VALUES (144477,740,3,'5');

INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
VALUES (144477,741,3,'A2');

INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
VALUES (144477,742,3,'10.0000000000');

INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
VALUES (144477,745,3,'A2');

INSERT INTO survey_answers_lookup(sid,qid,survey_table_row_id,answer)
VALUES (144477,748,3,'3@email.com');
</code>```

Which results in

```<code>select * from survey_answers_lookup;

# id, sid, qid, survey_table_row_id, answer
'1', '144477', '740', '1', '1'
'2', '144477', '741', '1', 'A1'
'3', '144477', '742', '1', '5.0000000000'
'4', '144477', '745', '1', 'A1'
'5', '144477', '748', '1', '1@email.com'
'6', '144477', '740', '2', '2'
'7', '144477', '741', '2', 'A2'
'8', '144477', '742', '2', '8.0000000000'
'9', '144477', '745', '2', 'A2'
'10', '144477', '748', '2', '2@email.com'
'11', '144477', '740', '3', '5'
'12', '144477', '741', '3', 'A2'
'13', '144477', '742', '3', '10.0000000000'
'14', '144477', '745', '3', 'A2'
'15', '144477', '748', '3', '3@email.com'
</code>```

Attempts to get the questions and answers.

```<code> -- Gives all of the rows from the answers_table even if that is not in the
 -- survey_answers_lookup table because of the left join.  740 should only be printed three times.
    select
        --SQ.qid,
        SA.code,
        SQ.question,
        SQ.type,
        -- Types can be: ! * 1 E F K L M N Q R S T X Y
        if (SQ.type IN ('B','M','L'), SA.answer, LU2.answer) as 'answer'
        from survey_questions SQ
           left join survey_answers SA on SA.qid = SQ.qid
              -- and SQ.type IN ('B','M','L')
           left join survey_answers_lookup LU on LU.qid = SQ.qid
               and LU.answer = SA.code
              -- and SQ.type IN ('B','M','L')
           left join survey_answers_lookup LU2 on LU2.qid = SQ.qid
                and SQ.title = 'Q7'
      where SQ.sid = 144477;

# --SQ.qid, code, question, type, answer
'740', '1', 'How would you rate our product\'s quality?', 'L', 'Excellent'
'740', '2', 'How would you rate our product\'s quality?', 'L', 'Very good'
'740', '5', 'How would you rate our product\'s quality?', 'L', 'Poor'
'740', '3', 'How would you rate our product\'s quality?', 'L', 'Good'
'740', '4', 'How would you rate our product\'s quality?', 'L', 'Fair'
'741', 'A1', 'Do you think our product helps your business?', 'L', 'Yes'
'741', 'A2', 'Do you think our product helps your business?', 'L', 'No'
'741', 'A2', 'Do you think our product helps your business?', 'L', 'No'
'742', NULL, 'One a scale of 1-10, how would you rate the value of our product?', 'K', NULL
'745', 'A1', 'Will you recommend our product?', 'L', 'Yes'
'745', 'A2', 'Will you recommend our product?', 'L', 'No'
'745', 'A2', 'Will you recommend our product?', 'L', 'No'
'748', NULL, 'Please enter your email address or phone number if you would like someone to contact you.', 'S', '1@email.com'
'748', NULL, 'Please enter your email address or phone number if you would like someone to contact you.', 'S', '2@email.com'
'748', NULL, 'Please enter your email address or phone number if you would like someone to contact you.', 'S', '3@email.com'
</code>```

-- Returns more than one row

```<code>  select
    SQ.qid,
    SQ.question,
    SQ.type,
    -- Types can be: ! * 1 E F K L M N Q R S T X Y
    if (SQ.type IN ('B','M','L'),
        (select SA.answer
           from survey_answers SA
              join survey_questions SQ2 on SQ2.qid = SA.qid
              join survey_answers_lookup LU on LU.qid = SQ2.qid
                 and LU.answer = SA.code),
          (select LU2.answer
              from survey_answers_lookup LU2
              join survey_questions SQ3 on SQ3.qid = LU2.qid)
     ) as answer
      from survey_questions SQ
      where SQ.sid = 144477;
</code>```

This works if the types are 'B','M','L'.

```<code>   select
    -- qid,
    -- SA.code,
    question,
    -- Types can be: ! * 1 E F K L M N Q R S T X Y
    if (type IN ('B','M','L'),
        (select answer
           from survey_answers
              where survey_questions.qid = survey_answers.qid
              and survey_answers_lookup.qid = survey_questions.qid
              and survey_answers_lookup.answer = survey_answers.code
         ),
         (0 -- select answer
            --  from survey_answers_lookup
             --  where survey_questions.qid = survey_answers_lookup.qid
          )
     ) as answer
      from survey_questions
      join survey_answers_lookup on survey_answers_lookup.qid = survey_questions.qid
      where survey_questions.sid = 144477;

'Do you think our product helps your business?', 'Yes'
'Do you think our product helps your business?', 'No'
'Do you think our product helps your business?', 'No'
'How would you rate our product\'s quality?', 'Excellent'
'How would you rate our product\'s quality?', 'Very good'
'How would you rate our product\'s quality?', 'Poor'
'One a scale of 1-10, how would you rate the value of our product?', '0'
'One a scale of 1-10, how would you rate the value of our product?', '0'
'One a scale of 1-10, how would you rate the value of our product?', '0'
'Please enter your email address or phone number if you would like someone to contact you.', '0'
'Please enter your email address or phone number if you would like someone to contact you.', '0'
'Please enter your email address or phone number if you would like someone to contact you.', '0'
'Will you recommend our product?', 'Yes'
'Will you recommend our product?', 'No'
'Will you recommend our product?', 'No'
</code>```

-- Error: Subquery returns more than one row.

```<code>  select
-- qid,
-- SA.code,
question,
-- Types can be: ! * 1 E F K L M N Q R S T X Y
if (type IN ('B','M','L'),
    (select answer
       from survey_answers
          where survey_questions.qid = survey_answers.qid
          and survey_answers_lookup.qid = survey_questions.qid
          and survey_answers_lookup.answer = survey_answers.code
     ),
     (select answer
         from survey_answers_lookup
          join survey_questions on survey_questions.qid = survey_answers_lookup.qid
              where not exists (select * -- if the value is not in survey_answers table (not exists)
                                 from survey_answers
                                    where survey_answers.qid = survey_answers_lookup.qid
                                       LIMIT 1
                              )
      )
 ) as answer
  from survey_questions
  join survey_answers_lookup on survey_answers_lookup.qid = survey_questions.qid
  where survey_questions.sid = 144477;

-- ----------------
-- ----------------

      select
      -- survey_answers_lookup.survey_table_row_id,
    -- qid,
    -- SA.code,
    question,
    -- Types can be: ! * 1 E F K L M N Q R S T X Y
    if (type IN ('B','M','L'),
        (select answer
           from survey_answers
              where survey_questions.qid = survey_answers.qid
              and survey_answers_lookup.qid = survey_questions.qid
              and survey_answers_lookup.answer = survey_answers.code
         ),
         (select answer
             from survey_answers_lookup
                 where qid not in (select qid
                                     from survey_answers
                                  )
          )
     ) as answer
      from survey_questions
      join survey_answers_lookup on survey_answers_lookup.qid = survey_questions.qid
      where survey_questions.sid = 144477;
</code>```

The idea is to get the answers from table survey_answers if the types are 'B','M','L' and to get the answers from table survey_answers_lookup if they are another type.  The qid is not in table survey_answers if the answers is not in that table. Table survey_answers_lookup has three answers for qid's 742 and 748 and the query does not know which value to get.  I guess I need to add another value to join it. Thanks

```<code>select answer, qid
             from survey_answers_lookup
                  where qid not in (select qid from survey_answers)

# answer, qid
'5.0000000000', '742'
'1@email.com', '748'
'8.0000000000', '742'
'2@email.com', '748'
'10.0000000000', '742'
'3@email.com', '748'
</code>```

It might be messy but maybe I can add a counter and then compare the counter to the id in table survey_answers_lookup?

```<code>select answer
             from survey_answers_lookup
                  where qid not in (select qid from survey_answers)
                  and id = @counter
</code>```

I think I got it to work.

```<code>## Create pivot table
CREATE TEMPORARY TABLE temp
SELECT id,'144477X148X740' AS lid, 740 AS qid, 144477X148X740 AS value FROM survey_survey_144477
UNION ALL
SELECT id, '144477X148X741' AS lid, 741 AS qid, 144477X148X741 AS value FROM survey_survey_144477
UNION ALL
SELECT id, '144477X148X742SQ001' AS lid, 742 AS qid, 144477X148X742SQ001 AS value FROM survey_survey_144477
UNION ALL
SELECT id, '144477X148X745' AS lid, 745 AS qid, 144477X148X745 AS value FROM survey_survey_144477;

## Get the answers.  Table survey_answers has answers from dropdowns and selection boxes.
## Other answers are in the temp table.
select T.id, SQ.question, SA.answer
   from survey_questions SQ
   join survey_answers SA on SA.qid = SQ.qid
   join temp T on T.qid = SQ.qid
   where SQ.sid = 144477
   --   and exists (select SA.answer as answer
   --                      from survey_answers SA
   --                     join survey_questions SQ on SA.qid = SQ.qid
   --                     join temp T on T.qid = SQ.qid)

CREATE TEMPORARY TABLE temp2
select id, qid, value
   from temp
   where not exists (select qid from survey_answers
                           where temp.qid = survey_answers.qid);

    describe survey_survey_144477;
    +---------------------+----------------+------+-----+---------+----------------+
    | Field               | Type           | Null | Key | Default | Extra          |
    +---------------------+----------------+------+-----+---------+----------------+
    | id                  | int(11)        | NO   | PRI | NULL    | auto_increment |
    | token               | varchar(35)    | YES  |     | NULL    |                |
    | submitdate          | datetime       | YES  |     | NULL    |                |
    | lastpage            | int(11)        | YES  |     | NULL    |                |
    | startlanguage       | varchar(20)    | NO   |     | NULL    |                |
    | ipaddr              | text           | YES  |     | NULL    |                |
    | refurl              | text           | YES  |     | NULL    |                |
    | 144477X148X740      | varchar(5)     | YES  |     | NULL    |                |
    | 144477X148X741      | varchar(5)     | YES  |     | NULL    |                |
    | 144477X148X742SQ001 | decimal(30,10) | YES  |     | NULL    |                |
    | 144477X148X745      | varchar(5)     | YES  |     | NULL    |                |
    | 144477X148X748      | text           | YES  |     | NULL    |                |
    +---------------------+----------------+------+-----+---------+----------------+

    describe survey_questions ;
    +----------------+--------------+------+-----+---------+----------------+
    | Field          | Type         | Null | Key | Default | Extra          |
    +----------------+--------------+------+-----+---------+----------------+
    | qid            | int(11)      | NO   | PRI | NULL    | auto_increment |
    | parent_qid     | int(11)      | NO   | MUL | 0       |                |
    | sid            | int(11)      | NO   | MUL | 0       |                |
    | gid            | int(11)      | NO   | MUL | 0       |                |
    | type           | varchar(1)   | NO   | MUL | T       |                |
    | title          | varchar(20)  | NO   |     |         |                |
    | question       | mediumtext   | NO   |     | NULL    |                |
    | preg           | mediumtext   | YES  |     | NULL    |                |
    | help           | mediumtext   | YES  |     | NULL    |                |
    | other          | varchar(1)   | NO   |     | N       |                |
    | mandatory      | varchar(1)   | YES  |     | NULL    |                |
    | question_order | int(11)      | NO   |     | NULL    |                |
    | language       | varchar(20)  | NO   | PRI | en      |                |
    | scale_id       | int(11)      | NO   |     | 0       |                |
    | same_default   | int(11)      | NO   |     | 0       |                |
    | relevance      | mediumtext   | YES  |     | NULL    |                |
    | modulename     | varchar(255) | YES  |     | NULL    |                |
    +----------------+--------------+------+-----+---------+----------------+

    describe survey_answers ;
    +------------------+-------------+------+-----+---------+-------+
    | Field            | Type        | Null | Key | Default | Extra |
    +------------------+-------------+------+-----+---------+-------+
    | qid              | int(11)     | NO   | PRI | 0       |       |
    | code             | varchar(5)  | NO   | PRI |         |       |
    | answer           | mediumtext  | NO   |     | NULL    |       |
    | sortorder        | int(11)     | NO   | MUL | NULL    |       |
    | assessment_value | int(11)     | NO   |     | 0       |       |
    | language         | varchar(20) | NO   | PRI | en      |       |
    | scale_id         | int(11)     | NO   | PRI | 0       |       |
    +------------------+-------------+------+-----+---------+-------+

    select id, 144477X148X740, 144477X148X741, 144477X148X742SQ001, 144477X148X745, 144477X148X748 from survey_survey_144477 limit 10;
    +----+----------------+----------------+---------------------+----------------+----------------+
    | id | 144477X148X740 | 144477X148X741 | 144477X148X742SQ001 | 144477X148X745 | 144477X148X748 |
    +----+----------------+----------------+---------------------+----------------+----------------+
    |  1 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    |  2 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    |  3 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    |  4 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    |  5 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    |  6 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    |  7 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    |  8 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    |  9 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    | 10 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    +----+----------------+----------------+---------------------+----------------+----------------+

select qid, sid, question from survey_questions where sid = 144477;
+-----+--------+-------------------------------------------------------------------------------------------+
| qid | sid    | question                                                                                |
+-----+--------+-------------------------------------------------------------------------------------------+
| 748 | 144477 | Please enter your email address or phone number if you would like someone to contact you. |
| 740 | 144477 | How would you rate our product's quality?                                                 |
| 741 | 144477 | Do you think our product helps your business?                                             |
| 742 | 144477 | One a scale of 1-10, how would you rate the value of our product?                         |
| 743 | 144477 |                                                                                |
| 745 | 144477 | Will you recommend our product?                                                           |
+-----+--------+-------------------------------------------------------------------------------------------+

select * from survey_answers where qid between 740 and 745;
+-----+------+-----------+-----------+------------------+----------+----------+
| qid | code | answer    | sortorder | assessment_value | language | scale_id |
+-----+------+-----------+-----------+------------------+----------+----------+
| 740 | 1    | Excellent |         1 |                1 | en       |        0 |
| 740 | 2    | Very good |         2 |                1 | en       |        0 |
| 740 | 3    | Good      |         3 |                1 | en       |        0 |
| 740 | 4    | Fair      |         4 |                1 | en       |        0 |
| 740 | 5    | Poor      |         5 |                1 | en       |        0 |
| 741 | A1   | Yes       |         1 |                0 | en       |        0 |
| 741 | A2   | No        |         2 |                0 | en       |        0 |
| 745 | A1   | Yes       |         1 |                0 | en       |        0 |
| 745 | A2   | No        |         2 |                0 | en       |        0 |
+-----+------+-----------+-----------+------------------+----------+----------+
</code>```0

Now I need to create a trigger that updates this table when a new survey is created and questions are answered.

(from comments)

```<code>## Create pivot table
CREATE TEMPORARY TABLE temp
SELECT id,'144477X148X740' AS lid, 740 AS qid, 144477X148X740 AS value FROM survey_survey_144477
UNION ALL
SELECT id, '144477X148X741' AS lid, 741 AS qid, 144477X148X741 AS value FROM survey_survey_144477
UNION ALL
SELECT id, '144477X148X742SQ001' AS lid, 742 AS qid, 144477X148X742SQ001 AS value FROM survey_survey_144477
UNION ALL
SELECT id, '144477X148X745' AS lid, 745 AS qid, 144477X148X745 AS value FROM survey_survey_144477;

## Get the answers.  Table survey_answers has answers from dropdowns and selection boxes.
## Other answers are in the temp table.
select T.id, SQ.question, SA.answer
   from survey_questions SQ
   join survey_answers SA on SA.qid = SQ.qid
   join temp T on T.qid = SQ.qid
   where SQ.sid = 144477
   --   and exists (select SA.answer as answer
   --                      from survey_answers SA
   --                     join survey_questions SQ on SA.qid = SQ.qid
   --                     join temp T on T.qid = SQ.qid)

CREATE TEMPORARY TABLE temp2
select id, qid, value
   from temp
   where not exists (select qid from survey_answers
                           where temp.qid = survey_answers.qid);

    describe survey_survey_144477;
    +---------------------+----------------+------+-----+---------+----------------+
    | Field               | Type           | Null | Key | Default | Extra          |
    +---------------------+----------------+------+-----+---------+----------------+
    | id                  | int(11)        | NO   | PRI | NULL    | auto_increment |
    | token               | varchar(35)    | YES  |     | NULL    |                |
    | submitdate          | datetime       | YES  |     | NULL    |                |
    | lastpage            | int(11)        | YES  |     | NULL    |                |
    | startlanguage       | varchar(20)    | NO   |     | NULL    |                |
    | ipaddr              | text           | YES  |     | NULL    |                |
    | refurl              | text           | YES  |     | NULL    |                |
    | 144477X148X740      | varchar(5)     | YES  |     | NULL    |                |
    | 144477X148X741      | varchar(5)     | YES  |     | NULL    |                |
    | 144477X148X742SQ001 | decimal(30,10) | YES  |     | NULL    |                |
    | 144477X148X745      | varchar(5)     | YES  |     | NULL    |                |
    | 144477X148X748      | text           | YES  |     | NULL    |                |
    +---------------------+----------------+------+-----+---------+----------------+

    describe survey_questions ;
    +----------------+--------------+------+-----+---------+----------------+
    | Field          | Type         | Null | Key | Default | Extra          |
    +----------------+--------------+------+-----+---------+----------------+
    | qid            | int(11)      | NO   | PRI | NULL    | auto_increment |
    | parent_qid     | int(11)      | NO   | MUL | 0       |                |
    | sid            | int(11)      | NO   | MUL | 0       |                |
    | gid            | int(11)      | NO   | MUL | 0       |                |
    | type           | varchar(1)   | NO   | MUL | T       |                |
    | title          | varchar(20)  | NO   |     |         |                |
    | question       | mediumtext   | NO   |     | NULL    |                |
    | preg           | mediumtext   | YES  |     | NULL    |                |
    | help           | mediumtext   | YES  |     | NULL    |                |
    | other          | varchar(1)   | NO   |     | N       |                |
    | mandatory      | varchar(1)   | YES  |     | NULL    |                |
    | question_order | int(11)      | NO   |     | NULL    |                |
    | language       | varchar(20)  | NO   | PRI | en      |                |
    | scale_id       | int(11)      | NO   |     | 0       |                |
    | same_default   | int(11)      | NO   |     | 0       |                |
    | relevance      | mediumtext   | YES  |     | NULL    |                |
    | modulename     | varchar(255) | YES  |     | NULL    |                |
    +----------------+--------------+------+-----+---------+----------------+

    describe survey_answers ;
    +------------------+-------------+------+-----+---------+-------+
    | Field            | Type        | Null | Key | Default | Extra |
    +------------------+-------------+------+-----+---------+-------+
    | qid              | int(11)     | NO   | PRI | 0       |       |
    | code             | varchar(5)  | NO   | PRI |         |       |
    | answer           | mediumtext  | NO   |     | NULL    |       |
    | sortorder        | int(11)     | NO   | MUL | NULL    |       |
    | assessment_value | int(11)     | NO   |     | 0       |       |
    | language         | varchar(20) | NO   | PRI | en      |       |
    | scale_id         | int(11)     | NO   | PRI | 0       |       |
    +------------------+-------------+------+-----+---------+-------+

    select id, 144477X148X740, 144477X148X741, 144477X148X742SQ001, 144477X148X745, 144477X148X748 from survey_survey_144477 limit 10;
    +----+----------------+----------------+---------------------+----------------+----------------+
    | id | 144477X148X740 | 144477X148X741 | 144477X148X742SQ001 | 144477X148X745 | 144477X148X748 |
    +----+----------------+----------------+---------------------+----------------+----------------+
    |  1 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    |  2 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    |  3 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    |  4 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    |  5 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    |  6 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    |  7 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    |  8 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    |  9 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    | 10 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    +----+----------------+----------------+---------------------+----------------+----------------+

select qid, sid, question from survey_questions where sid = 144477;
+-----+--------+-------------------------------------------------------------------------------------------+
| qid | sid    | question                                                                                |
+-----+--------+-------------------------------------------------------------------------------------------+
| 748 | 144477 | Please enter your email address or phone number if you would like someone to contact you. |
| 740 | 144477 | How would you rate our product's quality?                                                 |
| 741 | 144477 | Do you think our product helps your business?                                             |
| 742 | 144477 | One a scale of 1-10, how would you rate the value of our product?                         |
| 743 | 144477 |                                                                                |
| 745 | 144477 | Will you recommend our product?                                                           |
+-----+--------+-------------------------------------------------------------------------------------------+

select * from survey_answers where qid between 740 and 745;
+-----+------+-----------+-----------+------------------+----------+----------+
| qid | code | answer    | sortorder | assessment_value | language | scale_id |
+-----+------+-----------+-----------+------------------+----------+----------+
| 740 | 1    | Excellent |         1 |                1 | en       |        0 |
| 740 | 2    | Very good |         2 |                1 | en       |        0 |
| 740 | 3    | Good      |         3 |                1 | en       |        0 |
| 740 | 4    | Fair      |         4 |                1 | en       |        0 |
| 740 | 5    | Poor      |         5 |                1 | en       |        0 |
| 741 | A1   | Yes       |         1 |                0 | en       |        0 |
| 741 | A2   | No        |         2 |                0 | en       |        0 |
| 745 | A1   | Yes       |         1 |                0 | en       |        0 |
| 745 | A2   | No        |         2 |                0 | en       |        0 |
+-----+------+-----------+-----------+------------------+----------+----------+
</code>```1

答:
Whenever I need pivoting, I think about doing it in SQL.  But I quickly dismiss the thought as too painful to deal with.  Instead, I write code in PHP to pivot data that is stored is a more "natural" way for databases -- one row per thingie, not one column per thingie.

This should address your one-table-per-survey issue.  It is generally poor design to create tables that rapidly.

That said, I decided to see what it would take to automate the process in SQL.  Still a mess .

Body:
I'd like to create a report from tables that don't have an ideal layout. I can create pivot tables in excel but I want to create the reports online for a web based dashboard.  I've looked at
http://datacharmer.org/downloads/pivot_tables_mysql_5.pdf for pivot tables and Select from table if record found in another table for determining if a value exists but I cannot get the desired results. I was able to create a pivot table with a temp table in mysql but the reporting software would not allow data manipulation.

Some answers (qid 742) are in the survey_survey_144477 table but others are in the answers table. So I tried to use the value from survey_survey_144477 if the qid did nto exist in survey_answers.

This is only for one specific report. The survey results are in table survey_survey_144477 and only have a few questions but other surveys may have 20 or 30 questions.  So, I'd liek to make this dynmaic so I won't have to change all of the column heading for every report.  I also looked for parsing the column because part of the columns in survey_survey_144477 have the qid but no luck.  I also treied to determine how I could select all of the colums but disregard some such as token, last page, etc. because I thought it would be helpful if I made it dynamic but also no luck.

I'd appreciate any help you can offer.

The desired output would be:

| How would you rate ... | Do you think our product ... | One a scale of 1-10 ... | Will you recommend |
+-----------------+------+------------------------------+-------------------------+--------------------+
| Excellent              | Yes                          | 7                       | Yes                |
| Very good              | No                           | 8                       | Yes                |

I have tried:

## Create pivot table
CREATE TEMPORARY TABLE temp
SELECT id,'144477X148X740' AS lid, 740 AS qid, 144477X148X740 AS value FROM survey_survey_144477
UNION ALL
SELECT id, '144477X148X741' AS lid, 741 AS qid, 144477X148X741 AS value FROM survey_survey_144477
UNION ALL
SELECT id, '144477X148X742SQ001' AS lid, 742 AS qid, 144477X148X742SQ001 AS value FROM survey_survey_144477
UNION ALL
SELECT id, '144477X148X745' AS lid, 745 AS qid, 144477X148X745 AS value FROM survey_survey_144477;

## Get the answers.  Table survey_answers has answers from dropdowns and selection boxes.
## Other answers are in the temp table.
select T.id, SQ.question, SA.answer
   from survey_questions SQ
   join survey_answers SA on SA.qid = SQ.qid
   join temp T on T.qid = SQ.qid
   where SQ.sid = 144477
   --   and exists (select SA.answer as answer
   --                      from survey_answers SA
   --                     join survey_questions SQ on SA.qid = SQ.qid
   --                     join temp T on T.qid = SQ.qid)

CREATE TEMPORARY TABLE temp2
select id, qid, value
   from temp
   where not exists (select qid from survey_answers
                           where temp.qid = survey_answers.qid);

    describe survey_survey_144477;
    +---------------------+----------------+------+-----+---------+----------------+
    | Field               | Type           | Null | Key | Default | Extra          |
    +---------------------+----------------+------+-----+---------+----------------+
    | id                  | int(11)        | NO   | PRI | NULL    | auto_increment |
    | token               | varchar(35)    | YES  |     | NULL    |                |
    | submitdate          | datetime       | YES  |     | NULL    |                |
    | lastpage            | int(11)        | YES  |     | NULL    |                |
    | startlanguage       | varchar(20)    | NO   |     | NULL    |                |
    | ipaddr              | text           | YES  |     | NULL    |                |
    | refurl              | text           | YES  |     | NULL    |                |
    | 144477X148X740      | varchar(5)     | YES  |     | NULL    |                |
    | 144477X148X741      | varchar(5)     | YES  |     | NULL    |                |
    | 144477X148X742SQ001 | decimal(30,10) | YES  |     | NULL    |                |
    | 144477X148X745      | varchar(5)     | YES  |     | NULL    |                |
    | 144477X148X748      | text           | YES  |     | NULL    |                |
    +---------------------+----------------+------+-----+---------+----------------+

    describe survey_questions ;
    +----------------+--------------+------+-----+---------+----------------+
    | Field          | Type         | Null | Key | Default | Extra          |
    +----------------+--------------+------+-----+---------+----------------+
    | qid            | int(11)      | NO   | PRI | NULL    | auto_increment |
    | parent_qid     | int(11)      | NO   | MUL | 0       |                |
    | sid            | int(11)      | NO   | MUL | 0       |                |
    | gid            | int(11)      | NO   | MUL | 0       |                |
    | type           | varchar(1)   | NO   | MUL | T       |                |
    | title          | varchar(20)  | NO   |     |         |                |
    | question       | mediumtext   | NO   |     | NULL    |                |
    | preg           | mediumtext   | YES  |     | NULL    |                |
    | help           | mediumtext   | YES  |     | NULL    |                |
    | other          | varchar(1)   | NO   |     | N       |                |
    | mandatory      | varchar(1)   | YES  |     | NULL    |                |
    | question_order | int(11)      | NO   |     | NULL    |                |
    | language       | varchar(20)  | NO   | PRI | en      |                |
    | scale_id       | int(11)      | NO   |     | 0       |                |
    | same_default   | int(11)      | NO   |     | 0       |                |
    | relevance      | mediumtext   | YES  |     | NULL    |                |
    | modulename     | varchar(255) | YES  |     | NULL    |                |
    +----------------+--------------+------+-----+---------+----------------+

    describe survey_answers ;
    +------------------+-------------+------+-----+---------+-------+
    | Field            | Type        | Null | Key | Default | Extra |
    +------------------+-------------+------+-----+---------+-------+
    | qid              | int(11)     | NO   | PRI | 0       |       |
    | code             | varchar(5)  | NO   | PRI |         |       |
    | answer           | mediumtext  | NO   |     | NULL    |       |
    | sortorder        | int(11)     | NO   | MUL | NULL    |       |
    | assessment_value | int(11)     | NO   |     | 0       |       |
    | language         | varchar(20) | NO   | PRI | en      |       |
    | scale_id         | int(11)     | NO   | PRI | 0       |       |
    +------------------+-------------+------+-----+---------+-------+

    select id, 144477X148X740, 144477X148X741, 144477X148X742SQ001, 144477X148X745, 144477X148X748 from survey_survey_144477 limit 10;
    +----+----------------+----------------+---------------------+----------------+----------------+
    | id | 144477X148X740 | 144477X148X741 | 144477X148X742SQ001 | 144477X148X745 | 144477X148X748 |
    +----+----------------+----------------+---------------------+----------------+----------------+
    |  1 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    |  2 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    |  3 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    |  4 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    |  5 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    |  6 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    |  7 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    |  8 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    |  9 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    | 10 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
    +----+----------------+----------------+---------------------+----------------+----------------+

select qid, sid, question from survey_questions where sid = 144477;
+-----+--------+-------------------------------------------------------------------------------------------+
| qid | sid    | question                                                                                |
+-----+--------+-------------------------------------------------------------------------------------------+
| 748 | 144477 | Please enter your email address or phone number if you would like someone to contact you. |
| 740 | 144477 | How would you rate our product's quality?                                                 |
| 741 | 144477 | Do you think our product helps your business?                                             |
| 742 | 144477 | One a scale of 1-10, how would you rate the value of our product?                         |
| 743 | 144477 |                                                                                |
| 745 | 144477 | Will you recommend our product?                                                           |
+-----+--------+-------------------------------------------------------------------------------------------+

select * from survey_answers where qid between 740 and 745;
+-----+------+-----------+-----------+------------------+----------+----------+
| qid | code | answer    | sortorder | assessment_value | language | scale_id |
+-----+------+-----------+-----------+------------------+----------+----------+
| 740 | 1    | Excellent |         1 |                1 | en       |        0 |
| 740 | 2    | Very good |         2 |                1 | en       |        0 |
| 740 | 3    | Good      |         3 |                1 | en       |        0 |
| 740 | 4    | Fair      |         4 |                1 | en       |        0 |
| 740 | 5    | Poor      |         5 |                1 | en       |        0 |
| 741 | A1   | Yes       |         1 |                0 | en       |        0 |
| 741 | A2   | No        |         2 |                0 | en       |        0 |
| 745 | A1   | Yes       |         1 |                0 | en       |        0 |
| 745 | A2   | No        |         2 |                0 | en       |        0 |
+-----+------+-----------+-----------+------------------+----------+----------+

Update:

I created a table to map values from one table to another. Automating this will be a challenge that I will mention later.

CREATE TABLE `survey_answers_lookup` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sid` int(11) NOT NULL DEFAULT '0',
  `qid` int(11) NOT NULL DEFAULT '0',
  `survey_table_row_id` int(11) NOT NULL DEFAULT '0' COMMENT 'id that is in the survey_<id> table',
  `answer` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
VALUES (144477,740,1,1);

INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
VALUES (144477,741,1,'A1');

INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
VALUES (144477,742,1,'5.0000000000');

INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
VALUES (144477,745,1,'A1');

INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
VALUES (144477,748,1,'1@email.com');

INSERT INTO survey_answers_lookup(sid,qid,survey_table_row_id,answer)
VALUES (144477,740,2,'2');

INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
VALUES (144477,741,2,'A2');

INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
VALUES (144477,742,2,'8.0000000000');

INSERT INTO survey_answers_lookup(sid,qid,survey_table_row_id,answer)
VALUES (144477,745,2,'A2');

INSERT INTO survey_answers_lookup(sid,qid,survey_table_row_id,answer)
VALUES (144477,748,2,'2@email.com');

INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
VALUES (144477,740,3,'5');

INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
VALUES (144477,741,3,'A2');

INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
VALUES (144477,742,3,'10.0000000000');

INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
VALUES (144477,745,3,'A2');

INSERT INTO survey_answers_lookup(sid,qid,survey_table_row_id,answer)
VALUES (144477,748,3,'3@email.com');

Which results in

select * from survey_answers_lookup;

# id, sid, qid, survey_table_row_id, answer
'1', '144477', '740', '1', '1'
'2', '144477', '741', '1', 'A1'
'3', '144477', '742', '1', '5.0000000000'
'4', '144477', '745', '1', 'A1'
'5', '144477', '748', '1', '1@email.com'
'6', '144477', '740', '2', '2'
'7', '144477', '741', '2', 'A2'
'8', '144477', '742', '2', '8.0000000000'
'9', '144477', '745', '2', 'A2'
'10', '144477', '748', '2', '2@email.com'
'11', '144477', '740', '3', '5'
'12', '144477', '741', '3', 'A2'
'13', '144477', '742', '3', '10.0000000000'
'14', '144477', '745', '3', 'A2'
'15', '144477', '748', '3', '3@email.com'

Attempts to get the questions and answers.

 -- Gives all of the rows from the answers_table even if that is not in the
 -- survey_answers_lookup table because of the left join.  740 should only be printed three times.
    select
        --SQ.qid,
        SA.code,
        SQ.question,
        SQ.type,
        -- Types can be: ! * 1 E F K L M N Q R S T X Y
        if (SQ.type IN ('B','M','L'), SA.answer, LU2.answer) as 'answer'
        from survey_questions SQ
           left join survey_answers SA on SA.qid = SQ.qid
              -- and SQ.type IN ('B','M','L')
           left join survey_answers_lookup LU on LU.qid = SQ.qid
               and LU.answer = SA.code
              -- and SQ.type IN ('B','M','L')
           left join survey_answers_lookup LU2 on LU2.qid = SQ.qid
                and SQ.title = 'Q7'
      where SQ.sid = 144477;

# --SQ.qid, code, question, type, answer
'740', '1', 'How would you rate our product\'s quality?', 'L', 'Excellent'
'740', '2', 'How would you rate our product\'s quality?', 'L', 'Very good'
'740', '5', 'How would you rate our product\'s quality?', 'L', 'Poor'
'740', '3', 'How would you rate our product\'s quality?', 'L', 'Good'
'740', '4', 'How would you rate our product\'s quality?', 'L', 'Fair'
'741', 'A1', 'Do you think our product helps your business?', 'L', 'Yes'
'741', 'A2', 'Do you think our product helps your business?', 'L', 'No'
'741', 'A2', 'Do you think our product helps your business?', 'L', 'No'
'742', NULL, 'One a scale of 1-10, how would you rate the value of our product?', 'K', NULL
'745', 'A1', 'Will you recommend our product?', 'L', 'Yes'
'745', 'A2', 'Will you recommend our product?', 'L', 'No'
'745', 'A2', 'Will you recommend our product?', 'L', 'No'
'748', NULL, 'Please enter your email address or phone number if you would like someone to contact you.', 'S', '1@email.com'
'748', NULL, 'Please enter your email address or phone number if you would like someone to contact you.', 'S', '2@email.com'
'748', NULL, 'Please enter your email address or phone number if you would like someone to contact you.', 'S', '3@email.com'

-- Returns more than one row

  select
    SQ.qid,
    SQ.question,
    SQ.type,
    -- Types can be: ! * 1 E F K L M N Q R S T X Y
    if (SQ.type IN ('B','M','L'),
        (select SA.answer
           from survey_answers SA
              join survey_questions SQ2 on SQ2.qid = SA.qid
              join survey_answers_lookup LU on LU.qid = SQ2.qid
                 and LU.answer = SA.code),
          (select LU2.answer
              from survey_answers_lookup LU2
              join survey_questions SQ3 on SQ3.qid = LU2.qid)
     ) as answer
      from survey_questions SQ
      where SQ.sid = 144477;

This works if the types are 'B','M','L'.

   select
    -- qid,
    -- SA.code,
    question,
    -- Types can be: ! * 1 E F K L M N Q R S T X Y
    if (type IN ('B','M','L'),
        (select answer
           from survey_answers
              where survey_questions.qid = survey_answers.qid
              and survey_answers_lookup.qid = survey_questions.qid
              and survey_answers_lookup.answer = survey_answers.code
         ),
         (0 -- select answer
            --  from survey_answers_lookup
             --  where survey_questions.qid = survey_answers_lookup.qid
          )
     ) as answer
      from survey_questions
      join survey_answers_lookup on survey_answers_lookup.qid = survey_questions.qid
      where survey_questions.sid = 144477;

'Do you think our product helps your business?', 'Yes'
'Do you think our product helps your business?', 'No'
'Do you think our product helps your business?', 'No'
'How would you rate our product\'s quality?', 'Excellent'
'How would you rate our product\'s quality?', 'Very good'
'How would you rate our product\'s quality?', 'Poor'
'One a scale of 1-10, how would you rate the value of our product?', '0'
'One a scale of 1-10, how would you rate the value of our product?', '0'
'One a scale of 1-10, how would you rate the value of our product?', '0'
'Please enter your email address or phone number if you would like someone to contact you.', '0'
'Please enter your email address or phone number if you would like someone to contact you.', '0'
'Please enter your email address or phone number if you would like someone to contact you.', '0'
'Will you recommend our product?', 'Yes'
'Will you recommend our product?', 'No'
'Will you recommend our product?', 'No'

-- Error: Subquery returns more than one row.

  select
-- qid,
-- SA.code,
question,
-- Types can be: ! * 1 E F K L M N Q R S T X Y
if (type IN ('B','M','L'),
    (select answer
       from survey_answers
          where survey_questions.qid = survey_answers.qid
          and survey_answers_lookup.qid = survey_questions.qid
          and survey_answers_lookup.answer = survey_answers.code
     ),
     (select answer
         from survey_answers_lookup
          join survey_questions on survey_questions.qid = survey_answers_lookup.qid
              where not exists (select * -- if the value is not in survey_answers table (not exists)
                                 from survey_answers
                                    where survey_answers.qid = survey_answers_lookup.qid
                                       LIMIT 1
                              )
      )
 ) as answer
  from survey_questions
  join survey_answers_lookup on survey_answers_lookup.qid = survey_questions.qid
  where survey_questions.sid = 144477;

-- ----------------
-- ----------------

      select
      -- survey_answers_lookup.survey_table_row_id,
    -- qid,
    -- SA.code,
    question,
    -- Types can be: ! * 1 E F K L M N Q R S T X Y
    if (type IN ('B','M','L'),
        (select answer
           from survey_answers
              where survey_questions.qid = survey_answers.qid
              and survey_answers_lookup.qid = survey_questions.qid
              and survey_answers_lookup.answer = survey_answers.code
         ),
         (select answer
             from survey_answers_lookup
                 where qid not in (select qid
                                     from survey_answers
                                  )
          )
     ) as answer
      from survey_questions
      join survey_answers_lookup on survey_answers_lookup.qid = survey_questions.qid
      where survey_questions.sid = 144477;

The idea is to get the answers from table survey_answers if the types are 'B','M','L' and to get the answers from table survey_answers_lookup if they are another type.  The qid is not in table survey_answers if the answers is not in that table. Table survey_answers_lookup has three answers for qid's 742 and 748 and the query does not know which value to get.  I guess I need to add another value to join it. Thanks

select answer, qid
             from survey_answers_lookup
                  where qid not in (select qid from survey_answers)

# answer, qid
'5.0000000000', '742'
'1@email.com', '748'
'8.0000000000', '742'
'2@email.com', '748'
'10.0000000000', '742'
'3@email.com', '748'

It might be messy but maybe I can add a counter and then compare the counter to the id in table survey_answers_lookup?

select answer
             from survey_answers_lookup
                  where qid not in (select qid from survey_answers)
                  and id = @counter

I think I got it to work.

set @counter = 0;

  select
     @counter := @counter+1 AS newindex,
      survey_answers_lookup.id as id,
      survey_answers_lookup.survey_table_row_id,
      survey_answers_lookup.qid,
      question,
    -- Types can be: ! * 1 E F K L M N Q R S T X Y
    if (type IN ('B','M','L'),
        (select answer
           from survey_answers
              where survey_questions.qid = survey_answers.qid
              and survey_answers_lookup.qid = survey_questions.qid
              and survey_answers_lookup.answer = survey_answers.code
         ),
         (select answer
             from survey_answers_lookup
                  where qid not in (select qid from survey_answers)
                  and id = @counter
          )
     ) as answer
      from survey_questions
      join survey_answers_lookup on survey_answers_lookup.qid = survey_questions.qid
      where survey_questions.sid = 144477
      order by survey_answers_lookup.id;

# newindex, id, survey_table_row_id, qid, question, answer
'1', '1', '1', '740', 'How would you rate our product\'s quality?', 'Excellent'
'2', '2', '1', '741', 'Do you think our product helps your business?', 'Yes'
'3', '3', '1', '742', 'One a scale of 1-10, how would you rate the value of our product?', '5.0000000000'
'4', '4', '1', '745', 'Will you recommend our product?', 'Yes'
'5', '5', '1', '748', 'Please enter your email address or phone number if you would like someone to contact you.', '1@email.com'
'6', '6', '2', '740', 'How would you rate our product\'s quality?', 'Very good'
'7', '7', '2', '741', 'Do you think our product helps your business?', 'No'
'8', '8', '2', '742', 'One a scale of 1-10, how would you rate the value of our product?', '8.0000000000'
'9', '9', '2', '745', 'Will you recommend our product?', 'No'
'10', '10', '2', '748', 'Please enter your email address or phone number if you would like someone to contact you.', '2@email.com'
'11', '11', '3', '740', 'How would you rate our product\'s quality?', 'Poor'
'12', '12', '3', '741', 'Do you think our product helps your business?', 'No'
'13', '13', '3', '742', 'One a scale of 1-10, how would you rate the value of our product?', '10.0000000000'
'14', '14', '3', '745', 'Will you recommend our product?', 'No'
'15', '15', '3', '748', 'Please enter your email address or phone number if you would like someone to contact you.', '3@email.com'

Now I need to create a trigger that updates this table when a new survey is created and questions are answered.

(from comments)

SELECT  *
    FROM  survey_survey_144477
    UNION  ALL
SELECT  id,
        144477X148X741,
        741 AS qid,
        144477X148X741 AS value
     FROM  survey_survey_144477
    UNION  ALL
SELECT  id,
        144477X148X742SQ001,
        742 AS qid,
        144477X148X742SQ001 AS value
     FROM  survey_survey_144477
    UNION  ALL
SELECT  id,
        144477X148X745, 745 AS qid,
        144477X148X745 AS value
     FROM  survey_survey_144477;

select  T.id, SQ.question, SQ.type, -- SA.answer,
        case SQ.type
            when 'L' then SA.answer -- list choice --
            when 'M' then -- multiple choice --
            when 'B' then -- array-10(B)
            when 'T' OR  'N'  then T.value -- text or  numeric
            when 'K' then T.value -- slider --
            else "Do nothing" end as 'answer'
    from  survey_questions SQ
    join  survey_answers SA  ON SA.qid = SQ.qid
    join  temp T  ON T.qid = SQ.qid  and  T.value = SA.code
    where  SQ.sid = 144477 --
      and  SQ.type = 'K'
    order by  T.id;


来源: stackexchange

问:
How session works in coldfusion?
I have some doubts regarding the session and client management in coldfusion.

Note: The behavior is as follows when CFID and CFTOKEN are provided in the URL: If session exists, the CFID and CFTOKEN from the URL are ignored. If the session does not exist, CFID and CFTOKEN from the URL are used to validate the session and the session is used if it is valid. If the session is not valid, a new session is created. CFID and CFTOKEN are regenerated.

This is a statement from adobe ColdFusion online documentation. Below are my questions

How ColdFusion checks if the session exists without the help of CFID and CFTOKEN as in the first sentence?

Suppose we are using ColdFusion session management, if the CFTOKEN and CFID doesn't change after login can we hijack the session?

答: ColdFusion checks for a valid session by checking if CFID and CFTOKEN exist in the cookies scope. If the CFID and CFTOKEN cookies exist, it will use the cookie values to try and validate the session. If it can't validate the session using the cookie values, it will use the URL values to try and validate the session. </p>

ContentLicense: CC BY-SA 3.0

Title: How session works in coldfusion?


问:
How to erase flash memory (SD card)?
I am developing embedded system that writes to SD card.
As you all know, write access is faster if flash is pre-erased.
Is there any easy way to erase flash memory in windows?
Thanks,
Yony.</p>

答:
The flash memory on an SD card is managed by an on-card controller, you do not have the level of control to directly erase the NAND flash itself.

However when doing a multi-block write (CMD25) you can precede it with a pre-erase command (ACMD23) to pre-erase the blocks; this is significantly faster.  However if you specify a pre-erase then terminate the subsequent write, the content of the unwritten blocks is undefined according to the specification.  Also pre-erasure is supported only on SD cards, not MMC cards.  The details are in the SD Specifications Part 1 Physical Layer Simplified Specification

ContentLicense: CC BY-SA 2.5

Title: How to erase flash memory (SD card)?


问:
Python web scraping: difference between sleep and request(page, timeout=x)
When scraping multiple websites in a loop, I notice there is a rather large difference in speed between,

```<code>sleep(10)
response = requests.get(url)
</code>```

and,

```<code>response = requests.get(url, timeout=10)
</code>```

That is, timeout is much faster.

Moreover, for both set-ups I expected a scraping duration of at least 10 seconds per page before requesting the next page, but this is not the case.

Why is there such a difference in speed?

Why is the scraping duration per page less than 10 seconds?

I now use multiprocessing, but I think to remember the above holds as well for non-multiprocessing.

答:
```<code>response = requests.get(url, timeout=10)
# timeout specifies the maximum time program will wait for request to complete before throwing exception. It is not necessary that program will pause for 10 seconds. If response is returned early the program won't wait anymore.
</code>```

Read more about requests timeout here.

time.sleep cause your main thread to sleep , so your program will always wait for 10 seconds always before making a request to the url.

Body:
When scraping multiple websites in a loop, I notice there is a rather large difference in speed between,

sleep(10)
response = requests.get(url)

and,

response = requests.get(url, timeout=10)

That is, timeout is much faster.

Moreover, for both set-ups I expected a scraping duration of at least 10 seconds per page before requesting the next page, but this is not the case.

Why is there such a difference in speed?

Why is the scraping duration per page less than 10 seconds?

I now use multiprocessing, but I think to remember the above holds as well for non-multiprocessing.

Body:
response = requests.get(url, timeout=10)
# timeout specifies the maximum time program will wait for request to complete before throwing exception. It is not necessary that program will pause for 10 seconds. If response is returned early the program won't wait anymore.

Read more about requests timeout here.

time.sleep cause your main thread to sleep , so your program will always wait for 10 seconds always before making a request to the url.

ContentLicense: CC BY-SA 4.0


问:
Caffe Installation Ubuntu
I got this error while importing caffe library to python2.7

```<code>Python 2.7.12 (default, Nov 19 2016, 06:48:10)
[GCC 5.4.0 20160609] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import caffe
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/omer/caffe-master/python/caffe/__init__.py", line 1, in <module>
    from .pycaffe import Net, SGDSolver, NesterovSolver, AdaGradSolver, RMSPropSolver, AdaDeltaSolver, AdamSolver, NCCL, Timer
  File "/home/omer/caffe-master/python/caffe/pycaffe.py", line 13, in <module>
    from ._caffe import Net, SGDSolver, NesterovSolver, AdaGradSolver, \
ImportError: /home/omer/caffe-master/python/caffe/_caffe.so: undefined symbol: _ZN5boost6python6detail11init_moduleER11PyModuleDefPFvvE
</code>```

I have completed all steps

```<code>make all -j4
make test
make runtest
make pycaffe
make distribute
exporting PYTHONPATH
</code>```

答:
Try executing this commands</p>

```<code>sudo apt-get install libprotobuf-dev libleveldb-dev libsnappy-dev libopencv-dev libhdf5-serial-dev protobuf-compiler
sudo apt-get install --no-install-recommends libboost-all-dev
sudo apt-get install libgflags-dev libgoogle-glog-dev liblmdb-dev
</code>```

Title: Caffe Installation Ubuntu

Body:
I got this error while importing caffe library to python2.7

Python 2.7.12 (default, Nov 19 2016, 06:48:10)
[GCC 5.4.0 20160609] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import caffe
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/omer/caffe-master/python/caffe/__init__.py", line 1, in <module>
    from .pycaffe import Net, SGDSolver, NesterovSolver, AdaGradSolver, RMSPropSolver, AdaDeltaSolver, AdamSolver, NCCL, Timer
  File "/home/omer/caffe-master/python/caffe/pycaffe.py", line 13, in <module>
    from ._caffe import Net, SGDSolver, NesterovSolver, AdaGradSolver, \
ImportError: /home/omer/caffe-master/python/caffe/_caffe.so: undefined symbol: _ZN5boost6python6detail11init_moduleER11PyModuleDefPFvvE

I have completed all steps

make all -j4
make test
make runtest
make pycaffe
make distribute
exporting PYTHONPATH

Body:
Try executing this commands

sudo apt-get install libprotobuf-dev libleveldb-dev libsnappy-dev libopencv-dev libhdf5-serial-dev protobuf-compiler
sudo apt-get install --no-install-recommends libboost-all-dev
sudo apt-get install libgflags-dev libgoogle-glog-dev liblmdb-dev


问:
White space around background-image
There is white space appearing around the background image.

The code is as follow:

```<code>.class-of-div{

background: #00b5ff url(../img/cart.png) no-repeat;

background-position: 34px 4px;
}
</code>```

This happens in all browsers

答: It's the save for web option on photoshop and similar apps that causes this. I had the same problem using Adobe Illustrator Draw on the iPad. If the application you used for making the image doesn't have a "save as" option rather than "export for web" try using something like "preview" (mac) or paint on windows to crop it down.</p>

Title: White space around background-image

Body:
There is white space appearing around the background image.

The code is as follow:

.class-of-div{

background: #00b5ff url(../img/cart.png) no-repeat;

background-position: 34px 4px;
}

This happens in all browsers


问:
Truthiness test not performing as expected
I have a simple function that tests an item in a tuple to see if it is truthy or not. For some reason all tests come back True (...if connection:...), even though the value being tested is either a 1 (assumed True) or 0 (assumed false).

I tested to see if 1 == True (output: True), 1 == False (False), connection is True (False) just to try to understand what is going on.

User input: players = [('a', 1), ('b', 0)]

Function:

```<code>def validplayers(players):
    for player, connection in players:
        if connection:
            player
        print player, connection, 1 == True, 1 == False, connection is True
</code>```

Output:

```<code>a 1 True False False
b 0 True False False
</code>```

The connection var is passing a 1 or 0 which type(connection) defines as an int.

答:
Your error is looking at connection is True. This is always going to be False. is tests for identity, two expressions resulting in a reference to the same object in memory.

If you want to print the truth value of an object, use the bool() function:

```<code>>>> bool(1)
True
>>> bool(0)
False
</code>```

That 1 == True works at all is because in Python, the bool type is a subclass of int:

```<code>>>> issubclass(bool, int)
True
</code>```

The integer value of True is 1, and False has an integer value of 0. Testing for equality won't work for other integer values:

```<code>>>> 1 == True
True
>>> 2 == True
False
>>> bool(2)
True
</code>```

Note that your if test is otherwise meaningless; the only thing that is executed or not is the line player, which is a no-op expression that just returns the reference to player, but since there is nothing else there the reference is unused.

If you wanted the print statement to be executed only when if is true, you need to indent it to be further to the right from if and make it part of the nested block:

```<code>def validplayers(players):
    for player, connection in players:
        if connection:
            print player, connection, bool(connection)
</code>```

This then only prints something for player a:

```<code>>>> def validplayers(players):
...     for player, connection in players:
...         if connection:
...             print player, connection, bool(connection)
...
>>> validplayers([('a', 1), ('b', 0)])
a 1 True
</code>```

Body:
I have a simple function that tests an item in a tuple to see if it is truthy or not. For some reason all tests come back True (...if connection:...), even though the value being tested is either a 1 (assumed True) or 0 (assumed false).

I tested to see if 1 == True (output: True), 1 == False (False), connection is True (False) just to try to understand what is going on.

User input: players = [('a', 1), ('b', 0)]

Function:

def validplayers(players):
    for player, connection in players:
        if connection:
            player
        print player, connection, 1 == True, 1 == False, connection is True

Output:

a 1 True False False
b 0 True False False

The connection var is passing a 1 or 0 which type(connection) defines as an int.

Body:
Your error is looking at connection is True. This is always going to be False. is tests for identity, two expressions resulting in a reference to the same object in memory.

If you want to print the truth value of an object, use the bool() function:

>>> bool(1)
True
>>> bool(0)
False

That 1 == True works at all is because in Python, the bool type is a subclass of int:

>>> issubclass(bool, int)
True

The integer value of True is 1, and False has an integer value of 0. Testing for equality won't work for other integer values:

>>> 1 == True
True
>>> 2 == True
False
>>> bool(2)
True

Note that your if test is otherwise meaningless; the only thing that is executed or not is the line player, which is a no-op expression that just returns the reference to player, but since there is nothing else there the reference is unused.

If you wanted the print statement to be executed only when if is true, you need to indent it to be further to the right from if and make it part of the nested block:

def validplayers(players):
    for player, connection in players:
        if connection:
            print player, connection, bool(connection)

This then only prints something for player a:

>>> def validplayers(players):
...     for player, connection in players:
...         if connection:
...             print player, connection, bool(connection)
...
>>> validplayers([('a', 1), ('b', 0)])
a 1 True


问:
How to set unique string primary key in flask-sqlalchemy for API endpoint
I am building a Rest API using Flask dan Flask-SQLAlchemy. Just wonder, are there any ways to generate a unique ID string primary key? this key will be used for identity API endpoint, like:

POST v0.2/users/4q2VolejRejNmGQB/messages

Instead of

POST v0.2/users/123/messages

I am playing with http://hashids.org/, but seems like had a problem because the primary key will be generated automatically after I save the data while hashids need to generate given integer

答:
I just got the solution. I am using uuid for generating unique primary key and base64 to make it friendly.

```<code>import re
import uuid
import base64

def uuid_url64():
    rv = base64.b64encode(uuid.uuid4().bytes).decode('utf-8')
    return re.sub(r'[\=\+\/]', lambda m: {'+': '-', '/': '_', '=': ''}[m.group(0)], rv)
</code>```

here is example of the implementation

```<code>user1 = UserModel(user_id=str(uuid_url64()), name='rizkiaditya')
</code>```

Body:
I just got the solution. I am using uuid for generating unique primary key and base64 to make it friendly.

import re
import uuid
import base64

def uuid_url64():
    rv = base64.b64encode(uuid.uuid4().bytes).decode('utf-8')
    return re.sub(r'[\=\+\/]', lambda m: {'+': '-', '/': '_', '=': ''}[m.group(0)], rv)

here is example of the implementation

user1 = UserModel(user_id=str(uuid_url64()), name='rizkiaditya')


问:
Getting row for NSButton in NSTable
I am struggling with this one, can't help but feel i am missing something obvious!

I have an NSTable which contains a Table Cell View, which in turn contains an NSButton.

The button has an outlet defined, and the table view has an outlet defined.

I am using the following code to get the index of the row a button is in when the user clicks that button:

```<code>- (IBAction)approveButtonInTable:(id)sender {

    NSInteger selected = [self.tweetTableView clickedRow];
    NSLog(@"sender sends :%ld", selected);
}
</code>```

However this always replied with a -1, which is not massively helpful.

Am i missing something very obvious here?

Thanks

Gareth

Edit 1

I have worked out that this does work if i use it on the outlet of the tableview, using this:

```<code>- (IBAction)tweetTableAction:(id)sender {
    NSInteger selected = [_tweetTableView clickedRow];
    NSLog(@"sender sends :%ld", selected);
}
</code>```

However that doesn't really help me as i still can't get it to work when i click on the button, doh!

答:
If someone looking a Swift 3 version of Jablair answer.

Just implement @IBAction method.

```<code>@IBAction func approveButtonInTable(_ sender: Any) {
    let selectedRow = tweetTableView.row(for: sender as! NSView)
    print("sender sends \(selectedRow)")
 }
</code>```

Title: Getting row for NSButton in NSTable

Body:
I am struggling with this one, can't help but feel i am missing something obvious!

I have an NSTable which contains a Table Cell View, which in turn contains an NSButton.

The button has an outlet defined, and the table view has an outlet defined.

I am using the following code to get the index of the row a button is in when the user clicks that button:

- (IBAction)approveButtonInTable:(id)sender {

    NSInteger selected = [self.tweetTableView clickedRow];
    NSLog(@"sender sends :%ld", selected);
}

However this always replied with a -1, which is not massively helpful.

Am i missing something very obvious here?

Thanks

Gareth

Edit 1

I have worked out that this does work if i use it on the outlet of the tableview, using this:

- (IBAction)tweetTableAction:(id)sender {
    NSInteger selected = [_tweetTableView clickedRow];
    NSLog(@"sender sends :%ld", selected);
}

However that doesn't really help me as i still can't get it to work when i click on the button, doh!

Body:
If someone looking a Swift 3 version of Jablair answer.

Just implement @IBAction method.

@IBAction func approveButtonInTable(_ sender: Any) {
    let selectedRow = tweetTableView.row(for: sender as! NSView)
    print("sender sends \(selectedRow)")
 }


问:
Append a text in the end of the url php
i need to add a text in the end of the url while user click on a button.

for example :
www.example.com/people/xyz
 when the user click the button, it should go to
www.example.com/people/xyz/edit

i did a search, & built code, but it didn't work, here it's :

```<code>$editlink = $_SERVER['SERVER_NAME'].$_SERVER['REQUEST_URI'];

echo '<a href ="<?php $editlink;?>/edit" class="btn btn-info" role="button">EDIT</a>'
</code>```

Please Help me to solve it

答:
Correct your code as follows:</p>

```<code><?php
$editlink = $_SERVER['SERVER_NAME'].$_SERVER['REQUEST_URI'];
?>
<a href ="<?php echo $editlink;?>/edit" class="btn btn-info" role="button">EDIT</a>
</code>```

Title: Append a text in the end of the url php

Body:
i need to add a text in the end of the url while user click on a button.

for example :
www.example.com/people/xyz
 when the user click the button, it should go to
www.example.com/people/xyz/edit

i did a search, & built code, but it didn't work, here it's :

$editlink = $_SERVER['SERVER_NAME'].$_SERVER['REQUEST_URI'];

echo '<a href ="<?php $editlink;?>/edit" class="btn btn-info" role="button">EDIT'

Please Help me to solve it

Body:
Correct your code as follows:

<?php
$editlink = $_SERVER['SERVER_NAME'].$_SERVER['REQUEST_URI'];
?>
<a href ="<?php echo $editlink;?>/edit" class="btn btn-info" role="button">EDIT</a>


问:
Getting out of a while loop earlier with cout using c++
```<code>#include "stdafx.h"
#include <iostream>
#include <fstream>
#include <string>
#include <iomanip>
using namespace std;

int main()
{
    string sentence = "some random sentence";
    int i = 0; //runs through the bigger string
    int j = 0; //runs through the smaller string
    int k = 0; //variable to mark the position where the string starts being equal in order to delete it using substring
    string remove = "random";
    int a = sentence.size();
    int b = remove.size();
    while (i < a)
    {
        if (sentence[i] == remove[j])
        {
            if (b == j - 1)
            {
                cout << sentence.substr(0, k) << sentence.substr(i, (a - 1));
                break;
            }
            else
            {
                i++;
                j++;
            }
        }
        else
        {
            i++;
            j = 0;
            k++;

        }

    }
        return 1;
}
</code>```

I want to remove the word random from the bigger string and print it out but when I run the code, it does not return anything. What's missing?
I already tried putting a break right below de "cout", but it does not work.

Thank you :)

答:
Here is the code I edited

```<code>if (b-1 == j)
{
    cout << sentence.substr(0, k) << sentence.substr(i+2, (a - 1));
    break;
}
</code>```

This is assuming, you have spaces between the words.

Body:
#include "stdafx.h"
#include <iostream>
#include <fstream>
#include <string>
#include <iomanip>
using namespace std;

int main()
{
    string sentence = "some random sentence";
    int i = 0; //runs through the bigger string
    int j = 0; //runs through the smaller string
    int k = 0; //variable to mark the position where the string starts being equal in order to delete it using substring
    string remove = "random";
    int a = sentence.size();
    int b = remove.size();
    while (i < a)
    {
        if (sentence[i] == remove[j])
        {
            if (b == j - 1)
            {
                cout << sentence.substr(0, k) << sentence.substr(i, (a - 1));
                break;
            }
            else
            {
                i++;
                j++;
            }
        }
        else
        {
            i++;
            j = 0;
            k++;

        }

    }
        return 1;
}

I want to remove the word random from the bigger string and print it out but when I run the code, it does not return anything. What's missing?
I already tried putting a break right below de "cout", but it does not work.

Thank you :)

Body:
Here is the code I edited

if (b-1 == j)
{
    cout << sentence.substr(0, k) << sentence.substr(i+2, (a - 1));
    break;
}

This is assuming, you have spaces between the words.


问:
import input_data MNIST tensorflow not working
TensorFlow MNIST example not running with fully_connected_feed.py

I checked this out and realized that input_data was not built-in.  So I downloaded the whole folder from here. How can I start the tutorial:

```<code>import input_data
mnist = input_data.read_data_sets("MNIST_data/", one_hot=True)

---------------------------------------------------------------------------
ImportError                               Traceback (most recent call last)
<ipython-input-6-a5af65173c89> in <module>()
----> 1 import input_data
      2 mnist = tf.input_data.read_data_sets("MNIST_data/", one_hot=True)

ImportError: No module named input_data
</code>```

I'm using iPython (Jupyter) so do I need to change my working directory to this folder I downloaded? or can I add this to my tensorflow directory? If so, where do I add the files? I installed tensorflow with pip (on my OSX) and the current location is ~/anaconda/lib/python2.7/site-packages/tensorflow/__init__.py

Are these files meant to be accessed directly through tensorflow like sklearn datasets? or am I just supposed to cd into the directory and work from there? The example is not clear.

EDIT:

This post is very out-dated

答:
MNIST input_data was built-in, it's just not a individual module, it's inside Tensorflow module, try </p>

```<code>from tensorflow.examples.tutorials.mnist import input_data
</code>```

Body:
TensorFlow MNIST example not running with fully_connected_feed.py

I checked this out and realized that input_data was not built-in.  So I downloaded the whole folder from here. How can I start the tutorial:

import input_data
mnist = input_data.read_data_sets("MNIST_data/", one_hot=True)

---------------------------------------------------------------------------
ImportError                               Traceback (most recent call last)
<ipython-input-6-a5af65173c89> in <module>()
----> 1 import input_data
      2 mnist = tf.input_data.read_data_sets("MNIST_data/", one_hot=True)

ImportError: No module named input_data

I'm using iPython (Jupyter) so do I need to change my working directory to this folder I downloaded? or can I add this to my tensorflow directory? If so, where do I add the files? I installed tensorflow with pip (on my OSX) and the current location is ~/anaconda/lib/python2.7/site-packages/tensorflow/__init__.py

Are these files meant to be accessed directly through tensorflow like sklearn datasets? or am I just supposed to cd into the directory and work from there? The example is not clear.

EDIT:

This post is very out-dated

Body:
MNIST input_data was built-in, it's just not a individual module, it's inside Tensorflow module, try

from tensorflow.examples.tutorials.mnist import input_data


问:
Refactoring the following code?
I have two models blog and comment. Blog contains multiple comments, comment can only belong to 1 blog.

For showing the API like
blog/2/comment/1

I have to use the following code inside the comment controller

```<code>def show
    @blog = Blog.find(params[:blog_id])
    @comments = @blog.comments
    @comment = @comments[params[:id].to_i - 1]
  end
</code>```

I feel this code is quite akward, especially the last one @comments[params[:id].to_i - 1]. I have to convert parameter id to integer, then change to 0 based array index. Any refactoring can happen here

答:
Do you mean make it look more "Rails"? Like this?

```<code>def show
     @blog = Blog.find(params[:blog_id])
     @comment = @blog.comments.find params[:id]
end
</code>```

As per OP's comment. Your transformation is necessary. However, you can avoid loading the entire set of comments and only take the one you need.

```<code>@comment = @blog.comments.offset(params[:id].to_i - 1).limit(1)
</code>```

Title: Refactoring the following code?

Body:
I have two models blog and comment. Blog contains multiple comments, comment can only belong to 1 blog.

For showing the API like
blog/2/comment/1

I have to use the following code inside the comment controller

def show
    @blog = Blog.find(params[:blog_id])
    @comments = @blog.comments
    @comment = @comments[params[:id].to_i - 1]
  end

I feel this code is quite akward, especially the last one @comments[params[:id].to_i - 1]. I have to convert parameter id to integer, then change to 0 based array index. Any refactoring can happen here

Body:
Do you mean make it look more "Rails"? Like this?

def show
     @blog = Blog.find(params[:blog_id])
     @comment = @blog.comments.find params[:id]
end

As per OP's comment. Your transformation is necessary. However, you can avoid loading the entire set of comments and only take the one you need.

@comment = @blog.comments.offset(params[:id].to_i - 1).limit(1)


问:
Get all content from a folder with subfolder copied to another directory with bash
I have a directory that has many folders in it that have sub folders again and then there are some files. Can I write a bash script that copies all the files from the given directory into one single folder? So that I do not have to navigate through ever single folder and copy the content to another folder. </p>

答:
In the topmost dir under which you want the files to be copied:

```<code>find . -type f -exec cp {} /some/new/location \;
</code>```

Finds all the normal files and then copies them to /some/new/location

Body:
In the topmost dir under which you want the files to be copied:

find . -type f -exec cp {} /some/new/location \;

Finds all the normal files and then copies them to /some/new/location


问:
how to make the selected menu stay selected jquery, css , html
accordian menu , has to make the submenu to stay selected after clicking by js. or css .

not the static solution in html.
need solution comprising jquery and the css
it should appear same as hovering after clicked

check out the fiddle

https://jsfiddle.net/shaswatatripathy/ucgff65k/

$(document).ready(function() {

  $("#accordion > li > div").click(function() {
    $("#submenu li").slideUp();
    if (!$(this).next().is(":visible")) {
      $(this).next().slideDown();
    }
  });
});```
<pre class="snippet-code-css lang-css prettyprint-override"><code>.accordion {
  width: 100%;
  max-width: 260px;
  background: #FFF;
  margin: 0;
  padding: 0;
  box-sizing: border-box;
}

.accordion .link {
  cursor: pointer;
  display: block;
  padding: 15px 15px 15px 42px;
  font-size: 14px;
  font-weight: 700;
  border-top: 1px solid #CCC;
  border-bottom: 1px solid #CCC;
  border-right: 1px solid #CCC;
  position: relative;
  -webkit-transition: all 0.4s ease;
  -o-transition: all 0.4s ease;
  transition: all 0.4s ease;
}

/*-------------Submenu-----------------------------*/

.submenu {
  padding: 0px;
  display: none;
  font-size: 14px;
}

.submenu li {
  border-bottom: 1px solid #4b4a5e;
}

.submenu a {
  display: block;
  text-decoration: none;
  color: #23222d;
  background-color: #CCC;
  padding: 12px;
  padding-left: 42px;
  -webkit-transition: all 0.25s ease;
  -o-transition: all 0.25s ease;
  transition: all 0.25s ease;
}

#submenu li.active {
  display: block;
}

.submenu a:hover {
  background: #b63b4d;
  color: #FFF;
}</code>```

Menu 2

submenu1

submenu1

submenu1

Menu 3

submenu1

submenu1

submenu1

```

答:
Hope this is what you're looking for,

$(document).ready(function() {

  $("#accordion > li > div").click(function() {
    $("#submenu li").slideUp();
    if (!$(this).next().is(":visible")) {
      $(this).next().slideDown();
    }
  });

  $(".submenu li a").click(function(){
    $(".submenu li a.active").removeClass("active");
    $(this).addClass("active");
  });
});```
<pre class="snippet-code-css lang-css prettyprint-override"><code>.accordion {
  width: 100%;
  max-width: 260px;
  background: #FFF;
  margin: 0;
  padding: 0;
  box-sizing: border-box;
}

.accordion .link {
  cursor: pointer;
  display: block;
  padding: 15px 15px 15px 42px;
  font-size: 14px;
  font-weight: 700;
  border-top: 1px solid #CCC;
  border-bottom: 1px solid #CCC;
  border-right: 1px solid #CCC;
  position: relative;
  -webkit-transition: all 0.4s ease;
  -o-transition: all 0.4s ease;
  transition: all 0.4s ease;
}

/*-------------Submenu-----------------------------*/

.submenu {
  padding: 0px;
  display: none;
  font-size: 14px;
}

.submenu li {
  border-bottom: 1px solid #4b4a5e;
}

.submenu a {
  display: block;
  text-decoration: none;
  color: #23222d;
  background-color: #CCC;
  padding: 12px;
  padding-left: 42px;
  -webkit-transition: all 0.25s ease;
  -o-transition: all 0.25s ease;
  transition: all 0.25s ease;
}

#submenu li.active {
  display: block;
}

.submenu a:hover {
  background: #b63b4d;
  color: #FFF;
}
.submenu li a.active {
  background: #b63b4d;
  color: #FFF;
}</code>```

Menu 2

submenu1

submenu1

submenu1

Menu 3

submenu1

submenu1

submenu1

```

Body:
accordian menu , has to make the submenu to stay selected after clicking by js. or css .

not the static solution in html.
need solution comprising jquery and the css
it should appear same as hovering after clicked

check out the fiddle

https://jsfiddle.net/shaswatatripathy/ucgff65k/

$(document).ready(function() {

  $("#accordion > li > div").click(function() {
    $("#submenu li").slideUp();
    if (!$(this).next().is(":visible")) {
      $(this).next().slideDown();
    }
  });
});

.accordion {
  width: 100%;
  max-width: 260px;
  background: #FFF;
  margin: 0;
  padding: 0;
  box-sizing: border-box;
}

.accordion .link {
  cursor: pointer;
  display: block;
  padding: 15px 15px 15px 42px;
  font-size: 14px;
  font-weight: 700;
  border-top: 1px solid #CCC;
  border-bottom: 1px solid #CCC;
  border-right: 1px solid #CCC;
  position: relative;
  -webkit-transition: all 0.4s ease;
  -o-transition: all 0.4s ease;
  transition: all 0.4s ease;
}

/*-------------Submenu-----------------------------*/

.submenu {
  padding: 0px;
  display: none;
  font-size: 14px;
}

.submenu li {
  border-bottom: 1px solid #4b4a5e;
}

.submenu a {
  display: block;
  text-decoration: none;
  color: #23222d;
  background-color: #CCC;
  padding: 12px;
  padding-left: 42px;
  -webkit-transition: all 0.25s ease;
  -o-transition: all 0.25s ease;
  transition: all 0.25s ease;
}

#submenu li.active {
  display: block;
}

.submenu a:hover {
  background: #b63b4d;
  color: #FFF;
}

Menu 2

submenu1

submenu1

submenu1

Menu 3

submenu1

submenu1

submenu1

Body:
Hope this is what you're looking for,

$(document).ready(function() {

  $("#accordion > li > div").click(function() {
    $("#submenu li").slideUp();
    if (!$(this).next().is(":visible")) {
      $(this).next().slideDown();
    }
  });

  $(".submenu li a").click(function(){
    $(".submenu li a.active").removeClass("active");
    $(this).addClass("active");
  });
});

.accordion {
  width: 100%;
  max-width: 260px;
  background: #FFF;
  margin: 0;
  padding: 0;
  box-sizing: border-box;
}

.accordion .link {
  cursor: pointer;
  display: block;
  padding: 15px 15px 15px 42px;
  font-size: 14px;
  font-weight: 700;
  border-top: 1px solid #CCC;
  border-bottom: 1px solid #CCC;
  border-right: 1px solid #CCC;
  position: relative;
  -webkit-transition: all 0.4s ease;
  -o-transition: all 0.4s ease;
  transition: all 0.4s ease;
}

/*-------------Submenu-----------------------------*/

.submenu {
  padding: 0px;
  display: none;
  font-size: 14px;
}

.submenu li {
  border-bottom: 1px solid #4b4a5e;
}

.submenu a {
  display: block;
  text-decoration: none;
  color: #23222d;
  background-color: #CCC;
  padding: 12px;
  padding-left: 42px;
  -webkit-transition: all 0.25s ease;
  -o-transition: all 0.25s ease;
  transition: all 0.25s ease;
}

#submenu li.active {
  display: block;
}

.submenu a:hover {
  background: #b63b4d;
  color: #FFF;
}
.submenu li a.active {
  background: #b63b4d;
  color: #FFF;
}

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<ul id="accordion" class="accordion">
  <li>
    <div class="link">Menu 2</div>
    <ul class="submenu">
      <li><a href="#">submenu1</a></li>
      <li><a href="#">submenu1</a></li>
      <li><a href="#">submenu1</a></li>
    </ul>
  </li>
  <li>
    <div class="link">Menu 3</div>
    <ul class="submenu">
      <li><a href="#">submenu1</a></li>
      <li><a href="#">submenu1</a></li>
      <li><a href="#">submenu1</a></li>
    </ul>
  </li>
</ul>


问:
spring mvc 3.0: put a bunch of .jsp files under spring's control
In my spring mvc 3.0 project I have a directory full of .jsp files (~150) which I want to put under spring's control.

Until now they where invoked by web.xml directly, without going thru the spring servlet.

From now, I want to put them all under spring's control to enjoy spring's goodies and make my project more uniform.

Of course, I don't want to write a single controller for each .jsp file. How can I do this?

答:
In the case of a single JSP page you can use <mvc:view-controller>.

When you have many pages, you can write a catch-all controller like this:

```<code>@RequestMapping("/folder/{name}.jsp")
public ModelAndView catchAll(@PathVariable String viewName) {
    return new ModelAndView("legacy/" + viewName);
}
</code>```

However, it may conflict with JSP processing servlet. If so, you need to apply some URL rewriting filter.

Body:
In the case of a single JSP page you can use <mvc:view-controller>.

When you have many pages, you can write a catch-all controller like this:

@RequestMapping("/folder/{name}.jsp")
public ModelAndView catchAll(@PathVariable String viewName) {
    return new ModelAndView("legacy/" + viewName);
}

However, it may conflict with JSP processing servlet. If so, you need to apply some URL rewriting filter.


问:
How IDA will load DLL at constant memory segment at debug process?
Each time TEST_DEBUG.EXE loaded at 0x04000000 base in IDA-Modules, but
TEST_DEBUG.DLL file loaded at  any randoms base like 0x0C120000,  0x0C710000  , 0x0ABC0000
How i say to IDA debugger, load TEST_DEBUG.DLL every time at 0x0ABC0000 BASE ?

PS:
TEST_DEBUG.EXE  load many DLLS, and one of them is TEST_DEBUG.DLL

答:
NO ONE KNOW ?
C:\Program Files (x86)\Microsoft Visual Studio 12.0\VC\bin\x86_arm>EDITBIN /REBASE:BASE=0x61000000 mydll.dll</p>