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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

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

Post 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?
User avatar
stereofrog
Forum Contributor
Posts: 386
Joined: Mon Dec 04, 2006 6:10 am

Post 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.
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Post 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);
Post Reply