Determining type of MySQL field.

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

I have a problem of migrating a database from one host to another.
I can't do a dump on the source server. The only access I have is thru
queries. So it looks like I'm going to have to query all the
tables and insert to the destination.

I'm trying to come up with a general solution to this problem which
uses queries to determine the structure of the source db, and create
the tables on the destination, which I've already done using the nice
"show create table" query syntax combined with the PHP
mysql_list_tables() function.

The problem comes with doing the inserts. It looks like I'm going to
have to do a "select * from table" thing and then loop thru the
recordset doing insert after insert.

Q: Do I have to do it that way? Is there a way I can insert all the
records from source to destination more efficiently?

Q: How can I determine the type of value in a field returned from
a query? PHP's is_string returns true on ALL fields regardless of type
used in the database itself. I need to know whether to wrap the values
in quotes or not before I insert them.

I'd like to be able to at least fetch a record and insert the record
without having to process it. I'd like to grab the whole table and
insert it, but haven't a clue how to do this.

Q: Is there some kind of select/fetch that I can do that will give me a
record ready to insert i.e. with commas and quotes?


Re: Determining type of MySQL field. wrote:
Quoted text here. Click to load it

The following queries will return the info you need:

SHOW TABLES        // returns list of tables
DESCRIBE <table name>    // returns column name, type, options, etc...


convert UPPERCASE NUMBER to a numeral to reply

Re: Determining type of MySQL field.

May be if you try to use some PL/SQL script it will be easier, but
don't know how!!!
You can ask some Database experts for idea's and then get back to ask
about ways to convert these idea's to PHP script..

Re: Determining type of MySQL field.

News Me wrote:
Quoted text here. Click to load it

PHP's mysql_list_tables is more useful, because the result doesn't
need to be processed.

Quoted text here. Click to load it

MySQL's 'SHOW CREATE TABLE' returns a string that can be used to
duplicate the table on the other host.

Also, I made the mistake of assuming that MySQL was strongly typed,
i.e. that strings had to be quoted, but scalers could not be. It
turned out that if you quote everything, it works fine (but you
have to addslashes() to the string you create.

The problem now is that I have auto_increment fields. ... oooooh.

Re: Determining type of MySQL field.

Quoted text here. Click to load it

I have recently (like on Friday) completed a script to "backup" a mySQL
database from one server to another. It does this by first obtaining the
structure of the source DB and recreating that structure in the destination
(the DB must not exist on the destination host - it creates the DB then
inserts the tables). Then it cycles through the source DB, one table at a
time and one field at a time -  pulls a field form the source, then writes
it to the destination. In short, it's an automated means to totally
duplicate a DB. So far, It's worked on tables with 10,000+ records and sizes
of 100M+.

If it seems like something that could help, email me at "tony" at
"naturesflavors" dot "com" and I'll send you the code.

Re: Determining type of MySQL field.

Quoted text here. Click to load it

I have been unable to import most of my databases using mysqldump because
they're too big. I'm getting dump files of 100MB or so. phpmyadmin doesn't
process them (it doesn't even try), and when I try to insert via a command
line, it parses a portion of them then craps out on an error.

Quoted text here. Click to load it

When I looked at my mysqldump files, it had a series of statements to create
the tables, then an INSERT statement for each record:

INSERT INTO foobar VALUES ('var1', 'var2', ... );

Site Timeline