Kolom importeren in bestaande tabel

Frank Nietbelangrijk op 09/02/2022 16:32:50

Ik zou zeggen Of schrijf een php script die de hits uit de oude tabel inleest en in de nieuwe tabel wegschrijft of doe het met een tool zoals phpMyAdmin door beiden tabellen (desnoods met 1 met een aangepaste naam) in je database te schrijven waarna je de waarden van de oude tabel kopieert naar de nieuwe tabel:

UPDATE nieuweTabel t1
JOIN OudeTabel t2 ON t1.id = t2.id
SET t1.hits = t2.hits

Sorry voor mijn (zeer) late reactie.
Een klein testje leert dat deze code een eenvoudige manier is om de hits over te zetten. Ik loop echter tegen het volgende probleem aan: Bij het migreren van mijn artikelen zijn de ID's niet gelijk gebleven maar opgehoogd. Ik probeer nu de ID's te resetten met de volgende code:

SET @autoid := 0;
UPDATE `jos_content` SET id = @autoid := (@autoid+1);
ALTER TABLE `jos_content` auto_increment = 1;

Bij de nieuwe tabel gaat dit goed echter bij de oude tabel krijg ik de melding:

SQL-query: UPDATE `jos_content` SET id = @autoid := (@autoid+1);
MySQL meldt: #1062 - Dubbele ingang '3' voor zoeksleutel 'PRIMARY'

Heb je enig idee hoe dit op te lossen is?

Je kan eventueel eerst alle autoid's negatief maken.

UPDATE `jos_content` SET id = -id;

Wat je doet is de records aflopen zoals mysql ze vind. De 1° krijgt het nieuwe id 1, Het 2° 2 en zo verder. echter bij 3 is er al een ander record met id 3 en hier loopt het dus op vast.

Met dergelijke code moet je wel opletten dat je je joins niet gaat verminken.

Het negatief maken van de ID werkt bij mij niet goed. Het werkt wel als ik een ID kies die ver buiten mijn range ligt b.v. 10000 en daarna weer terugga naar (vanaf) 1.

Dit doe ik ook met mijn "oude" jos_content in de hoop dat mijn ID's gelijk worden. Helaas komen er hier en daar verschuivingen voor. Ik ben vrij lang bezig geweest om de volgorde van artikelen en ID's gelijk te krijgen en daarna op volgorde te resetten. Dit lukt steeds niet helemaal.

Mijn vraag is: Is het mogelijk om ID's te resetten in datumvolgorde?

De volgorde van de ID's heeft niets te maken met een zekere sorteervolgorde.

ID's zijn uit praktisch oogpunt met auto-increment oplopend uitgegeven.
Maar je had ze ook uit kunnen geven als

10, daarna 9, 8 tot aan 1
daarna
20, en aflopend tot 11.

Hoofdzaak is dat ze uniek zijn. En je gaat ze zeker niet aanpassen met als doel om te sorteren.

Kijk naar je BSN nummer: dat is een uniek nummer van 9 cijfers.
Maar ze worden uitgegeven in een compleet willekeurige volgorde.
Zelfs voor een tweeling kan dat nummer voor de een met 1234 beginnen en de ander met 9876

Maakt ook niet uit, want het nummer is niet bedoeld om leeftijden of geslacht danwel geboorteplaats uit af te kunnen leiden.

Net zo met je ID: dat nummer kan af en toe eens in een url opduiken, maar boeit verder niet.

Alleen als je door een foute query je id's hebt veranderd, kan het nuttig zijn om te proberen die terug te zetten. Maar dan terug naar wat het was (of je pakt een backup).

Maar als je vorig jaar een boek in je webshop verkocht met ID 1234 en omdat je na een besluit om boeken niet langer op alfabetische volgorde van titel, maar van schrijver te sorteren dit ID aanpast naar 9887

Dan ziet de klant in zijn bestel geschiedenis ineens dat hij een heel ander boek besteld zou hebben??

Sowieso:
Als je goed met FK restricties werkt, zou het al tot problemen moeten leiden als je ID ineens verdwijnt.
(of je hele database zou in alle tabellen het betreffende ID moeten updaten)

Bottom-line: van je ID kolom blijf je gewoon af: die gebruik je niet om aan klant of sitebheerder te tonen.

[size=xsmall]Toevoeging op 24/03/2022 09:40:03:[/size]

Oh ja:
en ORDER BY idkolom

zou je niet moeten gebruiken.
Je sorteert op naam, datum (invoerdatum, geboortedatum, verkoopdatum), of op prijs, kleur of wat voor eigenschap dan ook.

ID is alleen voor
WHERE idkolom = 1234;

Het is een logisch verhaal maar ik schiet er niet veel mee op. Mijn doel is het overzetten van hits van de ene naar de andere tabel (zie mijn beschrijving bij Dick VanBruggen, 09/02/2022 12:18:00). Omdat de code die Frank Nietbelangrijk
09/02/2022 16:32:50 geeft prima werkt is het van belang dat de ID's per artikel gelijk zijn (hoeft dus niet per se op volgorde). Het enige wat per artikel gelijk is, is de datum - tijd vandaar mijn vraag: Is het mogelijk om ID's te resetten in datumvolgorde?

Dan doe je dat toch eenmalig met een php bruggetje..

Zorg voor een nieuwe lege tabel met alle kolommen er in maar zonder records.

<?php
$id = 1;

// lees alle records in op volgorde van datum
$stmt = $pdo->query("SELECT * FROM old_table ORDER BY datum ASC"); // of DESC ?

// in deze volgorde vul een andere nieuwe nog lege tabel met dezelfde data
while ($row = $stmt->fetch()) {
$sql = "INSERT INTO new_table (id, name, datum) VALUES (?,?,?)";
$stmt2 = $pdo->prepare($sql);
$stmt2->execute([$id++, $row['name'], $row['datum']]);
}
?>

mits kolom "id" autoincrement is, zou dat ook moeten kunnen met de query:

INSERT INTO new_table (id, name, datum) 
SELECT null, name, datum FROM old_table ORDER BY datum ASC

Maar dat gaat er wel vanuit dat de reeks die je wilt matchen
a) geen dubbele datum-tijd combi bevat
b) geen gaten in de reeks heeft.

Dan zou ik eerder gaan voor

INSERT INTO new_table (id, naam, datumtijd, overige, kolommen)
SELECT oude_tabel.id,
invoer_tabel.naam, invoer_tabel.datumtijd, invoer_tabel.overige, invoer_tabel.kolommen
FROM invoer_tabel
JOIN oude_tabel ON oude_tabel.datumtijd = invoer_tabel.datumtijd

Daarmee zou je een nieuwe tabel op kunnen bouwen die uiteindelijk dan de aangepaste versie is van je oude_tabel. En dan is uiteindelijk alleen een rename nodig.

Als je je ID's hebt verknald maar je hebt nog unieke datums, dan is het aanmaken van nieuwe ID's nog mogelijk.

Gebruik hiervoor de ROW_NUMBER() functie van MySQL om een nieuwe (of bestaande) kolom met ID's te vullen. Voorbeeld:

CREATE TABLE `mijntabel` AS
SELECT CURRENT_DATE AS `datum`
UNION SELECT DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)
UNION SELECT DATE_SUB(CURRENT_DATE, INTERVAL 2 DAY);

ALTER TABLE `mijntabel` ADD COLUMN `id` int;

CREATE TABLE `mijntabel_id` AS
SELECT ROW_NUMBER() OVER () AS `id`, `datum`
FROM `mijntabel`;

UPDATE `mijntabel`
  INNER JOIN `mijntabel_id` ON `mijntabel`.`datum` = `mijntabel_id`.`datum`
  SET `mijntabel`.`id` = `mijntabel_id`.`id`;

DROP TABLE `mijntabel_id`;

Achteraf zou je nog met de MAX() -functie de sequence goed kunnen zetten, bijvoorbeeld zo:

ALTER TABLE `mijntabel` ADD PRIMARY KEY (`id`);
ALTER TABLE `mijntabel` MODIFY `id` int NOT NULL AUTO_INCREMENT;
SELECT MAX(`id`) FROM `mijntabel`;  -- 3
ALTER TABLE `mijntabel` AUTO_INCREMENT = 4;

1. Frank Nietbelangrijk
Als PHP beginner loop je tegen veel zaken aan. Ik heb onderstaande code opgenomen in een ,php bestand in de map van de website. Echter ik krijg een foutmelding. Ik heb ‘naam’ en ‘datum’ hernoemd naar ‘title’ en ‘created’. Ook heb ik de databasenaam toegevoegd. Ik doe vast iets verkeerd !

<?php
$id = 1;

// lees alle records in op volgorde van datum
$stmt = $pdo->query("SELECT * FROM 'deb27035_joomla'.'kjmm_content-oud' ORDER BY created ASC"); // of DESC ?

// in deze volgorde vul een andere nieuwe nog lege tabel met dezelfde data
while ($row = $stmt->fetch()) {
    $sql = "INSERT INTO 'deb27035_joomla'.'kjmm_content-nieuw' (id, title, created) VALUES (?,?,?)";
    $stmt2 = $pdo->prepare($sql);
    $stmt2->execute([$id++, $row['title'], $row['created']]);
}
?>

2. Ivo P
Met de volgende code kom ik een heel eind, maar net niet helemaal:

INSERT INTO `kjmm_content-nwtmp` (id, title, created)
SELECT null, title, created FROM `kjmm_content-nieuw` ORDER BY created ASC

INSERT INTO `kjmm_content-oudtmp` (id, title, created, hits)
SELECT null, title, created, hits FROM `kjmm_content-oud` ORDER BY created ASC 

en vervolgens de hits overzetten:

UPDATE `kjmm_content-nwtmp` t1
JOIN `kjmm_content-oudtmp` t2 ON t1.id = t2.id
SET t1.hits = t2.hits 

Ik heb nu echter alleen de kolommen id, title, created en hits. Ik ben er nog niet achter gekomen hoe ik al de andere kolommen kan overbrengen naar de nieuwe tabel. Of moet ik alle kolommen apart gaan benoemen zoals je aangeeft in:

INSERT INTO new_table (id, naam, datumtijd, overige, kolommen)

3. Ad Fundum
Bij de uitvoering van jouw code bij de SQL-tab en de benaming aangepast voor mijn tabellen krijg ik bij:

SELECT ROW_NUMBER() OVER () AS `id`, `datum`

de volgende foutmeldingen:

Een alias was al eerder gevonden. (nabij ‘id’)
Een alias werd verwacht. (nabij )
Onverwacht token. (nabij ‘id’)
Onverwacht token. (nabij , )
Onverwacht token. (nabij ‘datum’)

Het resultaat is:
Datum id
2022-03-30 3
2022-03-29 1
2022-03-28 2

Ah oke we moeten je dus wat meer in het zadel van het PHP-paard helpen.

Wat ik liet zien was maar een voorbeeld snippet.

Voordat je met dat voorbeeld gaat werken zul je eerst verbinding moeten maken met je database:

<?php
try {
$pdo = new PDO('mysql:host=localhost;dbname=databaseNaam', 'username', 'password', array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
));
}
// wijzig databaseNaam, username en password naar jouw mysql instellingen.
?>

Daarnaast is de algemene regel dat je er voor zorgt dat je script fouten laat zien. Die staan namelijk op de hosting standaard uitgeschakeld. Op je dashboard van je provider kun je deze waarschijnlijk aanzetten maar je kunt ook de foutafhandeling aanzetten door het volgende boven in je script te plakken:
<?php
ini_set('display_errors', 1);
error_reporting(E_ALL);
?>

In totaal krijg je dan:
<?php
ini_set('display_errors', 1);
error_reporting(E_ALL);

try {
$pdo = new PDO('mysql:host=localhost;dbname=databaseNaam', 'username', 'password', array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
));
}

$id = 1;

// lees alle records in op volgorde van datum
$stmt = $pdo->query("SELECT * FROM 'deb27035_joomla'.'kjmm_content-oud' ORDER BY created ASC"); // of DESC ?

// in deze volgorde vul een andere nieuwe nog lege tabel met dezelfde data
while ($row = $stmt->fetch()) {
$sql = "INSERT INTO 'deb27035_joomla'.'kjmm_content-nieuw' (id, title, created) VALUES (?,?,?)";
$stmt2 = $pdo->prepare($sql);
$stmt2->execute([$id++, $row['title'], $row['created']]);
}


?>



[size=xsmall]Toevoeging op 30/03/2022 18:46:40:[/size]

Omdat je zegt dat je een PHP bestand hebt aangemaakt in de map van je website even het volgende:

GEWIS jezelf er van dat je een goede BACKUP hebt van zowel je bestanden en de data uit de database alvorens je gaat lopen "knoeien" op je productie server!

Wanneer je het helemaal goed wilt doen dan richt je een testomgeving in waarin een exacte kopie staat van je website en dan ga je daarmee aan het sleutelen!