Do can do this in one query:
INSERT INTO students [student_id, first_name, last_name, teacher_id]
SELECT $student_id, t.id, t.first_name, t.last_name
FROM teachers t
WHERE first_name = '$teacher_first_name' AND last_name = '$teacher_last_name';
First note: Don't use variables stuffed into the string. Learn how to use parameters in your query. You are using mysqli_, so that is the right approach.
Second note: You should be including the student's name as well. You can do that using an update.
Third note: Don't repeat the teachers' names in the students table. A key reason for normalizing the database and having separate entities is to avoid storing the same
data in multiple places. So, your query should look like:
INSERT INTO students [student_id, teacher_id]
SELECT ?, t.id
FROM teachers t
WHERE first_name = ? AND last_name = ?;
Then you can fetch the teacher's names using a JOIN.
To get username using ID from two tables, you need to use JOIN and join the tables.
Let us create a table −
Example
mysql> create table demo77 -> [ -> userid int not null primary key, -> username varchar[20] -> ]; Query OK, 0 rows affected [2.63
Insert some records into the table with the help of insert command −
Example
mysql> insert into demo77 values[1,'John']; Query OK, 1 row affected [0.19 mysql> insert into demo77 values[2,'Bob']; Query OK, 1 row affected [0.36
Display records from the table using select statement −
Example
mysql> select *from demo77;
This will produce the following output −
Output
+--------+----------+
| userid | username |+--------+----------+
| 1 | John || 2 | Bob |
| 3 | Mike |+--------+----------+
Following is the query to create second table −
Example
mysql> create table demo78 -> [ -> id int not null primary key, -> countryname varchar[20], -> constraint fk_id foreign key[id] references demo77[userid] -> ]; Query OK, 0 rows affected [0.75
Insert some records into the second table with the help of insert command −
Example
mysql> insert into demo78 values[1,'US']; Query OK, 1 row affected [0.14 mysql> insert into demo78 values[2,'AUS']; Query OK, 1 row affected [0.15
Display records from the second table using select statement −
Example
mysql> select *from demo78 -> ;
This will produce the following output −
Output
+----+-------------+
| id | countryname |+----+-------------+
| 1 | US || 2 | AUS |
+----+-------------+2 rows in set [0.00 sec]
Following is the query to get username by using id by joining both the tables −
Example
mysql> select username from demo77 -> join demo78 -> on demo77.userid=demo78.id;
This will produce the following output −
Output
+----------+
| username |+----------+
| John || Bob |
+----------+2 rows in set [0.05 sec]
Updated on 11-Dec-2020 05:33:37
- Related Questions & Answers
- How to copy a table from one MySQL database to another?
- Extract the user ID from the username only in MySQL?
- How to get the id after INSERT into MySQL database using Python?
- How to get all table names from a database using JDBC?
- Select some data from a database table and insert into another table in the same database with MySQL
- How to Reset MySQL AutoIncrement using a MAX value from another table?
- How to retrieve table names from a database in MySQL?
- How to copy rows from one table to another in MySQL?
- Get the last record from a table in MySQL database with Java?
- Delete more than one rows from a table using id in MySQL?
- MySQL query for INSERT INTO using values from another table?
- Move rows from one table to another in MySQL?
- Insert data from one table to another in MySQL?
- Updating a MySQL table with values from another table?
- How do I get the id after INSERT into MySQL database in Python?
so as text is saying I need to grab user id by name from one table and then select count from another table by that ID. is it possible?
users table
ID | User
online table
ID
asked Sep 29, 2014 at 22:47
1
How about something more like:
SELECT COUNT[users.id]
FROM users
INNER JOIN online
ON online.ID = users.ID
WHERE users.id = 'myuser';
Paul White♦
73.8k26 gold badges381 silver badges596 bronze badges
answered Sep 30, 2014 at 1:13
PhrancisPhrancis
1,3101 gold badge7 silver badges24 bronze badges
2
Here it is:
SELECT COUNT[*] FROM online WHERE ID = [SELECT ID FROM users WHERE name = 'myuser']
answered Sep 29, 2014 at 23:14
trle94trle94
611 gold badge1 silver badge3 bronze badges