Update a custom field

pconstantino
Topic Author
Offline
New Member

11 years 3 months ago #39995 by pconstantino

Update a custom field was created by pconstantino

I have in flexicontent around 2000 items.

In each item i have 2 custom text fields.

I added another custom text field.

How can i update the third field with a criteria in the first two fields?

Example:

If field1=party and field2=social i want field3=portugal.

I can do this one by one, but it is possible to do a mass update by phpmyadmin?

I know that the table to work around is _flexicontent_fields_item_relations.

Can anyone help me?

I apologize for my english.

Thanks

Please Log in or Create an account to join the conversation.

ggppdk
Offline
Administrator

11 years 3 months ago #39997 by ggppdk

Replied by ggppdk on topic Update a custom field

Hello,

for this it maybe better to use a custom PHP script to do it,
since it gives much power

1. You need to update existing records to value, or also insert values that are missing e.g. some items may not have value for the 3rd field

2. Is the 3rd field multi-valued (i mean the field that you need to update for the items that matched the criteria) ?

-- Flexicontent is Free but involves a big effort on our part.
Like the our support? (for a bug-free FC, despite having a long list of functions) Like the features? Like the ongoing development and future commitment to FLEXIcontent?
-- Add your voice to the FLEXIcontent JED listing with a 5-star...

Please Log in or Create an account to join the conversation.

pconstantino
Topic Author
Offline
New Member

11 years 3 months ago #40000 by pconstantino

Replied by pconstantino on topic Update a custom field

Hello,

1 - I have to update if exists and insert if not.

2 - for each item and fot one criteria, one value

Thanks

Please Log in or Create an account to join the conversation.

ggppdk
Offline
Administrator

11 years 3 months ago #40004 by ggppdk

Replied by ggppdk on topic Update a custom field

Hello

you can test with this:
but you will have to correct any syntax or logical errors
please backup before trying,
it should alter only table
flexicontent_fields_item_relations

but better also take full backup

Code:

<?php
function getItemIds($dbprex, $value, $field_id)
{
	$query = "SELECT item_id"
		." FROM `".$dbprex."_flexicontent_fields_item_relations` "
		." WHERE field_id=".$field_id." AND value='".mysql_real_escape_string($value)."'"
		;
	$result = mysql_query($query) or die(mysql_error());  
	
	$item_id_arr[] = array();
	while($row = mysql_fetch_array( $result )) {
		$item_id_arr[ $row['item_id'] ] = 1;
	}
	return $item_id_arr;
}
?>


<?
// 0. Database variables
$dbhost = 'localhost';
$dbuser = 'mydb_user';
$dbpass = 'mydb_pass';
$dbname = 'mydb_name';
$dbprex = 'mydb_prefix';   // e.g. 'jos';

// 1. Create MySQL Connection and set utf8 as encoding
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error connecting to host '.$dbhost.' as user '.$dbuser);
mysql_select_db($dbname);
mysql_query("SET NAMES 'utf8'");

// 2. Get ids of items having some value A for field A
$field_id = 54;
$value    = "party";
$item_id_arr_a = getItemIds($value, $field_id);
echo "# ". count($item_id_arr_a) ." ITEMS have field no ".$field_id." with value '".$value."' <br/>\n";

// 3. Get ids of items having some value B for field B
$field_id = 55;
$value    = "social";
$item_id_arr_b = getItemIds($value, $field_id);
echo "# ". count($item_id_arr_b) ." ITEMS have field no ".$field_id." with value '".$value."' <br/>\n";

// 4. Intersect the array
$item_id_arr_ab = array_intersect_key($item_id_arr_a, $item_id_arr_b);
echo "# ". count($item_id_arr_ab) ." ITEMS have both values<br/>\n";

// 5. Update field C
$field_id = 57;
$value    = "portugal";
if (count($item_id_arr_ab))
{
	$query = "DELETE FROM ``".$dbprex."_flexicontent_fields_item_relations`"
		." WHERE field_id=".$field_id." AND item_id IN (". implode(",", $item_id_arr_ab) .")"
		;
	$result = mysql_query($query) or die(mysql_error());
	echo "Deleted # ". mysql_affected_rows() ." old field values for field '".$field_id."'";
	
	foreach ($item_id_arr_ab as $item_id) {
		$query = "INSERT INTO ``".$dbprex."_flexicontent_fields_item_relations`"
			." (field_id, item_id, valueorder, value) "
			." VALUES (".$field_id.", ".$item_id.", 1, '".mysql_real_escape_string($value)."')"
			;
		$result = mysql_query($query) or die(mysql_error());
	}
	echo "Inserted # ". count($item_id_arr_ab) ." rows with value '".$value."' for field '".$field_id."' for found items <br/>\n";
}

?>

Please Log in or Create an account to join the conversation.