StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL running an insert multiple times inside Stored-Procedure
text
Body
copied!<p>Okay, so let's say that I have this stored procedure in MySQL and this function written in php to post the data. I haven't tested either of these but I am fairly certain that they would work together if anyone feels like trying them. This code is based on the idea of creating a contract. How would I go about inserting a dynamic amount of clients into the <code>clients</code> and <code>clients_address</code> tables? I'm thinking that I should create the client data into an array in php, then send the client data along with another variable that has the array size, then in the stored procedure if there are 3 clients it would look like below with a </p> <pre><code>WHILE (x > 0) INSERT INTO `clients` INSERT INTO `client_address` SET x = x - 1; END WHILE; </code></pre> <p>-</p> <pre><code>DROP PROCEDURE IF EXISTS `create_contract`; DELIMITER '/'; CREATE PROCEDURE `create_contract` ( client_first_name VARCHAR(60), client_middle_name VARCHAR(60), client_last_name VARCHAR(60), client_date_of_birth DATETIME, client_street VARCHAR(60), client_apartment VARCHAR(60), client_city VARCHAR(60), client_state VARCHAR(60), client_zip SMALLINT(9), contract_title VARCHAR(60), contract_date DATETIME, contract_file_name VARCHAR(60), payor_first_name VARCHAR(60), payor_middle_name VARCHAR(60), payor_last_name VARCHAR(60), payor_date_of_birth DATETIME, payor_address_street VARCHAR(60), payor_address_apartment VARCHAR(60), payor_address_city VARCHAR(60), payor_address_state VARCHAR(60), payor_address_zip SMALLINT(9), ) BEGIN INSERT INTO `clients`(client_first_name, client_middle_name, client_last_name, client_date_of_birth) VALUES(client_first_name, client_middle_name, client_last_name, client_date_of_birth); INSERT INTO `client_address` (client_street, client_apartment, client_city, client_state,client_zip) VALUES (client_street, client_apartment, client_city, client_state, client_zip); INSERT INTO `payors` (payor_first_name, payor_middle_name, payor_last_name, payor_date_of_birth) VALUES (payor_first_name, payor_middle_name, payor_last_name, payor_date_of_birth); INSERT INTO `payor_address` (payor_street, payor_apartment, payor_city, payor_state, payor_zip) VALUES (payor_street, payor_apartment, payor_city, payor_state, payor_zip); INSERT INTO `contracts` (contract_title, contract_date, contract_file_name) VALUES (contract_title, contract_date, contract_file_name); END <?php public function create_new_contract( $client_first_name,$client_middle_name,$client_last_name, $client_date_of_birth, $client_street,$client_apartment,$client_city,$client_state,$client_zip, $contract_name,$contract_date,$contract_file_name, $payor_first_name,$payor_middle_name,$payor_last_name, $payor_date_of_birth, $payor_street,$payor_apartment,$payor_city,$payor_state,$payor_zip){ try{ $query = "CALL create_new_user (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; $stmt = $this->DBH->prepare($query); $stmt->bindParam(1, $client_first_name, PDO::PARAM_STR); $stmt->bindParam(2, $client_middle_name, PDO::PARAM_STR); $stmt->bindParam(3, $client_last_name, PDO::PARAM_STR); $stmt->bindParam(4, $client_date_of_birth, PDO::PARAM_STR); $stmt->bindParam(5, $client_street, PDO::PARAM_STR); $stmt->bindParam(6, $client_apartment, PDO::PARAM_STR); $stmt->bindParam(7, $client_city, PDO::PARAM_STR); $stmt->bindParam(8, $client_state, PDO::PARAM_STR); $stmt->bindParam(9, $client_zip, PDO::PARAM_INT); $stmt->bindParam(10, $contract_name, PDO::PARAM_STR); $stmt->bindParam(11, $contract_date, PDO::PARAM_STR); $stmt->bindParam(12, $contract_file_name, PDO::PARAM_STR); $stmt->bindParam(13, $payor_first_name, PDO::PARAM_STR); $stmt->bindParam(14, $payor_middle_name, PDO::PARAM_STR); $stmt->bindParam(15, $payor_last_name, PDO::PARAM_STR); $stmt->bindParam(16, $payor_date_of_birth, PDO::PARAM_STR); $stmt->bindParam(17, $payor_street, PDO::PARAM_STR); $stmt->bindParam(18, $payor_apartment, PDO::PARAM_STR); $stmt->bindParam(19, $payor_city, PDO::PARAM_STR); $stmt->bindParam(20, $payor_state, PDO::PARAM_STR); $stmt->bindParam(21, $payor_zip, PDO::PARAM_INT); $results = $stmt->execute(); if(!$results){ return 'Something went wrong please try again.'; }else{ return 'Thank you, the contracted was created.'; } } catch(PDOException $e){ return 'Something went wrong please try again.'; file_put_contents('PDOErrors.txt', $e->getMessage(), FILE_APPEND); } } ?> </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload