How do I execute the same stored procedure in multi-times in MySQL or PHP?


Problem :

In my previous question.I have got the solution to check out which data was duplicated before insert data. This is the MySQL stored procedure to check data:

DROP PROCEDURE IF EXISTS check_user_files;
DELIMITER \\  
 CREATE PROCEDURE check_user_files(IN p_user_id INT,IN p_file_id CHAR(10))
   BEGIN
        IF EXISTS(SELECT 1 FROM myTable WHERE user_id = p_user_id AND file_id= p_file_id) THEN
            UPDATE myTable SET `file_status`=0 WHERE user_id=p_user_id AND file_id=p_file_id;
        ELSE
            INSERT INTO myTable(`user_id`,`file_status`,`file_id`) 
            VALUES (p_user_id,0,p_file_id);
        END IF; 
    END \\
DELIMITER ;

To call the stored procedure:

CALL check_user_files('1','12');

The table [myTable] structure:

auto_id  user_id  file_id file_status
1        1        12        1
2        3        12        0
3        1        17        1
4        4        31        1
5        1        41        0
6        4        31        0
7        1        18        1
8        5        11        0
9        1        10        0

And my problem is, I have multi-data to check and insert at the same time.E.g:

('1','12'),('2','14'),('3','16'),...

But I can't call the stored procedure at the same time to check all records at one time.E.g:

CALL check_user_files('2','12');
CALL check_user_files('3','12');
...

It only call the first line of stored procedure.

So, how can I check all records in one query at the same time!>

Thank you very much!!



Solution :

Can be done using a single call stored procedure but due to mysql not supporting array parameters it's going to a little ugly as we have to pass in a comma separated string of user_id;file_id tokens. The only other alternative is to call the stored procedure multiple times.

Full script here : http://pastie.org/1722362

mysql> select * from user_files;
+---------+---------+-------------+
| user_id | file_id | file_status |
+---------+---------+-------------+
|       1 |       2 |           0 |
|       3 |       6 |           0 |
+---------+---------+-------------+
2 rows in set (0.00 sec)

call check_user_files('user_id;file_id, user_id;file_id...');

call check_user_files('1;2, 2;4, 3;6, 4;10, 50;50, 1000;1, 1;10001');

mysql> select * from user_files;
+---------+---------+-------------+
| user_id | file_id | file_status |
+---------+---------+-------------+
|       1 |       2 |           0 |
|       1 |   10001 |           0 |
|       2 |       4 |           0 |
|       3 |       6 |           0 |
|       4 |      10 |           0 |
|      50 |      50 |           0 |
|    1000 |       1 |           0 |
+---------+---------+-------------+
7 rows in set (0.00 sec)

Stored procedure

drop procedure if exists check_user_files;

delimiter #

create procedure check_user_files
(
in p_users_files_csv varchar(4096)
)
proc_main:begin

declare v_token varchar(255);

declare v_done tinyint unsigned default 0;
declare v_token_idx int unsigned default 1;

declare v_user_id int unsigned default 0;
declare v_file_id int unsigned default 0;

    set p_users_files_csv = replace(p_users_files_csv, ' ','');

    if p_users_files_csv is null or length(p_users_files_csv) <= 0 then
        leave proc_main;
    end if;

    -- the real ugly bit

    -- split the string into user_id/file_id tokens and put into an in-memory table...

    create temporary table tmp(
        user_id int unsigned not null,
        file_id int unsigned not null
    )engine = memory;   

    while not v_done do

      set v_token = trim(substring(p_users_files_csv, v_token_idx, 
        if(locate(',', p_users_files_csv, v_token_idx) > 0, 
          locate(',', p_users_files_csv, v_token_idx) - v_token_idx, length(p_users_files_csv))));

      if length(v_token) > 0 then

     set v_token_idx = v_token_idx + length(v_token) + 1;

         set v_user_id = mid(v_token, 1, instr(v_token, ';')-1);
         set v_file_id = mid(v_token, instr(v_token, ';')+1, length(v_token));

         insert into tmp (user_id, file_id) values(v_user_id, v_file_id);
      else
        set v_done = 1;
      end if;

  end while;

  -- the nice bit - insert the new values

  insert into user_files (user_id, file_id, file_status)
  select
   t.user_id, t.file_id, 0 as file_status
  from
   tmp t
  left outer join user_files uf on t.user_id = uf.user_id and t.file_id = uf.file_id
  where 
   uf.user_id is null and uf.file_id is null;

  drop temporary table if exists tmp;

end proc_main #

delimiter ;

Hope this helps


Mysql Tutorials

Mysql Howto..

How To Have Multiple Users Accessing Backend & Save Data? [closed]

How to reduce the execution time in stored procedure using mysql

mysql c++ connector, how to keep the connection alive and reconnect if connection is broked?

HTML & PHP - How to make multiple forms submittable

How to get ALL column names using mysql and php

PHP: How to delete user without delete the 'authenticate' function

how to edit permission of liferay guest and site member from mysql databse?

How can I get the most recent date using MySQL

How to query for both posts and post upvotes in MySQL?

How to lock mysql tables in php

How to write in DB without racing condition using a web service?

How to get Unix timestamp in MySQL from UTC time?

How to do an arithmetic operation in MySQL

How do I optimize an ORDER condition to prevent filesort?

how to store phonetics in mysql database

mysql - how to include zeros in the count in only one single query

How to connect to mysql using ride

How to select vertical data in MySQL table

How to random a zero leading string field mysql [duplicate]

How to change a lot of items using php and mysql

how to indent results of mysql SELECT INTO OUTFILE

How to create repeatable event php/mysql (weekly)

How to match comma delimited value with other column in mySql

MySQL | How to select only one record of each category?

how to output in 5-minute interval?(mysql )

How to insert rows in mysql Db , using light-orm in nodejs

How to update model from database in Visual Web Developer 2010 Express?

how to count the particular string in row wise in mysql table

How Upload large files (100 mb) in php [closed]

How to get rows with array of ids in MySQL