Databases
StackOverflow2013
Postsdbo
POMySQL INSERT if doesn't exist but with a changing value
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL INSERT if doesn't exist but with a changing value
    primarykey
    data
    text
    <p>First of all, many thanks to toph for solving my first issue query, </p> <p>I currently have 2 tables, one for courses and one that links tutors to courses, originally I wanted to just get a list of all courses a certain tutor wasn't link to and toph solved this for me with the following code:</p> <pre><code>SELECT c.course_id, c.course FROM course AS c LEFT JOIN course_tutor_link AS ctl ON c.course_id=ctl.course_id AND ctl.users_id=$users_id WHERE ctl.course_id IS NULL </code></pre> <p>which works like a charm my next step is to insert links for that tutor into course_tutor_link for the missing courses, I can do this with a bit of PHP looping through the results of above as a simple INSERT command resulting in</p> <pre><code>INSERT INTO course_tutor_link (course_id, position, users_id) VALUES(2,'swsa',9),(3,'swsa',9),(4,'swsa',9)... etc </code></pre> <p>But then I thought about whether or not I could do all of this as a single query, if it was just one set of values I could do it based on this SO link: <a href="https://stackoverflow.com/questions/3164505/mysql-insert-record-if-not-exists-in-table">MySQL: Insert record if not exists in table</a> but I do not know how to extend it so that it cycles through each course_id value that is outputted from my original query</p> <p>-----UPDATE----</p> <p>It appears I did not give enough data at the start so my apologies to all and I hope this additional data will be suffice:</p> <p>the table course has the following fields: course_id (PK), course, level, department_id</p> <p>the table course_tutor_link as the following fields: ctl_id (PK), users_id, position, course_id</p> <p>It is possible for a user to have different positions in different courses e.g be head of department for physics but be a tutor for chemistry. it is also possible that (at the start at least or when a new member joins) that a user will not be a member of any course and hence I cannot rely on just pulling the data for an existing position in the course_tutor_link as it may have the wrong position or the user not exist in it at all.</p> <p>The values for users_id is held in a php variable called $users_id and the position is held in a php variable called $user_type</p> <p>regards</p> <p>Zen</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USZenonline
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL INSERT if doesn't exist but with a changing value
      1. This table or related slice is empty.
      1. VTUpMod
    1. COMy answer is below but note that you're only selecting 2 values and inserting 3. I guessed at the select but you might want to modify.... ah $users_id. updating..
      singulars
      1. POMySQL INSERT if doesn't exist but with a changing value
      1. USDigital Chris
    2. COI actually only need to get the course_id (the course PK) from the course table as I already know the person position and users_id, I only had the c.course added in for my own info whilst doing the testing and printing out on phpAdmin
      singulars
      1. POMySQL INSERT if doesn't exist but with a changing value
      1. USZenonline
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload