I have developed a football statistics database that I update each week for
the 120 d1a college football teams and the 32 pro teams. Included is the
schedule so you can pick a game and the various yds and points are predicted
for upcoming games. Its fun and I do well on college pickem and pigskin
pickem on espn. I update from web pages to excel sheets and then merge to my
database with a view each week. What is the easiest way to reload a MySQL
table on a server [godaddy] from my foxpro data table?
Gary Jeurink
--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/06DD2B066F8A494493BE3E7E826A810D@OwnerPC
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
On Tue, Aug 9, 2011 at 5:26 PM, Gary Jeurink <g.jeurink@charter.net> wrote:
> I have developed a football statistics database that I update each week for
> the 120 d1a college football teams and the 32 pro teams. Included is the
> schedule so you can pick a game and the various yds and points are predicted
> for upcoming games. Its fun and I do well on college pickem and pigskin
> pickem on espn. I update from web pages to excel sheets and then merge to my
> database with a view each week. What is the easiest way to reload a MySQL
> table on a server [godaddy] from my foxpro data table?
>
-------------
I would do update statements as well as insert statements.
You can crunch many togther via a union and pass a long string as a script
So how do you set up to accomplish this? I would make two passes to
identify updates to existing rows and then add in new rows. Create a
script per table for both types of statements.
Just a guess but you have a row for each team in a game ? You also
put in some rows to support players accomplishments in that game?
Inserts are pretty simple :
INSERT INTO table1 (field1, field2)
VALUES ('foo', 'bar');
UPDATE table1 SET field1 = 'foobar', field2' = 'new value' where ID=???
--
Stephen Russell
Unified Health Services
60 Germantown Court
Suite 220
Cordova, TN 38018
Telephone: 888.510.2667
901.246-0159 cell
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CAJidMYJdGQKuaE6SKtCEiCyF81hozvJHt9JWSL_wc9gUojaJoA@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
I'm not great at PHP but I'm learning. Tables are cTeams, cGames, cTmstats.
The cTmstats gets updated each week along with cGames that have been played.
I don't know how to update the MySQL cTmStats with the foxpro cTmstats.dbf
each Tuesday morning. I need to update the MySQL tables at godaddy with my
foxpro data and I don't know how. PHP gives me access to MySQL but how do I
connect foxpro? ... I tried delimited text but they usually crash at 1/2 to
2/3rds using MyAdmin... whatever its called.
Gary Jeurink
-----Original Message-----
From: Stephen Russell [mailto:srussell705@gmail.com]
Sent: Tuesday, August 09, 2011 6:07 PM
To: ProFox Email List
Subject: Re: visual foxpro 6 to mysql
On Tue, Aug 9, 2011 at 5:26 PM, Gary Jeurink <g.jeurink@charter.net> wrote:
> I have developed a football statistics database that I update each week
for
> the 120 d1a college football teams and the 32 pro teams. Included is the
> schedule so you can pick a game and the various yds and points are
predicted
> for upcoming games. Its fun and I do well on college pickem and pigskin
> pickem on espn. I update from web pages to excel sheets and then merge to
my
> database with a view each week. What is the easiest way to reload a MySQL
> table on a server [godaddy] from my foxpro data table?
>
-------------
I would do update statements as well as insert statements.
You can crunch many togther via a union and pass a long string as a script
So how do you set up to accomplish this? I would make two passes to
identify updates to existing rows and then add in new rows. Create a
script per table for both types of statements.
Just a guess but you have a row for each team in a game ? You also
put in some rows to support players accomplishments in that game?
Inserts are pretty simple :
INSERT INTO table1 (field1, field2)
VALUES ('foo', 'bar');
UPDATE table1 SET field1 = 'foobar', field2' = 'new value' where ID=???
--
Stephen Russell
Unified Health Services
60 Germantown Court
Suite 220
Cordova, TN 38018
Telephone: 888.510.2667
901.246-0159 cell
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/33275FD21D904FC5A6EB095C1FA31506@OwnerPC
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Hi Gary
First of all download MyODBC from the MySQL website if not done already.
This will give you a new odbc driver in control panel.
The variables below for ipaddress, dbname, userid and password should all be on PHPMyAdmin which is the way most hosting companies allow you to manage MySQL. You may need to ask for the IP because it isn't always just an IP . On one of my sites it is "cluster.mysqlinstance.domainname.com".
This fox code downloads from one of my databases.
nMySQL1=Sqlstringconnect("Driver={MySQL ODBC 5.1 Driver}; Server=<ipaddress>; Database=<dbname>; UID=<userid>; PWD=<pass>; Option=3")
If nMySQL1>=0
SQLEXEC(nMySQL1,"select * from tblagents","crs")
Select crs
Endif
SQLDISCONNECT(nMySQL1)
Once you have a handle to the database via Stringconnect you can fire any SQL commands at it.
I sometimes have weird issues where commands do not complete. AsyncMode in the properties usually fixes this.
SQLSETPROP(nMySQL, 'asynchronous', .F.)
UDL files are a good way to get at a connection string. If you haven't used them before right click on your desktop/new/text doc. Rename this to .UDL and run it.
You'll get the standard windows wizard. Fill in the blanks and test connection. Rename back to .txt open it and there's your connstring.
If you can't connect remotely to the MySQL database another way is to FTP the file and run a php webpage both via fox.
Google has lots of resources for parsing a csv in php. This is the page at the top of the list
http://php.net/manual/en/function.fgetcsv.php
Cheers
Graham
-----Original Message-----
From: profox-bounces@leafe.com [mailto:profox-bounces@leafe.com] On Behalf Of Gary Jeurink
Sent: 10 August 2011 04:31
To: GrahamB
Subject: RE: visual foxpro 6 to mysql
I'm not great at PHP but I'm learning. Tables are cTeams, cGames, cTmstats.
The cTmstats gets updated each week along with cGames that have been played.
I don't know how to update the MySQL cTmStats with the foxpro cTmstats.dbf each Tuesday morning. I need to update the MySQL tables at godaddy with my foxpro data and I don't know how. PHP gives me access to MySQL but how do I connect foxpro? ... I tried delimited text but they usually crash at 1/2 to 2/3rds using MyAdmin... whatever its called.
Gary Jeurink
-----Original Message-----
From: Stephen Russell [mailto:srussell705@gmail.com]
Sent: Tuesday, August 09, 2011 6:07 PM
To: ProFox Email List
Subject: Re: visual foxpro 6 to mysql
On Tue, Aug 9, 2011 at 5:26 PM, Gary Jeurink <g.jeurink@charter.net> wrote:
> I have developed a football statistics database that I update each
> week
for
> the 120 d1a college football teams and the 32 pro teams. Included is
> the schedule so you can pick a game and the various yds and points are
predicted
> for upcoming games. Its fun and I do well on college pickem and
> pigskin pickem on espn. I update from web pages to excel sheets and
> then merge to
my
> database with a view each week. What is the easiest way to reload a
> MySQL table on a server [godaddy] from my foxpro data table?
>
-------------
I would do update statements as well as insert statements.
You can crunch many togther via a union and pass a long string as a script
So how do you set up to accomplish this? I would make two passes to identify updates to existing rows and then add in new rows. Create a script per table for both types of statements.
Just a guess but you have a row for each team in a game ? You also put in some rows to support players accomplishments in that game?
Inserts are pretty simple :
INSERT INTO table1 (field1, field2)
VALUES ('foo', 'bar');
UPDATE table1 SET field1 = 'foobar', field2' = 'new value' where ID=???
--
Stephen Russell
Unified Health Services
60 Germantown Court
Suite 220
Cordova, TN 38018
Telephone: 888.510.2667
901.246-0159 cell
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/0AF51B84D903A144B9EE71AEAFE7E81C0EC1E5@compsys1.CompySys.local
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Hello Gary,
Graham gave some good advice. One thing I seem to recall people talking
about GoDaddy, is that there is a setting to allow for external
connections to the MySQL database. Something needs to be turned on for
that to work, if I recall correctly.
If you are using phpMyAdmin, you can use Foxpro to create a string of
INSERT statements. Check out TEXT ... ENDTEXT with the TEXTMERGE option.
SELECT MyCursorOfNewTeamStats
SCAN
TEXT TO lcNewStats ADDITIVE TEXTMERGE NOSHOW
INSERT INTO cTmStats (gamePK, cStat, nValue) VALUES
(<<MyCursorOfNewTeamStats.gamePK>>,'<<MyCursorOfNewTeamStats.cStat>>',<<MyCursorOfNewTeamStats.nValue>>);
ENDTEXT
ENDSCAN
_CLIPTEXT = lcNewStats && <-- Copies the series of INSERT statements
from variable lcNewStats to the clipboard
Now you can switch over to phpMyAdmin and paste in the series of
commands into the SQL window and execute it.
Quick. Dirty. Works. I (basically) use this scenario to update my
archery club's MySQL based calendar for the year.
-Kevin
On 08/09/2011 11:29 PM, Gary Jeurink wrote:
> I'm not great at PHP but I'm learning. Tables are cTeams, cGames, cTmstats.
> The cTmstats gets updated each week along with cGames that have been played.
> I don't know how to update the MySQL cTmStats with the foxpro cTmstats.dbf
> each Tuesday morning. I need to update the MySQL tables at godaddy with my
> foxpro data and I don't know how. PHP gives me access to MySQL but how do I
> connect foxpro? ... I tried delimited text but they usually crash at 1/2 to
> 2/3rds using MyAdmin... whatever its called.
>
> Gary Jeurink
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/4E427BD3.8090900@cullytechnologies.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
On Wed, Aug 10, 2011 at 7:38 AM, Kevin Cully
<kcully@cullytechnologies.com> wrote:
> Hello Gary,
>
> _CLIPTEXT = lcNewStats && <-- Copies the series of INSERT statements
> from variable lcNewStats to the clipboard
>
> Now you can switch over to phpMyAdmin and paste in the series of
> commands into the SQL window and execute it.
>
------
CHEATER!
--
Stephen Russell
Unified Health Services
60 Germantown Court
Suite 220
Cordova, TN 38018
Telephone: 888.510.2667
901.246-0159 cell
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CAJidMYKb+J7Eo2mGmRYDGvCvAs8R_Hd3kJcH7d+Xpkg4Q8npvg@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Cheater?!? Genius, I say! LOL.
I update the calendar in a major way once a year. This is an unpaid
position.
Once a year = Get-r-done as quickly and efficiently as possible. No
major project development would be warranted.
On 08/10/2011 09:12 AM, Stephen Russell wrote:
> CHEATER!
--
Kevin Cully
CULLY Technologies, LLC
678-929-7762
kcully@cullytechnologies.com
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/4E429779.9070909@cullytechnologies.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
On 8/10/2011 10:36 AM, Kevin Cully wrote:
> Cheater?!? Genius, I say! LOL.
>
> I update the calendar in a major way once a year. This is an unpaid
> position.
>
> Once a year = Get-r-done as quickly and efficiently as possible. No
> major project development would be warranted.
I thought it as brilliant! :-)
--
Mike Babcock, MCP
MB Software Solutions, LLC
President, Chief Software Architect
http://mbsoftwaresolutions.com
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/4E4299FD.4090609@mbsoftwaresolutions.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
>> One thing I seem to recall people talking about GoDaddy, is that there is a setting to allow for external connections
to the MySQL database. Something needs to be turned on for that to work, if I recall correctly.<<
Yes, the hosting account has to be on a server that has the ability to "Allow Direct Database Access". They might have
to move your hosting to another server to get this feature. It could take a couple of days to get it moved too.
One other recommendation I have if you are using MySQL databases is to get a product like SQLyog Enterprise (~$80). It
is dozens of times better than phpMyAdmin
Rick
White Light Computing, Inc.
www.whitelightcomputing.com
www.swfox.net
www.rickschummer.com
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/006401cc576d$d9510880$8bf31980$@whitelightcomputing.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
On 8/10/2011 10:57 AM, Rick Schummer wrote:
>>> One thing I seem to recall people talking about GoDaddy, is that there is a setting to allow for external connections
> to the MySQL database. Something needs to be turned on for that to work, if I recall correctly.<<
>
> Yes, the hosting account has to be on a server that has the ability to "Allow Direct Database Access". They might have
> to move your hosting to another server to get this feature. It could take a couple of days to get it moved too.
>
> One other recommendation I have if you are using MySQL databases is to get a product like SQLyog Enterprise (~$80). It
> is dozens of times better than phpMyAdmin
I've been using SQLYog Enterprise for years now. Absolutely love it.
For me, it's the same class of quality tools as Doug Hennig's Stonefield
Database Toolkit is for my VFP databases, and Rick Schummer's ViewEditor
is for my views database management.
Urls:
http://www.stonefield.com/sdt.aspx
http://whitelightcomputing.com/prodvieweditorpro.htm
In my opinion, these are MUST HAVES if you're working with the
respective technologies (MySQL, VFP free or dbc tables, VFP views
databases). And I am NOT a paid spokesperson for any of them! :-)
--
Mike Babcock, MCP
MB Software Solutions, LLC
President, Chief Software Architect
http://mbsoftwaresolutions.com
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/4E42A018.50909@mbsoftwaresolutions.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.