mysql php script to show each record as a distinct .php/.htm.html page

Forum Moderators: coopster

Message Too Old, No Replies

mysql php script to show each record as a distinct .php/.htm.html page

mysql php

highseas

8:02 pm on Oct 22, 2010 (gmt 0)

I am new-ish to mysql and how to use it to automatically display "linkable" records on separate web pages.

I am trying to create a system of pages for a funeral home to show obituaries and allow friends/family to post comments regarding the deceased that they are associated with.

i have a comment system and that works fine. I am able to retrieve each recordset from the database and show it in an html table. I would like my webpage to show deceased names and allow the viewer to click on that deceased name, go to another page or "div" or something and then when the viewer arrives there, they can see the information regarding the deceased -- basically all of the fields for the record in the db. I will stick the comment script on that page, only for that deceased person's record.

How can I do this, this easiest method, the smallest "footprint?"

Thanks for any ideas.

Demaestro

8:14 pm on Oct 22, 2010 (gmt 0)

Highseas,

welcome to WebmasterWorld

If you are already able to pull infor from your DB and put it into a page then you are more than half way there.

what you want is to create a link that has the id of the record you want to display in the query string.

You then use that id to grab the specific row and then insert it into a new table..

First when you draw the HTML table you want to add in where the link goes.

It looks something like this.

<a href="detail_page.php?deceased_id=$ROW['deceased_id']">More details</a>

This will write the id of the record into the link... then on the detail_page.php you grab the record using the id from the query string and display it into another HTML table like you have... something like this

the_id = $_GET['deceased_id']

select * from deceased where deceased_id = $the_id

highseas

8:20 pm on Oct 22, 2010 (gmt 0)

Demaestro:

Thank you for the quick response. Ok, let me try your suggestion. I know it will work, i just need to figure out how to make it work.

Gimme a few mins.

Demaestro

8:21 pm on Oct 22, 2010 (gmt 0)

if you are having troubles with certain bits of code... post the code you have and we can work with that and get it working for you.

highseas

8:42 pm on Oct 22, 2010 (gmt 0)

here is my scratchy code (it's crude I know....)

1) this page will show a link to "obits list." Actually, on this page, it would be okay for me to see a list of deceased names all of which are linked to pages with all of the mysql data in the table, so each page is "automatically" created and the funeral home employees do not have to create a distinct .php/.htm/.html page for each deceased.

<html>
<head>

<title>Index Page</title>
</head>

<body>
<center><h2>INDEX</h2></center><BR>
<?php
//make a MySQL connection
include('config.php');
// echo " <a href=\"insert.php\">INSERT PHP</a><br>";
//get all the data from the page table
//*=select everything from the table page
$result = mysql_query("SELECT * FROM page")
or die(mysql_error());
while($link=mysql_fetch_array($result)){
//echo (display) a link using $link[linkid]
//echo " <a href=\"webpage.php?id=$link[linkid]\">Web Page 1</a><br>";
echo "<p>$result</p>";
echo " <a href=\"webpage.php?id=$link[linkid]\">obits list</a><br>";
}
?>
</body>

</html>

2) this web page is what appears when the user clicks on the "obits list" <a href></a> link in the above page and what I see is one deceased name (these are just test names, just to be clear...) John Smith which is an anchor, and when I click on John Smith i get a broken link to [easwebdesign.com...] So on this page above, if all of the information input into the db by the funeral home employees (name dod age description etc.) appears here I don't need to anchor it to another page at all.

I hope this is clear, and even do-able, maybe I am unclear how this works. The idea is that the funeral home just wants to enter there deceased data and have it magically appear on the website, all of the deceased names that they have entered. Later on I will write a filter to show months or something like that.

Thanks so much for your help!

highseas

8:43 pm on Oct 22, 2010 (gmt 0)

HERE IS THE EXAMPLE PAGE FOR #2......

2)

<html>

<body>

<?php

////make a MySQL connection
include('config.php');
//get ID from URL
$id = $_GET['id'];
//get linkid, text, linkurl from the page table
//..or..*=select everything from the table page
$result = mysql_query("SELECT linkid, text, linkurl
FROM page WHERE linkid='$id' ")
or die(mysql_error());
while($myrow = mysql_fetch_assoc($result))
{
//the meta title tag is used to declare
//the title of the page
echo "<title>Web Page $myrow[text]</title>";
echo "<center><h2>Web Page $myrow[text]</center><BR>";
echo "<a href=\"$myrow[linkurl]\">$myrow[text]</a>";
}
?>

</body>

</html>

Demaestro

8:50 pm on Oct 22, 2010 (gmt 0)

highseas....

What you are describing is what is called a "dynamic content page"

This means that instead of 1 page being for 1 thing.. a page can be a page for many things. You don't need 10 pages to show 10 people, you need 1 page that is smart enough to know what person to show.

The page we will build will do what you need... first lets get the listing page working

Does that first listing page work?

Demaestro

9:01 pm on Oct 22, 2010 (gmt 0)

We can get this all in 1 page as well if you want... You can either output it all into 1 page or you can use a link to show a hidden div.

highseas

9:22 pm on Oct 22, 2010 (gmt 0)

demaestro:

ah, i was just thinking of how I have built other sites with one single index.htm page with <div id="#1"> </div> and references parts of the page for what the user thinks is a separate "page."

The client would like an obituary.php (for a name and type example) that lists several names and possibly the date of decease. From that page they want to have viewers simply click on the name and then that person's information appears only, only that person's info. On that page I will place the comment script so that the viewer can post a "condolence."

I hope that is clear. and thanks so much for your help.

highseas

9:27 pm on Oct 22, 2010 (gmt 0)

yes, that first page works.

Demaestro

9:30 pm on Oct 22, 2010 (gmt 0)

Ok so we are on the right track.

On the obits page we are almost there. Can you show what is in the DB tables we are using?

I see you are selecting from a table called page.. is that correct? If so what's in it?

page
------------
id integer,
name varchar(256)
and so on......

Is there an Obits table?

Demaestro

9:49 pm on Oct 22, 2010 (gmt 0)

Ideally what you want is 2 tables... keep in mind you aren't really storing pages... you are storing data that you will plug into a dynamic page.

table 1 obits
____________
obit_id,
first_name,
last_name,
dob,
dod,
obit_text

table 2 obit_has_comment
____________
obit_id,
comment_text,
comment_made_by

On page 1 you want to

$result = mysql_query("SELECT * FROM obits")
or die(mysql_error());

while($obit_data=mysql_fetch_array($result)){
echo "<a href=\"details_page.php?id=$obit_data['obit_id']">";
echo "$obit_data['first_name'] $obit_data['last_name'] </a>";
echo "<br><br>";
}

This will list all the names and link them to the second page.

The second page is

the_id = $_GET['id']
$result = mysql_query("select * from obit where obit_id = " + $the_id)
or die(mysql_error());

while($obit_data=mysql_fetch_array($result)){
echo "$obit_data['first_name'] $obit_data['last_name'] <br>";
echo "$obit_data['dob']

and so on...

echo "<br><br>";
}

Then to get comments on the same page

$result = mysql_query("select * from obit_has_comment where obit_id = " + $the_id)
or die(mysql_error());

while($comment_data=mysql_fetch_array($result)){
echo "$comment_data['comment_text']" <br>;
echo "$comment_data['comment_made_by']";

[edited by: Demaestro at 9:51 pm (utc) on Oct 22, 2010]

highseas

9:51 pm on Oct 22, 2010 (gmt 0)

i have several db's that i have been testing, but i would like to use a db and table inside that db called ogle (the letter "o").

the fields are:

field type

name text
age int(3)
date format is yyyy-mm-dd
obit text

I may add or subtract a field or two later on....

Demaestro

9:52 pm on Oct 22, 2010 (gmt 0)

check out my above post.... I think we posted at the same time so you may have missed it.

highseas

9:52 pm on Oct 22, 2010 (gmt 0)

demaestro:

thanks for this code snippet, I will test it right now and let you know how i make out.

highseas

9:55 pm on Oct 22, 2010 (gmt 0)

yes, we hit the enter key simultaneously haha, i am testing right now.

highseas

11:23 pm on Oct 22, 2010 (gmt 0)

ok, i seem to be having a parse error with this part

while($obit_data=mysql_fetch_array($result)){
echo "<a href=\"details_page.php?id=$obit_data['obit_id']"\>";
echo "$obit_data['first_name'] $obit_data['last_name'] </a>";
echo "<br><br>";
}

highseas

5:02 pm on Oct 25, 2010 (gmt 0)

Demaestro -- are you out there today?

mooger35

10:23 pm on Oct 25, 2010 (gmt 0)

Try this:

while($obit_data=mysql_fetch_array($result)){
echo "<a href=\"details_page.php?id=".$obit_data['obit_id'].""\>";
echo $obit_data['first_name']." ".$obit_data['last_name']." </a>";
echo "<br><br>";
}

That should fix it.

mooger35

10:24 pm on Oct 25, 2010 (gmt 0)

duplicate post

highseas

10:39 pm on Oct 25, 2010 (gmt 0)

Thanks mooger35, i actually got this code to work for my "first page"

echo "<a href=\showobit.php?id=$obit_data[obit_id]\>" . $obit_data['first_name'] . " " . $obit_data['last_name'] . "   " . $obit_data['dod'] . "</a>" . "<br/>";

and then got this code to work for my "second page"

$the_id = $_GET['id'];
$result = mysql_query("select * from obits where obit_id = '$the_id'")
or die(mysql_error());

I hope that this "valid" code, it seems to work for me and does what I need it to do.

highseas

10:43 pm on Oct 25, 2010 (gmt 0)

what it is supposed to do is function as an obituary page for a funeral home. users go to the obituary page, they see the name that they would like more information about, click on that name and then a page appears with that deceased person's information, the obituary, services held, etc.

i also placed a shaky open-source comment system at the bottom of the page. haha, that seems to work as there is a comment form at the bottom of that person's obituary write-up. Folks can post public condolences onto the person's page.

thanks for your post, I greatly appreciate it!