This topic is locked

Query results in one database and insert them into another one

3/13/2023 2:10:33 AM
PHPRunner General questions
T
Travnicanin author

Hello everyone. I would like to Query data in one database and insert the results into another one.
Does anyone knows how to do that?
Short explanation: I want to query financial data in one database (let's assume in year 2022) and if any of the results is <> 0 then insert those results into a new (or existing) database (year 2023).
It would be nice also if someone knows how to create a new database with PHPRunner.(if database "2023" doesn't exist - create it).
I hope I was clear enough. Thanks!

admin 3/14/2023

The general approach would be like this. Point the connection to the first database, retrieve a record, point the connection to the second database, insert the record.

T
Travnicanin author 3/17/2023

Well, you have just repeated my idea, but I need a concrete example of how to do it...
Can you point me in the right direction for the following:
<ol> How to allow user to create a new database? How to create all (empty) tables in that new created database?* how to copy contents of certain tables from old database into the same tables in new database?
</ol>This is the algorythm:
CreateNewDatabase->CreateAllTablesFromSchema
OldDatabase.tableCLIENTS -> copyRecordsINTO -> NewDatabase.tableClients
Please advise. Thank you!

Dalkeith 3/17/2023

OK to me this is really a database question and the specific syntax may vary according to the type of database used.
Try this
Insert into
This might be helpful as well
Transfer data between databases

admin 3/17/2023

This revised question has really nothing to do with PHPRunner, just a matter of crafting and executing SQL queries. Here are some examples for MySQL.
Create a database:
https://dev.mysql.com/doc/refman/8.0/en/create-database.html
Create a table:
https://dev.mysql.com/doc/refman/8.0/en/create-table.html
Copy a bunch of records from one table to another:
https://dev.mysql.com/doc/refman/8.0/en/insert-select.html
And to execute a free form SQL query in PHPRunner you can use Database API:
https://xlinesoft.com/phprunner/docs/db_exec.htm

T
Travnicanin author 3/18/2023

admin & Dalkeith, either I am going in the wrong direction, or you.
I cannot explain to the app users to create queries because they are not programmers.
I need to create solutions in the app by providing them the possibilities of variuos data manipulation, and, it has to be possible of just one click.
They never know what is behind that "one click", but usually I explain in a few sentences like:
"By clicking 'TRANSFER' button you will transfer the data from database '2022' into database '2023' ".
"Please be patient while data is being transfered..."
So far I see I can read tables by entering connection parametres in the begining of the project creation.
Correct me if I am wrong :

  • I was thinking to have my database schema created (empty) = database "0000" I should have a separate database with only one table 'users' and appropriate fields like, username, password, databasename In the opening of the app, user must log in and choose database to work with. If the database exist = continue to work with it If the user must/want to create a new database = enter the database name (e.g.'2023') and the app should create the new one by copying all schema tables from database "0000"* Next is to provide options to users to calculate results from one database (query) and insert results into another database
    Now, please consider the fact that whatever operation is needed, it must be provided to the user to be possible on "one click" of a button. The users don't think about code needed under the button (they don't have to).
    Maybe I am wrong in some steps above because I am totally new with PHPRunner, but I have a desktop solution which is working for the past 12 years and is based on the same logic.
    Just one comment - if anyone wonders why separate years - '2022','2023'...some users has over 1Mil records in one table/per a year - I know we can fill the same table with data and get wanted results by querying the same table (there is a field named 'year' in it) - but, can you imagine to query that table after a couple of years with 7Mil+ records? Can you imagine to make a join (union) query between tables when eachone has several Mil records?
    That doesn't work well. So separating databases by fiscal years is only making all database operations faster.
    Getting back to the problem, can you explain me:
  • How to provide to user that they can create a new database by just entering the name of it? (I believe I should have connection between users and databases as well, permissions etc.on the server level)
    Thank you for your patience.

admin 3/18/2023

All these SQL queries need to be executed in your code that you need to add to button's Server part. You need to write this code, not your users.
Just create a bunch of SQL queries and execute them from button's code, that is all.