Argthtjtr

I have this procedure :

BEGIN 

  DECLARE done INT DEFAULT FALSE; 

  DECLARE `id_var` varchar(255);

  DECLARE `cur1` CURSOR FOR

  SELECT `id` FROM `clients` 

  WHERE `status` = 'Active';

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  DROP TABLE IF EXISTS `tblquota_nc`;

  CREATE TABLE IF NOT EXISTS `tblquota_nc` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `user_id` int(11) NOT NULL,

  `email` varchar(255),

  `pack_id` int(11) NOT NULL,

  `pack_name` varchar(255) NOT NULL,

  `quota` int(11) NULL,

   PRIMARY KEY (`id`)

);

  OPEN cur1;

  read_loop: LOOP

     FETCH NEXT

     FROM cur1

     INTO id_var;

     IF done THEN

         LEAVE read_loop;

     END IF;



     SELECT clients.id as `User ID`, `email`, `packageid` as `Pack ID`, `name` as `Pack`, (CASE

        WHEN `name` = "Basic" THEN '10'

        WHEN `name` = "Silver" THEN '100'

        WHEN `name` = "Gold" THEN '1000'

        ELSE '10'

     END) as Quota INTO @mid, @mail, @p_id, @packname, @quota

     FROM `clients` 

     INNER JOIN `tblhosting` ON clients.id = tblhosting.userid 

     INNER JOIN `tblproducts` ON tblhosting.packageid = tblproducts.id 

     WHERE clients.status = 'Active' 

     AND tblhosting.domainstatus = 'Active'

     AND clients.id = id_var;

     IF (SELECT id FROM tblquota_nc WHERE user_id = @mid) THEN

        BEGIN

        END;

        ELSE

        BEGIN

            if (@mid IS NOT NULL AND @p_id IS NOT NULL AND @packname IS NOT NULL) then

                INSERT INTO tblquota_nc (user_id, email, pack_id, pack_name, quota) VALUES (@mid, @mail, @p_id, @packname, @quota);

            end if;

        END;

        END IF;

  END LOOP;

  CLOSE cur1;

END

It seems like I have an error because the SELECT statment return several values. I thought doing another loop with these results to make an insert into the new table. I want to make a new table from these information.

table clients:

id | email          | status

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

1  | user1@mail.com | Active

2  | user2@mail.com | Inactive

3  | user3@mail.com | Active

table tblhosting

id | userid | packageid | domainstatus 

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

1  | 1      | 2         | Active

2  | 2      | 3         | Active

3  | 3      | 1         | Active

table tblproducts

id | name 

-----------

1  | Basic

2  | Silver

3  | Gold

I expect result like :

id | user_id | email          | pack_id | pack_name | quota

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

1  | 1       | user1@mail.com | 2       | Silver    | 100

2  | 2       | user2@mail.com | 3       | Gold      | 1000

3  | 3       | user3@mail.com | 1       | Basic     | 10

If I put max in the case statment, it will work but will not show all data.

I don't think you need a stored procedure to do this. Just use CREATE TABLE ... SELECT syntax:

CREATE TABLE tblquota_nc (id INT AUTO_INCREMENT PRIMARY KEY) AS

     SELECT c.id as user_id

         , c.`email`

         , h.`packageid` as pack_id

         , p.`name` as pack_name

         , (CASE WHEN `name` = "Basic" THEN '10'

                 WHEN `name` = "Silver" THEN '100'

                 WHEN `name` = "Gold" THEN '1000'

                 ELSE '10'

            END) as quota

     FROM `clients` c

     LEFT JOIN `tblhosting` h ON c.id = h.userid 

     INNER JOIN `tblproducts` p ON h.packageid = p.id 

     ORDER BY c.id;

Output from SELECT * FROM tblquota_nc:

id  user_id     email           pack_id     pack_name   quota

1   1           user1@mail.com  2           Silver      100

2   2           user2@mail.com  3           Gold        1000

3   3           user3@mail.com  1           Basic       10

Demo on dbfiddle