Updating a table with data from another tables

General PHP Help
#1

Hello. I want to populate column in my table with values that are results of a arithmetic operation. Operation take operands from another tables
Here is the details. There is my first table:

CREATE TABLE `farrowing` (
  `id` int(11) NOT NULL,
  `user` varchar(11) NOT NULL,
  `sowNumber` varchar(20) NOT NULL,
  `cycleNumber` int(11) NOT NULL,
  `penNumber` int(11) NOT NULL,
  `sectionNumber` int(11) NOT NULL,
  `pigletBornNumber` int(11) NOT NULL,
  `livePigletBorn` int(11) NOT NULL,
  `farrowingDate` date NOT NULL,
  `currentInFarrHouse` tinyint(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ge

INSERT INTO `farrowing` (`id`, `user`, `sowNumber`, `cycleNumber`, `penNumber`, `sectionNumber`, `pigletBornNumber`, `livePigletBorn`, `farrowingDate`, `currentInFarrHouse`) VALUES
(21, '1', '0001', 1, 1, 0, 12, 10, '2023-12-22', 1),
(22, '1', '0002', 1, 2, 0, 14, 13, '2023-12-23', 1),
(23, '1', '0003', 1, 3, 0, 17, 13, '2023-12-08', 1);

I want to show current number of piglets in the farrowing house. When I change data in my farrowing table I want the number of piglets to change accordingly. There is the other three tables:

CREATE TABLE `pigletlose` (
  `id` int(11) NOT NULL,
  `userId` int(11) NOT NULL,
  `dateOfLose` date NOT NULL,
  `cycle` int(11) NOT NULL,
  `penNumber` int(11) NOT NULL,
  `numberOfPiglets` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `pigletchange` (
  `id` int(11) NOT NULL,
  `userId` int(11) NOT NULL,
  `cycle` int(11) NOT NULL,
  `penFrom` int(11) DEFAULT NULL,
  `penTo` int(11) DEFAULT NULL,
  `pigletNumber` int(11) NOT NULL,
  `dateOfChange` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `weaningindi` (
  `id` int(11) NOT NULL,
  `userId` int(11) NOT NULL,
  `dateOfWeaning` date NOT NULL,
  `cycle` int(11) NOT NULL,
  `penFrom` int(11) NOT NULL,
  `numberOfPiglets` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;`

My arithmetic is: (number of piglets in pen in farrowing table) - (number of piglets lost in pigletlose table)-( number of piglets that are transferred to another pens in pigletchange table)+( piglets that came into the pen in pigletchange table)- ( weaned pigs from that pen in weanindi table)

I tried to fetch the data from farrowing table and then use that values for arithmethic, but result of the subqueries is null

$sql = "
    SELECT
        sowNumber,
        cycleNumber,
        penNumber,
        pigletBornNumber,
        livePigletBorn,
        farrowingDate,
        (
            SELECT IFNULL(livePigletBorn, 0) FROM farrowing WHERE penNumber = farrowing.penNumber LIMIT 1
        ) AS livePigletBorn,
        (
            SELECT IFNULL(numberOfPiglets, 0) FROM pigletlose WHERE penNumber = farrowing.penNumber LIMIT 1
        ) AS numberOfPigletsLost,
        (
            SELECT IFNULL(pigletNumber, 0) FROM pigletchange WHERE penTo = farrowing.penNumber LIMIT 1
        ) AS pigletsChangedIn,
        (
            SELECT IFNULL(pigletNumber, 0) FROM pigletchange WHERE penFrom = farrowing.penNumber LIMIT 1
        ) AS pigletsChangedOut,
        (
            SELECT IFNULL(numberOfPiglets, 0) FROM weaningindi WHERE penNumber = farrowing.penNumber LIMIT 1
        ) AS numberOfPigletsWeaned
    FROM
        farrowing
    WHERE
        user = '$userId' AND currentInFarrHouse = 1";

$result = $mysqli->query($sql);

if ($result) {
    echo '<table id="data_table" class="table table-striped">';
    echo '<tr>
            <th>Korisnik</th>
            <th>Broj krmače</th>
            <th>Broj ciklusa</th>
            <th>Broj boksa</th>
            <th>Broj prasadi pri rođenju</th>
            <th>Broj žive prasadi pri rođenju</th>
            <th>Datum prašenja</th>
            <th>Žive prasadi</th>
            <th>Broj prasadi izgubljenih</th>
            <th>Broj prasadi promenjenih (unutra)</th>
            <th>Broj prasadi promenjenih (spolja)</th>
            <th>Broj prasadi odviknutih</th>
        </tr>';

    while ($row = $result->fetch_assoc()) {
        echo '<tr>';
        echo '<td>' . $userId . '</td>';
        echo '<td>' . $row['sowNumber'] . '</td>';
        echo '<td>' . $row['cycleNumber'] . '</td>';
        echo '<td>' . $row['penNumber'] . '</td>';
        echo '<td>' . $row['pigletBornNumber'] . '</td>';
        echo '<td>' . $row['livePigletBorn'] . '</td>';
        echo '<td>' . $row['farrowingDate'] . '</td>';
        echo '<td>' . $row['livePigletBorn'] . '</td>';
        echo '<td>' . $row['numberOfPigletsLost'] . '</td>';
        echo '<td>' . $row['pigletsChangedIn'] . '</td>';
        echo '<td>' . $row['pigletsChangedOut'] . '</td>';
        echo '<td>' . $row['numberOfPigletsWeaned'] . '</td>';
        echo '</tr>';
    }

    echo '</table>';
    $result->close();
} else {
    echo "Error executing the SQL query: " . $mysqli->error;
}

I also tried with some foreach loop. Once again, need to populate a column that contains number of piglets in some pen. For that I have to take data about number of piglets in another tables and do the math. I hope it can be done simply, but I have no clue. Appreciate any help.

#2

I recommend some changes to your database design, that falls under data normalization and Don’t Repeat Yourself (DRY) programming.

In the farrowing table, remove the livePigletBorn column. The only quantity column in this table should be the total piglets pigletBornNumber. The number of piglets lost at birth will be handled later.

The id in the farrowing table establishes a farrowing id that you should use when storing related data. Knowing the farrowing id tells you all the unique information about each farrowing event, so that you don’t repeat this data in other tables.

Instead of having a separate table for each different type of transaction that affects the stock/inventory (numberOfPiglets), you should have a transaction type table that defines the different types of transactions - lost at birth, lost after birth, change in, change out, weaning, … You would then have a (one) transaction table that holds all transactions that affect the stock/inventory, with a type_id column that indicates the type of transaction.

Transaction types (the sign column can be used to conditionally add or subtract in a query, not used for what I am showing) -

CREATE TABLE `transaction_types` (
  `id` int(11) NOT NULL,
  `name` text NOT NULL,
  `sign` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `transaction_types` (`id`, `name`, `sign`) VALUES
(1, 'lost at birth', '-'),
(2, 'lost after birth', '-'),
(3, 'change in', '+'),
(4, 'change out', '-'),
(5, 'weaning', '-');

Transactions -

CREATE TABLE `transactions` (
  `id` int(11) NOT NULL,
  `parent_id` int(11) NOT NULL,
  `date` date NOT NULL,
  `quantity` int(11) NOT NULL,
  `type_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

// some example data -
INSERT INTO `transactions` (`id`, `parent_id`, `date`, `quantity`, `type_id`) VALUES
(1, 21, '2023-12-25', 2, 1),
(2, 22, '2023-12-25', 1, 1),
(3, 23, '2023-12-25', 4, 1),
(4, 21, '2023-12-26', 1, 2),
(5, 21, '2023-12-27', 2, 2),
(6, 23, '2023-12-26', 3, 5),
(7, 23, '2023-12-26', 1, 5),
(8, 22, '2023-12-26', 1, 3),
(9, 23, '2023-12-26', 1, 4);

The parent_id column in this table is the farrowing id.

For a change in or change out transaction, you should insert two rows into the transaction table, one for the ‘from’ farrowing id and one for the ‘to’ farrowing id.

Next, to get your query to produce the data that you want, you need to GROUP BY the farrowing id, and use conditional logic in the SELECT term to SUM the quantity values for each type_id -

$sql = "
    SELECT
		user,
        sowNumber,
        cycleNumber,
        penNumber,
        pigletBornNumber,
        farrowingDate,
		SUM(CASE WHEN t.type_id = 1 THEN t.quantity ELSE 0 END) AS lost_at_birth,
		SUM(CASE WHEN t.type_id = 2 THEN t.quantity ELSE 0 END) AS lost_after_birth,
		SUM(CASE WHEN t.type_id = 3 THEN t.quantity ELSE 0 END) AS change_in,
		SUM(CASE WHEN t.type_id = 4 THEN t.quantity ELSE 0 END) AS change_out,
		SUM(CASE WHEN t.type_id = 5 THEN t.quantity ELSE 0 END) AS weaning
    FROM
        farrowing f
	LEFT JOIN transactions t ON f.id = t.parent_id
    WHERE
        f.user = ? AND f.currentInFarrHouse = 1
	GROUP BY f.id	
		";

Note: I converted this to a prepared query, using the PDO extension, hence the ? place-holder in the f.user = ? comparison.

#3

Thank you very much for the answer. It’s very helpful.

Sponsor our Newsletter | Privacy Policy | Terms of Service