Ridge Robinson Ridge Robinson - 2 years ago 121
MySQL Question

PHP MySQL UPDATE join tables

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:

id first_name last_name
1 John Doe
2 Mary Lewis

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?

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

Answer Source

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

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download