I have found many examples and information around this but still cant get this to work. What I am trying to do is insert into a table, but query two other tables to get the foreign keys so I need to use a subquery.

I have 3 tables: user, hair and city. Hair contains different colours and city are different cities. The below example i am trying to use does not try and insert city (its been added manually for now but will need to be another subquery) and have tried to insert hair colour.

This is what I have so far:

INSERT INTO user (lid,random,email,phone,name,city,hair)
SELECT '','19293642446','someone@whereareyou.com','078953 33555','Someone Somewhere','london',hair.id
FROM hair
WHERE hid.colur = 'red'

There is no error just doesnt insert a new row.

sorry, there is so

where are you running this query from? php?

If your SELECT statement above returns a result, then you must have an error during the insert statement. If the SELECT statement fails (due to colur = 'red' not existing) then you will need to look at adding this in before attempting this insert.