Implementing many-to-many communication?

Question

PHP

Good afternoon, please tell me, or share a manual on how to make such a request:
We have a book table with the schema "booksid, title, price" The
author's table "authorid, phone"
And the third table through which contains "booksid, authorid"
What should be the request, so that when displaying all books, for each book its author or authors were displayed.
Thank you very much for your help.

Answer 1

Hello.
It's simple. Look, you have a crosstab with bookid, authorid. Roughly speaking, on either side of this table you will have a connection to the table - books, and to the table - authors.
That is, the general form of the request:
Conditionally

SELECT * FROM books
INNER JOIN books_authors ON books.id = books_authors.booksid
INNER JOIN authors ON books_authors.authorid = authors.id

Answer 2

Read about JOIN, or in two queries (get a book, get authors for a book -> generate a table)

Answer 3

Just in case, I’ll write in more detail, because it can be difficult to immediately realize. For example, there are tables that you described (I slightly renamed it)

CREATE TABLE IF NOT EXISTS `authors` (
  `authorid` int(11) NOT NULL AUTO_INCREMENT,
  `phone` varchar(100) NOT NULL,
  PRIMARY KEY (`authorid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

INSERT INTO `authors` (`authorid`, `phone`) VALUES
(1, '123456789'),
(2, '987654321');

CREATE TABLE IF NOT EXISTS `books` (
  `booksid` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `price` float NOT NULL,
  PRIMARY KEY (`booksid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

INSERT INTO `books` (`booksid`, `title`, `price`) VALUES
(1, 'Название1', 10),
(2, 'Название2', 20);

CREATE TABLE IF NOT EXISTS `third` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `authorid` varchar(100) NOT NULL,
  `booksid` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

INSERT INTO `third` (`id`, `authorid`, `booksid`) VALUES
(1, '1', '2'),
(2, '2', '1');

In the case of such a query, you will get joined tables and you can already choose anything from them.
SELECT * FROM books
INNER JOIN third ON books.booksid = third.booksid
INNER JOIN authors ON third.authorid = authors.authorid
Surely the gurus can come up with something smarter. It seems to me that many-to-many is not very justified here. Correct, if so.

Answer 4

3 days of testing led to this. See below. The diagram is exactly the same as yours.
How to correctly link posts and categories (product and categories, if you like)?
The queries that the comrades in the topic suggested here will be very slow with a large number of books.
At some stage, it may turn out that it will be easier to make 2 requests for a post than to make such a fuss.

Answer 5

In 2 requests it will work a little faster and can be cached.