How to get info from database to web page?

[Leslie]

What do I need to learn in order to build a web page that includes
info drawn from a database? The web pages will include only a line or
two of text from the database, but will need to change based on the
day of the week and the time of day.

I don't even know what the proper terms would be to Google for info.
If someone could please point me in the proper direction I would be
most appreciative!

Thank you!

Leslie
"I refuse to have a battle of wits with an unarmed person."

 

[Karl Groves]

What do I need to learn in order to build a web page that includes
info drawn from a database? The web pages will include only a line or
two of text from the database, but will need to change based on the
day of the week and the time of day.

I don't even know what the proper terms would be to Google for info.
If someone could please point me in the proper direction I would be
most appreciative!

First, you need a server that supports some sort of server-side scripting.
Most hosts offer one or more of the following Perl, ASP, PHP, Python or JSP
Second, you need a server that has a database such as MySQL, PostgreSQL, MS
Access.

Find out what your host supports, first. If you're starting from scratch,
I'd recommend PHP and MySQL. But I'm just partial. ;-)

If it is only a line or two, perhaps a database is a bit of overkill.
Can you be a bit more specific on the context? How many records you'd plan
on having? How often will they change? What kind of text is it (i.e. what
does it say?)

-Karl

 

[Webcastmaker]

What do I need to learn in order to build a web page that includes
info drawn from a database? The web pages will include only a line or
two of text from the database, but will need to change based on the
day of the week and the time of day.

You need server side scripting, the flavor depends on your host
www.w3schools.com covers database access pretty well. I think they
deal mostly with ASP, but the concepts will be pretty much the same
with any language.

If you google "php mysql tutorial" you will be rewarded with a ton of
tutorials.

 

[mark | r]

it would be easier to pre-create it each week in javascript. that way no
databases are involved... many radio sites use this solution.

mark

 

[rf]

mark | r wrote

it would be easier to pre-create it each week in javascript. that way no
databases are involved... many radio sites use this solution.

Hmmm. Each week?

The OP said <q>but will need to change based on the day of the week and the
time of day. </q>

although I don't know what relevance the time of the day has. FWIW it is way
into the late evening here but I suspect most of you are either in morning
or afternoon

 

[Leslie]

First, you need a server that supports some sort of server-side scripting.
Most hosts offer one or more of the following Perl, ASP, PHP, Python or JSP
Second, you need a server that has a database such as MySQL, PostgreSQL, MS
Access.

If it is only a line or two, perhaps a database is a bit of overkill.
Can you be a bit more specific on the context? How many records you'd plan
on having? How often will they change? What kind of text is it (i.e. what
does it say?)

It will be for a small radio station. The info will be what's
currently on air. The page will display "Currently on-air: {database
info}" The info could change as frequently as every hour on some
days, and every few hours on others.

Another poster suggested javascript - don't want to go that route,
though.

Leslie
"I refuse to have a battle of wits with an unarmed person."

 

[Philip Ronan]

It will be for a small radio station. The info will be what's
currently on air. The page will display "Currently on-air: {database
info}" The info could change as frequently as every hour on some
days, and every few hours on others.

Another poster suggested javascript - don't want to go that route,
though.

Then you definitely need to use some form of server scripting.

PHP is the most flexible and easiest to use, in my opinion.

It isn't *absolutely* necessary to use a database (mySQL, etc.), by the way.
You could just upload a file containing the relevant information once a week
and let the server script extract the current information.

For example, here's a dictionary I made that generates dynamic pages based
on information in a text file:

http://www.japanesetranslator.co.uk/your-name-in-japanese/

Phil

 

[Jeff Thies]

What do I need to learn in order to build a web page that includes
info drawn from a database? The web pages will include only a line or
two of text from the database, but will need to change based on the
day of the week and the time of day.

I don't even know what the proper terms would be to Google for info.

I'd suggest just hiring it out. Unless you really want to learn this,
which is not a bad thing to do. Someone with experience can do this in a
few hours.

Read up on SQL, those are the commands that tell the database what you
want. Read up on sorting and ordering.

Find out hout how your webhosts creates tables. Or download a utility to
do that.

Most *nix host run MySQL. Look at the MySQL docs as you will need to
learn a little about dates. MySQL has an awesome but high traffic list
serve.

On ASP your choices are Access and SQL Server (for the common webhost).
Some hosts also run MySQL here, but it is less often found.

MySQL can be easier to use. Access and SQL Server are fussy about all
sorts of things and throw arcane hard to decypher error messages. The
best feature of Access is that you can use Access on your desktop for
creating the database. Although this same data could be exported to
other databases.


Jeff

 

[Leslie]

I'd suggest just hiring it out. Unless you really want to learn this,
which is not a bad thing to do. Someone with experience can do this in a
few hours.

Well, it is hired out now, but I really want to learn how to do it.
I'm fairly proficient with HTML and getting better all the time with
CSS. I need to add to my 'bag of tricks.'

The site in question is currently a real mess. I have designed a
completely new site for the radio station that should go online in the
next few weeks. The way things are now I build the pages and then
have to pass them through to the "hired guy" to add the Currently
On-Air info. In the process he strips the doc type and character
encoding. No clue why. He uses Frontpage to build all his web pages,
maybe that explains it. I've never used Frontpage and have no desire
to. I code by hand.

The pages in question are at: http://www.krosradio.com/ (Years ago I
created the pages this site is now based on. About the only thing
"hired guy" has kept are the background image and the logo. The
coding there now is all his.)

Leslie
"I refuse to have a battle of wits with an unarmed person."

 

[Leslie]

Just been through a similar learning curve myself

Got it working ok now with Perl CGI scripts (using the DBD::ODBC module) &
MSAccess. If you'd like a look at my scripts just ask (WIP at the mo... but
they do work).

Yes, I would like to see your scripts. My email here is valid - not
checked too often, but valid!

Uh.. what is WIP?

Leslie
"I refuse to have a battle of wits with an unarmed person."

 

[Jeff Thies]

Well, it is hired out now,

Smart! Develope a good relationship with them. A team will go farther
than one.

but I really want to learn how to do it.
I'm fairly proficient with HTML and getting better all the time with
CSS. I need to add to my 'bag of tricks.'

Pick an environment/language and learn a bit about server pages. Learn
the basics.

Personally I'm partial to perl, runs in any environment and if there is
something you can't do with it, I haven't found it.

You may find you like PHP. If you want to go the MS route, look at
ASP.net which is fixes a lot of the mess of ASP. They are completely
different.

SQL is pretty simple.

Your SQL would look something like:

SELECT start_time, end_time, program, day_type FROM programs WHERE
day_type = ? ORDER BY start_time

A little bit of code tells the database to process that and you get the
results back. You just loop through the results and insert that in your
template.

I'd say most programers have libraries that handle most of these routine
tasks and just recycle them. Being a newbie it would be a bit before you
had that. It would take someone with that about an hour to get what you
want. This is easy stuff.

The site in question is currently a real mess.

Yes, I see that!

I have designed a

completely new site for the radio station that should go online in the
next few weeks. The way things are now I build the pages and then
have to pass them through to the "hired guy" to add the Currently
On-Air info. In the process he strips the doc type and character
encoding. No clue why. He uses Frontpage to build all his web pages,
maybe that explains it. I've never used Frontpage and have no desire
to. I code by hand.

Online content management. Take the editing off the desktop and put it
online using your template. This keeps them from toying with your
template (doctypes and everything else). It also gives you the ability
to completely sidestep the inhouse "expert" and put it in the hands of
those who actually know the content.

Eventually you come to the conclusion that site maintenance is everything.

Cheers,
Jeff

 

[Jeff Thies]

This is fine, a few suggestions:

To Leslie, his example uses the perl module: DBI. DBI rocks!

WIP - Work In Progress, sorry that might be rather an old TLA

Script for reading from database follows:

#!perl -wT
use CGI qwstandard);
use CGI::Carp qw(warningsToBrowser fatalsToBrowser);
use strict;
use DBI;

# param input - takes input from web form
my $user = param('user');
my $pword = param('pword');

# open users database
# open connection to Access database
my $dbh = DBI->connect('DBI:ODBC:Users') or
die "Can't connect to database: $DBI::errstr\n";

# prepare and execute SQL statement
my $sqlstatement="SELECT UserName,Password
FROM users
WHERE UserName = '$user'";

placeholders are cool.

my $sqlstatement='SELECT UserName,Password
FROM users
WHERE UserName = ?';


keeps you from trouble if $user has a single quote in it.

# prepare it
my $sth = $dbh->prepare($sqlstatement) or
die "Can't prepare database: $DBI::errstr\n";

# execute it
$sth->execute or
die "Could not execute SQL statement: $DBI::errstr\n";

placeholder plugged in here:

$sth->execute($user)...

# get database results
my $DBUser = " "; # set user to something to catch null entry.
my $DBPWord = "";

while (my @row=$sth->fetchrow_array){
$DBUser = @row[0];
$DBPWord = @row[1];
}

I'm crazy about hashrefs, arrays may cause trouble if you add columns or
rearrange the columns. BTW your one row example doesn't need a loop!

while(my $hashref=$sth->fetchrow_hashref){
my %ROW=%$hashref;

print qq{<div>$ROW{UserName} $ROW{Password}</div>}; # in this case field
names are case sensitive

}

Checkout the DBI listserve.

All perl documentation can be found at http://perldoc.com , or on your
local machine if you have perl installed.

Cheers,
Jeff

 

[Andy Stevenson]

What do I need to learn in order to build a web page that includes
info drawn from a database? The web pages will include only a line or
two of text from the database, but will need to change based on the
day of the week and the time of day.

I don't even know what the proper terms would be to Google for info.
If someone could please point me in the proper direction I would be
most appreciative!

Thank you!

Leslie
"I refuse to have a battle of wits with an unarmed person."

Just been through a similar learning curve myself

Got it working ok now with Perl CGI scripts (using the DBD::ODBC module) &
MSAccess. If you'd like a look at my scripts just ask (WIP at the mo... but
they do work).

 

[Andy Stevenson]

[snip]

Yes, I would like to see your scripts. My email here is valid - not
checked too often, but valid!

Uh.. what is WIP?

Leslie
"I refuse to have a battle of wits with an unarmed person."

WIP - Work In Progress, sorry that might be rather an old TLA

Script for reading from database follows:

#!perl -wT
use CGI qwstandard);
use CGI::Carp qw(warningsToBrowser fatalsToBrowser);
use strict;
use DBI;

# param input - takes input from web form
my $user = param('user');
my $pword = param('pword');

# open users database
# open connection to Access database
my $dbh = DBI->connect('DBI:ODBC:Users') or
die "Can't connect to database: $DBI::errstr\n";

# prepare and execute SQL statement
my $sqlstatement="SELECT UserName,Password
FROM users
WHERE UserName = '$user'";

# prepare it
my $sth = $dbh->prepare($sqlstatement) or
die "Can't prepare database: $DBI::errstr\n";

# execute it
$sth->execute or
die "Could not execute SQL statement: $DBI::errstr\n";

# get database results
my $DBUser = " "; # set user to something to catch null entry.
my $DBPWord = "";

while (my @row=$sth->fetchrow_array){
$DBUser = @row[0];
$DBPWord = @row[1];
}

# close the connection
$dbh->disconnect;

Others will doubtless improve on the code, but it's my first attempt, & does
work I'll refine it from here (the obvious next job is un-tainting the
input).

You'll need to make sure your server's set up with ODBC. If your running a
Widows OS I can point you in the right direction.

 

[Andy Stevenson]

This is fine, a few suggestions:

To Leslie, his example uses the perl module: DBI. DBI rocks!

Right, I should have been more clear... I'm using Apache, which has the DBI
modulr loaded as a default, probably why I forgot to mention it. I has to
set up DBI & DBD::ODBC in Perl & ODBC in Windows for the code to work.
[snip]

placeholders are cool.

my $sqlstatement='SELECT UserName,Password
FROM users
WHERE UserName = ?';


keeps you from trouble if $user has a single quote in it.

Thanks for that, I was wondering what use placeholders where. Good point.
[snip]

I'm crazy about hashrefs, arrays may cause trouble if you add columns
or rearrange the columns. BTW your one row example doesn't need a
loop!

while(my $hashref=$sth->fetchrow_hashref){
my %ROW=%$hashref;

print qq{<div>$ROW{UserName} $ROW{Password}</div>}; # in this case
field names are case sensitive

}

Of course! Thanks, that's much better. I was concerended about the array
falling over if I altered the DB.

Yeah, I know the loop is redundant, the code came from my first effort & DBI
that involved looping through records, I'd not got round to trimming it out
(then forgot to )

[snip]

 

[Jeff Thies]

I'm crazy about hashrefs, arrays may cause trouble if you add columns

Of course! Thanks, that's much better. I was concerended about the array
falling over if I altered the DB.

For your specific example column binding may be better.

my($password,$username);
$sth->bind_columns(undef,\$password,\$username);
$sth->fetch();

There must be at least 6 ways to get data back from DBI.

Jeff

 

[Andy Stevenson]

[snip]

There must be at least 6 ways to get data back from DBI.

Jeff

So I'm learning. I'm tending towards the "not broken, don't fix" theory 'til
I have a better understanding of the differences (this may take a while

Thanks for your input.

Leslie, sorry, I seem to have hijacked your thread. I'll get my coat...

 

[Toby Inkster]

There must be at least 6 ways to get data back from DBI.

TMTOWTDI.

 

[Toby Inkster]

The best feature of Access is that you can use Access on your desktop
for creating the database. Although this same data could be exported to
other databases.

Access is also able to connect to other databases via ODBC, so you can use
Access as a frontend for, say, MySQL, PostgreSQL or Oracle.

 

[Leslie]

[snip]

There must be at least 6 ways to get data back from DBI.

Jeff

So I'm learning. I'm tending towards the "not broken, don't fix" theory 'til
I have a better understanding of the differences (this may take a while

Thanks for your input.

Leslie, sorry, I seem to have hijacked your thread. I'll get my coat...

Nah.. no need to get your coat. I'm reading all the posts, but to be
quite honest, at this point not a bit of it makes any sense. I've got
a week's vacation starting in 2 days. My son and I will be enjoying
all Denver, Colorado has to offer, and that will *not* include any
computers! (Renaissance Faire here we come!!!) When we get back to
reality I'll be digging into databases, etc.

Thanks for all the help everyone has offered. I'll be plowing through
this info bit by bit, but I'll get there eventually. Just like I did
with HTML and just like I'm doing with CSS.

Thanks again!!

Leslie
"I refuse to have a battle of wits with an unarmed person."