(sql) input using select statement

Question

i wanna make a project to find a rented house / room user can post his room/house to rent and others can see it on the map

first user should login then he register his location in table location. table location consist id_location, lat, lng, id_user, etc. id_location is auto increment,its primary key. id_user is from session that user logged on, it reads that username, Its foreign key. rest are input by user, i've succeed to make till this point

and the next thing is user gonna pick house or room to rent example; they wanna rent a room. this room table has id_room, id_user,id_location, rate, etc. id_room is auto increment, primary key. id_user is from session that user logged on, so it reads the username. and the problem is for the id_location. how can the database had that id_location from the last location input / location tbl?

Because that a location can have more than 1 room and the type of the room can be different, like it rate, facility, or etc. so i make it several input for each room, then user can creates its own room data for each room but has a same id_location

i mean something like

insert into tbl_room
values (null, '$_SESSION[username]', '$rate','$bla' ,'...','...' ,'...' , )
 where id_location from location.id_location

but i know 'where' cant be in input scenario ..

i've read something about select statement but i dont know how that select works in my case, i mean the exact query will be

i hope u r understand about my problem thanks be4

If you'd want to `INSERT` things you might want to take a look at [`INSERT INTO`](https://dev.mysql.com/doc/refman/5.7/en/insert.html) instead of `SELECT`. — Tom Udding, Nov 25 '17 at 10:16
Possible duplicate of [Insert into ... values ( SELECT ... FROM ... )](https://stackoverflow.com/questions/25969/insert-into-values-select-from) — yacc, Nov 25 '17 at 10:20

score 0 · Answer 1 · answered Nov 25 '17 at 11:24

On this case you have a one to many relationship, where one location can have many rooms. The simple solution is to have a column on the rooms table that references the location id from the location the room belongs to.

You can make something like this on MySQL

Create table locations (
    Id int auto_increment,
    Location varchar(100)
)

Create table rooms (
    Id int auto_increment,
    Location_id int
) 

ALTER TABLE rooms ADD CONSTRAINT fk_location 
    FOREIGN KEY ( location_id` ) REFERENCES locations (id)

Insertions would be similar to this:

Insert into room (location_id) values ({$locationId});

And then you can get the room location by something like

Select l.location
From location l
    Inner join rooms r on r .id_location = l.id
Where r.id = {$roomId}

(sql) input using select statement

1 Answers1