Adding text from mySQL table to Middle of URL in PHP page

Question

Hi fellows! I'll try to ask this correctly. This has got me licked.

I have a script online that will bring up items according to a search on let's say, page1.php. The results come from a table called items. The results list has links to the items that look like this: a href=\"/" . "boats/$name/" . "$p". $line["PID"]. ".html\"

In another table in the same database, there is a table named "morphlinks" with two fields in it named elink and ulink. elink has id numbers which correspond to id numbers in the table named "items".

CREATE TABLE IF NOT EXISTS morphlinks ( 
  elink varchar(128) NOT NULL default '',
  ulink varchar(255) NOT NULL default '', 
  PRIMARY KEY (elink) 
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO morphlinks (elink, ulink) VALUES ('12345', 'canoes/Blue-Canoes/Bob-Morris/');

What I'm hoping to do is to add what's in the corresponding row in "morphlinks" to the url where you see "boats/$name/". So the list of items with urls to display them will look like this on a php page: a href=www.domain.com/canoes/Blue-Canoes/Bob-Morris/12345.html where canoes/Blue-Canoes/Bob-Morris/ is what's in the "morphlinks" table and 12345 is the PID number.

It all works fine except for the canoes/Blue-Canoes/Bob-Morris/ part. I can't figure out how to get that part into the url. The url works fine and goes to the same item whether it's in there or not but we want it in there for seo purposes. We will manually populate the "morphlinks" table occasionally.

Thanks, Douglas

Answer 1

Your question wasnt exatly clear on the code iterating over the rows but i assume $line is the mysql row as an assoc array.. im not sure where $name is coming from so im going to ignore that for now... if you want to update your question with more info i can give you something more exact, but the following is a general way to go about it...

First modify the query to join the morphlinks to the items.. i assume pid == elink

SELECT items.*, ml.ulink from items
LEFT JOIN morphlinks ml ON (items.pid = ml.elink)

So now in your rsults array you chould have a $line['elink'].

from here its easy (im goign to use sprintf for sanity!):

sprintf(
   'a href="/%s%s%s.html"', 
   (!empty($line['elink']) ? $line['elink'] : "boats/$name/"), 
   $p,
   $line['PID']
)