I have a database setup with the following schema:
CREATE TABLE siteInfo (
siteID SERIAL NOT NULL,
siteName TEXT NOT NULL,
siteHost TEXT NOT NULL,
notes TEXT NOT NULL,
allowableCountries TEXT NOT NULL,
displayChance INT NOT NULL,
imagePath TEXT NOT NULL,
PRIMARY KEY (siteID)
);
CREATE TABLE siteLinks (
siteID SERIAL NOT NULL,
URL TEXT NOT NULL,
FOREIGN KEY (siteID) REFERENCES siteInfo(siteID) ON DELETE CASCADE
);
CREATE TABLE siteDemographicLinks (
siteID SERIAL NOT NULL,
URL TEXT NOT NULL,
country TEXT NOT NULL,
chance INT NOT NULL,
FOREIGN KEY (siteID) REFERENCES siteInfo(siteID) ON DELETE CASCADE
);
CREATE TABLE linkVisits (
siteID SERIAL NOT NULL,
rawVisit BIGINT NOT NULL,
redirects BIGINT NOT NULL,
FOREIGN KEY (siteID) REFERENCES siteInfo(siteID) ON DELETE CASCADE
);
Data from my form is in the following format:
array(7) {
["siteName"]=>string(9) "Site Name"
["siteHost"]=>string(9) "SIte Host"
["notes"]=>string(5) "Notes"
["links"]=>array(2) {[0]=>string(6) "Link 1" [1]=>string(6) "Link 2"}
["countries"]=>string(4) "None"
["displayChance"]=>string(2) "15"
["extraLink"]=>array(2) {
[0]=>array(3) {
["url"]=>string(7) "dSite 1"
["countries"]=>string(4) "NONE"
["chance"]=>string(2) "11"
}
[1]=>array(3) {
["url"]=>string(6) "dSite2"
["countries"]=>string(4) "None"
["chance"]=>string(2) "13"
}
}
}
How would I go abound inserting data into the many type tables so they reference tuples inserted into the one type table?
I have this query constructed:
INSERT INTO siteInfo (siteName, siteHost, notes, allowableCountries, displayChance, imagePath)
VALUES ('Sample', 'sample.com', 'note', 'NONE', '50', '../var');
I would then need to insert data into the siteLinks
table, which is related to the entry already in the siteInfo
table.
INSERT INTO siteLinks (siteID, URL)
VALUES ((SELECT siteid FROM siteinfo WHERE sitename='Sample'), 'test');
Is there a better way of doing this?
Best Answer
Chain your insert with data-modifying CTEs:
Or use
lastval()
orcurrval()
:Related: