Hello. I have 2 tables in my MYSQL database called "Komplektacija" and "pack_to_light"


"komplektacija" table holds the information about Item1, Item2, Item3 ......
"pack_to_light" table holds the information about which Item is assigned to certain devices

What I need to do, is compare both tables and find a device in "pack_to_light" table which has "Item" and "Serial" that matches the "Item" and "Serial" in "komplektacija" table and update Quantity in "pack_to_light" table.

For this particular example. I would need to return:
device 1 | Item1 | 12345 | 1

I do not have much PHP experience but I believe I would need to use UNION operator in PHP to find matching data. I am trying to understand how to find matching data first, then I worry about updating quantity. Would someone be able to give me a hint

 

Will the serial numbers be your bar codes? Check out https://www.w3schools.com/php/php_mysql_select.asp for a pretty good example (in my opinion).

If you need to match two columns you could do something like

SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1' AND  field2_index = '1'

If you haven't already tried it I would suggest trying things out in MySQL cli https://dev.mysql.com/doc/refman/8.0/en/mysql.html.
I learned a lot about how to make things work out using it myself and taking notes of what worked and what doesn't. Luckily once you figure out how to make the right queries it's just a matter of writing them into a PHP script.

It's been 10 years or so since I've worked with MySQL so I'm a little rusty.

 

As far as updating...

UPDATE pack_to_light SET Quantity='2' WHERE item='item1' AND Serial='12345'

Should work. If the serial will be unique to each item I would just match the serial myself and make things a little easier.

Also note the Quantity returned is a text field and not an actual number. To increment it in PHP you will have to covert it to an integer variable, increment, then convert back to a text variable to be updated. I hope that makes sense.

 

Thanks for the reply.
I need to

Will the serial numbers be your bar codes? Check out https://www.w3schools.com/php/php_mysql_select.asp for a pretty good example (in my opinion).

If you need to match two columns you could do something like

SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1' AND  field2_index = '1'

If you haven't already tried it I would suggest trying things out in MySQL cli https://dev.mysql.com/doc/refman/8.0/en/mysql.html.
I learned a lot about how to make things work out using it myself and taking notes of what worked and what doesn't. Luckily once you figure out how to make the right queries it's just a matter of writing them into a PHP script.

It's been 10 years or so since I've worked with MySQL so I'm a little rusty.

I need to match two different tables and go through all available collums to find match in Item and Serial names so I dont think this script is right in that case. I will play little bit more and try that tommorow morning

 

I tried to find an old program I had written to refresh my memory a bit and pull out some examples, but unfortunately it's on an old computer that I never made it around to repairing.

It seems the MySQL documentation has changed since I last worked with it and I can't seem to locate what seemed to help me the most. I did find https://www.mysqltutorial.org/getting-started-with-mysql/connect-to-mysql-server/ which has links to various topics at the right that break down a lot of what you will need to make it happen.

If I'm understanding what you are trying to do this may work or at least give you an idea of where to go. I made a few quick changes to an example I found so it isn't exact, but enough to show a few things. It will pull the quantity from Komplektacija and update it in pack_to_light.

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT Quantity FROM Komplektacija WHERE Item='Item1' AND Serial='12345'";
$result = $conn->query($sql);

if ($result->num_rows == 1) { // I'm assuming you will only have one match if the item exists
  $row = $result->fetch_assoc();
  $sql = "UPDATE pack_to_light SET Quantity='".$row["Quantity"]."' WHERE item='item1' AND Serial='12345'
  $result = $conn->query($sql);
  // check for update errors
} else {
  echo "0 results";
}
$conn->close();
?>

 

If you are just trying to see if an item exists in both tables the UNION would work although it may take some formatting to get it to work.

$sql = "SELECT Serial FROM Komplektacija WHERE Item='Item1' AND Serial='12345'\nUNION\nSELECT Serial FROM pack_to_light WHERE Item='Item1' AND Serial='12345"

If the item exists in both tables it should return 1 for $result->num_rows or 0 if it doesn't.

It may work without the '\n'... I don't have MySQL installed these days so can't try it myself.

If you are trying to update one table from the other in one query I don't believe it will work.

I hope this all helps a little!!

 

Thank you as always sir. Got it to work using this script:

update pack_of_light p, Komplektacija k
set p.Quantity = k.Quantity
where k.Serial = p.Serial and k.Item = p.Item

Now just need to import it in PHP or im considering using python to do all the work. Either way, I should be good from here

 

Thank you as always sir. Got it to work using this script:

update pack_of_light p, Komplektacija k
set p.Quantity = k.Quantity
where k.Serial = p.Serial and k.Item = p.Item

Now just need to import it in PHP or im considering using python to do all the work. Either way, I should be good from here

Where did you find this? Much cleaner looking than the PHP I came up with. I've never worked with Python, but it also looks a bit cleaner PHP. My last few projects with MySQL were C++ and I did a few with PHP and Perl also before that. They all pretty much use the same query format, but getting the data out is a little different for each one.

Glad it's working. Good luck with the rest of the project!

 

Hello. I have 2 tables in my MYSQL database called "Komplektacija" and "pack_to_light"
View attachment 212670

FYI, this is a bad way to organize your data. A relational database shouldn't have data redundancy (same field in more than one table), as it is inefficient and a potential source of inconsistency errors. For example, you might update one table but forget to update the other.

Notice that the only unique field in the pack table is "Device"; the other fields duplicate the data in the Komp table. The simplest solution is to add the "Device" column to the Komp table and drop the pack table.

Alternatively, you can define a relation between Komp and pack. To do this, create a primary key on the Komp table, which you'll use as a foreign key in the pack table. You could use the "Item" column in Komp as the primary key, but for performance reasons people usually use an auto-incrementing integer column, typically named "ID". For example, the Komp table might look like:

ID	Item	Serial	Quantity
1	Item1	12345	1
2	Item2	1357	2

And then the pack table would look like:

ID	Device
1	device1
2	device2

The single ID column relates the two tables to each other, allowing them to store distinct data while giving us a way to JOIN the data from the two tables. For example, if we want to find the device associated with Item2, we could run a query such as:

SELECT Item,
       Device
FROM   Komplektacija AS k
       INNER JOIN pack_to_light AS p
               ON k.id = p.id
WHERE  Item = 'Item1';

This design principle has the tremendous advantage that any changes to the data -- e.g., the name of an item, its quantity, or its device name -- requires an update of exactly one table.

 

Okay thanks for your suggestion. Let me explain more about what data I actually need to store as I dont think this method will be very effective in my case. The database system that I am designing is for a "pick_to_light" project. I have multiple boxes I need to guide an operator and show him which box he needs to take an item form.

To do this, I need 2 tables:

pack_to_light table:

This table just stores information about what Items are currently assigned to all available boxes. If I have lets say 10 boxes, I need to know exactly what items are assigned to which box. The box in this case, I refer to "device". So lets say that device=box.

So the pack_to_light table will not change very often. After an operator fills the boxes with the items, he fills the database and then he does not need to worry about this table anymore. The table that is changing is the "komplektacija" table

komplektacija table:

When an operator starts an operation(picking item from boxes), he scans a barcode which then returns him what items he needs to take and the quantity, it does not know which items are in which boxes though - that is why I need to link pack_to_ligh table to komplektacija table - to know what device number (box number) the certain item is assigned to.

Once the operation is finished, the operator will scan a new barcode which will clear the last "komplektacija" table and fill a new one with a new set of items and quantities and again, I need to link both of these tables to know what items from which boxes he needs to collect it

 

When an operator starts an operation(picking item from boxes), he scans a barcode which then returns him what items he needs to take and the quantity, it does not know which items are in which boxes though - that is why I need to link pack_to_ligh table to komplektacija table - to know what device number (box number) the certain item is assigned to.

In relational databases like MySQL, the "link" is a key -- a unique integer column -- that is shared by the two tables. The goal is to not duplicate data across multiple tables. Perhaps the 'Quantity' column in both tables refer to different things (if so, I would give them different names), but it seems like the 'Item' and 'Serial' columns contain duplicate data. By using a key, only one of these tables needs to store that data.

Once the operation is finished, the operator will scan a new barcode which will clear the last "komplektacija" table and fill a new one with a new set of items and quantities and again, I need to link both of these tables to know what items from which boxes he needs to collect it

By "clear" do you mean delete the rows? Typically we want an audit trail of what was done, so we usually don't want to clear any tables. That said, I don't understand the purpose of the komplektacija table. Google translate tells me it means "complete set" in Lithuanian. I was under the impression that komplektacija holds all the items that can be stored in boxes, which are found in pack_to_light. But it seems that you're using komplektacija as a temporary table to hold the relevant data for the current operation. If so, the usual way to do this is to store that data in a PHP data structure (memory) rather than write it to a table (disk). The canonical approach is to use the database storage for persistent data (needs to last a long time) and programming language storage for temporary data.

If you're writing to a table to keep operational state across failures (program crash, power outage, reboot, etc.), then I'd create a "current_operation" table that includes the job number, time and date started, and keys pointing to the columns in the relevant data tables. I'd also create a "completed_operations" table with job number, time and date completed, and any other data you think is appropriate (such as packing and shipping IDs). Anyway, just some suggestions. Best of luck.

 

In relational databases like MySQL, the "link" is a key -- a unique integer column -- that is shared by the two tables. The goal is to not duplicate data across multiple tables. Perhaps the 'Quantity' column in both tables refer to different things (if so, I would give them different names), but it seems like the 'Item' and 'Serial' columns contain duplicate data. By using a key, only one of these tables needs to store that data.

I understood, but I need to "item" and "serial" pack_to_light table because that is how I know what item is assigned to a specific box.

By "clear" do you mean delete the rows? Typically we want an audit trail of what was done, so we usually don't want to clear any tables. That said, I don't understand the purpose of the komplektacija table. Google translate tells me it means "complete set" in Lithuanian. I was under the impression that komplektacija holds all the items that can be stored in boxes, which are found in pack_to_light. But it seems that you're using komplektacija as a temporary table to hold the relevant data for the current operation. If so, the usual way to do this is to store that data in a PHP data structure (memory) rather than write it to a table (disk). The canonical approach is to use the database storage for persistent data (needs to last a long time) and programming language storage for temporary data.

The purpose of "komplektacija" table is exactly as you said - temporary table to hold the information about the current oepration as I did not figure out any other good way to do that. But as you suggested, it might be better to just store it in a function (my raspberry PI runs a python script, and scans a barcode, after the barcode is scanned, it reads the information and fills the "komplektacija" table. So maybe you are right, thats not necessary, instead, I can just save it on my raspberry PI memory and update the quantity table from python script. Otherwise, I can have a table for "completed_operations" and "current_operations" ( current operations would be very simmilar to my "komplektacija" table, I just need to add a time stamp and other relevant information.

Thank you very much for a good advice either way!