Solved

upload db to MySQL

Posted on 2012-03-13
19
572 Views
Last Modified: 2012-03-24
Dear Experts,

I have trouble to upload a file to MySQL. At first what I did was to create a database named <videos>, then create a table named <myvideos>. I added 4 fields (video_id, video_name, ftp_folder, html_file). These folders were added directly in the phpMyAdmin.

When I tried to export the database to the computer, it worked without any problem.

But when I tried to import the database (the file was updated with 4 rows), MySQL returned a 1064 error.

I don't know what I did wrong. But the file that I wanted to upload had a .SQL extension. I also tried with a .CSV extension, but same problem.

Any help would be appreciated.

Thanks

currentdb
0
Comment
Question by:currentdb
  • 9
  • 3
  • 3
  • +2
19 Comments
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 37718617
1064 appears to be a syntax error.  You need to post your SQL for us to help you find it.  Are both versions of MySQL the same?  Same OS?  Did the database and/or tables already exist in the second server?
0
 
LVL 1

Author Comment

by:currentdb
ID: 37718625
Hi DaveBaldwin,

Here's the full syntax error:

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'video_id,video_name,ftp_folder,html_file
1234,able.mov,videos/english,index/his' at line 1

I created the database and tables directly on the server.
Then I added one row, again directly on the MySQL server.
I exported the database to my computer. The database was exported and I was able to open it with Excel.
Once the file was opened with Excel, I added 4 other rows, saved it into .SQL format and tried to upload it back on the server. And there I saw the error 1064.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 37718662
I'm wondering if your original export is in CSV instead of SQL format because I don't think Excel will read MySQL format either.  And I'm pretty sure that Excel will not export in MySQL format.  MySQL format is not exactly the same as Microsoft SQL format.  

In any case, I won't be able to tell what is wrong until you show me the SQL file.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 1

Author Comment

by:currentdb
ID: 37718667
Here's the SQL file
myvideos.sql
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 37718687
The file you uploaded is actually a CSV file.  MySQL can not import that directly.  phpMyAdmin can be used to import it but Only if the database and table are already created.  I have attached the MySQL dump file which is in MySQL format and that you can use to upload to MySQL but you must have the database created because this is only the table that you sent in CSV format converted to MySQL format.  You can open the two files in a text editor and clearly see the difference.  Note that Excel can not be used to edit the SQL files.
myvideos.sql
0
 
LVL 1

Author Comment

by:currentdb
ID: 37718822
Actually what I did was to use phpMyAdmin to export the database and I exported it into a CSV format and I was then able to open the same file using Excel.

You mentioned that I must have the database created. Actually this database was created with the table and both were created with phpMyAdmin.

At the same file that I exported in CSV from phpMyAdmin, I modified it by adding some extra rows then again, I used phpMyAdmin to import it back on the server.

I am still a newbie to MySQL so...how I can use the dump file you attached and update the database by doing an import directly in phpMyAdmin ?

Thanks again
0
 
LVL 21

Assisted Solution

by:theGhost_k8
theGhost_k8 earned 400 total points
ID: 37718900
you can execute following query in your phpmyadmin / commandprompt:

load data local in file '/path/to/filename.sql' into table myvideos fields terminated by ',' lines terminated by '\n' ignore 1 lines;

You may read more here.
0
 
LVL 1

Author Comment

by:currentdb
ID: 37718914
Hi theGhost_k8,

I guess I can execute this line here "Run SQL query/queries on server custsqlmoo10"

But in order to replace the old table with the updated one, how I can upload the file? I guess the only option I have in mind would be to upload it via FTP, then indicate the path to the sql file ?
0
 
LVL 3

Expert Comment

by:kalpeshchavan
ID: 37719125
Hey you export db as cvs first export db as sql then it is use to import
0
 
LVL 21

Expert Comment

by:theGhost_k8
ID: 37719156
nop! What you can do is create a copy of old table for time being.
- you may use mysqldump
- use export of php myadmin
- execute following mysql queries:
create table old_mymovie like mymovie; insert into old_mymovie select * from mymovie; truncate table mymovie;

And then you may execute the query.
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 37719978
Here is a script that will usually work to export a data base table into a CSV file.
<?php // RAY_db_to_excel.php
error_reporting(E_ALL);
echo "<pre>\n";


// DEMONSTRATE HOW TO EXPORT A TABLE SO THAT IT CAN BE USED IN EXCEL


// SET YOUR TABLE NAME HERE - OR MAYBE USE THE URL GET ARGUMENT?
$table_name = '???';


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}

// OPEN THE CSV FILE - PUT YOUR FAVORITE NAME HERE
$csv = 'EXPORT_' . date('Ymdhis') . "_$table_name" . '.csv';
$fp  = fopen($csv, 'w');

// GET THE COLUMN NAMES
$sql = "SHOW COLUMNS FROM $table_name";
if (!$res = mysql_query($sql))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
if (mysql_num_rows($res) == 0)
{
    die("WTF? $table_name HAS NO COLUMNS");
}
else
{
    // MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
    while ($show_columns = mysql_fetch_assoc($res))
    {
        $my_columns[] = $show_columns["Field"];
    }
    // var_dump($my_columns); ACTIVATE THIS TO SEE THE COLUMNS
}

// WRITE THE COLUMN NAMES TO THE CSV
if (!fputcsv($fp, $my_columns)) die('DISASTER');

// GET THE ROWS OF DATA
$sql = "SELECT * FROM $table_name";
$res = mysql_query($sql);
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}

// ITERATE OVER THE DATA SET
while ($row = mysql_fetch_row($res))
{
    // WRITE THE COMMA-SEPARATED VALUES.  MAN PAGE http://php.net/manual/en/function.fputcsv.php
    if (!fputcsv($fp, $row)) die('CATASTROPHE');
}

// ALL DONE
fclose($fp);

// SHOW THE CLIENT A LINK
echo "<p><a href=\"$csv\">$csv</a></p>\n";

Open in new window

0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 37719985
Here is a script that will usually work to import a CSV file into a data base table.
<?php // RAY_csv_to_db.php
error_reporting(E_ALL);
echo "<pre>\n";  //READABILITY FOR var_dump()



// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES



// TEST DATA, SAVED FROM THE POST AT EE
$csv = "http://laprbass.com/RAY_csv_to_db.csv";
$fpo = fopen($csv, 'r');
if (!$fpo ) die('CRUMP');

// GET THE FIELD NAMES FROM THE TOP OF THE CSV FILE
$top = fgetcsv($fpo);
$cnt = count($top);

// SET UP KEY NAMES FOR USE IN OUR QUERY
$query_cols = implode(',', $top);

// SET A ROW COUNTER
$counter = 0;

// KEEP TRACK OF ROWS THAT HAVE THE WRONG NUMBER OF FIELDS
$errors = array();

// LOOP THROUGH THE CSV RECORDS PERFORMING CERTAIN TESTS
while (!feof($fpo))
{
    $counter++;

    // GET A RECORD
    $csvdata = fgetcsv($fpo);

    // SKIP OVER EMPTY ROWS
    if (empty($csvdata)) continue;

    // CHECK THE NUMBER OF FIELDS
    if ($cnt != count($csvdata))
    {
        $errors[] = $counter;
        continue;
    }

    // MAYBE ASSIGN KEYS TO THE ROW OF FIELDS - ACTIVATE THIS TO SEE THE ASSOCIATIVE ARRAY
    //  $csvdata = array_combine($top, $csvdata);
    //  var_dump($csvdata);

    // ESCAPE THE INFORMATION FOR USE IN THE QUERY
    foreach ($csvdata as $ptr => $value)
    {
        $csvdata[$ptr] = mysql_real_escape_string($value);
    }

    // SET UP VALUE FIELDS
    $query_data = "'" . implode("', '", $csvdata) . "'";

    // SET UP A QUERY
    $sql = "REPLACE INTO myTable ( $query_cols ) VALUES ( $query_data )";

    // RUN THE QUERY HERE....
    var_dump($sql);

}


// SHOW THE NUMBER OF ROWS PROCESSED
echo "<br/>RECORDS PROCESSED $counter \n";

// SHOW THE NUMBERS OF THE ROWS WITH THE WRONG NUMBER OF FIELDS
if (count($errors))
{
    echo "<br/>ROWS WITH THE WRONG NUMBER OF FIELDS: \n";
    var_dump($errors);
}

Open in new window

Run it once to see if the SQL statements look OK, then add your query at line 88.
0
 
LVL 1

Author Comment

by:currentdb
ID: 37722900
Hi Ray_Paseur,

I ran your query, the one for the import. I ran it into phpMyAdmin window. Unfortunately, it did not work and there was an error at line 1, but it may not really be at this line cause it looks ok for me.

Here's the error:

There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem

ERROR: Unknown Punctuation String @ 1
STR: <?
SQL: <?php // RAY_csv_to_db.php
error_reporting(E_ALL);


SQL query:

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<?php // RAY_csv_to_db.php
error_reporting(E_ALL)' at line 1
0
 
LVL 109

Assisted Solution

by:Ray Paseur
Ray Paseur earned 100 total points
ID: 37722928
It is a standalone PHP script.  It is not designed to run in a phpMyAdmin window.  Try installing it on your server and then accessing it via a regular URL (a GET request).  

If you are not a PHP programmer you might want to get some background information.  This book will not make you a pro, but it will help you get started with a good foundation.
http://www.sitepoint.com/books/phpmysql4/

HTH, ~Ray
0
 
LVL 1

Author Comment

by:currentdb
ID: 37722968
Hey Ray_Paseur,

I thought your script could run in a phpMyAdmin window. I don't even have MySQL on my computer so basically, I have to run it directly on the server. I also use Excel a lot and I was wondering if I can use it to update the entire database without having all the pain to go into phpMyAdmin.

And thanks for suggesting this book. I'll try to find it online and get some background info.
0
 
LVL 1

Author Comment

by:currentdb
ID: 37722985
Hi theGhost_k8,

Attached is the export dump from phpMyAdmin window.

I ran your query like this 'C:/WebDeveloper/myvideos.sql' into table myvideos fields terminated by ',' lines terminated by '\n' ignore 1 lines;

But still ran into the same error 1064.
export-phpMyAdmin.txt
0
 
LVL 1

Assisted Solution

by:currentdb
currentdb earned 0 total points
ID: 37723036
Finally it works! What I did was to save the file as CSV. After that,  I had to change the field ''Fields terminated by ; and change this to ," and the database was updated successfully.

Here's the SQL code:

$sql = 'INSERT IGNORE INTO `myvideos` VALUES (\'video_id\', \'video_name\', \'ftp_folder\', \'html_file\')# MySQL returned an empty result set (i.e. zero rows).'
        . ' INSERT IGNORE INTO `myvideos` VALUES (\'1234\', \'able.mov\', \'videos/english\', \'index/history/able.mov\')# MySQL returned an empty result set (i.e. zero rows).'
        . ' INSERT IGNORE INTO `myvideos` VALUES (\'1235\', \'able2.mov\', \'videos/english\', \'index/history/able.mov\')# Affected rows: 1'
        . ' INSERT IGNORE INTO `myvideos` VALUES (\'1236\', \'able3.mov\', \'videos/english\', \'index/history/able.mov\')# Affected rows: 1'
        . ' INSERT IGNORE INTO `myvideos` VALUES (\'1237\', \'able4.mov\', \'videos/english\', \'index/history/able.mov\')# Affected rows: 1'
        . ' ';

Open in new window


Now that it works, I have to find a way to update it directly from Excel without having to go through phpMyAdmin.
0
 
LVL 21

Accepted Solution

by:
theGhost_k8 earned 400 total points
ID: 37723690
1064 was for syntax as there was  an extra space. IN FILE ==>> INFILE

load data local infile '/path/to/filename.sql' into table myvideos fields terminated by ',' lines terminated by '\n' ignore 1 lines;

Go through the link reffered to know the basics, it'd be an efficient way to load such data.
0
 
LVL 1

Author Closing Comment

by:currentdb
ID: 37760319
I had to change the ";" in the MySQL syntax and everything worked.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to dynamically set the form action using jQuery.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question