Allowing user to download, edit and upload a database table

I'm looking to create an easy way for a user to create a table, and upload it to the server using ftp. On th开发者_JAVA百科e server side, I'd like to query this table like an SQL-like query.

As I'd like the user to edit this in something like OO Calc, or MS Excel, would csv files be the best/fastest to parse? Is doing a fgetcsv a good way? Could you suggest any alternatives?

---

Your best bet is to allow users to upload a CSV file with the first line containing field names (and maybe field types (i.e. int, varchar, etc)). Then you can parse it and validate it for valid/malicious data. If it passes inspection then create a database table escaping all relevant data from which you then can query from.

This way not only do you validate the data first, which is always a good idea, but you control everything like the naming of tables, etc, which helps to keep your data out of malicious hands.

---

If the user needs to create table give him access to phpMyAdmin (of course properly secured). That can support uploading data, too.

---

I think CSV would be the easiest thing to work with and will be very fast. Check out the fgetcsv() function. You can read the file into an array and search through that. If your files aren't huge and your queries are standard, then you can write your own search code and not worry about using a database.

If you need to handle any query your user types in, then you'll need to move the data into an SQL compatible database to query.

---

I've done this before using XML. The nice thing about this is that you can insert data that isn't flat (parent child relationships). If someone is having you load a table it is likely that eventually they will ask you do include child information.