[I've continued this in the same thread but have changed the subject, as
it's essentially a follow-on question..]

On 29/10/10 14:36, David wrote:
(about: Re: Complicated SELECT query using data from multiple tables)
in a web form, as a precursor to allowing the user to update their data, and
then send the changed data back to the database..]

After some kind help from members of the group, I eventually came up with
the following SELECT query:


SELECT person.id AS pid
, prt.position AS prfl_tpl_posn

, prf.id AS prf_id
, prf.name
, prf.content_type

, prr.content
, prr.is_visible_on_site AS field_vis

FROM person
LEFT JOIN profile_template prt ON person.staff_group_id = prt.staff_group_id
LEFT JOIN profile_field prf ON prt.profile_field_id = prf.id
LEFT JOIN profile_record prr ON (person.id=prr.person_id AND prf.id =
prr.profile_field_id)

WHERE person.username = 'somebody'
ORDER BY prf.id


This resulted in the following data:

+-----+---------------+--------+-----------+-----------+-----------+------+

(fvis = field_vis)


Now, after the user submits the web form, I need to update the profile_record
table with the data which they have amended.

The user (depending on their staff group) can update the rows in profile_record
associated with the appropriate profile_field_id(s).

(Users in *different* staff groups can update *different* profile_field_id
fields, but there is exactly 1 row for each profile_field_id that that user can
update ..unless they have not previously entered profile data, where the
relevant row does not yet exist.)

For each of the profile fields available, the web form submits the
profile_field_id, the content, and the 'is_visible_on_site' value.

The user's username is known to the system (but not their id or staff group
without having to look it up using their username (the joys of stateless systems!)).

To help explain, here's some (spoof) existing data for a user:

+-----+-----------+--------------------+-----------+------------------+


What I need to do, is update this profile_record table, using the data submitted
from the form.

However, there are a number of snags, which have me confused (again)!

From the submitted form data, I know the values of:
profile_field_id, content, is_visible_on_site
(for the appropriate number of fields)

and I know the user's username (but not their id, which I need to look up when
needed.)

1st snag: what if a malicious user manipulates their form data to submit a bogus
value for the profile_field_id? Because this is a system _for internal use_ and
because of time pressures, I'm going to (with permission) ignore this for now
and assume that these are trusted values (well, I'll check that they are
numeric, at least), but it would be nice to know how to be able to handle this
ideally (although it's not really an SQL question but to do with the PHP form
handling code).

2nd snag: if a user already has data in the database for a field, the row should
be UPDATED; if the user does not have data for a field, an appropriate new row
should be INSERTED.

3rd snag: for data integrity reasons, once again I presumably have to try to
make the update take place as an atomic transaction.


I've been scratching my head over this for a while, and I'm afraid I think this
is beyond me at this stage.

As I see it, there's quite a sequence of tasks involved:

(pseudo-code)

select the user's id and store it somehow(!?)..
for «each record's-worth of form data» {
update profile_record set «relevant data from form»
where profile_field_id = «id from form» and person_id = «user id»

unless (there is no matching row already (rows affected?)) then
insert profile_record values (NULL¹, «relevant data»)
}
loop around to the next record's-worth of form data until all done..


¹ The NULL as the first value should cause an appropriate primary key to be
generated for the new record.



Am I thinking along the right lines here, and can anybody give me any clues in
the right direction?

I can't see how I would be able to do a single UPDATE that would update all of
the appropriate rows, because it is different data going into different rows
with different WHERE clauses..

...then there is the issue of needing to know whether a field already exists
before trying to update it, and INSERTing a new row, if not.

...and the issue of having to find out the user id before I can do any updating
or inserting in any case.


I'm sorry if this seems like another "do my homework" question, but I think that
this particular project has perhaps turned out to be a bit too much of a step up
for me at this stage, from being reasonably comfortable with 'beginner' MySQL to
trying to leap up to more advanced tasks, before I'm quite ready for it!


Thanks very much if anyone can offer any advice,


David.

On Nov 16, 1:07*pm, David
<da... (AT) 55952163-3189045 (DOT) bogus.domain.invalid> wrote:
1. Supply the profile_id either as a 'hidden' input value or as as
session value.
The user would be able to discover the value (by scanning the html
source code) but they wouldn't be able to manipulate it.
As you suggest, these are matters for further exploration in a PHP
forum.

2. See INSERT... ON DUPLICATE KEY syntax

3. Actually, you can do a hell of a lot with a single UDPATE, but it's
tricky - and, with respect to '2', it won't help in this instance!

So the answer is 'Yes. Use a PHP loop to process all elements of the
form'.

On Nov 16, 1:07*pm, David
<da... (AT) 55952163-3189045 (DOT) bogus.domain.invalid> wrote:
Also, be prepared to uncover structural weakenesses in your database
design!