DB fundamentals

[SilverBean]

I’m struggling on a fundamental level. My skills in using Databases are merging with my Perl/CGI skills for the first time in my initial stab at some Web Applications accessing on-line databases. I don’t know about you but I’m excited. I’ve got a fair amount of database experience starting all the way back with a course in dBase, going to VB front ends for access databases, and currently seem to have settled quite nicely in the world of VBA for Access. I’ve tested Database applications and good familiarity with SQL statements, although to be honest I’ve seen some really wild looking ones that I could never dream up in a million years.

On the Perl side. I’ve written a discussion type forum it mostly just plays with HTML files. It includes a language filter, and search capabilities.

I’m actually putting the finishing touches on my first on-line shopping cart/ purchasing thing. It’s pretty easy for a non profit no need to really keep track of the sales, or transactions – just put a list of all items on the screen. _Javascript basically does most of the work handling the sum based on whatever gets checked. At the end a CGI submit to help massage the data for PayPal that’s it, then after confirmation, and onto PayPal. Once again I’ sure everybody is impressed. So I’ve dabbled with accessing data from a MySQL server. I actually have pieces of an actual web interface. I can add, edit, delete records or fields as I see fit.

Now putting it altogether the merger. My struggle comes in when I start thinking of how to put all the pieces together in an attractive looking interface. The world of Access Database with a nice comfortable Jet to rely on vs. this seemingly cold, dark world of the online databases.

I’m hoping I can get some feedback to help me get rolling. Maybe confirm or explain some suspicions I have to this point. When I search for posts I get a lot about DBI, DBD stuff which I’m confident I have working at this point.

To start with I’m understanding that there is 2 ways I can use the CGI to exchange data between the surfer side and the server side particularly getting data into my perl code. I can POST or GET. The one example I have of an on-line web database has the author putting all the records from the database into an HTML table. Each row has a delete check box, each row has a hyperlink on the data(i.e., a href="blahblah/cgi-bin/update.cgi?m=property&sm=edit&id=3") I guess the original cgi generates this. I've equated this to kind manually forcing the cgi to execute as GET. Anyway so one the table is displayed a POST will just execute a cig which is looking at those check boxes to see which ones to delete. Update processing occurs with a click on the hyperlink documented above. While add processing is easy enough to figure out.

First question: Is this a "typical" Add/ Edit/ Delete scheme for Perl/CGI DB apps?

My first reaction is to absolutely hate relying on GET and that extra command line stuff. I’m not sure why. I'd rather just use POST. Anyway to manually simulate that? Is there anyway to implement the Add/ Edit/ Delete with just a single post and one cgi file? I’m thinking if my HTML file can send the user’s request like edit, add, or delete to a cgi file which calls other functions based on which operation.

I know based on my experience looming in the darkness is wanting to update parts of my form with information. In the Access, VB these are called child forms which are linked to the main form. For instance, I was anticipating migrating my discussion forum to an on-line DB Application. Let’s say the user is looking at SubjectID=5. I’d like to show him all the Replies that have that subject ID. I don’t necessarily need to be able to edit or delete just show them to the user. Let him tell me what he wants to do with it. Sorry poor terminology, in this on-line world I’ve got all the records being shown there is no current, navigation etc. How would I show all the reply IDs corresponding to a specific Subject ID in some area of my HTML Form? Some guy wanted to put the output of counter in a specific area of his web site and he used something like this

<p align="center"><font color="#FF0000" size="2" face="Arial">You are visitor number <!--#exec cgi="cgi-bin/counter.cgi"--> to this web site</font></p>

Is this even legal? I would probably buy into using the command line stuff for something like this.

Has anybody had luck with frames for DB apps?

 

[PaulTEG]

where possible always use post, get leaves information in the browser history.

The counter question - if you're generating the output from perl, you wont be able to use server side includes, as control has been passed from the webserver to perl, which then produces the output, but you can require files to accomplish the same task.

my $dbh = DBI->connect($db, $username, $password) or die "Couldn't connect to database: " . DBI->errstr;
my $sth= $dbh->prepare('select * from forum where parent_id=? order by subject_id');
print "<table>";
$sth->execute($subject_id);
while ($hash = $sth->fetchrow_hashref) {
  print '<tr><td><a href="/cgi-bin/myscript.pl?subj='.$hash{'subject_id'}.">".$hash{'title'}."</a> </td><td>'. $hash{'content'} .'</td><td>'.$hash['date'}.'</td></tr>';
}
print "</table>";

Paul
------------------------------------
Spend an hour a week on CPAN, helps cure all known programming ailments ;-)

 

[SilverBean]

I just want to check my understanding. Isn't this just like a GET? A manual GET. Ok you haven't done the form method=GET thing - but the end result still the same? Still in the user's browser.

 

[PaulTEG]

yeah, you're right, to do it as a post, you could build a number of forms, don't know what i was thinking :-o
...
<form method=post action=/cgi-bin/myscript.pl>
<input type=hidden name=subj value=[$subject_id]>
<input type=submit value="Check this out">
</form>
<form method=post action=/cgi-bin/myscript.pl>
<input type=hidden name=subj value=[$subject_id2]>
<input type=submit value="Check this out">
</form>
....

Paul
------------------------------------
Spend an hour a week on CPAN, helps cure all known programming ailments ;-)