Running an insert script from multiple values

I’m trying to run a pretty simple script that does the following: Takes the id of a content module and assigns it to multiple locations

So say I click the link on a content module with ID of 123, I want to assign it to all multiple locations. In SQL I would just say :

INSERT INTO table (cont_id,loc_id) 
VALUES (123, select(id from location_table where active = 1))

I’m currently using this:

$pageID = $_GET['pageID'];

$assignPage = "
INSERT INTO locationContent(page_id, display_id)
    VALUES ( '$pageID', select(id from locations where active = 1))
    ON DUPLICATE KEY UPDATE active = 1
";

$performAssign = $mysqlConn->query($assignPage);

The issue I’m wondering about though, is do I need to put this into a foreach or while loop? If I were to just run as is, I feel like that would only work for one record

Answer

You can’t mix the INSERT INTO .. VALUES and INSERT INTO ... SELECT syntax, however SELECT constant, var FROM .. is possible like:

$assignPage = $mysqlConn->prepare("
INSERT INTO locationContent(page_id, display_id)
    SELECT :page as page_id, id FROM locations WHERE active = 1
    ON DUPLICATE KEY UPDATE active = 1
";

$performAssign = $assignPage->execute(array('page' =>$pageID));

Advertisement

Answer