Updating meta descriptions

dave_finlayson

Wonder if someone can help. I'm looking to automatically update our meta descriptions with the first 150 characters of our product descriptions. My php is fine (well ok anyway) but I've never used it to insert into databases so, before I really f**k it up I'm wondering if anyone can give me some basic code.

I've got so far as getting the data into the format I want using the code below, I just need to know how to then take that data and insert it into the meta description field in Interspire (prodmetadesc).

Anyone able to help?


<?
$hostnamesp = \"xxxxxx\";
$databasesp = \"xxxxxx\";
$usernamesp = \"xxxxxx\";
$passwordsp = \"xxxxxx\";
@$intdata = mysql_connect($hostnamesp, $usernamesp, $passwordsp) or die ('Error connecting to database');

mysql_select_db($databasesp, $intdata);
$query = \"SELECT * FROM isc_products\";
$result = mysql_query($query) or die('Could not connect to database');
while($row = mysql_fetch_array($result)){
	$clean_desc = strip_tags($row['proddesc']);
	$trimmed = str_replace(\"\n\", \" \", $clean_desc);
	$str = wordwrap($trimmed, 150);
	$str = explode(\"\n\", $str);
	$str = $str[0];
	echo $row['prodname']. \" \". $str;
	echo \"<br />\";
	}
?>

Gabriel Crowe

How about just doing in entirely in mysql?

UPDATE isc_product SET $Meta-desc-col-name = LEFT(proddesc, 150);

This does not take into consideration html and line breaks in the product description. Also, I dont even know the col name for the meta desc.

Here is a nother way:
https://www.interspire.com/forum/showthread.php?t=16688

What you could do is update the records as you loop through them.


$query = \"SELECT * FROM isc_products\"; 
$result = mysql_query($query) or die('Could not connect to database'); 
while($row = mysql_fetch_array($result)){ 
    $clean_desc = strip_tags($row['proddesc']); 
    $trimmed = str_replace(\"\n\", \" \", $clean_desc); 
    $str = wordwrap($trimmed, 150); 
    $str = explode(\"\n\", $str); 
    $str = $str[0]; 

[B]$query2 = \"
UPDATE isc_products 
SET META_DESC_COL = '{$str}' 
WHERE prodid = \".$row['prodid'];

$result2 = mysql_query($query2) or die('Could not connect to database');
[/B]
    echo $row['prodname']. \" \". $str; 
    echo \"<br />\"; 
    }

or, VERY psudocode. not tested.

dave_finlayson

Thanks Gabe, pointed me in the right direction. For those of you who might want it here's the finished code (backup before running, if it screws your site it's not my fault!).

This can probably be done better so if you have any ideas/changes then post them here for all to see!!

PS Gabe, I would have used mysql but getting the data sanitised required PHP (removing image references etc from the descriptions)

<?
$hostnamesp = \"xxxxxxxx\";
$databasesp = \"xxxxxxxx\";
$usernamesp = \"xxxxxxxx\";
$passwordsp = \"xxxxxxxx\";
@$intdata = mysql_connect($hostnamesp, $usernamesp, $passwordsp) or die ('Error connecting to database');

mysql_select_db($databasesp, $intdata);
$query = \"SELECT * FROM isc_products\";
$result = mysql_query($query) or die('Could not connect to database');
while($row = mysql_fetch_array($result)){
	$desc = strip_tags($row['prodname']) . \" \" . strip_tags($row['proddesc']);
	$trimmed = str_replace(\"\n\", \" \", $desc);
	$trimmed = str_replace('\"','',$trimmed);
	$trimmed = str_replace('\'','',$trimmed);
	$str = wordwrap($trimmed, 150);
	$str = explode(\"\n\", $str);
	$str = $str[0];
	$new_meta_desc = $str;
	$rowid = $row['productid'];
	$query2 = \"UPDATE isc_products SET prodmetadesc = '$new_meta_desc' WHERE productid = '$rowid'\";
	$result2 = mysql_query($query2) or die('Could not connect to database');
	}
printf(\"Meta Descriptions changed: %d\n\", mysql_affected_rows());
?>

dave_finlayson

[UPDATE]

* Incorporates Interspire template for prettiness
* Displays changed rows (product name and code)


<?
/* Include Interspire Shopping Cart Top Template */
require(\"../init.php\");
$GLOBALS[\"ISC_CLASS_TEMPLATE\"]->SetTemplate(\"top\");
$GLOBALS[\"ISC_CLASS_TEMPLATE\"]->ParseTemplate();
$hostnamesp = \"xxxxxxxx\";
$databasesp = \"xxxxxxxx\";
$usernamesp = \"xxxxxxxx\";
$passwordsp = \"xxxxxxxx\";
@$intdata = mysql_connect($hostnamesp, $usernamesp, $passwordsp) or die ('Error connecting to database');

mysql_select_db($databasesp, $intdata);
$query = \"SELECT * FROM isc_products\";
$result = mysql_query($query) or die('Could not connect to database');
while($row = mysql_fetch_array($result)){
	$desc = strip_tags($row['prodname']) . \" \" . strip_tags($row['proddesc']);
	$trimmed = str_replace('\n', \" \", $desc);
	$trimmed = str_replace('\"','',$trimmed);
	$trimmed = str_replace('\'','',$trimmed);
	$str = wordwrap($trimmed, 150);
	$str = explode(\"\n\", $str);
	$new_meta_desc = $str[0];
	$rowid = $row['productid'];
	if ($row['prodmetadesc'] !== $new_meta_desc) {
		echo \"<i>changed meta description on product</i><strong> \" . $row['prodname'] . \" - \" . $row['prodcode'] . \"</strong><br />\";
	}
	$query2 = \"UPDATE isc_products SET prodmetadesc = '$new_meta_desc' WHERE productid = '$rowid'\";
	$result2 = mysql_query($query2) or die('Could not connect to database');
	}
printf(\"Total Meta Description Changes: %d\n\", mysql_affected_rows());
/* Include Interspire Shopping Cart bottom Template */
$GLOBALS[\"ISC_CLASS_TEMPLATE\"]->SetTemplate(\"bottom\");
$GLOBALS[\"ISC_CLASS_TEMPLATE\"]->ParseTemplate();
?>

Howdy, Stranger!

Categories

In this Discussion

Who's Online (0)