Help with explode and foreach in one function

[17seconds]

Hi there

 

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 "updatedroles"

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 updatedroles

content_id authors editors

23 153,789 656,55,4,789

 

 

As you can see, it records them with commas all on one row.

Now, I need to write a script that will insert values from updatedroles to pageroles

 

 

 

// 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 ('".$author->content_id."', '".$author->authors."', '2')";
$db->setQuery($query);
$db->query();
}

}

else
{
//script to retain current roles here -> this works.
}

 

Can someone please help me with the explode and foreach?

I need to be able to insert values into pageroles one row for each author and one row for each editor.

 

[17seconds]

Oops...just changed something in the INSERT command... this doesnt fix the problem, just needed to fix it so people wont pick this out.

 

 

$query = "INSERT INTO pageroles (content_id, user_id, roles_id)  VALUES ('".$_REQUEST['cid'][0]."', '".$author->authors."', '2')";

[Alt_F4]

Hi,

 

I'm not exactly sure where you are having problems?

 

If you wanted to do less trips to the database (and assuming you are using mysql) you could do the following:

 

please note that i have not tested this as i am too lazy to add some tables and fields to a database 

$update_query="";
$query = "SELECT authors FROM updatedroles WHERE authors IS NOT NULL AND content_id = ".$_REQUEST['cid'][0]; 
$result = mysql_query($query,$db);
$num_results = mysql_num_rows($result)

if($num_results > 0) {
while($row=mysql_fetch_array($results)) {
	$authors = explode(",",$row['authors']);

	foreach($authors as $author) {
		$update_query .= "INSERT INTO pageroles (content_id, user_id, roles_id)	VALUES ('".$_REQUEST['cid'][0]."', '".$author."', '2') ";	
		//please note the space at the end of the query string needs to be there
	}		 
}
}

//update database
mysql_query($update_query,$db);

 

if this doesn't help you at all can you explain to me what the problem is that you have. Your code seems ok - are the values not being saved in the database?