For example, there are 3 tables, USER , WORK , and the link user_work . Binding contains the id main tables. I now can not understand how to combine them in it? What should a query for inserting data into the main tables and in the spanner look like? If you can sample code with the implementation of php.

  • one
    @ Michael74, curious: one work can be assigned to several users? If so, create a user, work, then a connection. Those. the user of the program will have 3 different pages, for example. - Pleshkov Ivan
  • 2
    so the whole point is that the data is inserted into these tables at different times. When you want to add a user and work link, you already have both id's. Well, or if you edit, say, goes on the same page and add both user and work and user_work, then do it consistently. put in user, got id, put work in, got id, put both id in user_work. - Yura Ivanov

1 answer 1

Mysql

For definiteness, let the structure of the USER , WORK and user_work be defined by the following three tables.

 CREATE TABLE USER ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(256) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE WORK ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(256) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE user_work ( id int(11) NOT NULL AUTO_INCREMENT, user_id int(11) NOT NULL, work_id int(11) NOT NULL );

Usually, the USER and WORK tables are filled first, after each of the INSERT statements they get the identifiers of the newly inserted records. In MySQL, the LAST_INSERT_ID() function is used for this. If we perform a fill operation in SQL, then we will have to save them to variables using the SET operator.

 INSERT INTO USER VALUES(NULL, 'hello'); SET @user_id = LAST_INSERT_ID(); INSERT INTO WORK VALUES(NULL, 'world'); SET @work_id = LAST_INSERT_ID();

Having received foreign keys for the user @user_id and the type of work @work_id we can form a request to insert a record into the link table user_work

 INSERT INTO user_work VALUES(NULL, @user_id, @work_id);

Php

When filling out tables in PHP, we will focus on the extension of PDO

 <?php try { $pdo = new PDO( 'mysql:host=localhost;dbname=test', 'root', '', [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => true]); // Добавляем сообщение в таблицу USER $query = "INSERT INTO USER VALUES(NULL, :name)"; $user = $pdo->prepare($query); $user->execute(['name' => 'hello']); // Получаем только что сгенерированный идентификатор id $user_id = $pdo->lastInsertId(); // Добавляем сообщение в таблицу WORK $query = "INSERT INTO WORK VALUES(NULL, :name)"; $work = $pdo->prepare($query); $work->execute(['name' => 'world']); // Получаем только что сгенерированный идентификатор id $work_id = $pdo->lastInsertId(); // Вставляем содержимое новостного сообщения в таблицу user_work $query = "INSERT INTO user_work VALUES (NULL, :user_id, :work_id)"; $relate = $pdo->prepare($query); $relate->execute(['user_id' => $user_id, 'work_id' => $work_id]); } catch (PDOException $e) { echo "Ошибка выполнения запроса: " . $e->getMessage(); }