PHP MySQL UPDATE join tables

Question

I have seen that there are similar questions, but they do not seem to be helping...could just be that I am not understanding correctly and able to translate their examples to my needs. Any help is appreciated.

I am trying to convert an input field on a $_POST form to my users table, specifically on the managers key, from the managers name (in the form), to the managers id (in the DB). Let me show an example.

Our DB has (among other tables) a users table and a managers name. The managers table just has the manager's id, first & last name. The users table has their own id, first_name, last_name, and associated manager id. This is how it looks:

MANAGERS
id  first_name  last_name
1   John        Doe
2   Mary        Lewis

USERS
id  first_name  last_name   manager
1   Steve       Thompson    1

I have created an edit form for the users table where this information is inputted from the database, but I have joined the two tables in my SELECT statement so that instead of the managers.id showing, it actually shows the managers.first_name and managers.last_name in the form.

So what I am trying to do is allow someone to edit the manager associated with a user, by submitting their name instead of the ID, but in the DB it associates it back to the ID. We have many managers so most people don't know what their ID in the DB is. However, they all know their names. So is it possible to have an SQL UPDATE query to achieve:

As you can see, the User Steve Thompson has manager 1 (John Doe), but the manager of this user needs to be changed to Mary Lewis. How can I enter the name 'Mary Lewis' into the Users Edit Form, and have it update the DB table using the manager's name instead of the manager's id.

I have seen people say you can't update multiple tables in one query, but I feel this is a little different as it is more like a JOIN, but I am unsure how to do it. Does anybody know if this is possible?

EDIT I have seen this one and feel it might help...but not sure how to implement correctly. Perhaps this can help someone as well? Update one MySQL table with values from another

aren't you just saying you want that user row to have the manager changed from a 1 to a 2 ? To me your challenge is how do you want to focus on the UX to have this happen. Not the difficulty of the update stmt — Drew, Jun 18 '16 at 22:48
I just want to be able to use the manager name in the form, and have it update in the DB with their associated ID from the manager's table. Do you think this is possible? — Ridge Robinson, Jun 18 '16 at 23:10
Ah you're back Ridge. You went away on me there. (you deleted). You started talking about the user (Steve Thom) and how you wanted to change his Mgr to a different person. You need to communicate how you want to inform the server of that. Like user 1 gets changed to mgr 2. So to me it is a UX challenge. Like a chooser. The sql is easy. — Drew, Jun 18 '16 at 23:10
Yes...I apologize for that. It was an accident! But it's back up now haha — Ridge Robinson, Jun 18 '16 at 23:13
I see how you are saying it is like a UX problem, and I agree that it can be thought of that way. For the sake of UX though, I feel it should be the Manager Name vs ID number that is showing...but in my users table it is still better to have the manager id. What do you think? Would you mind sharing your thoughts on the "easy sql"? — Ridge Robinson, Jun 18 '16 at 23:18
The way I would do it is like this. Have a PHP multi-dimensional array for the managers. It can support pagination. Say 20 at a time come into your GUI. But whatever is in the 2 dimensional array is visible in the GUI and it re-fetches (pagination) for next page and cleans itself up. So, only 20. So the array has mgrId, FirstName, LastName. The chooser then has the info for the appropriate `mgrId` upon hitting the update button, and this doesn't make the mistake that Paul is suggesting in his answer. — Drew, Jun 18 '16 at 23:28
And there is no need for a join. You are ditching the old mgr anyway. — Drew, Jun 18 '16 at 23:30
Incidentally, you *can* update multiple tables in a single query - not that you need to here. I agree with Drew that the user interface should simply submit the id when the name is selected. — Strawberry, Jun 18 '16 at 23:35
Also, typically, managers are simply users who don't have managers. So you can get rid of one table. Yay. — Strawberry, Jun 18 '16 at 23:42

Paul Spiegel · Accepted Answer · 2016-06-19T00:15:54.533

1

After you managed to parse the managers name and split it to first and last name you can just select the managers id in a subselect

update USERS set
  first_name = :user_first_name,
  last_name  = :user_last_name,
  manager = (
    select id
    from MANAGERS
    where first_name = :manager_first_name
      and last_name  = :manager_last_name
)
where id = :user_id

You can also use a (CROSS) JOIN:

update USERS u
cross join MANAGERS m
set
  u.first_name = :user_first_name,
  u.last_name  = :user_last_name,
  u.manager    = m.id,
where u.id = :user_id
  and m.first_name = :manager_first_name
  and m.last_name  = :manager_last_name

Note that it won't work if the managers name is not unique. It will also not work if no manager with that name exists.

However i would probably use a drop down selection with the managers id as value. You still can display the first and last name:

<select name="manager">
  <option value="1">John Doe</option>
  <option value="2">Mary Lewis</option>
</select>

This way you will display the name but get the ID on submit.

edited Jun 19 '16 at 00:15

answered Jun 18 '16 at 23:24

Paul Spiegel

30,925
5
44
53

I wouldn't do that. You can have seven Mary Lewises working there. – Drew Jun 18 '16 at 23:25
That is a great point Drew...and one of the reasons that it is necessary to keep the manager id. Thanks for the answer Paul, I'll see if I can maybe work off your logic here. – Ridge Robinson Jun 18 '16 at 23:28
@Drew, the business logic suggests that you cannot have multiple managers called Mary Lewis there. That said, the subquery is unnecessary, so I wouldn't do that either - but for a different reason. ;-) – Strawberry Jun 18 '16 at 23:31
I don't see much business logic for that @Strawberry other than it suggests "Sorry, we cannot hire you, we already have a Mary Lewis" :p – Drew Jun 18 '16 at 23:33
@Drew, i just show how to solve such a problem. I wouldn't do it either in that case :-) – Paul Spiegel Jun 18 '16 at 23:36
@Strawberry, i would like to know how to do it without a subquery and without a second query. – Paul Spiegel Jun 18 '16 at 23:38
It says "we can hire you, just not as manager". I agree that that might not be a particularly fair or sensible arrangement - but that *is* the arrangement implied by the schema – Strawberry Jun 18 '16 at 23:38
@paulspiegel it's just a simple join – Strawberry Jun 18 '16 at 23:41
I was able to work off of Paul's logic, and change it just a little using my $_GET variables to do the job. Thanks everyone for the comments, they are all very helpful – Ridge Robinson Jun 18 '16 at 23:43

PHP MySQL UPDATE join tables

1 Answers1