Front-End for MySQL Forms (Windows or OSX)

Question

[Edit: I have awarded the bounty but not the question, as I feel there is still a chance for a better answer.]

I usually code my MySQL data entry interfaces in php.

But for quick projects where I would like to focus on the web side of things (pulling data from the database), I am looking for a form front-end to MySQL. Ideally, this would be an off-the-shelf Win or OSX program that I can hand over to a friend or client so she can input data into forms.

An Example

The lines above summarize the question, but here is a typical situation to showcase why "just give them phpMyAdmin" or some other web interface to MySQL generally wont work.

We have two main tables: images and albums. The third is an associative table that associates images with albums.

Images: id (PK), filename

Albums: id (PK), album

Album_Image: id (PK), imageid, albumid (these are foreign keys: the two id PKs from the Images and Albums tables)

For big projects, I don't mind coding a nice "back-end" interface (CRUD) that lets me edit data in a very comfortable, customized way.

For small projects, for instance an image gallery for a friend, I would like to be able to only program the "front-end": web interfaces that pull data from the database. For the back-end, ideally, I would like to give my friend an off-the-shelf solution so she can enter images, albums, and associations between images and albums. In the old days (I am thinking of DBaseIII), it was really easy to give interfaces to do that entry side of things.

Here is a crude approach I have tried. I have set up a view of the associative table joined to the parent tables, so it shows albumid, imageid (the PKs) and album, filename (associated fields in the parents) using this query:

SELECT albumid, imageid, album, filename FROM album_image axi
JOIN albums a ON a.`id` = axi.`albumid`
JOIN images i ON i.`id` = axi.`imageid`

The idea with this crude approach is that my friend would enter the parent PKs (albumid and imageid) in the associative table through the view, and that after hitting Insert, the full parent fields (filename and album) would be visible in the View for visual feedback. I have tried this view in three GUI tools: SQLyog (Win), HeidiSQL (Win) and Sequel Pro (OSX).

1. In Heidi and Sequel Pro, I have not found a way to edit the view.

2. In SQLyog, I can edit the PK fields (albumid and imageid) in the view, and the parent fields (album and filename) show properly when I hit "refresh". That's great. It works because even though the view shows three tables, we are only editing from one table. But I cannot delete a row as SQLyog doesn't know from which of the three tables we are trying to delete. On the other hand, in SQLyog, I able both able to insert and delete in a Query tab that contains the same query that creates the view, because in this kind of tab SQLyog allows me select the table being edited. So that works, but this workflow might start to be a little complex for my friend: she would have to open SQLyog, connect, open a saved query, select the right table to edit within the query, and make all the other right moves.

Wondering if any one knows an existing tool that is really good at making forms for MySQL---ideally a Windows or OSX binary. I will consider a web solution, but I am not interested in a framework---the key is speed of deployment, and if we went the framework route I would be better off making the interface with my own CRUD libraries. Again, I can code it, but my goal with these kinds of "quickie projects" is to focus my workload on the front end (web interface to the database), leaving the back end to an off-the-shelf tool.

Thanks heaps in advance for any insights. :)

Edit: I see that no one has mentioned Navicat or MySQL Workbench. I haven't tried either, wondering if someone knows off the top of their head whether they would do the trick.

Answer 1

I think what you're looking for is a form front-end for MySQL.

Since it sounds like you are more interested in binaries than web apps, have you looked into Microsoft Access? It can talk to MySQL and could be just what you need.

Open Office Base may not have quite the features you're looking for.