homepage Welcome to WebmasterWorld Guest from 54.204.58.87
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / WebmasterWorld / New To Web Development
Forum Library, Charter, Moderators: brotherhood of lan & mack

New To Web Development Forum

    
Export Database info to a php template
MBundy




msg:3678665
 2:28 pm on Jun 19, 2008 (gmt 0)

I have xampp installed as localhost.

I have created a mysql database with details of foals born from 2004 - 2007.

I have one table called foals, with 7 seperate fields. name (primary key), dob, reg_no, colour, gender, height, status.

I have a basic html page with details of the mares for these foals.
Each Mare has a list of the foals. At the moment what happens when i click on a foal it loads a new page for each foal. All these pages are the same, except for the individuial input data for each one. I have at the moment 30 individual pages.

Okay what i would like to happen is when i click the links, it will then load the page, but instead of this being a manually made web page, it collects the data from the database and inputs it into a template page. Similar to the fuctions that happen when lets say you click a members name in a forum, and you get the members details.

Can someone point in the correct direction for creating this, I have dabbled with DW but cant seem to find what i'm looking for. I've thought about taking a forum and altering the templates etc to suit my needs but i'd rather start from scratch and code it myself.

Thanks in advance.

 

rocknbil




msg:3678791
 4:56 pm on Jun 19, 2008 (gmt 0)

As I'm sure you've guessed, you're going to need a little server-side programming to do this - most people prefer PHP these days, but you could use perl, ASP, coldFusion, whatever.

You create a plain text/html template with "markers" like so:

<html><head><title>¦TITLE¦</title></head>
<body>
<h1>¦TITLE¦</h1>
¦CONTENT¦
</body>
</html>

Note my example of markers are all in caps and use a pipe character. Choose any format you want, but stick with it and make sure the markers are unique and absolutely can't occur in the normal data you would use.

Now you do do two things: select the data from the DB and store it in a variable. (The below is in no real language, for logic example only:)

select title, content from table;
($title,$content) = data fetch

Second, you open the template, read it in line-by-line, and use the substitution methods of the programming language of your choice to swap out the marker for the content. While doing so, you compile a new variable, let's call it "$final:"

open (your template)
while ($line) {
if ($line =~ /¦TITLE¦/) { $line =~ s/¦TITLE¦/$title/; }
if ($line =~ /¦CONTENT¦/) { $line =~ s/¦CONTENT¦/$content/; }
$final = $final + $line;
}
close (your template)

Now you have the whole page in memory, you print it to the browser:

print "content-type: text/html\n\n";
print $final;

This can be very robust. I have only exemplified title and content, you could have a neatly formatted template with markers for all the various bits - pictures, thumbnails, created date, and so on - positioned where you want them and substitute on the fly.

MBundy




msg:3679584
 2:51 pm on Jun 20, 2008 (gmt 0)

Thanks for your reply, but to be honest it went just a lttle bit over my head. S:

Okay heres what i have so far in my template. called card.php

<?php
// set database server access variables:
$host = "localhost";
$user = "******";
$pass = "******";
$db = "trondrastud";

$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");

// select database
mysql_select_db($db) or die ("Unable to select database!");

// create query
$query = "SELECT * FROM foals";

// execute query
$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());
?>

<link rel="stylesheet" href="trondra.css">

<?php
echo "<table cellpadding='0' cellspacing='0' border='1' width='410' height='515' valign='top' bgcolor='#005555' background='images/pony_card_bk.gif'>";
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td valign='top'>";
echo "<table cellpadding='1' cellspacing='0' border='0' width='100%'>";
echo "<tr>";
echo "<td valign='top'><h1>". $row['name'] . "</h1></td>";
echo "</tr>";
echo "<tr>";
echo "<td><hr/></td>";
echo "</tr>";
echo "<tr>";
echo "<td>";
echo "<table cellpadding='0' cellspacing='0' border='0' width='100%'>";
echo "<tr>";
echo "<td><h2>Reg No</h2></td>";
echo "<td><h2>" . $row['reg_no'] . "</h2></td>";
echo "</tr>";
echo "<tr>";
echo "<td><h2>D.O.B</h2></td>";
echo "<td><h2>" . $row['dob'] . "</h2></td>";
echo "</tr>";
echo "<tr>";
echo "<td><h2>Gender</h2></td>";
echo "<td><h2>" . $row['sex'] . "</h2></td>";
echo "</tr>";
echo "<tr>";
echo "<td><h2>Colour</h2></td>";
echo "<td><h2>" . $row['colour'] . "</h2></td>";
echo "</tr>";
echo "<tr>";
echo "<td><h2>Sire</h2></td>";
echo "<td><h2>" . $row['sire'] . "</h2></td>";
echo "</tr>";
echo "<tr>";
echo "<td><h2>Dam</h2></td>";
echo "<td><h2>" . $row['dam'] . "</h2></td>";
echo "</tr>";
echo "</table>";
echo "</td>";
echo "</tr>";
echo "<tr>";
echo "<td><hr/></td>";
echo "</tr>";
echo "<tr align='center'>";
echo "<td height='345'><img src='" . $row['image'] . "' alt='" . $row['name'] . "' width='385' height='331'></td>";
echo "</tr>";
echo "</table>";

echo "</td>";
echo "<tr>";
echo "<tr>";
echo "<td align='right' valign='bottom'><form><input TYPE='button' VALUE='Close' onClick='window.close()'></form></td>";
echo "</tr>";
}
echo "</table>";
?>

okay so the above works great and creates a working page with all my db entries.

Next step is to call this from a link.

<a href="javascript:openWindow('card.php')" onmouseover="window.status='Click to view this Stud information card';return true;" onmouseout="window.status='';return true;">
<font size="3" color="#ffffff"><b>Trondra Sunstruck</b></font></a>

Javascript is for a popup window sized so data fits its perfectly.
// display window
function openWindow(url) {

popupWin = window.open(url, 'remote', ',addressbar=0,scrollbars=0,resizable,width=411,height=598,left=1,top=1')

}

Is there a specific command that i would add to the end of 'card.php' that would call and input the data from Trondra Sunstuck.

example 'card.php?&querie=Trondra Sunstruck'

Am i going in the right diraction or am i completely of course.

Thanks again for you input.

rocknbil




msg:3679906
 10:16 pm on Jun 20, 2008 (gmt 0)

AHHH . . . .I am so totally off topic, I have to apologize.

When you use the word "template" most of us think in terms of the template holding the content, that is, the graphic and layout framework of the actual pages. You would normally use a PHP script to access the data, then output it in a page template to make it look like the rest of the site. This allows you to change your site's look without rewriting all your scripts. That's what all that hullaballoo was about.

You are speaking about something else entirely, a "template" for outputting pages. Alrighty then!

The answer is heck yeah you can do this.

'card.php?&querie=Trondra Sunstruck'

Although I don't know if you are querying a different database or just a field in the current DB. For the sake of example, let's say you are querying a field. It's more flexible and efficient, however to use a numeric identifier for the query. The titles may change, but the unique identifier should remain the same. This means you can make changes to your database without having to update your pages or scripts:


auto_id¦unique_id¦title
1 ¦1234 ¦Trondra Sunstruck

With this in mind, we modify your query string to

'card.php?&querie=1234

I'm going to take a short-cut here for example; normally you would want to cleanse the querie variable to avoid SQL injection and reject the query if it's blank. But the eventual goal is to pass that query to your select:

if (isset($_REQUEST['querie'])) { $q = $_REQUEST['querie']; }

Then you just add it to the select as a where:

$query = "SELECT * FROM foals where unique_id=\"$q\"";

If you have troubles as in, my PHP is a bit rusty this month do an echo to see if the select is valid:

echo "$query <br>\n";

MBundy




msg:3681384
 12:37 pm on Jun 23, 2008 (gmt 0)

Excellent , thankyou very much.

Works perfectly.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / WebmasterWorld / New To Web Development
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved