Hi,
Wow, I?m pretty sure this is the first question I?ve taken on where I
have way too much to give you. Heh.. so I?ll put your own words back
to you first, I?m open for clarification if you need it. I do this
type of thing all day long, so I might speak ?above? you a bit, but
I?ll try not to. Again, if you need to, just ask for clarification.
Alright. First off, I think PHP is probably the most documented
language out there on the web , especially with regards to connecting
to MySQL. Perhaps Perl might be a squeak ahead, on a given day if
someone?s main document serer is down, but that?s about it. Also, you
have at your fingers a huge assortment of libraries to use to save you
a great deal of time. The one I will point you too is Pear. Or the
Pear System.
http://www.pear.org
PHP has a great collection of functions to work with MySQL directly,
http://www.php.net/manual/en/ref.mysql.php
In the Pear DB library you will find classes that access MySQL with
exactly the same functions you would just ?use? anyway.. for example
you might find functions that will look like these:
function db_num_rows( $qid ) {
return mysql_num_rows( $qid );
}
function db_fetch_row( $cur ) {
return mysql_fetch_row( $cur );
}
function db_fetch_assoc( $cur ) {
return mysql_fetch_assoc( $cur );
}
function db_fetch_array( $cur ) {
return mysql_fetch_array( $cur );
}
As you can see, all these functions are doing basically is changing
the name of the PHP native function. So why am I pointing you to Pear?
Really, or at least because of, the type of thing you are facing right
now. Trust me, it won?t be the last time some one wants to change a
core object on you.
If we wrote the whole program using the mysql_functions in our code,
then our code is sprinkled with calls we will have to hunt down and
alter, by hand, causing many many bugs, when someone wants our
program, but needs it to work on .. say, Oracle or DB2. The SQL is
basically the same, but the calls are all different.
But, if we have all these in a single file, and have called these
included functions throughout our program using the db_functions
instead, all we have to do is make a file which does the same thing,
using Oracle calls. We also now have a new library so if someone askes
us for the same thing, we have both.
Pear, has already done this for you with several databases. The DB
library in Pear talks to just about every database out there, and a
few that aren?t out there any longer. So definitely check that out. On
top of that, its all object calls, which makes the coding much faster
(at least I think it is, your milage may vary there).
Now, about this MsAccess. thing. You are in luck there as well. We
will use ODBC for that, or in this case MyODBC, I?m not much of an
Access fan, but I have to say this set up is pretty cool, and I?ve
used it myself several times.
http://www.mysql.com/products/connector/odbc/
What we do with this is set it up on the clients computer, and then,
using Access, make Data Links to the MySQL database. Now, both Web
users, and Office users can use the same set of live data, at the same
time. No downloading, uploading, or any of that other nonsense. For
them, they are working exactly as they are use to working and your
code continues with no changes at all.
Since they probably know what information they want, and probably
already have al the data files, the best way to go is have them export
the database schema and use that to create your MySQL database. That
way they can use any forms and report they have already created. The
database schema is simply an SQL statement set that creates the
database for you.
The last time I setup the ODBC (in fact, every time) I found it to be
a bit tricky. I had to read the instructions several times (even
though it is very well documented) and play with it. Be prepared to be
a bit frustrated by the time you get it to work. Once you have it
though, its as solid as a rock. Its just getting the settings right.
As soon as you have that, making the data links to the data tables is
simple and from there the users in the office will never know they are
not working with Access directly.
Database security.
When you set your permissions on the MySQL server for this setup, you
are going to have to be very specific. For your website your grant
line is
grant all on mydatabase.* to myuser@localhost identified by ?mypassword?;
for the ODBC connections, you need their IP address. This will
probably not be the address of their machine, but the address of the
router between them and the Internet. This line will look like
grant all on mydatabase.* to my_odbcuser@199.199.199.199 identified by
*.my_odbc_password?
You can do this with as many machine IP addresses as you need to, but
make sure you do each seperatly, and each having its own username and
password set. This username and password set is used by the ODBC to
connect, so the user actually using the database, will never need to
know it. So, make the passwords as cryptic as you like (the more the
better).
This is a nice setup, because even if someone happens to find out the
username and password, they still have to be accessing the data base
from that IP address. Make sure you keep these written down some where
incase of problems, so you can use the MySQL logs to help find out
where those problems are coming from.
Most PHP installs have Pear installed by default. So no worries there.
Another PHP package you might want to look into since you have a bit
of time before starting, is the Smarty Template Library. This is a
very cool system. I just started using this last year, and man I wish
I had it for the previous five. I weep at the time I could have saved
and the hassle of changing simple things for users.
A template library separates the design of the website, from the code
of the website.
http://smarty.php.net
What this means to you as the programmer is when they, the customer,
wants to change something, such as moving the menu bar from the side
to the top, or adding a menu bar to the top, or changing the page
flows or changing the way the links look like, or anything. You change
the template and you are done. No coding changes. If they, the
customer, know how to create pages in Dreamweaver, instead of having
to go through all the Dreamweaver files, altering links and adding
code headers, you just let them, and even give them the set of
template keys they can use. They can change it to their hearts
content. Won?t bother your code at all. They wont? even see it.
Smarty is just one of several hundred template libraries out there.
Again, we?ve seen this coming just like we saw the database changes
coming. But, Smarty has a few more advantages to it that others don?t
do as well (in my opinion anyway). First, the templates are compiled.
What this means is they are much faster on the web than non-compiled.
PHP, like Perl is a Scripting Language. C++ would be a compiled
language. So, when a user calls your script on the webserver, the
server has to get your file, compile your file, and then show your
file. Smarty, using a good template engine, takes out this middle man.
The setup is a bit weird, and it takes some time to go through the
documentation and some tutorials on their website to get the hang of
it but once you do, it really starts saving time. And, my clients love
it.
Okay, I think I?ve given you plenty to start with. If you think I
might have left something out, just let me know.
thanks,
webadept-ga |
Clarification of Answer by
webadept-ga
on
26 Apr 2004 19:24 PDT
Sorry about that.. this line here
grant all on mydatabase.* to my_odbcuser@199.199.199.199 identified by
*.my_odbc_password?
is
grant all on mydatabase.* to my_odbcuser@199.199.199.199 identified by
"my_odbc_password?
I have happy fingers today .. I guess.
webadept-ga
|
Clarification of Answer by
webadept-ga
on
26 Apr 2004 19:32 PDT
Just a long foot note here. I don't use the Pear library, unless I can
see that there is a strong possibility of my program being used across
several different databases. If I'm doing an OpenSource project I will
use it, for example, but for most website projects I use a include
file I've been using for years. Here it is, and you are free to use
it or not. It is just a set of functions that have constently come in
handy over time.
Put the whole lot into a file and call it something like database.inc
and use in your code
include_once 'database.inc'
and that's it.
// webadept-ga's handy MySQL functions for PHP
//
function connect_db()
{
global $DBUSER;
global $PASSWD;
global $DBASE;
$link = mysql_pconnect("localhost", $DBUSER, $PASSWD) or die("Could
not connect: " . mysql_error());
if($link && mysql_select_db($DBASE))
{
return $link;
}
print "Damn!<BR><HR>";
return FALSE;
}
/*/------------------------------------------------------------------------------//
//------------------------------------------------------------------------------/*/
function db_error() {
return mysql_error();
}
function db_errno() {
return mysql_errno();
}
function db_insert_id() {
$q = "SELECT LAST_INSERT_ID() as lastID";
$r = mysql_query($q);
$a = mysql_fetch_array($r);
return $a['lastID'];
}
function db_exec( $sql ) {
$r = mysql_query( $sql );
if( !$r ) {
return false;
}
return $r;
}
function db_free_result( $r ) {
mysql_free_result( $r );
}
function db_num_rows( $qid ) {
return mysql_num_rows( $qid );
}
function db_fetch_row( $r ) {
return mysql_fetch_row( $r );
}
function db_fetch_assoc( $r ) {
return mysql_fetch_assoc( $r );
}
function db_fetch_array( $r ) {
return mysql_fetch_array( $r );
}
function db_fetch_object( $r ) {
return mysql_fetch_object( $r );
}
function db_escape( $str ) {
return mysql_escape_string( $str );
}
/* added by ed on 7 oct 2003 */
function db_addslash( $str ) {
return addslashes( $str );
}
function db_version() {
;
if( ($r = mysql_query( "SELECT VERSION()" )) ) {
$row = mysql_fetch_row( $r );
mysql_free_result( $r );
return $row[0];
} else {
return 0;
}
}
function db_unix2dateTime( $time ) {
// converts a unix time stamp to the default date format
return $time > 0 ? date("Y-m-d H:i:s", $time) : null;
}
function db_dateTime2unix( $time ) {
if ($time == '0000-00-00 00:00:00') {
return -1;
}
if( ! preg_match( "/^(\d{4})-(\d{2})-(\d{2})
(\d{2}):(\d{2}):(\d{2})(.?)$/", $time, $a ) ) {
return -1;
} else {
return mktime( $a[4], $a[5], $a[6], $a[2], $a[3], $a[1] );
}
}
/*/------------------------------------------------------------------------------//
This global function loads the first field of the first row
returned by the query.
@param string The SQL query
@return The value returned in the query or null if the query failed.
//------------------------------------------------------------------------------/*/
function db_loadResult( $sql ) {
$r = db_exec( $sql );
$r or exit( db_error() );
$ret = null;
if ($row = db_fetch_row( $r )) {
$ret = $row[0];
}
db_free_result( $r );
return $ret;
}
/*/------------------------------------------------------------------------------//
This global function loads the first row of a query into an object
If an object is passed to this function, the returned row is bound
to the existing elements of <var>object</var>.
If <var>object</var> has a value of null, then all of the returned
query fields returned in the object.
@param string The SQL query
@param object The address of variable
//------------------------------------------------------------------------------/*/
function db_loadObject( $sql, &$object, $bindAll=false ) {
if ($object != null) {
$hash = array();
if( !db_loadHash( $sql, $hash ) ) {
return false;
}
bindHashToObject( $hash, $object, null, true, $bindAll );
return true;
} else {
$r = db_exec( $sql );
$r or exit( db_error() );
if ($object = db_fetch_object( $r )) {
db_free_result( $r );
return true;
} else {
$object = null;
return false;
}
}
}
/*/------------------------------------------------------------------------------//
//------------------------------------------------------------------------------/*/
/**
* This global function return a result row as an associative array
*
* @param string The SQL query
* @param array An array for the result to be return in
* @return <b>True</b> is the query was successful, <b>False</b> otherwise
*/
function db_loadHash( $sql, &$hash ) {
$r = db_exec( $sql );
$r or exit( db_error() );
$hash = db_fetch_assoc( $r );
db_free_result( $r );
if ($hash == false) {
return false;
} else {
return true;
}
}
/*/------------------------------------------------------------------------------//
//------------------------------------------------------------------------------/*/
/**
* Document::db_loadHashList()
*
* { Description }
*
* @param string $index
*/
function db_loadHashList( $sql, $index='' ) {
$r = db_exec( $sql );
$r or exit( db_error() );
$hashlist = array();
while ($hash = db_fetch_array( $r )) {
$hashlist[$hash[$index ? $index : 0]] = $index ? $hash : $hash[1];
}
db_free_result( $r );
return $hashlist;
}
/*/------------------------------------------------------------------------------//
//------------------------------------------------------------------------------/*/
/**
* Document::db_loadList()
*
* { Description }
*
* @param [type] $maxrows
*/
function db_loadList( $sql, $maxrows=NULL ) {
GLOBAL $AppUI;
if (!($r = db_exec( $sql ))) {;
$AppUI->setMsg( db_error(), UI_MSG_ERROR );
return false;
}
$list = array();
$cnt = 0;
while ($hash = db_fetch_assoc( $r )) {
$list[] = $hash;
if( $maxrows && $maxrows == $cnt++ ) {
break;
}
}
db_free_result( $r );
return $list;
}
/*/------------------------------------------------------------------------------//
//------------------------------------------------------------------------------/*/
/**
* Document::db_loadColumn()
*
* { Description }
*
* @param [type] $maxrows
*/
function db_loadColumn( $sql, $maxrows=NULL ) {
if (!($r = db_exec( $sql ))) {;
return false;
}
$list = array();
$cnt = 0;
while ($row = db_fetch_row( $r )) {
$list[] = $row[0];
if( $maxrows && $maxrows == $cnt++ ) {
break;
}
}
db_free_result( $r );
return $list;
}
/*/------------------------------------------------------------------------------//
//------------------------------------------------------------------------------/*/
/* return an array of objects from a SQL SELECT query
* class must implement the Load() factory, see examples in Webo classes
* @note to optimize request, only select object oids in $sql
*/
function db_loadObjectList( $sql, $object, $maxrows = NULL ) {
$r = db_exec( $sql );
if (!$r) {
die( "db_loadObjectList : " . db_error() );
}
$list = array();
$cnt = 0;
while ($row = db_fetch_array( $r )) {
$list[] = $object->Load( $row[0] );
if( $maxrows && $maxrows == $cnt++ ) {
break;
}
}
db_free_result( $r );
return $list;
}
/*/------------------------------------------------------------------------------//
//------------------------------------------------------------------------------/*/
/**
* Document::db_insertArray()
*
* { Description }
*
* @param [type] $verbose
*/
function db_insertArray( $table, &$hash, $verbose=false ) {
$fmtsql = "insert into $table ( %s ) values( %s ) ";
foreach ($hash as $k => $v) {
if (is_array($v) or is_object($v) or $v == NULL) {
continue;
}
$fields[] = $k;
$values[] = "'" . db_escape( $v ) . "'";
}
$sql = sprintf( $fmtsql, implode( ",", $fields ) , implode( ",", $values ) );
($verbose) && print "$sql<br />\n";
if (!db_exec( $sql )) {
return false;
}
$id = db_insert_id();
return true;
}
/*/------------------------------------------------------------------------------//
//------------------------------------------------------------------------------/*/
/**
* Document::db_updateArray()
*
* { Description }
*
* @param [type] $verbose
*/
function db_updateArray( $table, &$hash, $keyName, $verbose=false ) {
$fmtsql = "UPDATE $table SET %s WHERE %s";
foreach ($hash as $k => $v) {
if( is_array($v) or is_object($v) or $k[0] == '_' ) // internal or NA field
continue;
if( $k == $keyName ) { // PK not to be updated
$where = "$keyName='" . db_escape( $v ) . "'";
continue;
}
if ($v == '') {
$val = 'NULL';
} else {
$val = "'" . db_escape( $v ) . "'";
}
$tmp[] = "$k=$val";
}
$sql = sprintf( $fmtsql, implode( ",", $tmp ) , $where );
($verbose) && print "$sql<br />\n";
$ret = db_exec( $sql );
return $ret;
}
/*/------------------------------------------------------------------------------//
//------------------------------------------------------------------------------/*/
/**
* Document::db_delete()
*
* { Description }
*
*/
function db_delete( $table, $keyName, $keyValue ) {
$keyName = db_escape( $keyName );
$keyValue = db_escape( $keyValue );
$ret = db_exec( "DELETE FROM $table WHERE $keyName='$keyValue'" );
return $ret;
}
/*/------------------------------------------------------------------------------//
//------------------------------------------------------------------------------/*/
/**
* Document::db_insertObject()
*
* { Description }
*
* @param [type] $keyName
* @param [type] $verbose
*/
function db_insertObject( $table, &$object, $keyName = NULL, $verbose=false ) {
$fmtsql = "INSERT INTO $table ( %s ) VALUES ( %s ) ";
foreach (get_object_vars( $object ) as $k => $v) {
if (is_array($v) or is_object($v) or $v == NULL) {
continue;
}
if ($k[0] == '_') { // internal field
continue;
}
$fields[] = $k;
$values[] = "'" . db_escape( $v ) . "'";
}
$sql = sprintf( $fmtsql, implode( ",", $fields ) , implode( ",", $values ) );
($verbose) && print "$sql<br />\n";
if (!db_exec( $sql )) {
return false;
}
$id = db_insert_id();
($verbose) && print "id=[$id]<br />\n";
if ($keyName && $id)
$object->$keyName = $id;
return true;
}
/*/------------------------------------------------------------------------------//
//------------------------------------------------------------------------------/*/
/**
* Document::db_updateObject()
*
* { Description }
*
* @param [type] $updateNulls
*/
function db_updateObject( $table, &$object, $keyName, $updateNulls=true ) {
$fmtsql = "UPDATE $table SET %s WHERE %s";
foreach (get_object_vars( $object ) as $k => $v) {
if( is_array($v) or is_object($v) or $k[0] == '_' ) { // internal or NA field
continue;
}
if( $k == $keyName ) { // PK not to be updated
$where = "$keyName='" . db_escape( $v ) . "'";
continue;
}
if ($v === NULL && !$updateNulls) {
continue;
}
if( $v == '' ) {
$val = "''";
} else {
$val = "'" . db_escape( $v ) . "'";
}
$tmp[] = "$k=$val";
}
$sql = sprintf( $fmtsql, implode( ",", $tmp ) , $where );
return db_exec( $sql );
}
/*/------------------------------------------------------------------------------//
//------------------------------------------------------------------------------/*/
/**
* Document::db_dateConvert()
*
* { Description }
*
*/
function db_dateConvert( $src, &$dest, $srcFmt ) {
$result = strtotime( $src );
$dest = $result;
return ( $result != 0 );
}
/*/------------------------------------------------------------------------------//
//------------------------------------------------------------------------------/*/
/**
* Document::db_datetime()
*
* { Description }
*
* @param [type] $timestamp
*/
function db_datetime( $timestamp = NULL ) {
if (!$timestamp) {
return NULL;
}
if (is_object($timestamp)) {
return $timestamp->toString( '%Y-%m-%d %H:%M:%S');
} else {
return strftime( '%Y-%m-%d %H:%M:%S', $timestamp );
}
}
/*/------------------------------------------------------------------------------//
//------------------------------------------------------------------------------/*/
/**
* Document::db_dateTime2locale()
*
* { Description }
*
*/
function db_dateTime2locale( $dateTime, $format ) {
if (intval( $dateTime)) {
$date = new CDate( $dateTime );
return $date->format( $format );
} else {
return null;
}
}
/*/------------------------------------------------------------------------------//
//------------------------------------------------------------------------------/*/
/*
* copy the hash array content into the object as properties
* only existing properties of object are filled. when undefined in
hash, properties wont be deleted
* @param array the input array
* @param obj byref the object to fill of any class
* @param string
* @param boolean
* @param boolean
*/
function bindHashToObject( $hash, &$obj, $prefix=NULL,
$checkSlashes=true, $bindAll=false ) {
is_array( $hash ) or die( "bindHashToObject : hash expected" );
is_object( $obj ) or die( "bindHashToObject : object expected" );
if ($bindAll) {
foreach ($hash as $k => $v) {
$obj->$k = ($checkSlashes && get_magic_quotes_gpc()) ?
stripslashes( $hash[$k] ) : $hash[$k];
}
} else if ($prefix) {
foreach (get_object_vars($obj) as $k => $v) {
if (isset($hash[$prefix . $k ])) {
$obj->$k = ($checkSlashes && get_magic_quotes_gpc()) ?
stripslashes( $hash[$k] ) : $hash[$k];
}
}
} else {
foreach (get_object_vars($obj) as $k => $v)
{
if (isset($hash[$k]))
{
$obj->$k = ($checkSlashes && get_magic_quotes_gpc()) ?
stripslashes( $hash[$k] ) : $hash[$k];
}// end if
}// end foreach
} // end if else
//echo "obj="; print_r($obj); exit;
} // end function
/*/------------------------------------------------------------------------------//
//------------------------------------------------------------------------------/*/
function db_nowdate()
{
$mysqldate = date(Ymd);
return($mysqldate);
}
/*/------------------------------------------------------------------------------//
//------------------------------------------------------------------------------/*/
function db_make_array($result, $col=0)
{
/*
Takes a result set and turns the optional column into
an array
*/
$i=0;
while($row = db_fetch_array($result))
{
$a_return[$i] = $row[$col];
$i++;
}
return $a_return;
}
/*/------------------------------------------------------------------------------//
//------------------------------------------------------------------------------/*/
function findMonday($date)
{
//$date is in the mySQL form of YYYYMMDD
$year = intval(substr($date,0,4));
$month = intval(substr($date,4,2));
$day = intval(substr($date,6,2));
$dow = ((date("w", mktime(0,0,0,$month,$day,$year)) + 6) % 7);
$monDate = date("Ymd", mktime(0,0,0,$month,($day-$dow),$year));
return $monDate;
}
function db_fetch_enums( $link, $table_name, $field_name ){
/* Takes a connection link identifier, MySQL table name and field name for
an enum type field
Returns an associative array containing the enum values as both key and
value,ready to feed
to dropdown.inc; or 0 on error */
//mysql_select_db($database);
$mysql_datatype_field = 1;
if (!$result = mysql_query ("SHOW COLUMNS FROM $table_name LIKE
'$field_name'", $link ) )
{
$output=0;
echo mysql_error();
//print "<h2>SHOW COLUMNS FROM $table_name LIKE '$field_name'</h2>";
}
else
{
$mysql_column_data = mysql_fetch_row( $result );
if ( !$enum_data= $mysql_column_data[$mysql_datatype_field] ){
$output=0;
}
else if ( !$buffer_array=explode("'", $enum_data) )
{
$output = 0;
}
else
{
$i = 0;
reset ($buffer_array);
while (list(, $value) = each ($buffer_array))
{
if( $i % 2 ) $output[stripslashes($value)] = stripslashes($value);
++$i;
}
}
}
return $output;
} // end function
Have fun!
|
Request for Answer Clarification by
garbonzo-ga
on
26 Apr 2004 22:29 PDT
Wowzers!
Thank you very much for so much great information, so quick too!
This is a lot to chew on. This will all be very helpful in developing
this project. And a special thanks for the code. However, it still
leaves part of my question unanswered. The fact is, the people I will
be doing this for, Magnolia Music and Events, may not have a constant
connection to the internet, and I also may not be able to set up the
ODBC on their computer. Although your solution sounds like it would be
ideal, I do not think it can work in my case. Also, for the
information I will be dealing with, the volunteer information, they
currently have all of that on paper right now, with a few excel files
here and there. So they do not already have a database schema set up.
So what I'm dealing with is a MySQL database online, and an Access
database on a computer in their office. I will look into the situation
to see if your solution could work, but I would like to know how I
would solve this problem another way.
Is there, perhaps, a way that I could, using PHP, create a file for
them to load into their Access database? And also, load an Access file
to update my MySQL database? This probably seems like a cooky
roundabout way to do things, but viewing the situation, looks like the
only way I'll be able to meet their needs. Thank you so much for your
time and effort. Your help is greatly appreciated.
|
Clarification of Answer by
webadept-ga
on
27 Apr 2004 00:45 PDT
Hi again.
Not the ideal way to go about things certainly. One solution could
then be XML. MySQL will output XML as a download, and Access will load
an XML file. Also, a straight SQL output would probably work. Your
fields are probably not going to be 'weird'. And even if they are, a
conversion function would not be difficult.
What is going to drive you bonkers now however is duplications.
Unfortunately it is going to have to wait until later for me to get to
this for more details. I'll load up my copy of Access, and open a few
books to brush up. Can you post the version of MySQL you will be using
and the version of Access they will be using? That will help.
I'll get back to you in the afternoon sometime. One thing that sticks
out in my mind right now is that Access allows multiple key fields,
and MySQL doesn't. So your design needs to have single primary key
field. Indexes are fine. I know I have a list somewhere of the
conversions for this. I'll find that and load it up as well. I might
still have a function or two from the last time I did this as well. ..
But.. until tomorrow.
thanks,
webadept-ga
|
Request for Answer Clarification by
garbonzo-ga
on
27 Apr 2004 08:14 PDT
Hi again,
I will be using MySQL 4.0.15, but right now I'm not sure what
version of Access they are running. Thank you so much for your extra
efforts.
garbonzo-ga
|
Clarification of Answer by
webadept-ga
on
27 Apr 2004 21:04 PDT
Really I'm going to have to know that.. but it looks like the XML
solution is turning out to be the best. There is a software package
for Dungeons and Dragons that does Character generation, and runs off
the Access Database. Add-on files to this package for libraries come
as XML documents. Here's a small part of one of the add on libraries.
:
-------------------------
<dbTable name="types Set">
<dbStructure>
<colDef name="Id" type="72" size="16"/>
<colDef name="Name" type="202" size="50"/>
<colDef name="Title" type="202" size="255"/>
<primaryKey name="PrimaryKey"><col nref="Id"/></primaryKey>
<indexedKey name="Id" unique="No"><col nref="Id"/></indexedKey>
</dbStructure>
<dbData>
<row>
<col nref="Id">{17C67443-32B0-47A3-842F-68A6681C96E5}</col>
<col nref="Name">phsmall</col>
<col nref="Title">Small Sized Gear from the PHB</col>
</row>
</dbData>
</dbTable>
-------------------------------
Its pretty basic really. As you can see the first thing that the file
does is tell Accss what table it is going to deal with, and then
describes that tables fields. This particular table has more fields
than that (i checked) so it looks like this description should only
describe the fields the following data portion is dealing with.
The next part is the data area itself.
The only thing I'm not sure about right now is that "type" area. I'm
looking that up now and I'll try to get back to you tonight with an
answer to how to use this.
Really.. this is the easy part, getting information from MySQL to
Access, and back again. What is going to be hard, and I do mean hard,
is keeping the data clean. If this was a simple import/export, with
one database being the master, you could give weight to the entries,
meaning, if the Master says it is this, then, that's it, its that..
but you are working with a setup that both Access and MySQL are going
to have live data adds and edits (if I read you right). This is ... in
technical terms .. hell. Really.. it might sound funny but its not.
Let's say a volunteer gets called. She enters the data from the
caller. The Caller then calls back, but calls the office, The office,
not having that entry on their system, re-enters the same data, but,
with a note, because the Caller called back just to add that note to
her file. Now, one of two things is going to happen here. Either you
are going to have a duplicate record, or, you are going to loose that
note. Worse, if the duplicate isn't caught, you could have conflicting
notes in several records.
Some how you have to figure out a way too merge these records, keeping
notes and changes updated. This is not easy. And it is going too be a
great deal of code on your part.
What makes this worse, is you are only dealing with the website. But,
in order to make sure the data is not corrupted, there is going to
have to be a MsAccess code written for their import... wait, it gets
worse. Their MsAccess code has to use the same rules that your PHP
code uses, which is going to slow down production .. just from
experience there.
An alternative might be to have the Office always upload their copy to
the MySQL/PHP program first, and then, make the download from that
upload. That would be the best alternative, but the hardest to code
out. And the hardest on the system.
The upload will have to go into an exact duplicate of the existing
database.. not into the database.. that would be way too much .. trust
me. The Office uploads their entire database, everything, AND, has to
stop entering data until they get their download back and installed
into the Access database.
(what is pinging in my brain right now is that since they are using
Access, .. and Access is not really a "server" .. what they intend,
and what you are going to be dealing with ? is having several
individual Access databases with different information. If this is
what is going to happen, stop now. It's just not going work.
Situations like that is why there are Servers and Clients. You can't
make a Server/Client package out of that mess, not by the end of
summer, and probably not in the next year, not one that works.).
Once the Office database is uploaded, your script starts going through
every record, checking for differences between what they sent, and
what is already there. This is a lot of code.
after you find all the differences, your PHP code then creates a
diff.xml file that can be downloaded by the Office staff. This
diff.xml is then loaded into the Access database, changing and adding
records as it goes.
The problem here is Deletions (just to name one). Let's say the last
time Glenn's record was added to the database at MySQL. Office staff
said, screw that and deleted Glenn's record from the Access file. On
the next upload/download, Glenn's record looks new, so it is added to
the diff.xml, and back in the database in the Office.
The only way to solve a situation like that is to do a great deal of
Access programming. Cause what you really need on both sides of this
Database ocean is a transaction log. The transaction log idea was
pretty popular back in the 80's, because we didn't really have access
to database servers like we do now. It still had a great deal of
problems to it however. But the way it goes is, Anything that is done
to the MySQL database is also recorded in a transaction log. Anything
done on the Access database is recorded in a transaction long. Then,
you exchange transaction logs periodically. Usually once a week or
once a day (the shorter the time frame the better). With XML in use
now, a transaction log setup may work better than it ever did in the
past, and you might have time to code something like that out. But,
you still need an Access programmer to make that end of it for you.
Or, you are doing it yourself.
I need to check up on some things.. a friend of mine noticed this
question last night and gave me a call earlier with a few ideas. I
have to tell you, it is a good thing we are old farts and remember
when this type of setup was the norm. heh. This setup is from back in
the sneaker net days... which means, you made a disk and walked it
over to the other computer.
Back to you soon.
*Note : By the way.. .. from 15+ years of experince on databases and
database programming, I know.. know.. this is a very difficult thing
to do. This problem you are dealing with is why Sever/Client setups
were created. There is just no way to do this, that I'm aware of,
without a great deal of manual clean up on both ends of the database
ocean. So much so, it gets to the point where you might as well be
doing this on paper. So, make a copy of all that code I posted here,
if you haven't already, because if I can't come up with something that
is better than what I know now, I'm going to request this answer to be
pulled. I didn't realize your Office wasn't going to have full time
internet access, or I wouldn't have even tried to answer this one in
the first place. But, I'm going to give it my best and see what I can
find. A few things that my friend suggested might work out... don't
hold your breath though.
webadept-ga
|
Request for Answer Clarification by
garbonzo-ga
on
27 Apr 2004 21:46 PDT
Hiya again,
I can't express how much you have already helped me. Although the
solution to my problem isn't black and white, you have really learned
me a good deal that will help greatly. I easily consider this question
answered, and then some. I will use the information you have given me
to solve this problem in one way or another. I intend to try to talk
MagMusic into adjusting their system, as it would be better for
everyone in the long run.
Seriously, though, I'm eating this stuff up. I have a lot to
research, from PEAR to XML. Your expertise has been much appreciated.
I only wish I had more to give, for this answer deserves more than
$25. Send my thanks to your friend for his/her input as well. I have
saved this whole question, in the event is does go away one day. But
feel free to add any new info, if it tickles your fancy.
your humble student
garbonzo
|
Clarification of Answer by
webadept-ga
on
28 Apr 2004 22:34 PDT
Hi, Thanks for the comment and the extra... I feel like a bit of a cad
though. I talked over some of the suggestions with my friend and
others, and we all came up with "he's so fragged.." cause nothing has
really changed since back in the sneaker net days.
Yes, try to get your client to use the linked ODBC solution. That will
be so much smoother for them, you, and everyone using or affected by
your system. They can make backups and all kinds of things. Really it
is the best of both worlds. I've setup several systems using that and
it just rocks.
If you can't talk them into it, even after you show them this answer
.. perhaps.. then my only real advice before sending you down that
road is .. "blink" ... often.
http://www.allaboutvision.com/cvs/irritated.htm
cause its going to be long nights ahead.
:-)
Good luck to you and thanks,
webadept-ga
|
,
0 Comments
)