title: How to create and call MySQL stores procedures?
body: <p>The table[<code>file_request</code> ] structure:</p>
  
  <pre><code>user_id[INT]  file_id[CHAR(10)]  all_files
  </code></pre>
  
  <p>This is the SQL code which work fine in MySQL clien: I am using <a href="http://www.heidisql.com/" rel="nofollow">heidisql</a></p>
  
  <pre><code>DROP PROCEDURE IF EXISTS check_user_files;
  DELIMITER \\  
   CREATE PROCEDURE check_user_files(IN p_user_id INT,IN p_file_id CHAR(10),IN p_all_files VARCHAR(500))
     BEGIN
          IF EXISTS(SELECT 1 FROM file_request WHERE user_id = p_user_id AND movie_id = p_file_id) THEN
              UPDATE file_request SET `requring_date`=NOW(),`all_files`= p_all_files WHERE `user_id`=p_user_id AND              `movie_id`=p_file_id;
          ELSE
              INSERT INTO file_request (`user_id`,`requring_date`,`movie_id`,`all_files`) 
              VALUES (p_user_id,NOW(),p_file_id,p_all_files);
          END IF; 
      END \\
  DELIMITER ;
  CALL check_user_files('23','T40431284','07 08 10 11 13 14');
  DELIMITER ;
  CALL check_user_files('23','F87951','01 02 03');
  </code></pre>
  
  <p>And I trying to create and execute the SQL query from PHP [didn't work] :</p>
  
  <pre><code>// create the call procedure statements
  foreach($fileData as $key=&gt;$value){
      $callSP .= "DELIMITER ; \n
                  CALL check_user_files('$userID','$key','$value');\n";            
  }
  
  $insert_file_request_query = "DROP PROCEDURE IF EXISTS check_user_files;
                                DELIMITER \\\\  
                                CREATE PROCEDURE check_user_files(IN p_user_id INT,IN p_file_id CHAR(10),IN p_all_files VARCHAR(500))
                                  BEGIN
                                      IF EXISTS(SELECT 1 FROM file_request WHERE user_id = p_user_id AND movie_id = p_file_id) THEN
                                          UPDATE file_request SET `requring_date`=NOW(),`all_files`= p_all_files WHERE `user_id`=p_user_id AND `movie_id`=p_file_id;
                                      ELSE
                                          INSERT INTO file_request (`user_id`,`requring_date`,`movie_id`,`all_files`) 
                                          VALUES (p_user_id,NOW(),p_file_id,p_all_files);
                                      END IF; 
                                  END \\\\
                                $callSP";
  
  mysqli_query($conn,$insert_file_request_query);
  </code></pre>
  
  <p>The SQL query which created from PHP didn't work as in the MySQL client!?</p>
  
  <p>So, how can I fix it!?</p>
  
  <p><strong>[update1]</strong></p>
  
  <p>I found that the <code>SQL query</code> must in <code>the special format [ the formate which work fine in the MySQL client]</code> or shouldn't work,I tried to copy and paste <code>the query which echo from the PHP</code>, the query code become <code>one line</code> and couldn't execute in MySQL client,too.</p>
  
  <p><strong>[update2]</strong>
  The code of create <code>store procedure</code> will work fine when I execute it alone from PHP.I mean,I split the whole process into three parts and execute them one by one.</p>
  
  <pre><code>part1: drop the procedure if it was exists; [using mysqli_query()]
  part2: create the procedure;[using mysqli_query()]
  part3：call the procedure;[using mysqli_multi_query()]
  
  $insert_file_request_query = '';    
  
  foreach($fileData as $key=&gt;$value){
      $insert_file_request_query .= "CALL check_save_file_request('$userID','$key','$value');";            
  }
  
  mysqli_multi_query($conn,$insert_file_request_query);
  </code></pre>
  
  <p>And my final solution was to create the Store Procedure in MySQL and call it from the PHP.It works fine now.</p>
  
  <p>Thank you very much!!</p>