CODEWITHSUNDEEP
mysqlsql-insert
Ginge3030
Ginge3030

Reputation: 27

MySQL query using INSERT into and WHERE, possible?

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.

Upvotes: 1

Views: 1744

Answers (1)

O. Jones
O. Jones

Reputation: 108766

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.

Upvotes: 1

Related Questions