SQL CRUD: what’s good and what’s crud?

I maintain a membership database for my canoe club and I implemented the database years ago using a PHP library called Phormation which let me make an index page with simple code like:

query = “SELECT * FROM member WHERE year=2015”
show_table(column1, column2)

and an entry editing page with something like this:

query = “SELECT * FROM member WHERE id=$id”
widgets.append([column1, “name”, Textfield])
widgets.append([column2, “joined”, Date])

and voila I had a basic UI to edit the database.

Now I want to move to a new server but it seems PHP has made a backwards incompatible change between 5.0 and 5.5 and Phormation no longer runs and it’s no longer maintained.

So lazyweb, what’s the best way to make a basic web database editor where you can add some basic widgets for different field types and there’s two tables with a 1:many relationship which both need edited?


10 Replies to “SQL CRUD: what’s good and what’s crud?”

  1. Rails allows quick CRUD prototyping:

    $ rails new canoeclub
    $ cd canoeclub
    $ rails generate scaffold member year:integer name:string joined:date
    $ rails generate scaffold tag name:string member:belongs_to

    This creates:

    * a database migration file that will add two tables ‘members’ and ‘tags’
    * columns with types as given above (‘tags’ will have a ‘member_id’ column)
    * automatic columns: id, created_at, updated_at
    * files under app/model that describe the models
    * files under app/controllers that do basic CRUD
    * files under app/views that show basic CRUD html

    Rails automatically generates these files with the names given. Note that a scaffold for ‘member’ creates a model ‘Member’, a ‘MembersController’ and a database table ‘members’. It automatically expands the plural form in the controller and table case. All this is customizable see: http://guides.rubyonrails.org/association_basics.html

    If your current DB schema fits those conventions you could go ahead and skip executing the database migrations. Else you could either customize the conventions to your needs or migrate the DB to the conventions.

  2. I just wrote my first serious PHP code. It’s the commenting system on my static blog. There is no database in there. So I cannot advise you on a good PHP library. Instead another tip: use PHPLint to check the code. It allows you to annotate you PHP with types. Especially, if you do not write PHP often, it will helps you catch small errors.

    The last time webpage with a database that I wrote used Yesod with Persistent. That resulted in very save and pretty code.

  3. Since noone else has commented yet, I’ll chip in. I have heard good things about Ruby/Rails with scaffolding. Not tried it myself, but it’s supposedly simple to get basic CRUD stuff.

  4. Hi Jonathan
    First: I”ve been a use of Kubuntu since 6.06 and is a great fan of your work. Thanks for all you’ve done for ‘my dist’ ๐Ÿ™‚
    Now, thats a great question, I’ve been thinking to do the same thing – I’ve developed a minimalistic open source member management system (because those are apparently hard to find) that you can dump on any apache server with php and sqlite-support. Because it’s a generic solution and the idea is to let any ‘techie-light’ deploy it it needs an GUI editor to adjust the member fields. Right now I’m transitioning it to a REST api (for integrations and easier UI) and adding some authentication beyond htaccess.
    I don’t know how advanced your system is, but I’d love to have more people (especially devs ๐Ÿ˜‰ ) using OpenSourceMemberManager. First feature was a good CSV import/export function ๐Ÿ˜‰
    The datastructure is stored in a JSON file – I guess it will be a lot easier to get started when there is a GUI field editor.
    Anyway, I’m also all ears to hear about what other people have to say to your question ๐Ÿ™‚

  5. I am really surprised nobody has mentioned django’s admin here. I guess you know python, and it’s heavily customizable (custom widgets etc. ) while very easy to set up. Though not its primary purpose, I think you can find examples of complex sites written entirely with the admin feature. And you don’t have to write the SQL.

Comments are closed.