Hi,
This is a two part request.
Part 1:
I have an Excel spreadsheet almost 44,000 rows long, but I have been
unable to get it to put the information into my phpMyAdmin SQL type of
database. When I use this script:
LOAD DATA INFILE "C:/sample1.csv" INTO TABLE products FIELDS
TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\n";
I get this error:
MySQL said:
#1045 - Access denied for user: 'bcwqbb302974@26.785.415.6' (Using password: YES)
(actual numbers changed here for security)
However, when I use this proceedure I get the following syntax error.
I use either a .csv file created by Excel save as or a file created
in my editor. I've tried comma delimited and tab delimited files with
careful attention to var types. At this point I use just a one row
file because I'm trying to eliminate error possibilities, but I don't
know what else to do at this point. The access denied error only
comes up when using the above script, but when I use this procedure:
double click the "products" link in the left hand margin to bring up
the products table, click the "Query window" link in the left hand
margin, then click the "import files" tab, then do "browse", click
"autodetect", then click "go", then the syntax error comes up (but not
the access denied error). I've circumvented the script "access error"
with the procedure stated in this paragraph, but there seems to be
some syntax error I can't figure out. Where could the syntax error be
coming from?
MySQL said:
#1064 - You have an error in your SQL syntax near 'b2, c3, d4, e5, f6,
h7, i8, j9' at line 1
I'm not a programmer, so you will need to tell me what information you
will need to answer this question and how to get that information.
So what I'm looking for is either a script that does not give an
access error (and no syntax error when access is allowed) or an
explanation of how I transfer my data without syntax errors using the
click commands of phpMyAdmin.
Part 2:
There is another simple script I'd like to execute that will allow me
to do a complete mirror copy for back-up of my web site to another
url. Because I have a slow dial-up connection, and the products
database is so large and growing, and there are so many web pages and
a growing customer database, I'd prefer to simply do the copy-back-up
from one url server to another automatically and at high-speed. To
ftp the copy as a backup to my pc is not practical. Ideally I will
just have 10 to 15 directories in my other url and each time I execute
the backup it will copy it into the next consecutive directory. That
way if I do it once a day, I will have 2 weeks worth of back-ups. If
I don't catch a corruption for a couple of days, I still have a
previous good back-up I can restore from.
I have php and cgi-bin available to me at both sites. For a quick
answer with working code, I will include a $25 tip.
Thank you for your help in this.
Steve |
Clarification of Question by
stevep234-ga
on
16 Nov 2003 13:57 PST
I meant to say "I'm not a programmer, so you will need to tell me what
information you need me to get for you to answer these questions and
the procedure of how I can get that information for you".
Thanks
|
Request for Question Clarification by
mathtalk-ga
on
16 Nov 2003 15:11 PST
Hi, Steve:
Let's start with some suggestions on the first part, trying to upload
a .CSV (comma-separated-value) file into MySQL.
The error messages you report sound like two distinct problems, but
sometimes there's only one problem and it justs appears with two
symptoms.
The first error message makes it sound like there's a "permissions"
problem, e.g. that the user account corresponding to the credentials
described don't have insert permission on the underlying table. It
might be worth trying attaching to the database in another way, just
to verify the validity and privilege level of the user credentials
used here. However you say you aren't a programmer, so if you don't
know another way to talk to the database directly with that account,
let's set that issue aside for the moment.
Now let's consider the second error message, which certainly sounds
like it's trying to report a syntax error in the file. Probably the
first line of your file contains "column headings" instead of values,
and the column headings (being nonnumeric data) don't make sense for
inserting into the underlying table (whose name seems to be
"products").
So I'd like you to do these experiments:
1) Look at the first line of your .CSV file to verify if it differs
from the rest of the file (by consisting of column headings, rather
than numbers).
2) Assuming that's the case, then build a smaller test file, say only
50 rows long, and remove the first line (thereby omitting the column
headings).
If you need help performing these tasks, let me know what "text
editor" if any you are familiar with, and the platform (PC/Unix/Mac)
that you are most comfortable with.
thanks, mathtalk-ga
|
Clarification of Question by
stevep234-ga
on
16 Nov 2003 16:03 PST
hi, and thank you!
I can insert information manually using the phpMyAdmin interface.
The table is complete and I'm sure the var values are set properly.
My actual Excel spreadsheet is 28 columns by 43,890 rows, so first I
tried a sample of 25 rows, but that did not work, so I've made a test
"sample" database of only one row long, saved it in both comma and tab
delimited formats, double checked with my editor but still got the
same errors - access denied with a script, or syntax error when using
their click commands. I'm using Windows 98SE, I can use an editor ok,
and I think I know enough about Excel, but don't take any of my
knowledge for granted, as I really only know vague theory about
databases, and not the "nuts and bolts" of them.
Did that help to clarify any?
Let me know, and thank you.
Steve
|
Clarification of Question by
stevep234-ga
on
16 Nov 2003 16:05 PST
The text editor I use is called "Edit Pad Lite".
|
Clarification of Question by
stevep234-ga
on
16 Nov 2003 16:13 PST
hi,
Did you unlock the question because you gave up?
Steve
|
Clarification of Question by
stevep234-ga
on
16 Nov 2003 16:17 PST
these are the fields in my "products" table:
INSERT INTO `products` ( `id` , `partNumber` , `supplierID` ,
`category` , `manufacturer` , `model` , `subCategory` , `itemUse` ,
`color` , `description` , `listPrice` , `ourPrice` , `perQty` ,
`warranty` , `chemistry` , `volts` , `amphours` , `dimensions` ,
`availability` , `picture1` , `picture2` , `product_notes` ,
`product_updated` , `ship_weight` , `my_cost` , `other_costs` ,
`private_notes` , `BBmodel` , `BBmanufacturer` , `BBcategory` ,
`spare1` , `spare2` , `spare3` )
The "id" field is set to auto increment and the "product_updated" is
auto time/date stamp. What do I do with those fields in the data file
- skip them or leave them there as an empty cell?
Steve
|
Request for Question Clarification by
dewolfe001-ga
on
16 Nov 2003 16:20 PST
As to part 1:
1) There may be a problem with the CSV file itself. I've seen
phpMyAdmin choke on otherwise ideal CSV files. Can you post the first
few lines of the CSV file to your website or as an answer
clarification?
2) Are you using the web browser based phpMyAdmin tools?
3) Has your ISP limited your capabilities with phpMyAdmin? Namely,
have they disallowed file uploads and imports?
Part 2:
There are ways to zip the whole site and DB and send it to another
URL, but what sort of site would you send it to? (e.g. Linux vs.
Windows; size allowance; does it have a regular FTP set-up or use
something like secure shell, etc..)?
Would you want to keep multiple versions of your site? In other words,
a version from this month AND a last month?
|
Clarification of Question by
stevep234-ga
on
16 Nov 2003 17:04 PST
hi,
The Part 1 of this question just became irrelevant. After 3 days my
webmaster just got some kind of a script to work to load the data
information into my database. Of course my offer here still stands of
$50 plus $25 tip for a working script for this question Part 2.
I do have full ftp access to both sites, and both sites have php. The
second site, at just a few dollars a month, I feel may make for a nice
place to back-up to as I only have dial-up available to me.
Yes, I'd like to keep multiple copies of my site. I was thinking
perhaps 15, that way I would always have 2 weeks worth (at one auto
copy/backup per day). I can always do an ftp copy from time to time
to my pc when I want to permanently save. The script could just loop
through to the next url subdirectory on the next save, and after 15
times, start over at the first subdirectory again and overwrite
whatever is there.
I especially want a simple way to restore a web page or customer
database, etc if ever its needed. Thats why I was thinking a simple
url to url copy of each web page might be best. Then I can just use
my ftp program if I ever need to restore anything. Simplicity and
reliability and security are the three most important considerations,
along with very economical operation. I am open to any suggestions
you may have on what I'm attempting. How do other people back-up
their web site?
Thanks.
Steve
|
Clarification of Question by
stevep234-ga
on
17 Nov 2003 01:16 PST
FORGET PART 1 OF THIS QUESTION. ONLY PART 2 NOW MATTERS TO ME.
|
Below is the PHP script (I have also FTP'd it to my site:
http://mike.dewolfe.bc.ca/scripts/buftp.php -- MS site, so it won't
execute the PHP code).
What this does is generates two archives using a system command, tar
and GZIP-- the HTML directory (stored in $wwwd) and the MySQL
database.
It puts these files into a tmp directory.
Then, it makes an FTP connection with your other site, creates a
backup directory out of the year, month and day. It liberalized the
permissions for that directory then uploads these two files and closes
the connection. There is no HTML output.
You need to look at the variables between lines 27 and 42 and change
them to match your setup (FTP user name, MySQL user name, directory
for the HTML, etc.)
To get this to run at regular intervals, you need to set up a "cron
job" on the main site. This is something your ISP may not allow.
If they do, you can use this discussion to help you along:
http://www.phparch.com/discuss/viewtopic.php?p=1659
Basically, get a terminal session going and type in:
crontab -e
This opens the cron job editor.
If you want to run this daily, your cron entry should look something like this:
30 3 * * * php /your/site/called/from/the/root/buftp.php
So, at 3:30 AM everyday, this would make PHP call your back-up PHP script.
If you want to run this weekly, your cron entry should look something like this:
30 3 * * 5 php /your/site/called/from/the/root/buftp.php
So, at 3:30 AM every Friday, this would make PHP call your back-up PHP script.
Because of how this works, you need to call the "buftp.php" script
with its full directory name. If makes good sense to run this script
during a time you expect to be quiet, otherwise it will be competing
to do a full site back up while serving your other visitors.
Save this code below at "buftp.php" in a directory where you can execute PHP.
<?php
////////////////////////////////////////////////
/// //
// backup and FTP DB //
// by Mike DeWolfe //
// //
// Requires: //
// PHP 4.1.0+ with zlib support //
// MySQL 3.22 (or higher) //
// //
// This will post to any FTP server //
// //
////////////////////////////////////////////////
// Changeable Variables
if ($_SERVER['DOCUMENT_ROOT'])
{
$docroot = $_SERVER['DOCUMENT_ROOT'];
}
else
{
$docroot = ".";
}
// Directory stuff
$arcd = $docroot."/tmp/";
$arcf = "website.tar";
$arcm = "mysql.gz";
$wwwd = $docroot."/web/";
// Database stuff
$host = "localhost";
$user = " "; // The MySQL Username
$pass = " "; // The MySQL password
$db = " "; // The database's name
// FTP stuff
$ftpsite = "your ftp site";
$ftpuser = "your ftp username";
$ftppass = "your ftp password";
$ftpdir = "where the subdirs go";
// get the files and zip them
// - first, delete the old file:
unlink($arcd.$arcf);
// - find them and zip them into one location
// - use tar first
$wdump = sprintf(
'tar --create --directory=%s --file=%s%s',
$wwwd,
$arcd,
$arcf,
);
system($wdump);
// - then gzip the tar
$wdump = sprintf(
'gzip -9 %s%s',
$arcd,
$arcf
);
system($wdump);
// get the database and zip it
// - first, delete the old file:
unlink($arcd.$arcm);
// - zip them into one location near the files
$mdump = sprintf(
'mysqldump --opt -h %s -u %s -p%s %s | gzip > %s%s',
$host,
$user,
$pass,
$db,
$arcd,
$arcm
);
system($mdump);
// open an FTP connection
// make a date relative directory
// upload the file archive and the database archive
$ftpconn = ftp_connect($ftpsite);
$login_result = ftp_login ($ftpconn, $ftpuser, $ftppass);
if ($ftpconn && $login_result)
{
// away we go
ftp_pasv ($ftpconn, true);
// set up the new directory
$today = getdate();
ftp_chdir($ftpconn,$ftpdir);
$path = $today[year].$today[mon].$today[mday];
ftp_mkdir($ftpconn,$path);
$chmod_cmd="CHMOD 0777 ".$path;
$chmod=ftp_site($ftpconn, $chmod_cmd);
ftp_chdir($ftpconn,$path);
// upload the mysql and webdir archives
$upload = ftp_put($ftpconn, $ftpdir."/".$path."/".$arcf.".gz",
$arcd.$arcf.".gz", FTP_BINARY);
$upload = ftp_put($ftpconn, $ftpdir."/".$path."/".$arcm, $arcd.$arcm,
FTP_BINARY);
}
// whether successful or failed...
ftp_quit($ftpconn);
?> |
Request for Answer Clarification by
stevep234-ga
on
18 Nov 2003 20:55 PST
hi Mike,
Thank you for your response. I'll try and check it out tomorrow or
Thursday at the latest. If all is good, (I can get it to work) then
I'll immediately close the question and award the tip promised. With
this posting, I just wanted to let you know I haven't forgotten.
Thank you again.
Steve
|
Clarification of Answer by
dewolfe001-ga
on
18 Nov 2003 23:29 PST
Great. If your ISP doesn't support the use of gzip or tar, that may
mean a reconsideration of how this works. With that said, I do have a
plan in mind.
|
Request for Answer Clarification by
stevep234-ga
on
19 Nov 2003 22:51 PST
hi Mike,
Although I did not get the script to work, that most likely just means
I'm not doing something correctly. I've given the script and a copy
of your directions to my webmaster and I'll let you know if he has any
questions on its use.
Thanks.
Steve
|
Clarification of Answer by
dewolfe001-ga
on
20 Nov 2003 06:49 PST
Great. Let me know what I can do.
|
Request for Answer Clarification by
stevep234-ga
on
04 Jan 2004 18:08 PST
hi Mike,
It's been a long time, for that I apologize. Various emergencies came
up, etc and I'm just now getting back to my computer and web site. I
have not forgotten about you, but I am way behind schedule of where I
wanted to be by now. I will close this question with full tip as soon
as I can get your script to work.
My present web hosting company does not support all the necessary
modules for my shopping cart to work properly (which I thought was the
last thing I needed to complete before installing your scripts) so I
am now looking for a new web hosting company. Any suggestions?
Also, exactly what should I ask a new web hosting company so I will
know that it supports the type of script you created here?
Again I apologize for the long delay in bringing this post to a close.
It looks like my time line is approximately one week to find and
switch my web site to a new web hosting company, one week to get the
shopping cart to work, then another day or so to test your scripts.
Thank you again for your work, help, and patience.
I wish you a Happy New Year!
Sincerely,
Steve
|
Clarification of Answer by
dewolfe001-ga
on
21 Feb 2004 09:40 PST
Sorry, I'll try this again:
Hi Steve,
The things you need from a web host:
Linux/Unix platform (this script could run on Windows, but it would be
more difficult).
PHP 4.1.0+ with zlib support (fairly common)
MySQL 3.22 (or higher) (very common)
|
