#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    2
    Rep Power
    0

    Create a procedure and process


    Hi,

    I need a sample stored procedure which can process records in a loop from a table. say below is the condition

    Select id,name from person where age = 25


    There are 75 records matching the results and I need to loop through each of the records and insert them in another table. Please provide the sample procedure based on my condition.


    Thanks,
    Rakesh.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Location
    kerala
    Posts
    72
    Rep Power
    2
    Step 1: Create procedure in PHP Myadmin

    DELIMITER //
    CREATE PROCEDURE get_user(IN age VARCHAR(15))
    BEGIN
    SELECT id,name FROM person WHERE age = age;
    END //

    Step 2: Call procedure in PHP Page

    Start PHP Tag

    $age = 25;
    $sql = $mysqli->real_query("CALL get_user('".$age."')");

    if($sql){
    $result = $mysqli->use_result();
    while($rs = $result->fetch_array())
    {
    $id = $rs['id'];
    $name = $rs['name']);
    }
    while ($mysqli->next_result()) {
    $result = $mysqli->use_result();
    if($result instanceof mysqli_result) {
    $result->free();
    }
    }
    //insert them in another table
    }

    End PHP Tag
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,207
    Rep Power
    4279
    Originally Posted by rakesh2404
    There are 75 records matching the results and I need to loop through each of the records and insert them in another table. Please provide the sample procedure based on my condition.
    what is this, a homework assignment?

    because the proper way to do this is ~not~ with a loop, but rather with an INSERT statement using the SELECT option
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    The proper way to do this is "not at all". Copying records has exactly one application and that is to make a historical backup.

    If you want to select 25 records and use them in a different table to add some more information to them, like in a banner rotation system, then you probably do want a loop because you may want to process each record separately depending on wether or not you could add them to the new table.

    MySQL's truely odd way of dealing with multiple inserts could mean that if one record fails, the rest is still added, leaving you with a problem to solve.

    Rule #1 in MySQL: never assume that it will do what you think, because it won't.

IMN logo majestic logo threadwatch logo seochat tools logo