Data Entry In View Of Crosstable Join

[regex]

[EDIT: I have been using a wrong word. Wherever I used “crosstable”, I should have said “associative table”. Correcting it in the two first posts.]

Hi everyone,

I am interested in ideas about ways to enter data in a view of an associative table.

For my main apps I code extensive database management interfaces in php, but at the moment I am working on a small project for a friend, and my job would be easier if she could enter data into an existing product (such as SQLyog), so that I just need to code and maintain the web page generation, not the CRUD.

Here is the basic need. To simplify, there are three tables for photo albums:

1. images: id, filename

2. albums: id, album

3. albumximage: albumid, imageid (the associative table, where the fields refer to the parent PK id fields in images and albums)

The View shows ambumximage (the associative table) and the parent fields:

albumid, imageid, album, filename, i.e.

Quote:

SELECT album, filename, albumid, imageid FROM imagesxalbums ixa

JOIN albums a ON a.`id` = ixa.`albumid`

JOIN images i ON i.`id` = ixa.`imageid`

I want my friend to be able to add images to albums in the associative table. The View is convenient so she can have feedback about the IDs she is inputting by seeing the album name and file name.

I tried adding data to the view in SQLyog Ultimate (F11). I am able to enter IDs in the albumid and imageid fields, but when I click refresh, the record is not added.

Wondering if anyone knows a convenient method (in SQLyog or otherwise) to enter data into such an associative table (through a view / join so you can see the image name and album name).

I would be very keen to avoid having to code and maintain interfaces on such small projects.

Thanks!

[Author]

Posts