HeidiSQL as Basic User Interface

I need to create a front end for a MySQL database. Right now it's a fairly simple database (4 tables) which replaces an excel spreadsheet. We desperately need to get away from that excel spreadsheet but don't have time to develop the user interface we need. If I were the one inputting the data, this would be no problem. However, the person who is inputting the data is going to need this to be as simple as possible so that he doesn't inadvertently corrupt/delete data. I figure that if I can make the 4 tables look like the excel sheet then we will be golden. That is my problem...

I am new to HeidiSQL so I don't know if it has the facilities to help me achieve this task. I was thinking I'd just create a view which joined the tables and shows the fields in a manner he's used to. That's simple. I would then lock down his privileges so that he can only insert and update (no deleting). Now, the part which I am hoping HeidiSQL can help with... is there anyway to simplify the selection of joined items? For example, say the main table references a products table using the product_id key (joining on product_id). Each product has a model and description (VARCHAR). Ideally he could click on any field from the products table and get a drop down to select from. Make sense?

I am trying to avoid using access as a front end... I hope that HeidiSQL can help me.

You can set your tables to use InnoDB engine, then create appropriate foreign keys with Heidi's table designer (tab "Foreign keys"). Then, when you're on the data tab to edit a table's content, you will get drop down's in columns which have a foreign key reference to some other table. Just try it out.

What won't work is joining tables together while preserving editing capabilities. HeidiSQL let's you edit rows in data tab and also on the query tabs, but only as long as you have data from exactly one table.

Well, there is a drop down selector to pick values for foreign keys but I don't think HeidiSQL is a suitable tool for inexperienced users. It's basically designed to provide full access to the database.

Have you considered building some forms with Microsoft Access? You have a wizard to create the forms and you can link the *.mdb file with the MySQL server using ODBC. I believe that OpenOffice.org/LibreOffice have a similar functionality as well.

Anse, thanks! Can you tell me how it decides which text to place in the drop down? For example, I select the products field (FK) to edit it and the model field is shown (1:Model A,2:Model B,etc). There is a description field as well but it is not shown. Any method to control which is shown?

Kalvaro, that is my fall back plan. I really despise Access.

The guy that is going to enter the data is pretty trustworthy so I don't have to worry about him poking around where he shouldn't be. If I can give him very clear instructions to get to the data tab then I think we'll be ok. I just need that data tab to look similar to what he's used to with Excel.

In a foreign column, HeidiSQL takes the first X characters of the first TEXT or VARCHAR typed column of the target table. Dunno exactly how many chars. What about trying out?

In fact I already gave some editor colleagues in my company the advice to use HeidiSQL to fill a simple table with dates for tax accountants. Reason was the same - no time to develop a dedicated PHP editing page. I gave these colleagues only minimal SELECT/INSERT/UPDATE privileges on this single table. These colleagues just needed 5 minutes to get HeidiSQL explained: expand the database, click the table, click "Data" tab and go. Although there's always some gutt feeling they break the table content at some point in the future, up to now they had no problems at all.

Thanks for all your input! This is working out well. I've got two of three people up and running using this as a simple UI. I've locked down the permissions so they can do minimal damage. So far, so good.

One more question though. On the screen shots page there is a multi-value selector tool (see mappings field). How is that set up? I need something similar.

Disregard. From the other screen shots I can see that mappings is just a bitfield. I was thinking it was somehow linked to another table.

Yes, "mappings" in that screen is a SET field providing its own list of selectable values (not BIT btw).

I've got a linked table set up with foreign keys which isn't working (no drop down). I have other similar links which do. They are set up in a similar fashion. In looking for a reason why only this one doesn't work the only thing I can come up with is that a drop down will not be created if the key links to a table with a lot of records (~ 3500 in this case).

Thoughts?

No, the number of records in the target table is totally irrelevant here. Not sure, I guess HeidiSQL does not parse the key the right way or there is some privilege missing. Please post the CREATE TABLE code here for all tables you have, if I shall analyze that.

In the rma table I link to a few other tables (you'll see the 3 foreign keys). Two of them work (status_id and defect_id) and serial_id doesn't. By 'works' I mean that I get a populated drop down list when I go to edit the field. Anything stick out?

rma table:
CREATE TABLE `rma` (
`rma_id` INT(10) NOT NULL AUTO_INCREMENT,
`serial_number` VARCHAR(50) NOT NULL DEFAULT '0' COMMENT 'Delete this field once serial_id is linked',
`serial_id` INT(10) NOT NULL DEFAULT '0',
`model` VARCHAR(50) NOT NULL DEFAULT '0',
`status_id` INT(10) NOT NULL DEFAULT '1',
`opened_date` DATE NOT NULL,
`opened_notification` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`customer` VARCHAR(50) NOT NULL,
`contact_person` VARCHAR(50) NOT NULL,
`contact_phone` VARCHAR(50) NOT NULL,
`contact_address` VARCHAR(100) NOT NULL COMMENT 'Needs to be parsed into fields',
`contact_state` VARCHAR(20) NOT NULL,
`contact_zip` INT(30) NOT NULL,
`contact_email` VARCHAR(50) NOT NULL,
`problem_description` VARCHAR(300) NOT NULL,
`problem_area` SET('Flash','Camera','Motion','Battery','Controller','Charger','Key FOB','Mechanical','Device Server','Speaker','Other') NOT NULL,
`disposition` VARCHAR(300) NOT NULL,
`defect_id` INT(10) NOT NULL DEFAULT '1',
`labor` DECIMAL(10,1) NOT NULL DEFAULT '0.0',
`cost` DECIMAL(10,0) NOT NULL DEFAULT '0',
`warranty_repair` ENUM('Y','N') NOT NULL DEFAULT 'N',
`repair_date` DATE NOT NULL,
`received_date` DATE NOT NULL,
`received_notes` VARCHAR(100) NOT NULL,
`received_items` SET('Chassis','Case','Key FOB','Keys','Wall Bracket','C-Clamp','Padlock','Chain','Box with Foam') NOT NULL,
`shipped_date` DATE NOT NULL,
PRIMARY KEY (`rma_id`),
INDEX `FK_rma_rma_status` (`status_id`),
INDEX `FK_rma_defects` (`defect_id`),
INDEX `FK_rma_serial_number` (`serial_id`),
CONSTRAINT `FK_rma_defects` FOREIGN KEY (`defect_id`) REFERENCES `defects` (`defect_id`),
CONSTRAINT `FK_rma_rma_status` FOREIGN KEY (`status_id`) REFERENCES `rma_status` (`rma_status_id`),
CONSTRAINT `FK_rma_serial_number` FOREIGN KEY (`serial_id`) REFERENCES `serial_number` (`serial_id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=11235

serial_number table:
CREATE TABLE `serial_number` (
`serial_id` INT(10) NOT NULL AUTO_INCREMENT,
`serial_number` VARCHAR(50) NOT NULL DEFAULT '0',
`product` VARCHAR(50) NOT NULL DEFAULT '0',
`inspector_id` INT(10) NOT NULL DEFAULT '1',
`ship_date` DATE NOT NULL,
`sales_order` VARCHAR(50) NULL DEFAULT NULL,
`customer_id` INT(10) NULL DEFAULT NULL,
`customer` VARCHAR(100) NULL DEFAULT NULL COMMENT 'To be eliminated once customer table is built',
`camera_serial` VARCHAR(25) NULL DEFAULT NULL,
`warranty_id` INT(10) NOT NULL DEFAULT '1',
`remarks` VARCHAR(100) NULL DEFAULT NULL,
PRIMARY KEY (`serial_id`),
INDEX `FK_serial_number_old_inspectors` (`inspector_id`),
INDEX `FK_serial_number_old_warranty` (`warranty_id`),
INDEX `FK_serial_number_customers` (`customer_id`),
CONSTRAINT `FK_serial_number_customers` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`),
CONSTRAINT `FK_serial_number_old_inspectors` FOREIGN KEY (`inspector_id`) REFERENCES `inspectors` (`inspector_id`),
CONSTRAINT `FK_serial_number_old_warranty` FOREIGN KEY (`warranty_id`) REFERENCES `warranty` (`warranty_id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=3872

defects table:
CREATE TABLE `defects` (
`defect_id` INT(10) NOT NULL AUTO_INCREMENT,
`defect` VARCHAR(300) NULL DEFAULT NULL,
PRIMARY KEY (`defect_id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=3

I needed to remove some foreign keys to tables I don't have but serial_id and defect_id works here perfectly. I see these automatic queries when editing the serial_id field:

SHOW CREATE TABLE `serial_number`;
SELECT `serial_id`, LEFT(`serial_number`, 256) FROM `serial_number` GROUP BY `serial_id` ORDER BY `serial_number` LIMIT 1000;

I see those exact queries too but no drop down. It behaves the same on two different machines (both win7). Any other thoughts?