The Cache: Technology Expert's Forum
 
*
Welcome, Guest. Please login or register. September 16, 2019, 01:22:15 PM

Login with username, password and session length


Pages: [1] 2
  Print  
Author Topic: best way to insert a sql dump file into a db without php myadmin  (Read 4507 times)
jammaster82
Lifer
*****
Offline Offline

Posts: 666


Thats craigs list for ya


View Profile
« on: April 01, 2009, 09:11:50 AM »

Okay i have this .gz file , usually i would import it through php my admin... without php my
admin how would you unzip this and insert it through php?    The dump file is 1.3mb and
looks to contain everything needed to recreate the database... how would a non tard approach this?

lol

 Need Help
Logged

The watched pot, never boils... But if you walk away from it , the soup burns.  What gives?
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #1 on: April 01, 2009, 09:20:36 AM »

First, tar -xzvf (thefile).tar.gz.

Can you run MySQL (the command line app)? This is how we did it when we were still hiking 2 miles to school everyday in the snow.
You'll need to SSH into your box, then run mysql. You'll get a dot prompt or > prompt. Use the "source" command like this:

mysql> use mydatabase
mysql> source /usr/local/jmm/myfile.sql

and that should do it.
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
vsloathe
vim ftw!
Global Moderator
Lifer
*****
Offline Offline

Posts: 1669



View Profile
« Reply #2 on: April 01, 2009, 10:30:49 AM »

mysql -u [USERNAME] -p [DATABASE_NAME] < [INPUT FILE]
Logged

hai
jammaster82
Lifer
*****
Offline Offline

Posts: 666


Thats craigs list for ya


View Profile
« Reply #3 on: May 09, 2009, 10:10:22 PM »

Code:
$buff = file_get_contents($thisfilename);

 $myArr = explode(chr(59), $buff);
 $i = 0;   
 foreach($myArr as $line)
 {
     
   $db->query($line); 
   $lasterror = $db->error();
    if (!$lasterror) {
           // log it succeeded..
        }
                        else
                {
          // log that it failed...

                }       

  $i++;


 }

Okay so i am exploding the $buff on the  semicolon and im getting
individual lines of sql successfully..

My question is more theory in action / design.... considering that i want
this app to run over and over to grab any sort of new data in any sql dump...
im gonna want to store and find out later what sql statements/lines did or didn't get inserted so i can go back and reconcile them.

The question is, what method to use to store the success/failure... a few come to mind, csv file, xml file.... What would be the best method of storing the information about which queries did or didnt work so i could go back and reference them later?


Logged

The watched pot, never boils... But if you walk away from it , the soup burns.  What gives?
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #4 on: May 10, 2009, 05:05:36 PM »

Sounds an awfully lot like an instance where master->slaving would be a much better solution. That's exactly what the MySQL slave solution does, and quite well I might add.

IMO, keeping your own sync log is just reinventing a really big wheel.
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
jammaster82
Lifer
*****
Offline Offline

Posts: 666


Thats craigs list for ya


View Profile
« Reply #5 on: May 14, 2009, 06:55:33 AM »

I wish!  Im trying to migrate users from phpBB to wordpress
so i have to do this by hand im thinking... still researching it
though... I thought id just chew up the sql and insert it throw
all the errors into a file...


edit:  All i get is a .gz dump of the old databases.....
« Last Edit: May 14, 2009, 07:34:45 AM by jammaster82 » Logged

The watched pot, never boils... But if you walk away from it , the soup burns.  What gives?
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #6 on: May 14, 2009, 09:35:16 AM »

migrate or integrate? I did a little work like that for SMF and wordpress. I wrote an intermediate table that linked the WP records to the SMF records and poof! had my answer.
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
jammaster82
Lifer
*****
Offline Offline

Posts: 666


Thats craigs list for ya


View Profile
« Reply #7 on: May 14, 2009, 12:34:26 PM »

Gotta move the users and posts over to wordpress... .. i see a wp_users
table in the wordpress db.... is there something else besides just inserting a
user into the wp_users table?
Logged

The watched pot, never boils... But if you walk away from it , the soup burns.  What gives?
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #8 on: May 14, 2009, 01:09:23 PM »

Nope, it's all pretty straight up. If you keep a table of user_ids that you've moved, then you'd know what you've already bridged.I assume this is going to be an ongoing process...?
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
jammaster82
Lifer
*****
Offline Offline

Posts: 666


Thats craigs list for ya


View Profile
« Reply #9 on: May 27, 2009, 05:57:12 AM »

Usually they always are ongoing processes, arent they?

Im still stuck on insert... master slaving isnt an option
cause i am the slave i suppose.. hahaha

Right now im looking through a 'didntmakeit.txt' file
I dont even know how to google this error...

trying to insert the SQL:

the sql has a

&#187;

in it so its splitting that sql line that contains html on
that semicolon...  in it
and ending the mysql statement, so i cant just split it on
the semicolons..   D'oh!

so what do i do now, search and replace all the semicolons that
are inside of quotes after  insert*into*values types of lines?
Then wont i also run into a double quote issue or escape strings
or .... okay im having a panic attack here i need four miller lites and two xanax

 ROFLMAO

Seriously though, what am i looking at with this architecture and in inserting and parsing and storing and just generally dealing with storing html in databases..

seperately how about storing html in xml and would this be the same processes?

Logged

The watched pot, never boils... But if you walk away from it , the soup burns.  What gives?
jammaster82
Lifer
*****
Offline Offline

Posts: 666


Thats craigs list for ya


View Profile
« Reply #10 on: May 27, 2009, 07:59:21 PM »

wtf is this &#187; and what do i do with it?!?!

Logged

The watched pot, never boils... But if you walk away from it , the soup burns.  What gives?
jammaster82
Lifer
*****
Offline Offline

Posts: 666


Thats craigs list for ya


View Profile
« Reply #11 on: June 22, 2009, 02:30:08 PM »

Ok so it turns out the #187; is some html for displaying a >> kind of graphic or whatever...

so that kills my splitting it on the semicolons...
what should i do search replace #0; through #255; with a
temporary symbol then split on the semicolons?
this way i could still split on the semicolons and then just re-replace
this when i deal with it as an individual line?

« Last Edit: June 22, 2009, 02:40:13 PM by jammaster82 » Logged

The watched pot, never boils... But if you walk away from it , the soup burns.  What gives?
jammaster82
Lifer
*****
Offline Offline

Posts: 666


Thats craigs list for ya


View Profile
« Reply #12 on: June 22, 2009, 02:48:59 PM »

and it looks like its more than just 0-255 it could
be &#8217;

in tparser it would be:

Code:
)\&\#n\;;=somethingiwanttotemporarilyreplace

-- collect until you find a literal ampersand, a literal pound sign, any number of numbers 0-9 and followed by literally a semicolon into a named collection record called somethingiwanttotemporarilyreplace

how would i do this with php?

so is replacing this temporarily the best plan and
how is the best way to replace this in php.. it looks
like im gonna be storing html and xml so i better
deal with this and get it out of the way..
« Last Edit: June 22, 2009, 02:52:21 PM by jammaster82 » Logged

The watched pot, never boils... But if you walk away from it , the soup burns.  What gives?
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #13 on: June 22, 2009, 02:58:20 PM »

preg_replace('|#(\d+);|','##$1##',$yourstring);
then to switch back
preg_replace('|##(\d+)##|','#$1;',$yourstring);

something like that.
I am multitasking. but thats the basic idea. syntax is probably wrong. lol
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
jammaster82
Lifer
*****
Offline Offline

Posts: 666


Thats craigs list for ya


View Profile
« Reply #14 on: August 03, 2009, 07:15:22 AM »

okay ugh... still on this one this query worked in the phpmyadmin window but when im trying to do it  in mysql command line its giving me the following error:

Code:
insert into wp_users_final1
    -> (
    ->
    ->   user_id,
    ->   user_login,
    ->   user_pass,
    ->   user_nicename,
    ->   user_email,
    ->   user_registered,
    ->   user_activation_key,
    ->   user_status,
    ->   display_name
    ->
    -> )
    ->
    -> values
    ->
    -> (
    -> SELECT
    ->   user_id+94,
    ->   user_login,
    ->   user_pass,
    ->   user_nicename,
    ->   user_email,
    ->   user_registered,
    ->   user_activation_key,
    ->   user_status,
    ->   display_name
    ->
    -> FROM aaatest2
    -> )
    -> ;
ERROR 1064 (42000): 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 'SELECT
  user_id+94,
  user_login,
  user_pass,
  user_nicename,
  user_email,
 ' at line 19

Any ideas why it would work in one and not the other? maybe another set of eyes will help me see what im missing?

 Need Help
« Last Edit: August 03, 2009, 07:18:24 AM by jammaster82 » Logged

The watched pot, never boils... But if you walk away from it , the soup burns.  What gives?
Pages: [1] 2
  Print  
 
Jump to:  

Perkiset's Place Home   Best of The Cache   phpMyIDE: MySQL Stored Procedures, Functions & Triggers
Politics @ Perkiset's   Pinkhat's Perspective   
cache
mart
coder
programmers
ajax
php
javascript
Powered by MySQL Powered by PHP Powered by SMF 1.1.2 | SMF © 2006-2007, Simple Machines LLC
Seo4Smf v0.2 © Webmaster's Talks


Valid XHTML 1.0! Valid CSS!