MySQL query using INSERT into and WHERE, possible?

Question

I need to insert values into a table within my database from an HTML form using PHP. Following the query

INSERT INTO tableX 
      (attractionID, customerID, number_of_people, date_of_attraction)
values ('X', 'Y', '2', '2020-12-01')

the record is created fine. The strings have been entered to demonstrate that the query works, the actual query uses variables with data stored from html form user input in their place.

I have used a query to store the value for customerID (auto_increment'ed) from a customer table. I am also using $_SESSION['UserName'] to store the username of the user from the customer table. All of the variables created from the html form user inputs are stored correctly because they can all be echoed out and echo as they should.

My issue comes when trying to use the following query "

INSERT INTO tableX 
        (attractionID, customerID, number_of_people, date_of_attraction)
 values ('X', 'Y', '2', '2020-12-01') WHERE UserName = '$UserName'

$UserName has been created from $UserName = $_SESSION['UserName'].

Is it not possible to use WHERE in a query if the record being created is depending on a column from another table?

Sorry if this doesn't make sense, I have tried to explain as best as I can but I am new to MySQL and PHP.

insert with `where` makes no sense. either UserName should be inserted as well or perhaps you want an update. — , Dec 05 '18 at 21:11
`INSERT INTO` inserts a new row. `UPDATE WHERE` updates an existing row. `INSERT INTO.. ON DUPLICATE KEY` inserts a new row or updates an existing row where it matches a primary key. — aynber, Dec 05 '18 at 21:12
It wouuld be helpful to see your table(s) structures. If you have a "userName" column in your "attractions" table, then you need to just insert the userName as a vaule, like you do with the other columns. If you have a seperate table, say "attractionUsers" that contains an attractionID and a userID, then you'd need to insert a relation... — Stuart, Dec 05 '18 at 21:13
I don't really understand what you're trying to do. What would "insert into.. where" do? It may be posible to do what you want, just try to explain it in English — Joni, Dec 05 '18 at 21:14
Possible duplicate of [On Duplicate Key Update same as insert](https://stackoverflow.com/questions/14383503/on-duplicate-key-update-same-as-insert) — Sakura Kinomoto, Dec 05 '18 at 21:30

score 1 · Answer 1 · answered Dec 05 '18 at 21:42

WHERE clauses have the purpose of filtering existing rows from tables. But, INSERT works without reference to any existing row, so WHERE makes no sense. It causes a SQL error, as you have learned.

If you hope to INSERT rows based on rows in other tables, you can do that with a variant of INSERT that looks something like this:

 INSERT INTO tableX  
        (attractionID, customerID, number_of_people, date_of_attraction, ownerId)
 SELECT 'X', 'Y', '2', '2020-12-01', userId
   FROM users WHERE UserName = '$UserName';

This query generates a row for tableX based on some constants from your example, and on a column in another table.

MySQL query using INSERT into and WHERE, possible?

1 Answers1