Junction table, insert into it

I have a junction table

CREATE TABLE providers(
id SMALLINT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
name VARCHAR(50),
...
UNIQUE KEY(email)
);

CREATE TABLE territories (
id SMALLINT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
state CHAR(2),
city VARCHAR(50)
);

CREATE TABLE provider_territory (
provider_id SMALLINT REFERENCES id (providers),
territory_id SMALLINT REFERENCES id (territories),
PRIMARY KEY (provider_id, territory_id)
);

to connect providers to territories, When the provider selects a territory (they can choose more than 1)
http://cdn.discourse.org/sitepoint/uploads/default/original/3X/d/4/d4812eec6502875a3aeedc742fad1b3421a2535e.jpg

After I insert a provider, how do I INSERT the City and State into the territories table (They can click the link to add more if they want),
I store each value in an array like city, and state to hold multiple entries but how ,do I put the entries in to a database, do I use a php foreach loop?

looks to me like you’re asking a php question, not a database question

I moved the thread to PHP

thannks…
I’m guessing it would go inside a foreach or for loop like

$arrlength = count($city);

for($x = 0; $x < $arrlength; $x++) {
//put the insert statement here
}

Ideally, you would create the insert statement inside the loop, and execute it once outside. But I don’t know how that works with pdo/mysqli and prepared statements.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.