Skip to main content

PROCEDURE to execute multiple query in mysql by php

Ok, my problem was that i have to execute query to multiple mysql server, The easy way to do that to make connection to each of those servers and send query. And i work with php. In some part of my project i need to do hundreds of query at a time and normally php don't support multiple query to database. To do that i have to change some (.h) file in server what i don't have access. I't take lots more time to execute query cos every time send a query to the remote serve the php wait for the result and then send the next query. but if i can send all the query once then it will take less time. I saw searching net for that and couldn't find a better way. Then i thought to do it by my my salfe. And i thought to make a PROCEDURE where i will send all the query with some delimiter. And i coded the below  PROCEDURE

CREATE  PROCEDURE `execute_query`(var_complexe_query LONGTEXT,var_query_delimiter VARCHAR(20))
BEGIN
SET @new_string := var_complexe_query;
SET @execute_query:='';
SET @s_delimiter := var_query_delimiter;
SET @s_length := CHAR_LENGTH(var_query_delimiter)+1;
SET @end_loop := 0;
SET @loop_count := 0;

  label1: LOOP
    SET @loop_count = @loop_count + 1;
    IF @loop_count > 1000 THEN LEAVE label1; END IF;
    SET @execute_query:= SUBSTRING_INDEX(@new_string , @s_delimiter, 1);
    SET @new_string := SUBSTRING(@new_string, CHAR_LENGTH(@execute_query)+@s_length);
    
    PREPARE exequery FROM @execute_query;
    EXECUTE exequery;
    IF CHAR_LENGTH(@new_string) < 1 THEN LEAVE label1; END IF;
    
 END LOOP label1;

 END$$

If i have like 3 queries query1,query2 adn query3 so i add all the quires with delimiter like "$$_$$" what im sure  not in my query  like in php

$query = $query1. "$$_$$".$query2. "$$_$$".$query3;

and call the PROCEDURE

`execute_query($query,"$$_$$");

 and the PROCEDURE separate all the quires from the string by delimiter and execute them

any one who read this post and have any other idea please let me know.
Labels:

Comments

Post a Comment

Popular posts from this blog

OpenCart : Route

In open cart the routeing technique is very simple, If we wan  to call a module or controller /catalog/controller/product/category.php the we have to call it like 'index.php?route=product/product'. Here product is the folder name category is the controller file name without the file extension.   'index.php?route=product/product'  it will execute the   public function index(){     ...  }  'index.php?route=product/product/menthod' then is ill call    public function method(){     ...  } the method should be public otherwise the system will not be able to call it we will know more about that later.
1 comment
Read more

Mysql function: GROUP_CONCAT() (Like implode() in php)

Today i have to solve a problem in MySql , The problem is i have two table in openCart 1. category_description category_id language name 1 1 category 1 2 1 category 2 3 1 category 3 4 1 category 4 5 1 category 5 6 1 category 6 7 1 category 7 8 1 category 8 1. product_category produc_id category_id 1 1 1 2 1 5 2 1 2 3 2 4 3 2 3 6 The resulting data should be like that produc_id category_name 1 category 1,category 2,category 5 2 category 1,category 3,category 4 3 category 2,category 6 I can do tha by in php by taking the category name to it's respective product id and implode them , but it will take much time and processor power cos the i have to do query every product id separately, I was searching the right query in MySql and found the function   GROUP_CONCAT()  http://dev.mysql.com/doc/refman/5.0/en/gr...
6 comments
Read more

PHP : Creating a CSV file as download file from array

Few days earlier I need to Create a text Or SCV file what will return some of the datafeed. Condition is i can't save the file in the serve i have to create and make to download for the user. So i made the below function   function genarateFeedTextFile($data=array(),$file_name=''){ $text=''; $temp=array(); if($data){ foreach($data as $value){          $text .= implode("\t",$value)."\n"; } } header("Content-Type: text/plain"); header("Content-disposition: attachment; filename=".$file_name.".txt");  header("Content-Transfer-Encoding: binary");  header("Pragma: no-cache");  header("Expires: 0"); echo $text; } This function solved my problem. But soon i have a little more problem. Some of my data contain new line "\n" or "\r" to tab "\b" so the result was not expected so i made a new function and puss all the string...
Post a Comment
Read more