I am trying to runs some stats and need to pull about 90% of columns from one table and put the data in to another.
is there an easy way to query the empty db for all the current columns then query to the live DB and grab the data for what colums that table has and put the data in to my empty db?
SHOW COLUMNS FROM table_name;
for that you need to ensure that the data type specification matches up (i.e. putting a string into a numeric column obviously won’t work out).
if the empty database is supposed to be a clone, you better populate it from an SQL dump.
I think my first question would be… why are there 100 columns in your mysql table? I get the feeling some better database design would improve this more than grabbing ridiculous numbers of columns. (remember you’ve also gotta store all that data in PHP - which has a fixed memory size)
Same with me.
Back when I was a complete database newbie (and not much more than a PHP newbie), my databases consisted of “one big table with everything” and my PHP code did all the heavy lifting.
It “worked” and while tables were relatively small and simple it wasn’t noticeably a problem.
That soon changed and things got very messy and unmanageable.
If that is the point you are at now, IMHO you should do what I was “forced” to do.
That is, come up with a better database design.
This will allow you to use better (albeit more complex) queries and shift a lot of the work from PHP to the database where it should be.
and actually have the database do less work, as it has to sort through more targetted data rather than ALL of it.
If you want a very readable book on the subject, try this one. I bought an earlier imprint around 15 years ago, and the ideas and concepts have stuck with me ever since.
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.