Connecting to MySQL on Web server

[Carstonio]

I'm developing a Calendar of Events database for a Web site. Visitors to the
Web site would use an online form to submit calendar items. Then, I would
load recent changes to the database into Access 2000, do some edits, and
insert the results into another table in the database. The editing stage has
to be done because some events occur on more than one day.

Using some information from my Web host (Pair Networks) and on CPAN's page
on DBI, I wrote a Perl script to insert the visitor-entered data into a
table. All this data is text. The script fetches and inserts the current
date, to enable me to select the most recently added items for editing.

I want to use Access 2000 to connect to the database, because that seems the
simplest way of editing the contents of each field. If Access is on my Win2K
machine, and MySQL is on my Web server, how do I set up the connection? I'm
assuming this would be a DSN-less connection, since I don't have MySQL on my
Win2K machine and I don't have administrator rights to add the MySQL driver.
Any help would be great.

 

[Dan Artuso]

Hi,
Go to the MySql site and look for MyAccess. It's an add-in for Access that allows you to manage a MySql
database from within Access. You of course have to create a log in in MySql that will allow you to connect.
Also, you defintely need to have the driver on your box or you will never connect.

 

[Mike Painter]

Carstonio wrote:

I want to use Access 2000 to connect to the database, because that
seems the simplest way of editing the contents of each field. If
Access is on my Win2K machine, and MySQL is on my Web server, how do
I set up the connection? I'm assuming this would be a DSN-less
connection, since I don't have MySQL on my Win2K machine and I don't
have administrator rights to add the MySQL driver. Any help would be
great.

Another poster told you how but the chance of any host allowing you to do
this today is about zero.
If they allow it, MOVE to one that does not because if you can, *they* can,
and they might not be nice.

 

[Dreama]

If you can write a script to update the database, why bother with brain-dead
Access and associated format and data-type compatibility issues?
A desktop based web client would be a reasonable alternative for direct
editing if a GUI is really what you need. Even the MySQL Administrator
software would suffice ( http://www.mysql.com/products/administrator/ ).

The repeating events could easily be managed with a script and SQL, either
with repeated records with new trigger times or by using a more
sophisticated schema with one EVENT table containing distinct events and
another TRIGGER table containing the triggers and a foreign key pointing to
the EVENT table.

DLM

 

[Carstonio]

That's what I was thinking, too. I started exploring some Perl scripts to
install on my Web server. But I suspect the MySQL people have some better GUI
scripts. All I need is a GUI for editing the database itself, not the
server's MySQL setup. This would have to on the Web server, so I could use
the login and password to log in from the Web browser.

 

[Dreama]

Personally, I have used VB to interface to Access tables, GUI style, but for
mysql I use either the text mode SQL client on my linux server or for
regular changes build a web interface to send the changes to a perl cgi
script which then use the perl DBI/DBD mechanism to execute the SQL with
appropriate permissions. The html does not need to be public, and the cgi
does not even have to be on the mysql server host - it can authenticate
remotely. The key is to be able to write perl to intercept the CGI stream,
build an SQL query string based on that and then connect to the DB and
execute it. Just 3 tiny things.
The perl looks something like this:

#!/usr/bin/perl

use DBI;
use CGI;
use CGI qwstandard);
use CGI::Carp qw(fatalsToBrowser);
use vars qw($DBH $CGI);

my $DBname = ""; #edit this
my $username = ""; #edit this
my $password = ""; #edit this
my $server = ":server.domain"; #edit this - empty or :domain
my $database = "DBI:mysql:$DBname$server";
$dbh=DBI->connect($database, $username, $password) or die "Couldn't connect
to $database";

#get data from CGI
my $v1 = param("v1");
my $v2 = param("v2");
my $v3 = param("v3");
#... etc mapping form input names to vars

#perl processing as required

# execute SQL
my $query = "select * from diary where $v1 like $v2 limit $v3"; # build SQL
with interpolated variables
$sth = $dbh->prepare($query);
$sth->execute;
# is query was an edit no need to retrieve anything but a return value /
refresh web view
# can also use single statement do syntax
print "Content-type: text/html\n\n"; #keep http happy
# print html as req
exit;
################################################

 

[Carstonio]

Thanks for your help. For editing the database, I installed phpMyAdmin on my
Web server. It allows me to edit a row's fields in the database, and it's
compatible with MySQL without any tweaking.