Make a duplicate table with all the data

Wappler General Need Help
1

MySQL / PHP

I am adding a facility for clearing out a database so it’s empty ready for another year of data. I want to create a backup of that data before I delete it all but I can’t see how to achieve this in Wappler. Would I need to do some custom SQL or is there a Wappler way?

2

In the title you mean you want to duplicate a table, is that what you mean?

You’d need to use a custom SQL query:

CREATE TABLE new_table SELECT * FROM original_table;

To erase you can use a Database Delete step. The other way would be using a custom query “TRUNCATE”, but that also resets auto-increment IDs, not sure if that’s a good-idea tbh

1 Like
3

Thanks @Apple. That’s the route I was going but wanted to check Wappler didn’t already have a similar feature.

I want the new table to be named with a datetimestamp so have used Set Value to create a variable and then have a parameter in the custom query but it doesn’t like it.

How can I set the table name to be like this? tablename_202207251344 (YearMonthDayHourMin) for example?

4

I believe you can use expressions in the custom query, but I’ve never tried:

SELECT * FROM tablename_{{NOW}}

It makes sense Wappler wouldn’t provide a feature to clone tables, as such should only be done in migrations :wink:

For doing in a migration, you’d need to use .raw() I think in the migration file

Edit: {{NOW}} might not return a number, you might want to use another variable or convert it to Unix timestamp first

2 Likes
5

That was what I needed. Thanks @Apple.

CREATE TABLE {{BackupTableName}} LIKE names;

I use the Set Value action before it to set the value of {{BackupTableName}} so now I can have that formatted however I want. It works perfectly.

So, if you want to dynamically set the table name, use {{varName}} instead of the normal :P1 and parameters.

EDIT:

The Set Value I used had this code:

'names_'+NOW.formatDate('yyyyMMddHHmm')

6

Don’t forget about the possibility of SQL injection, BackupTableName should be properly sanitized (e.g.: only allow a-z, A-Z, 0-9 characters)

1 Like
7

The script has the usual Security Restrict and doesn’t have any variables posted to it.