|All requests||>||What is the best PHP mysql database b...||>||Request new recommendation||>||Featured requests||>||No recommendations|
by Dennis Ampere - 7 years ago (2015-08-17)
I have many companies data in one database.
For each table I want to write a query to select all of a company's data and export the result in one .sql file like in phpMyAdmin.
1. by Manuel Lemos - 7 years ago (2015-08-17) Reply
Do you want to take a backup of all database tables with company data, but with just the data of a specific company or all companies?
2. by Zhao - 7 years ago (2015-08-19) in reply to comment 1 by Manuel Lemos Comment
I think that there are many tables and each table contains records from different companies. Dennis Ampere just wants to export records from different tables for each company separately. It is difficult to export data if we do not know the schema of all tables.
If Dennis Ampere just wants to backup the data, he had better use mysqldump to backup the whole database.
3. by Manuel Lemos - 7 years ago (2015-08-19) in reply to comment 2 by Zhao Comment
Right. That is what I got.
That is called a multi-tenant application, i.e. the same application is serving different customers using a single database with tables storing data from all customers.
I think that a variant of a mysqldump like package could easily do the job if he could pass a parameter to tell which fields in the tables store the company ID we wants to take a backup. So it would just be something like:
SELECT <fields> FROM <table> WHERE <company_id>=?
4. by Zhao - 7 years ago (2015-08-19) in reply to comment 3 by Manuel Lemos Comment
Yes, it is right. The schema of tables is needed.
5. by Manuel Lemos - 7 years ago (2015-08-19) in reply to comment 4 by Zhao Comment
Yes, probably it is the same field name and field value for the company in all tables.
The actual schema of the tables it is something the the mysqldump like package can figure.
6. by Zhao - 7 years ago (2015-08-19) in reply to comment 5 by Manuel Lemos Comment
The export script like phpMyAdmin may not suit mass data in database, e.g. records exceed 100 millions.
The best way is to use bash based on cron ....
7. by Manuel Lemos - 7 years ago (2015-08-19) in reply to comment 6 by Zhao Comment
Well there are classes here that do the same as mysqldump. They just needed to have an option to filter the relevant records of the company he wants to backup.
8. by Dennis Ampere - 7 years ago (2015-08-25) in reply to comment 1 by Manuel Lemos Comment
Please sorry for the delay. I want to backup all database tables with company data for a specific company.
Companies that have subscribed to my service wants a backup of their data. I therefore want to have a backup of all tables for a specific company.
Thanks in advance.
9. by Dennis Ampere - 7 years ago (2015-08-25) in reply to comment 3 by Manuel Lemos Comment
Yeah, you got it. I have a field (comp_id) in each table which identifies each company's record.
so for each table you can do something like:
SELECT * FROM <table> WHERE comp_id = ?;
by Orazio Principe 35 - 7 years ago (2015-08-19) Comment
Hi, with this class you can backup your database to another mysql and there is no limits to schemas or rows. Currently I backup 137 databases and millions of rows without problems
4. by Orazio Principe Reply- 7 years ago (2015-08-19) in reply to comment 3 by Orazio Principe
I forgot to say that you can define a list of tables and many other features to better define your backup
5. by Manuel Lemos - 7 years ago (2015-08-19) in reply to comment 4 by Orazio Principe Reply
Well, the requirement seems to be that the query that retrieves the database table records only retrieves those that belong to a certain company.
So, it seems it is not so much about what tables to backup or limit the number of records, but rather just about a subset of the records.
It would be more useful if your class would allow defining conditions for the records the user wants to backup.
by Dmitry Mamontov 280 - 7 years ago (2015-08-18) Comment
I think it is enough to console commands mysqldump. but I suggest you make a backup of that class to the popular server via dav protocol.
1. by Manuel Lemos - 7 years ago (2015-08-18) Reply
I think the problem is that Dennis wants just to backup records of certain tables that belong to just one company.
So mysqldump or similar packages would not do because they backup all records of the tables.
Does you class allow to specify additional conditions to filter just the records of a specific company probably defined by a certain value in a specific field that identifies the company?
2. by Dmitry Mamontov Reply- 7 years ago (2015-08-19) in reply to comment 1 by Manuel Lemos
Today I'll try to add the ability to filter class.
6. by Manuel Lemos - 7 years ago (2015-08-19) in reply to comment 2 by Dmitry Mamontov Reply
Just follow up here when you do that.