Page 1 of 1

replacing fields and mysql... what method is faster?

Posted: Mon Aug 13, 2007 1:37 am
by Mr Tech
I've created a CMS that allows search engine friendly links... The script has a unique id for each page. In the template code, you can put linkid-1-... the PHP code will then replace that with whatever the search engine friendly link is.

At the moment I use this code:

Code: Select all

// ==============================
// Link All The Pages
// ==============================
$match_count = preg_match_all("/linkid\-([0-9]*)\-/si", $body_content, $matches);
$matches[1] = array_unique($matches[1]);
for ($i = 0; $i < $match_count; $i++) {
	$linkid = "";
	$body_content_linkid = "";
	$linkid = $matches[1][$i];
	$linkid_info = mysql_fetch_array(mysql_query("select slug from {$tbl_name}pages where id='$linkid'"));

	###############################
	# Mod_Rewrite
	if ($mod_rewrite == "y") {
		$body_content_linkid = $websiteurl."/".$linkid_info[slug];
	} else {
		$body_content_linkid = "index.php?page=".$linkid_info[slug];
	}
	###############################

	$body_content = str_replace("http://linkid-{$linkid}-", $body_content_linkid, $body_content);
	$body_content = str_replace("linkid-{$linkid}-", $body_content_linkid, $body_content);
}
The only problem with that is if there are say 30 links in the template, it has to run 30 queries... that sucks... I'm sure it effects the load time of the page...

Is there a better way to do it so that it loads faster? Maybe run one query and put all the results into an array and then use preg_replace or erg_replace (I'm not sure which one) to replace the fields from the array.

What do you think? Any ideas?

Posted: Mon Aug 13, 2007 4:08 am
by stereofrog
I'd collect them into array first

Code: Select all

preg_replace("/linkid-(\d+)-/sie", '$links[] = $1', $body_content);
then run a query against the list of ids:

Code: Select all

mysql_query("select id,slug from {$tbl_name}pages where id IN (" . implode(',', $ids) . ")")
collect mysql results into array (link_id => slug) and replace links from that array:

Code: Select all

$body_content = preg_replace("/linkid-(\d+)-/sie", '$slugs[$1]', $body_content);
Something like this.

Posted: Mon Aug 13, 2007 6:45 pm
by Mr Tech
Awesome, thanks mate! Here's my code. Is that good enough?

Code: Select all

// ==============================
// Link All The Pages
// ==============================
$links = array();
$page_ids = array();
preg_replace("/linkid-(\d+)-/sie", '$links[] = $1', $body_content);
$linkids = mysql_query("select id,slug from {$tbl_name}pages where id IN (" . implode(',', $links) . ")");
while ($linkid = mysql_fetch_array($linkids)) {
	$id = $linkid[id];
	###############################
	# Mod_Rewrite
	if ($mod_rewrite == "y") {
		$page_ids[$id][slug] = $websiteurl."/".$linkid[slug];
	} else {
		$page_ids[$id][slug] = "index.php?page=".$linkid[slug];
	}
	###############################	
}
$body_content = preg_replace("/http\:\/\/linkid-(\d+)-/sie", '$page_ids[$1][slug]', $body_content); 
$body_content = preg_replace("/linkid-(\d+)-/sie", '$page_ids[$1][slug]', $body_content);