My table (pageroles) records who are the authors and editors of content pages.
Some pages have a single author and editor and some have multiple.
roles_id records if they are an author or editor. Authors are "2" and Editors are "4"
//An example of a content page with 1 editor and 1 author creates two rows in the table.
content_id user_id roles_id
23 153 2
23 58 4
//An example of a content page with multiple authors and a single editor:
content_id user_id roles_id
23 153 2
23 265 2
23 58 4
Now, when I need to update roles, they are stored temporarily in another table called "updatesroles"
This table doesn't record roles the same way. It records all authors in one column called "authors"
//An example of a content page with multiple authors and editors in updates roles
content_id authors editors
23 153,789 656,55,4,789
As you can see, it records them with commas.
Now, I need to write a script that will insert values from updatedroles to pageroles
Code: Select all
// CHECK TO SEE IF THERE ARE NEW ROLEPLAYERS
$checkforchangesquery = "SELECT COUNT(*) FROM updatedroles WHERE authors IS NOT NULL AND content_id = ".$_REQUEST['cid'][0];
// IF THERE ARE NEW ROLEPLAYERS, EXECUTE THE SCRIPT
if ($checkforchangesquery > 0)
{
//THIS IS FOR AUTHORS ONLY, I HAVE TO RUN THE SAME THING AGAIN FOR EDITORS
//Select new authors for this content article
$query = "SELECT authors from updatedroles WHERE authors IS NOT NULL AND content_id = ".$_REQUEST['cid'][0];
$db->setQuery($query);
$authorsquery = $db->loadAssocList();
//explode
if (count($authorsquery[0]) > 0)
{
$authors = explode(',', $authorsquery[0]['authors']);
}
//Duplicate authors and insert them into pageroles
foreach ($authors as $author) {
$query = "INSERT INTO pageroles (content_id, user_id, roles_id) VALUES ('".$_REQUEST['cid'][0]."', '".$author->authors."', '2')";
$db->setQuery($query);
$db->query();
}
}
else
{
//script to retain current roles here -> this works.
}
I need to be able to insert values into pageroles one row for each author and one row for each editor.