Jump to content

Duplicating Mysql Table rows but also updating some content

Adamhumbug

By Adamhumbug
in PHP Coding Help

 Share
Go to solution Solved by Barand,

Recommended Posts

Adamhumbug Prolific Member

Adamhumbug

Posted
Posted

I have quotes and quotes have items.

When i duplicate a quote i need to also duplicate the items.

I have achieved this in one instance with the following. 

    $sql2 = "INSERT INTO quote (client_id, total_value, job_id, version, name, currency, kit_delivery, kit_return, quote_status_id)
            SELECT client_id, total_value, job_id, (version+1), name, currency, kit_delivery, kit_return, 2 from quote where quote.id = :quoteId";
    $stmt2 = $pdo ->prepare($sql2);
    $stmt2 -> execute([
        ":quoteId" => $quoteId
    ]);
    $iid = $pdo -> lastInsertId();

This works but the version + 1 that i have in there is not going to work as i first need to check for the highest version that has been used.

I may have gone down a rabbit hole here however: 

function createNewQuoteFromThisVersion($quoteId){
    include 'includes/dbconn.php';
    $sql ="UPDATE quote SET quote_status_id = 1";
    $stmt = $pdo ->query($sql) -> execute();

    $sql4 = "SELECT client_id, job_id from quote where id = :quoteId";
    $stmt4 = $pdo -> prepare($sql4);
    $stmt4 -> execute([
        ':quoteId' => $quoteId
    ]);
    if($row = $stmt4 -> fetch()){
        $clientId = $row['client_id'];
        $jobId = $row['job_id'];

    }

    $sql5 = "SELECT max(version) as v from quote where client_id = :clientId and job_id = :jobId";

    $stmt5 = $pdo -> prepare($sql5);
    $stmt5 -> execute([
        ':clientId' => $clientId,
        ':jobId' => $jobId
    ]);
    if($row = $stmt5 -> fetch()){
        $versionNumber = $row['v'];

    }

    $sql2 = "INSERT INTO quote (client_id, total_value, job_id, version, name, currency, kit_delivery, kit_return, quote_status_id)
            SELECT client_id, total_value, job_id, :version, name, currency, kit_delivery, kit_return, 2 from quote where quote.id = :quoteId";
    $stmt2 = $pdo ->prepare($sql2);
    $stmt2 -> execute([
        ":quoteId" => $quoteId,
        ':version' => $versionNumber
    ]);
    $iid = $pdo -> lastInsertId();

    $sql3 = "INSERT INTO quote_items (quote_id, item_id, quantity, start_date, end_date, notes, amount_charged_each, original_price, chargable_units)
            SELECT :iid, item_id, quantity, start_date, end_date, notes, amount_charged_each, original_price, chargable_units from quote_items where quote_id = :quoteId";
    $stmt3 = $pdo -> prepare($sql3);
    $stmt3 -> execute([
        ':iid' => $iid,
        ':quoteId' => $quoteId
    ]);
}

This was my effort at doing what i needed to.

The sql2 section is where i am going wrong i think.  What is the correct way to select what is in the database but also amend the some of the data you are putting in.

I can give data here if needed.

Please forgive the naming convention here it will be tidied as i go.

Link to comment
Share on other sites
Adamhumbug Prolific Member

Adamhumbug

Posted
  • Author
Posted 
-- phpMyAdmin SQL Dump
-- version 4.9.11
-- https://www.phpmyadmin.net/
--
-- Host: db5014142045.hosting-data.io
-- Generation Time: Sep 03, 2023 at 08:51 PM
-- Server version: 10.6.12-MariaDB-1:10.6.12+maria~deb11-log
-- PHP Version: 7.0.33-0+deb9u12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `dbs11785372`
--

-- --------------------------------------------------------

--
-- Table structure for table `quote_items`
--

CREATE TABLE `quote_items` (
  `id` int(11) NOT NULL,
  `quote_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `start_date` varchar(30) DEFAULT NULL,
  `end_date` varchar(30) DEFAULT NULL,
  `notes` varchar(250) NOT NULL,
  `amount_charged_each` float(10,2) NOT NULL,
  `original_price_each` float(10,2) NOT NULL,
  `chargable_units` float(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

--
-- Dumping data for table `quote_items`
--

INSERT INTO `quote_items` (`id`, `quote_id`, `item_id`, `quantity`, `start_date`, `end_date`, `notes`, `amount_charged_each`, `original_price_each`, `chargable_units`) VALUES
(168, 64, 1, 1, '', '', '12121212', 10000.00, 0.00, 1.00),
(169, 64, 9, 1, '', '', '121212', 1250.00, 0.00, 1.00),
(170, 64, 7, 1, '', '', '212', 3000.00, 0.00, 1.00),
(171, 65, 1, 1, '', '', '', 10000.00, 0.00, 1.00),
(172, 65, 9, 100, '', '', 'notwe', 1250.00, 0.00, 1.00);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `quote_items`
--
ALTER TABLE `quote_items`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `quote_items`
--
ALTER TABLE `quote_items`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=173;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
-- phpMyAdmin SQL Dump
-- version 4.9.11
-- https://www.phpmyadmin.net/
--
-- Host: db5014142045.hosting-data.io
-- Generation Time: Sep 03, 2023 at 08:51 PM
-- Server version: 10.6.12-MariaDB-1:10.6.12+maria~deb11-log
-- PHP Version: 7.0.33-0+deb9u12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `dbs11785372`
--

-- --------------------------------------------------------

--
-- Table structure for table `quote`
--

CREATE TABLE `quote` (
  `id` int(11) NOT NULL,
  `client_id` int(11) NOT NULL,
  `total_value` float(10,2) NOT NULL,
  `date_created` timestamp NOT NULL DEFAULT current_timestamp(),
  `job_id` int(11) NOT NULL,
  `version` int(11) NOT NULL,
  `name` varchar(200) NOT NULL,
  `currency` varchar(3) NOT NULL,
  `kit_delivery` varchar(30) NOT NULL,
  `kit_return` varchar(30) NOT NULL,
  `quote_status_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

--
-- Dumping data for table `quote`
--

INSERT INTO `quote` (`id`, `client_id`, `total_value`, `date_created`, `job_id`, `version`, `name`, `currency`, `kit_delivery`, `kit_return`, `quote_status_id`) VALUES
(65, 15, 135000.00, '2023-09-03 20:17:52', 7, 5, 'first', '1', '2023-09-01', '2023-09-03', 1),
(71, 15, 135000.00, '2023-09-03 20:40:42', 7, 6, 'first', '1', '2023-09-01', '2023-09-03', 2);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `quote`
--
ALTER TABLE `quote`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `quote`
--
ALTER TABLE `quote`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=72;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

 

Link to comment
Share on other sites
  • Solution
Barand Prolific Member

Barand

Posted
  • Solution
Posted

Start with 

SELECT q.id
     , q.version
FROM quote q
     JOIN (
            SELECT client_id
                 , max(version) as version
            FROM quote
            WHERE client_id = 15
          ) latest USING (client_id, version);
          
+----+---------+
| id | version |
+----+---------+
| 71 |       6 |
+----+---------+

Now you know that for client #15 the latest quote has id = 71 and its version is 6.

Duplicate quote #71, giving the dupe a verion of 7, and duplicate the items for #71 with the quote_id of the new duped quote.

Link to comment
Share on other sites
Barand Prolific Member

Barand

Posted
Posted
15 hours ago, Barand said:

Duplicate quote #71, giving the dupe a verion of 7, and duplicate the items for #71 with the quote_id of the new duped quote.

example... 

$client = 15;

$res = $pdo->prepare("SELECT   q.id
                             , q.version
                        FROM quote q
                             JOIN (
                                    SELECT client_id
                                         , max(version) as version
                                    FROM quote
                                    WHERE client_id = ?
                                  ) latest USING (client_id, version)
                        ");
$res->execute([ $client ]);
list($quote, $version) = $res->fetch(PDO::FETCH_NUM);
if ($quote)  {
    
    try {
        $pdo->beginTransaction();
        $stmt = $pdo->prepare("INSERT INTO quote (client_id, total_value, job_id, version, name, currency, kit_delivery, kit_return, quote_status_id)
                               SELECT client_id, total_value, job_id, ?, name, currency, kit_delivery, kit_return, quote_status_id
                               FROM quote
                               WHERE id = ?
                              ");
        $stmt->execute([ ++$version, $quote ]);
        $newquote = $pdo->lastInsertId();
    
        $stmt = $pdo->exec("INSERT INTO quote_items (quote_id, item_id, quantity, start_date, end_date, notes, amount_charged_each, original_price_each, chargable_units)
                            SELECT  $newquote, item_id, quantity, start_date, end_date, notes, amount_charged_each, original_price_each, chargable_units
                            FROM quote_items
                            WHERE quote_id = $quote
                           ");
        $pdo->commit();
    }
    catch (PDOException $e)  {
        $pdo->rollBack();
        throw $e;
    }
}
else {
    echo "Client quotes not found";
}

 

Link to comment
Share on other sites
Adamhumbug Prolific Member

Adamhumbug

Posted
  • Author
Posted
1 hour ago, Barand said:

example... 

$client = 15;

$res = $pdo->prepare("SELECT   q.id
                             , q.version
                        FROM quote q
                             JOIN (
                                    SELECT client_id
                                         , max(version) as version
                                    FROM quote
                                    WHERE client_id = ?
                                  ) latest USING (client_id, version)
                        ");
$res->execute([ $client ]);
list($quote, $version) = $res->fetch(PDO::FETCH_NUM);
if ($quote)  {
    
    try {
        $pdo->beginTransaction();
        $stmt = $pdo->prepare("INSERT INTO quote (client_id, total_value, job_id, version, name, currency, kit_delivery, kit_return, quote_status_id)
                               SELECT client_id, total_value, job_id, ?, name, currency, kit_delivery, kit_return, quote_status_id
                               FROM quote
                               WHERE id = ?
                              ");
        $stmt->execute([ ++$version, $quote ]);
        $newquote = $pdo->lastInsertId();
    
        $stmt = $pdo->exec("INSERT INTO quote_items (quote_id, item_id, quantity, start_date, end_date, notes, amount_charged_each, original_price_each, chargable_units)
                            SELECT  $newquote, item_id, quantity, start_date, end_date, notes, amount_charged_each, original_price_each, chargable_units
                            FROM quote_items
                            WHERE quote_id = $quote
                           ");
        $pdo->commit();
    }
    catch (PDOException $e)  {
        $pdo->rollBack();
        throw $e;
    }
}
else {
    echo "Client quotes not found";
}

 

Love this - thank you so much

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.