How to execute a query which is stored in a table

Question

mysql> select * from CT;

| CID | MID | REPORT_QUERY           |
|   1 |   1 | select * from emp;     |
|   2 |   2 | select * from student; |

2 rows in set (0.00 sec)

I want to execute queries in REPORT_QUERY column.

DELIMITER //
 CREATE PROCEDURE TRYct()
   BEGIN
SET @str=(SELECT GROUP_CONCAT(REPORT_QUERY SEPARATOR ' ') FROM CT);
PREPARE q from @str;
EXECUTE q;
   END //
 DELIMITER ;

i use this code but it works if there is only one query in my table. if there is two query than it gives an error.

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select * from student' at line 1

where is the problem ? Help me.

Answer 1

If you want to execute SQL that is stored in column of some MySQL table, try this:

SELECT sql_text INTO @sql FROM sql_queries WHERE id = 1;
PREPARE sql_query FROM @sql;
EXECUTE sql_query;

Contents of the table sql_queries:

+----+--------------------------+
| id |         sql_text         |
+----+--------------------------+
|  1 | select * from companies; |
+----+--------------------------+

Answer 2

As i understand your question is that you need to run the queries which are stored in table column in SQL Form.

Here you can fetch the queries form table column and execute them in mysqli.

SQL QUERY TABLE IMAGE HERE THE QUERIES ARE STORED

SQL EMPLOYEE TABLE [WHICH IS DYNAMICALLY CALLED BY QUERY TABLE DURING EXECUTION]

PHP SQL CODE : `

<?php
    $link = new mysqli ('localhost','root','admin','demo');

    if($link->connect_error){
        die ("Connection Failed".$link->connect_error);
    }

    //YOU NEED THIS AS YOU NEED TO ACCESS THE COLUMN DATA IN TABLE
    $sql = "SELECT * FROM querytable WHERE id=1";

    if($res = $link->query($sql)){
        //IF THE ROW EXISTS
        if($res->num_rows > 0){
            while($row = $res->fetch_assoc()){

                $query = $row['report_query']; //HERE IS THE SQL QUERY STORED IN TABLE , WHICH IS STORED IN ANOTHER VARIABLE
                $result = $link->query($query); // NOW EXECUTE THE QUERY
                if($result->num_rows > 0){
            ?>
                    <table border="1" cellpadding="10">
                        <tr>
                            <th>First Name</th>
                            <th>Last Name</th>
                        </tr>
            <?php
                    while($rows = $result->fetch_assoc()){
                        //DISPLAY RESULTS HERE
            ?>          
                        <tr>
                            <td><?php echo $rows['first_name'];?></td>
                            <td><?php echo $rows['lastname'];?></td>
                        </tr>
            <?php
                    }
            ?>
                    </table>
        <?php
                }
            }
        }   
    }
    else
    {
        echo $link->error;
    }
?>

`

Answer 3

You can use a cursor to get each REPORT_QUERY on CT table, and execute that using prepared statements:

delimiter $$
drop procedure if exists run_queries$$
create procedure run_queries()
begin

    declare s_query varchar(255);

    declare done bool default false;
    declare c_queries cursor for    
        select REPORT_QUERY from CT;
    declare continue handler for not found set done = true;


    open c_queries;
    read_loop: loop

        fetch c_queries into s_query;
        if done then 
            leave read_loop;
        end if;

        -- run the query
        set @sql = s_query;
        prepare stmt from  @sql;
        execute stmt;
        deallocate prepare stmt;
    end loop;

end$$

After create procedure, you can call as bellow:

call run_queries();

That's it.