Embedded HTML Links in Database

[thegreypanther]

I have a website dedicated to the remembrance of men of the Yorkshire Regiment who fell in WW1.

As part of this I am trying to set up a searchable on-line database, so that by inputting a man's name, the memorials commemorating him are shown.

I have successfully set up the PHP coding (and the MySQL database), whereby by entering a surname all records associated with a particular surname are retrieved.

However, what I now need to do is to embed URLs into the memorial names so that the user is taken to the web page for details of the memorial.

I CANNOT FIND any simple help to enable me to get the relevant coding.

Any ideas? Suggestions? Websites Tutorials where coding is available and easy to understand?

[Mchl]

Store the url in a separate field alongside the names. When the name is displayed, enclose it in <a href="$url"> </a> tags. Nothing special about it, unless I understood you wrong.

[thegreypanther]

I thought that it was a simple matter to begin with, and tried what you have suggested. That doesn't seem to work with me.

From what I can gather, you have to have two sets of tables for the database, - one with the "main" data in it (soldiers names, memorials etc.), and another table with the memorials and URLs in it.

Then, somehow you have to have the required PHP coding to marry the two sets of data together.

[Mchl]

Well... it's hard to tell anything, until you show us some actual code of yours. That would help a lot.

[thegreypanther]

The code for retrieving the basic data from the table "soldiers" in the MySQL database is

 

$query1 = "SELECT rank, forenames, surname, day_died, mnth_died, yr_died, age, remembrance1, remembrance2, remembrance3, remembrance4, remembrance5, remembrance6 FROM soldiers WHERE (soldiers.surname LIKE '%$surname%') and  (soldiers.initial LIKE '%$forename%')";

 

remembrance 1, remembrance2.... are the memorials with which I would like to attach a URL

 

The code for retrieving the URLs from the table "hyperlinks" is

 

$query2 = "SELECT memorial, url FROM hyperlinks";

 

The field "memorial" is equivalent to "remembrance 1, remembrance2....", so that when remembrance 1, remembrance2.... etc are retrieved then their equivalent URLs (the field "url") can be displayed as an embedded link in the table.

 

The coding for the output of the table is

 

echo '<table border = "1" align="center">';

echo "<tr><th>Rank</th><th>Forename(s)</th><th>Surname</th><th>Day Died</th><th>Month Died</th><th>Year Died</th><th>Aged</th><th>Remembrance 1</th><th>Remembrance 2</th><th>Remembrance 3</th><th>Remembrance 4</th><th>Remembrance 5</th><th>Remembrance 6</th></tr>";

 

 

while ($row = mysql_fetch_assoc($result1))

{

echo "<tr><td>";

echo $row ["rank"].'</td><td>';

echo $row ["forenames"].'</td><td>';

echo $row ["surname"].'</td><td>';

echo $row ["day_died"].'</td><td>';

echo $row ["mnth_died"].'</td><td>';

echo $row ["yr_died"].'</td><td>';

echo $row ["age"].'</td><td>';

echo $row ["remembrance1"].'</td><td>';

echo $row ["remembrance2"].'</td><td>';

echo $row ["remembrance3"].'</td><td>';

echo $row ["remembrance4"].'</td><td>';

echo $row ["remembrance5"].'</td><td>';

echo $row ["remembrance6"].'</td></tr>';

 

}

 

Hope this helps.

[laPistola]

if thats how you already set it up then you would need to make the first query get the data from both tables and weather link them in the where clause or a group by some thing like

 

 $query1 = "SELECT soldiers.rank, soldiers.forenames, soldiers.surname, soldiers.day_died, soldiers.mnth_died, soldiers.yr_died, soldiers.age, soldiers.remembrance1, soldiers.remembrance2, soldiers.remembrance3, soldiers.remembrance4, soldiers.remembrance5, soldiers.remembrance6, hyperlinks.memorial, hyperlinks.url FROM soldiers, hyperlinks WHERE (soldiers.surname LIKE '%$surname%') and  (soldiers.initial LIKE '%$forename%') AND (hyperlinks.memorial = soldiers.remembrance1)";

 

i think this is what your looking for but im not 100% sure to what your after

[Mchl]

What if you modified your tables a little?

 

table soldiers
ID, rank, forenames, surname, day_died, mnth_died, yr_died, age

table remembrances
soldierID, remembrance, url

 

This way you could have any number of remembrances per soldier, and also retrieving them together with urls would be easier.

 

[thegreypanther]

The query still produces a table with the required results, but there are no embedded links in the memorials.

 

Basically, somehow or other the "<a href = > xxxx </a>" still needs to be incorporated into the coding.

[Mchl]

$query1 = "SELECT
  s.rank, 
  s.forenames, 
  s.surname, 
  s.day_died, 
  s.mnth_died, 
  s.yr_died, 
  s.age, 
  s.remembrance1, 
  s.remembrance2, 
  s.remembrance3, 
  s.remembrance4, 
  s.remembrance5, 
  s.remembrance6,
  h1.url AS url1, 
  h2.url AS url2, 
  h3.url AS url3, 
  h4.url AS url4, 
  h5.url AS url5,
  h6.url AS url6 
FROM 
  soldiers AS s, 
  hyperlinks AS h1,
  hyperlinks AS h2,
  hyperlinks AS h3,
  hyperlinks AS h4,
  hyperlinks AS h5,
  hyperlinks AS h6
WHERE 
  (s.surname LIKE '%$surname%') AND  
  (s.initial LIKE '%$forename%') AND 
  (h1.memorial = s.remembrance1) AND
  (h2.memorial = s.remembrance2) AND
  (h3.memorial = s.remembrance3) AND
  (h4.memorial = s.remembrance4) AND
  (h5.memorial = s.remembrance5) AND
  (h6.memorial = s.remembrance6)";

 

That's one ugly query, that could be a lot prettier if you modified your database structure as I propsed above

For showing urls this should work

echo "<a href=\"{$row['url1']}\">{$row["remembrance1"]}</a></td><td>";  // (6 times)

[thegreypanther]

Many thanks for all this help.

I'm afraid that I have to be out all day today, so will give it a go tomorrow a.m and let you know how it works.

[thegreypanther]

To help show more clearly the problem that I'm wrestling with, the test pages can be found by selecting

http://www.thegreypanthers.com/ww1-yorkshires/html-files/search-database.htm

 

The data that has been loaded into the test database has 146 surnames all beginning with "A".

Try a search for "*" "Atkinson".

This produces a clutch of results which show the sort of data where I would like to link the memorial names to the pages where they actually appear on the website.

e.g. Tyne Cot Memorial, Ypres (Menin Gate) Memorial..... etc.

 

There are 10 memorials with associated URLs in the url table.

 

Although the "search database" page is under test, and isn't accessible to anybody who doesn't know the address, the rest of the website is live and operational.

[Mchl]

I would strongly advise to rethink your database design.

Read about database normalisation.

[thegreypanther]

I give up!

 

The Yorkshire Regiment WW1 Remembrance website is one that started out from small beginnings, to a website that has "grown like Topsy". I'm too old to get stuck into advanced web design processes, so will let things cool for a while.

[Mchl]

I give up!

 

Don't!

Normalisation is not that difficult. It's quite logical in fact. And once done properly, getting data from database is much easier than with flawed design.

 

Don't rush. Take it one thing at a time, and you will manage.