Using Formtools with existing database and tables

[royo]

I searched the docs and forums and an answer didn't jump out at me.

I just installed a fresh install with version 2.1.5. All good no problems with the install.

It seems that the premise of this tool is for greenfield data usage, i.e., creating a new database and adding new data.

How can I use the forms for existing data in an existing table in an existing database? I would have thought that you could simply give the tool permissions to the database that you want, define which table, and then you could select which fields that you want to use in your form. No?

Hopefully I'm just overlooking something.

[royo]

Anyone? Is having a form that can be used to update existing data in any database or table beyond the scope of Formtools? If so and you can recommend another open source tool, please direct me.

Thanks!

[Ben]

Hi Royo,

I'm afraid not! Form Tools has a specific way of storing the data in a database; each field type may store the info in different ways, with different column sizes and so forth. You can't just tell it to look at another database table to provide an interface for the data. That would be very cool, but an absolute monster to code properly!

Personally, I haven't encountered a script that allows you to do that. Anyone?

- Ben

[royo]

Thank you Ben.

It looks like I'll have to hand code it.

I appreciate your work. I have Form Tools installed now, so I'm sure I will come up with a use case for it.

[Ben]

haha fingers crossed!

- Ben

[royo]

Ben, per our email exchange, I am posting what I ended up doing for the benefit of others. Thanks for your thoughts and your contributions to Form Tools. I'm sure I will use it sometime soon for another project.

This is what I ended up finding for my project above: I used PHPmyEdit.

http://www.phpmyedit.org/article.php?download

You enter the name of the database that you want to create forms with (where your data is) enter the db username and password and it creates the php file ready to roll with browsing, sorting, edit/updating, inserting and deleting. It's ugly but highly functional and you can mod that php code from there. Especially good if you are prototyping like I am.

Assuming that you have all of your data in an excel file:

So five minutes to import an excel file natively (or export to CSV first). Then import with PHPmyadmin to create a database. Two minutes to create the forms with phpmyedit and you have a functional site. A general note, take the time to remove all minus signs from sheet names, and columns. Use lowercase and underscores to make them readable. This will save you a lot of time. Also, name some extra sheet columns if you get an error for duplicate null columns during a csv import. Likely there is data that didn't export properly due to double quotes in a text field (making the import think it has extra columns). I actually ran into that. A simple solution was to create a couple of extra column titles (named anything like A B C D) then you can sort by those columns to clean up the data. Anyway, it's pretty easy if you know the tricks.

For a reporting tool I have found http://www.eclipse.org/birt/phoenix/intro/ but have not tried that yet. Soon.

Cheers!

[Mophilly]

royo,

Thank you for posting the results of your research. I find FormTools a very compelling project and I use it for various forms in new web sites. Works very well.

Like you, I engage in a lot of heavy database work and sometimes a typical client/server database application is not possible. For example, if the web app is located on a commercial hosting service that is subject to unrelenting attacks and must prohibit access from outside the firewall.

For this type of work, a robust set of forms must be built. Building these in PHP, when compared to programming tools designed for database work, can be quite tedious. So, with this in mind, thanks again!

[royo]

Mophilly,

You are welcome. Glad to help. A couple of things.

Unless you do business with China or Korea, block them. It will cut down on a lot / most of attacks to your systems. One of the tricks they use is to hammer your unprotected forms from multiple IPs causing major SQL processing between the webserver and the database. Getting your CPUs running at 100% will open the door to triggering a crash and gaining access to your system (they go after known bugs and exploit them). Just block access.

For one of my use cases, I only had a handful of users so I also added a .htaccess file requiring authorization handled by the web server which is a lot more resource friendly than allowing SQL look ups! There are many other ways to address this but those are the no brainer easy ones with good results.

Here are some very useful sites to aid in blocking China.

Iptables
http://www.okean.com/antispam/iptables/iptables.html

Apache
http://www.wizcrafts.net/chinese-blocklist.html

General
http://www.countryipblocks.net/

royo,

Thank you for posting the results of your research.

[Desy231]

For a very long time I was looking for information on this topic. Thanks to all!

[Laura Kilvington]

For a very long time I was looking for information on this topic. Thanks to all!

This new version is awesome and all the bugs errors are removed when you can update this database or new install in your system.  If you don’t understand this then visit this Website and get some informative things that may be you help out.