2

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
);

The data I have 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 around inserting data into the tables so that the data I insert into the many type tables reference the tuple I've 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?

3

Chain your insert with data-modifying CTEs:

WITH ins0 AS (
   INSERT INTO siteInfo (siteName, siteHost, notes, ... )
   VALUES ('Sample', 'sample.com', 'note', ... );
   RETURNING siteid
   )
INSERT INTO siteLinks (siteID, URL)
SELECT siteid, 'test'
FROM   ins0;

Or use lastval() or currval():

BEGIN;

INSERT INTO siteInfo (siteName, siteHost, notes, ... )
VALUES ('Sample', 'sample.com', 'note', ... );

INSERT INTO siteLinks (siteID, URL)
VALUES (lastval(), 'test');

COMMIT;

Related answers:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.