Json array omzetten naar een SQL INTO statement
Deze string wil ik inserten in een MySQL database waar de tabel en de velden al bestaan.
Vraag is hoe ik de $result string kan plaatsen in een SQL INTO statement met gebruik makend van dat de veldnaam en veldwaarde in de $result staan.
Bedankt.
$result /* string */ =
{"d":{"ClientId":1090,"Passnumber":"1096","TitleId":2,"Title":"Mevr.","Initials":"","Firstname":"Wouter","Middlename":"","Lastname":"Verweij","DDName":"","DDNumber":"","Address":"Hogedijk","Housenumber":"64","Zipcode":"2861 GD","City":"Bergambacht","Birthdate":"1960-10-22","PhonePrivate":"","PhoneWork":"","PhoneMobile":"","PhoneFax":"","Email":"[email protected]","Accountnumber":"","Gender":"M","Id":"","SocSecNumber":"","District":"","Password":"psw","Credits":0,"Bonus":0,"Memberships":[{"MembershipDefId":20,"Description":"All in clusief","ShortDescription":"All inclusief","StartDate":"2012-11-03","PaymentEndDate":"2012-12-01","ContractEndDate":"2012-12-01","CancelledAt":"","CancelledPer":"","CancelReason":"","Amount":63.75,"Discount":"","LastVisit":""},{"MembershipDefId":1,"Description":"Onbeperkt 12 maanden","ShortDescription":"Onbeperkt 12 maanden","StartDate":"2012-11-03","PaymentEndDate":"2012-12-01","ContractEndDate":"2012-12-01","CancelledAt":"","CancelledPer":"","CancelReason":"","Amount":40.75,"Discount":"","LastVisit":""},{"MembershipDefId":15,"Description":"65+ woensdagmiddag","ShortDescription":"65+ woensdagmiddag","StartDate":"2012-11-03","PaymentEndDate":"2012-12-01","ContractEndDate":"2012-12-01","CancelledAt":"","CancelledPer":"","CancelReason":"","Amount":28.25,"Discount":"","LastVisit":""}]}}
Gewijzigd op 08/11/2012 16:28:03 door Wouter verweij
De persoonlijke informatie die je geeft is ook bedenkelijk. Ik heb in ieder geval je wachtwoord eruit gehaald. Dat kun jij ook beter doen.
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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
<?php
$result /* string */ = '{"d":{"ClientId":1090,"Passnumber":"1090","TitleId":2,"Title":"Mevr.","Initials":"","Firstname":"Wouter","Middlename":"","Lastname":"Verweij","DDName":"","DDNumber":"","Address":"Hogedijk","Housenumber":"64","Zipcode":"2861 GD","City":"Bergambacht","Birthdate":"1960-10-22","PhonePrivate":"","PhoneWork":"","PhoneMobile":"","PhoneFax":"","Email":"[email protected]","Accountnumber":"","Gender":"M","Id":"","SocSecNumber":"","District":"","Password":"xxxxx","Credits":0,"Bonus":0,"Memberships":[{"MembershipDefId":20,"Description":"All in clusief","ShortDescription":"All inclusief","StartDate":"2012-11-03","PaymentEndDate":"2012-12-01","ContractEndDate":"2012-12-01","CancelledAt":"","CancelledPer":"","CancelReason":"","Amount":63.75,"Discount":"","LastVisit":""},{"MembershipDefId":1,"Description":"Onbeperkt 12 maanden","ShortDescription":"Onbeperkt 12 maanden","StartDate":"2012-11-03","PaymentEndDate":"2012-12-01","ContractEndDate":"2012-12-01","CancelledAt":"","CancelledPer":"","CancelReason":"","Amount":40.75,"Discount":"","LastVisit":""},{"MembershipDefId":15,"Description":"65+ woensdagmiddag","ShortDescription":"65+ woensdagmiddag","StartDate":"2012-11-03","PaymentEndDate":"2012-12-01","ContractEndDate":"2012-12-01","CancelledAt":"","CancelledPer":"","CancelReason":"","Amount":28.25,"Discount":"","LastVisit":""}]}}';
$reeks = json_decode($result, true);
/** maakInsert is een functie die SQL insert statements maakt
* van een ingevoerd array.
* In: associatief array; de key namen worden omgezet in column namen.
* sub-arrays worden inserts in sub-tabellen.
* Uit: een array met insert statements
* Return: void
* Beperking: de tabelnamen zijn er een beetje ingefrutseld.
*/
function maakInsert(array $invoer,array &$uitvoer) {
$kolommen=''; //hier komen de column names in
$waarden=''; //hier komen de values in
$opbouw = ''; //hier wordt het inser statement opgebouwd
$eerstekeer=true; //loop control: begin van string of toevoeging
//Doorloop de array
foreach ($invoer as $col=>$val) {
if (is_array($val)) { //sub-array aangetroffen
maakInsert($val, $uitvoer); //maak daar een sub-insert van
} else { //geen subarray dus gewoon verwerken
if (is_null($val) || ($val == '')) { //lege waarden door NULL vervangen
$waarde = 'NULL';
} else {
$waarde = "'" . $val . "'"; //..anders quoten.
}
if ($eerstekeer) { //de eerste keer gewoon de waarden invullen
$kolommen = mysql_real_escape_string($col);
$waarden = mysql_real_escape_string($waarde);
$eerstekeer = false;
} else { //..volgende keren toevoegen met een komma ertussen
$kolommen .= ', ' . mysql_real_escape_string($col);
$waarden .= ', ' . mysql_real_escape_string($waarde);
}
}
}
if (strlen($kolommen) > 0) { //alleen als er kolommen zijn gevonden
if (stripos($kolommen, 'ClientId') !== false) { //tabel zal wel client heten
$opbouw = 'insert into CLIENT ';
} elseif(stripos($kolommen, 'MembershipDefId') !== false) { //tabel zal wel membership heten
$opbouw = 'insert into MEMBERSHIPDEF ';
} /* else {
$opbouw = 'insert into ONBEKENDETABEL ';
} */
$opbouw .= '(' . $kolommen . ') values (' . $waarden . ')';
$uitvoer[] = $opbouw;
}
}
//Test: laat insert statements in array $sqlInserts zetten.
$sqlInserts = array();
maakInsert($reeks, $sqlInserts);
foreach ($sqlInserts as $insert) {
echo "$insert\n\n";
}
?>
Uitvoer:
insert into MEMBERSHIPDEF (MembershipDefId, Description, ShortDescription, StartDate, PaymentEndDate, ContractEndDate, CancelledAt, CancelledPer, CancelReason, Amount, Discount, LastVisit) values ('20', 'All in clusief', 'All inclusief', '2012-11-03', '2012-12-01', '2012-12-01', NULL, NULL, NULL, '63.75', NULL, NULL)
insert into MEMBERSHIPDEF (MembershipDefId, Description, ShortDescription, StartDate, PaymentEndDate, ContractEndDate, CancelledAt, CancelledPer, CancelReason, Amount, Discount, LastVisit) values ('1', 'Onbeperkt 12 maanden', 'Onbeperkt 12 maanden', '2012-11-03', '2012-12-01', '2012-12-01', NULL, NULL, NULL, '40.75', NULL, NULL)
insert into MEMBERSHIPDEF (MembershipDefId, Description, ShortDescription, StartDate, PaymentEndDate, ContractEndDate, CancelledAt, CancelledPer, CancelReason, Amount, Discount, LastVisit) values ('15', '65+ woensdagmiddag', '65+ woensdagmiddag', '2012-11-03', '2012-12-01', '2012-12-01', NULL, NULL, NULL, '28.25', NULL, NULL)
insert into CLIENT (ClientId, Passnumber, TitleId, Title, Initials, Firstname, Middlename, Lastname, DDName, DDNumber, Address, Housenumber, Zipcode, City, Birthdate, PhonePrivate, PhoneWork, PhoneMobile, PhoneFax, Email, Accountnumber, Gender, Id, SocSecNumber, District, Password, Credits, Bonus) values ('1090', '1090', '2', 'Mevr.', NULL, 'Wouter', NULL, 'Verweij', NULL, NULL, 'Hogedijk', '64', '2861 GD', 'Bergambacht', '1960-10-22', NULL, NULL, NULL, NULL, '[email protected]', NULL, 'M', NULL, NULL, NULL, 'xxxxx', NULL, NULL)
$result /* string */ = '{"d":{"ClientId":1090,"Passnumber":"1090","TitleId":2,"Title":"Mevr.","Initials":"","Firstname":"Wouter","Middlename":"","Lastname":"Verweij","DDName":"","DDNumber":"","Address":"Hogedijk","Housenumber":"64","Zipcode":"2861 GD","City":"Bergambacht","Birthdate":"1960-10-22","PhonePrivate":"","PhoneWork":"","PhoneMobile":"","PhoneFax":"","Email":"[email protected]","Accountnumber":"","Gender":"M","Id":"","SocSecNumber":"","District":"","Password":"xxxxx","Credits":0,"Bonus":0,"Memberships":[{"MembershipDefId":20,"Description":"All in clusief","ShortDescription":"All inclusief","StartDate":"2012-11-03","PaymentEndDate":"2012-12-01","ContractEndDate":"2012-12-01","CancelledAt":"","CancelledPer":"","CancelReason":"","Amount":63.75,"Discount":"","LastVisit":""},{"MembershipDefId":1,"Description":"Onbeperkt 12 maanden","ShortDescription":"Onbeperkt 12 maanden","StartDate":"2012-11-03","PaymentEndDate":"2012-12-01","ContractEndDate":"2012-12-01","CancelledAt":"","CancelledPer":"","CancelReason":"","Amount":40.75,"Discount":"","LastVisit":""},{"MembershipDefId":15,"Description":"65+ woensdagmiddag","ShortDescription":"65+ woensdagmiddag","StartDate":"2012-11-03","PaymentEndDate":"2012-12-01","ContractEndDate":"2012-12-01","CancelledAt":"","CancelledPer":"","CancelReason":"","Amount":28.25,"Discount":"","LastVisit":""}]}}';
$reeks = json_decode($result, true);
/** maakInsert is een functie die SQL insert statements maakt
* van een ingevoerd array.
* In: associatief array; de key namen worden omgezet in column namen.
* sub-arrays worden inserts in sub-tabellen.
* Uit: een array met insert statements
* Return: void
* Beperking: de tabelnamen zijn er een beetje ingefrutseld.
*/
function maakInsert(array $invoer,array &$uitvoer) {
$kolommen=''; //hier komen de column names in
$waarden=''; //hier komen de values in
$opbouw = ''; //hier wordt het inser statement opgebouwd
$eerstekeer=true; //loop control: begin van string of toevoeging
//Doorloop de array
foreach ($invoer as $col=>$val) {
if (is_array($val)) { //sub-array aangetroffen
maakInsert($val, $uitvoer); //maak daar een sub-insert van
} else { //geen subarray dus gewoon verwerken
if (is_null($val) || ($val == '')) { //lege waarden door NULL vervangen
$waarde = 'NULL';
} else {
$waarde = "'" . $val . "'"; //..anders quoten.
}
if ($eerstekeer) { //de eerste keer gewoon de waarden invullen
$kolommen = mysql_real_escape_string($col);
$waarden = mysql_real_escape_string($waarde);
$eerstekeer = false;
} else { //..volgende keren toevoegen met een komma ertussen
$kolommen .= ', ' . mysql_real_escape_string($col);
$waarden .= ', ' . mysql_real_escape_string($waarde);
}
}
}
if (strlen($kolommen) > 0) { //alleen als er kolommen zijn gevonden
if (stripos($kolommen, 'ClientId') !== false) { //tabel zal wel client heten
$opbouw = 'insert into CLIENT ';
} elseif(stripos($kolommen, 'MembershipDefId') !== false) { //tabel zal wel membership heten
$opbouw = 'insert into MEMBERSHIPDEF ';
} /* else {
$opbouw = 'insert into ONBEKENDETABEL ';
} */
$opbouw .= '(' . $kolommen . ') values (' . $waarden . ')';
$uitvoer[] = $opbouw;
}
}
//Test: laat insert statements in array $sqlInserts zetten.
$sqlInserts = array();
maakInsert($reeks, $sqlInserts);
foreach ($sqlInserts as $insert) {
echo "$insert\n\n";
}
?>
Uitvoer:
insert into MEMBERSHIPDEF (MembershipDefId, Description, ShortDescription, StartDate, PaymentEndDate, ContractEndDate, CancelledAt, CancelledPer, CancelReason, Amount, Discount, LastVisit) values ('20', 'All in clusief', 'All inclusief', '2012-11-03', '2012-12-01', '2012-12-01', NULL, NULL, NULL, '63.75', NULL, NULL)
insert into MEMBERSHIPDEF (MembershipDefId, Description, ShortDescription, StartDate, PaymentEndDate, ContractEndDate, CancelledAt, CancelledPer, CancelReason, Amount, Discount, LastVisit) values ('1', 'Onbeperkt 12 maanden', 'Onbeperkt 12 maanden', '2012-11-03', '2012-12-01', '2012-12-01', NULL, NULL, NULL, '40.75', NULL, NULL)
insert into MEMBERSHIPDEF (MembershipDefId, Description, ShortDescription, StartDate, PaymentEndDate, ContractEndDate, CancelledAt, CancelledPer, CancelReason, Amount, Discount, LastVisit) values ('15', '65+ woensdagmiddag', '65+ woensdagmiddag', '2012-11-03', '2012-12-01', '2012-12-01', NULL, NULL, NULL, '28.25', NULL, NULL)
insert into CLIENT (ClientId, Passnumber, TitleId, Title, Initials, Firstname, Middlename, Lastname, DDName, DDNumber, Address, Housenumber, Zipcode, City, Birthdate, PhonePrivate, PhoneWork, PhoneMobile, PhoneFax, Email, Accountnumber, Gender, Id, SocSecNumber, District, Password, Credits, Bonus) values ('1090', '1090', '2', 'Mevr.', NULL, 'Wouter', NULL, 'Verweij', NULL, NULL, 'Hogedijk', '64', '2861 GD', 'Bergambacht', '1960-10-22', NULL, NULL, NULL, NULL, '[email protected]', NULL, 'M', NULL, NULL, NULL, 'xxxxx', NULL, NULL)
Toegift: ik heb toch maar die real_escape_string toegevoegd.
Gewijzigd op 08/11/2012 13:32:04 door Ivo Breeden
Bedankt voor je snelle puzzelwerk.
De tabelnaam is Klanten. Ik zal deze in de code invullen en testen.
Verder is inderdaad ClienId de sleutel van de Klanten tabel. Die komt mee in de JSON structuur. Ik zal daarom van te voren checken of deze al in de child tabel zit. Kan dus een insert of update statement worden afhankelijk of de betreffende ClientId al voorkomt.
Ook voor de tabel Membership is ClientId de sleutel.
En inderdaad password heb ik eruit gehaald !!
De toegift die je geeft is: real_escape_string --> wat doet dit statement?
Nogmaals bedankt.
Gr
Wouter
Ook geldige input van data kan wel eens problemen opleveren als daar bijv. quotes inzitten: bijvoorbeeld: "'s-Hertogenbosch", en dat wordt ook opgelost met *_real_escape_string().
Alle input die van buiten komt en die je in je database stopt moet je bewerken met *_real_escape_string().
Groet,
Ivo
Gewijzigd op 08/11/2012 18:09:45 door Ivo Breeden
nog een vraag:
Sommige velden zijn integers bv ClientId en sommige strings. Ik zie in het SQL statement dat het allemaal strings zijn. Maakt dat nog uit?
Gr
Wouter Verweij
Nee het meest ideaal zou zijn om de strings te single-quoten en de integers niet. Maar ik weet niet hoe je dat onderscheid moet aanpakken. Maar MySQL heeft er in ieder geval geeen probleem mee, die zet strings automatisch om naar integers als dat nodig is.
Groet,
Ivo