Solved

upload db to MySQL

Posted on 2012-03-13
19
563 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 82

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 82

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
 
LVL 1

Author Comment

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

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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 108

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 108

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 108

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now