2 tabellen vergelijken
Ik heb 2 "bijna" dezelfde tabellen.
Tabel 1 wordt gevuld vanuit een tool met stappenplan om producten samen te stellen.
Tabel 2 bestaat uit samengestelde producten die al eerder verkocht zijn.
Ik wil nu een vergelijking doen aan het einde van de tool om te controleren of de invoer in tabel 1 wel of niet bestaat in tabel 2. En bij zo ja; wat het artikel_ID is. Dan moet dat artikel_ID gekoppeld worden in tabel 2. Bij zo nee; een nieuw artikel_ID aanmaken.
Maar helaas kom ik er niet aan uit.
Dit zou toch alleen/grotendeels in MySQL moeten kunnen zonder allerlei trage php?
Ik heb al van alles gevonden met UNION etc, maar het blijft vaag voor me.
Van 1 set (set_ID, tabel 1) zullen meerdere regels bestaan net als van 1 hoofd artikel (parrent_artikel_ID , tabel 2).
De database opzet van de betreffende 2 tabellen:
Tabel 1:
ID
set_ID
stap_ID *
invoer_resultaat *
artikel_ID *
volgnummer *
waarde *
aantal *
afmeting *
inkoop_prijs *
Tabel 2:
ID
parrent_artikel_ID
stap_ID *
invoer_resultaat *
artikel_ID *
volgnummer *
waarde *
aantal *
afmeting *
inkoop_prijs *
Als bij alle rijen met een bepaalde set_ID in tabel 1 dus de kolommen met * exact hetzelfde zijn als een bepaald aantal rijen in tabel 2; wil ik het parrent_artikel_ID weten.
Kan iemand helpen of me de goeie richting wijzen?
Misschien wat kort door de bocht, maar zou je niet van beide varianten een soort van string kunnen breien in PHP, waarbij de kolomwaarden gescheiden worden door een pipe ofzo, en je de resultaten sorteert op stap_ID (deze hoef je dan niet op te nemen in de string), vervolgens pak je de hash van die string en die vergelijk je dan met de hash van de op dezelfde manier gegenereerde string van een configuratie uit de andere tabel. Zolang je in beide tabellen data op eenzelfde wijze kunt sorteren/serialiseren (en hashen) kun je deze vergelijking uitvoeren. Zijn beide hashes gelijk zijn de configuraties gelijk.
De hashes kun je dan ergens opslaan zodat je zo'n vergelijking aan het einde van het stappenplan meteen efficiënt kunt uitvoeren.
Er zijn vast betere manieren om dit te doen, maar ik zie het bovenstaande wel werken. Vraag is alleen, welke kolommen neem je op in deze hash ter identificatie, oftewel, wanneer zijn twee configuraties "hetzelfde"?
Gewijzigd op 08/08/2018 13:11:10 door Thomas van den Heuvel
Dat van de inkoop_prijs is een goede tip. Zal ik eens met de opdrachtgever overleggen, product blijft inderdaad hetzelfde.
Heb het nu voorlopig opgelost door een php arry te vullen met de data uit de tool en die te vergelijken met de productconfiguraties. Het werkt nu, maar ik had verwacht dat het alleen SQL ook wel zou moeten kunnen.
Een bepaalde query als
SELECT parrent_artikel_ID FROM tabel1 WHERE (SELECT kolommen FROM tabel1) = (SELECT kolommen FROM tabel2)
Maar dat is er blijkbaar niet (in MySQL)
Ramon van Dongen op 08/08/2018 13:30:51:
Heb je een reden om allerlei kolommen erbij te selecteren? Zoals hierboven al gezegd, een WHERE EXISTS of WHERE NOT EXISTS en dan uiteraard alleen op de key(s)??SELECT parrent_artikel_ID FROM tabel1 WHERE (SELECT kolommen FROM tabel1) = (SELECT kolommen FROM tabel2)
Maar dat is er blijkbaar niet (in MySQL)
Maar dat is er blijkbaar niet (in MySQL)
Hele bakken gegevens ophalen en in een array gooien en met php gaan vergelijken vreet geheugen en zal op termijn vastlopen.
Toevoeging op 08/08/2018 20:34:55:
John D op 08/08/2018 20:33:50:
Hele bakken gegevens ophalen en in een array gooien en met php gaan vergelijken vreet geheugen en zal op termijn vastlopen. Dit soort vragen zijn met SQL op te lossen.
Ramon van Dongen op 08/08/2018 13:30:51:
Heb je een reden om allerlei kolommen erbij te selecteren? Zoals hierboven al gezegd, een WHERE EXISTS of WHERE NOT EXISTS en dan uiteraard alleen op de key(s)??SELECT parrent_artikel_ID FROM tabel1 WHERE (SELECT kolommen FROM tabel1) = (SELECT kolommen FROM tabel2)
Maar dat is er blijkbaar niet (in MySQL)
Maar dat is er blijkbaar niet (in MySQL)
Hele bakken gegevens ophalen en in een array gooien en met php gaan vergelijken vreet geheugen en zal op termijn vastlopen. Dit soort vragen zijn met SQL op te lossen.
Gewijzigd op 08/08/2018 20:34:18 door John D
John D op 08/08/2018 20:33:50:
Hele bakken gegevens ophalen en in een array gooien en met php gaan vergelijken vreet geheugen en zal op termijn vastlopen.
Dit doe je uiteraard (in een) eenmalig (uit te voeren dataverrijkingsscriptje), de hashes sla je op, en als je de wizard hebt doorlopen voer je een eenvoudige query uit om te kijken of de hash al voorkomt.
Gewijzigd op 08/08/2018 22:04:59 door Thomas van den Heuvel
Thomas van den Heuvel op 08/08/2018 22:01:21:
Uiteraard, uitstekende oplossing maar onder ons zijn hele volksstammen die SELECT * FROM ophalen met php van een of meer tabellen en dat in array's vergelijken om een enkel relatie record te vinden. Gewoon uit gebrek aan gedegen SQL kennis.Dit doe je uiteraard (in een) eenmalig (uit te voeren dataverrijkingsscriptje), de hashes sla je op, en als je de wizard hebt doorlopen voer je een eenvoudige query uit om te kijken of de hash al voorkomt.
John D op 08/08/2018 20:33:50:
Helemaal mee eens dat het geheugen vreet en (op den duur) veel te langzaam zal zijn. Ik wist alleen even geen andere oplossing.Hele bakken gegevens ophalen en in een array gooien en met php gaan vergelijken vreet geheugen en zal op termijn vastlopen. Dit soort vragen zijn met SQL op te lossen.
John D op 08/08/2018 20:33:50:
Alle (genoemde) kolommen, behalve de inkoop_prijs zoals eerder aangestipt, moeten gelijk zijn. Qua echt selecteren/ophalen is alleen het parrent_artikel_ID nodig.Heb je een reden om allerlei kolommen erbij te selecteren? Zoals hierboven al gezegd, een WHERE EXISTS of WHERE NOT EXISTS en dan uiteraard alleen op de key(s)??
Het komt eigenlijk neer op het volgende qua data;
Ik heb dus set_ID 5 en wil graag parrent_artikel_ID 617 hebben.
Hoe zou ik dit met WHERE EXISTS kunnen doen dan?
Gewijzigd op 09/08/2018 09:33:07 door Ramon van Dongen
Regels (met hetzelfde set_ID) die in tabel 1 staan kunnen óf niet óf 1 keer voorkomen in tabel 2.
Bij het afsluiten van de tool na het vullen van tabel 1 wordt namelijk gekeken of alle betreffende regels in tabel 1 exact overeenkomen met regels in tabel 2.
Zo ja; het parrent_artikel_ID gebruiken.
Zo nee; een nieuw parrent_artikel_ID aanmaken en tabel 2 vullen vanuit tabel 1.
2 parrent_artikel_ID's met exact dezelfde samenstelling is dus niet mogelijk.
Het lastige in de controle is dus dat alle kolommen gelijk moeten zijn maar ook nog eens voor alle regels (met hetzelfde set_ID/parrent_artikel_ID). Als bij 9 van de 10 regels de kolommen exact hetzelfde zijn maar 1 regel anders, is het totale dus niet exact hetzelfde.
Ik ga me eens goed verdiepen in EXISTS. Blijft nog een beetje een vage syntax voor me (nooit eerder gebruikt).
Gewijzigd op 09/08/2018 12:07:44 door Ramon van Dongen
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
<?php
// https://www.phphulp.nl/php/forum/topic/2-tabellen-vergelijken/102327
// $in: associative array of records
// return: hash
function calculateProductConfigurationHash($records) {
$columns = array('stap_ID', 'i_r', 'artikel_ID', 'volgnummer', 'waarde', 'aantal');
$out = [];
foreach ($records as $record) {
foreach ($columns as $column) {
$out[] = $record[$column];
}
}
return md5(implode('|', $out));
}
$records = array(
array('ID' => 100, 'set_ID' => '5', 'stap_ID' => '1', 'i_r' => '1', 'artikel_ID' => NULL, 'volgnummer' => NULL, 'waarde' => '3', 'aantal' => 34),
array('ID' => 101, 'set_ID' => '5', 'stap_ID' => NULL, 'i_r' => '2', 'artikel_ID' => '14', 'volgnummer' => '1', 'waarde' => 'bla bla 1', 'aantal' => 22),
array('ID' => 102, 'set_ID' => '5', 'stap_ID' => NULL, 'i_r' => '2', 'artikel_ID' => '20', 'volgnummer' => '2', 'waarde' => 'bla bla 2', 'aantal' => 12),
);
echo calculateProductConfigurationHash($records); // 57dc4f59463e7f016f26adf8c706969e
echo '<hr>';
$records = array(
array('ID' => 55, 'parrent_a' => '617', 'stap_ID' => '1', 'i_r' => '1', 'artikel_ID' => NULL, 'volgnummer' => NULL, 'waarde' => '3', 'aantal' => 34),
array('ID' => 56, 'parrent_a' => '617', 'stap_ID' => NULL, 'i_r' => '2', 'artikel_ID' => '14', 'volgnummer' => '1', 'waarde' => 'bla bla 1', 'aantal' => 22),
array('ID' => 57, 'parrent_a' => '617', 'stap_ID' => NULL, 'i_r' => '2', 'artikel_ID' => '20', 'volgnummer' => '2', 'waarde' => 'bla bla 2', 'aantal' => 12),
);
echo calculateProductConfigurationHash($records); // 57dc4f59463e7f016f26adf8c706969e
?>
// https://www.phphulp.nl/php/forum/topic/2-tabellen-vergelijken/102327
// $in: associative array of records
// return: hash
function calculateProductConfigurationHash($records) {
$columns = array('stap_ID', 'i_r', 'artikel_ID', 'volgnummer', 'waarde', 'aantal');
$out = [];
foreach ($records as $record) {
foreach ($columns as $column) {
$out[] = $record[$column];
}
}
return md5(implode('|', $out));
}
$records = array(
array('ID' => 100, 'set_ID' => '5', 'stap_ID' => '1', 'i_r' => '1', 'artikel_ID' => NULL, 'volgnummer' => NULL, 'waarde' => '3', 'aantal' => 34),
array('ID' => 101, 'set_ID' => '5', 'stap_ID' => NULL, 'i_r' => '2', 'artikel_ID' => '14', 'volgnummer' => '1', 'waarde' => 'bla bla 1', 'aantal' => 22),
array('ID' => 102, 'set_ID' => '5', 'stap_ID' => NULL, 'i_r' => '2', 'artikel_ID' => '20', 'volgnummer' => '2', 'waarde' => 'bla bla 2', 'aantal' => 12),
);
echo calculateProductConfigurationHash($records); // 57dc4f59463e7f016f26adf8c706969e
echo '<hr>';
$records = array(
array('ID' => 55, 'parrent_a' => '617', 'stap_ID' => '1', 'i_r' => '1', 'artikel_ID' => NULL, 'volgnummer' => NULL, 'waarde' => '3', 'aantal' => 34),
array('ID' => 56, 'parrent_a' => '617', 'stap_ID' => NULL, 'i_r' => '2', 'artikel_ID' => '14', 'volgnummer' => '1', 'waarde' => 'bla bla 1', 'aantal' => 22),
array('ID' => 57, 'parrent_a' => '617', 'stap_ID' => NULL, 'i_r' => '2', 'artikel_ID' => '20', 'volgnummer' => '2', 'waarde' => 'bla bla 2', 'aantal' => 12),
);
echo calculateProductConfigurationHash($records); // 57dc4f59463e7f016f26adf8c706969e
?>
Eenmalig script voor berekening van hashes in tabel 2:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<?php
$res = $db->query('SELECT ... FROM tabel2 ORDER BY ...');
$currentSet = false;
$batch = false;
while ($row = $res->fetch_assoc()) {
if ($currentSet != $row['parrent_a']) {
// store (previous) batch
if ($batch !== false) {
$hash = calculateProductConfigurationHash($batch);
// @todo store $currentSet (parrent_a) and $hash in parent table for future reference
}
// reset data
$currentSet = $row['parrent_a'];
$batch = [];
}
$batch[] = $row;
}
// store last batch
if (empty($batch) === false) {
$hash = calculateProductConfigurationHash($batch);
// @todo store $currentSet (parrent_a) and $hash in parent table for future reference
}
?>
$res = $db->query('SELECT ... FROM tabel2 ORDER BY ...');
$currentSet = false;
$batch = false;
while ($row = $res->fetch_assoc()) {
if ($currentSet != $row['parrent_a']) {
// store (previous) batch
if ($batch !== false) {
$hash = calculateProductConfigurationHash($batch);
// @todo store $currentSet (parrent_a) and $hash in parent table for future reference
}
// reset data
$currentSet = $row['parrent_a'];
$batch = [];
}
$batch[] = $row;
}
// store last batch
if (empty($batch) === false) {
$hash = calculateProductConfigurationHash($batch);
// @todo store $currentSet (parrent_a) and $hash in parent table for future reference
}
?>
Vervolgens kun je aan het eind van de wizard hashes controleren met de opgeslagen hashes in de parenttabel.
Dan moet de array uiteraard wel steeds met exact dezelfde volgorde opgebouwd worden. Maar dat zal weinig problemen opleveren verwacht ik.
Ramon van Dongen op 09/08/2018 19:08:24:
Dan moet de array uiteraard wel steeds met exact dezelfde volgorde opgebouwd worden.
Da's wel belangrijk ja :). En mocht de kolommen-combinatie die een unieke groepering samenstelt ooit veranderen, pas je $columns aan en draai je het scriptje opnieuw. Meer hoef je (theoretisch :)) niet te doen omdat alles toch van die zelfde functie gebruik maakt.