CODEWITHSUNDEEP
phpmysql
Kevin White
Kevin White

Reputation: 51

MySql search among array elements in a field

I have the following table

 title               year        authors
 --------------------------------------------------------------------------
 title book 1        2015-12-01        White McGregor Waine
 title book 2        2016-10-14        McGregor Bush Rossi
 title book 3        2017-05-22        Bush McGregor Lopes
 ......              ....              .......

Authors field is composed of names separated by a white-space (I might also use a different separation character, if needed).

How do I extrapolate a desc list of authors who published more books? Consider I don't know the names of authors.

In the example the list is:

 Author                 Books published
 --------------------------------------- 
 McGregor                     3     
 Bush                         2
 Whaite                       1
 Whaine                       1
 Rossi                        1
 Lopes                        1

Upvotes: 1

Views: 64

Answers (3)

Abey
Abey

Reputation: 1428

The apt way to do this is to normalize.

This falls under many to many relationship.

For storing many-to-many relationships, an intermediate table that mainly stores the primary keys (IDs) of each relationship is required. In your case,

authors_table(author_id, name,...)

books_table(book_id, name,...)

authors_books_table(id, author_id, book_id)

Here is a more elaborate explanation.

This followed by a simple join, will get you the desired result.

Upvotes: 3

Lajos Arpad
Lajos Arpad

Reputation: 76574

As already pointed out by others, you will need to normalize your database. The advantages of normalization include but do not limit to the following:

  • you will have an easier time finding the data you are interested in, in your case you will be able to find the authors in a given table and the relations in an intermediary table instead of handling varchars using white space
  • your database will be consistent, that is, you will be able to easily handle CRUD operations with your authors, for example if you change the name of an author in the authors table, it will be changed for all places the author is referenced at. Also, you will be able to differentiate authors with the same name
  • you will not have redundant data stored, increasing the size of the data you have

You will therefore have the following tables:

authors(id, name)

author_of_book(id, author_id, book_id)

books(id, title, year)

Make sure the id fields are primary keys and author_id and book_id, respectively are foreign keys.

With this new structure you will be able to do the selection you want like this:

select authors.name, count(*) as `books published`
from authors
join author_of_books
on authors.id = author_of_books.author_id
join books
on author_of_books.book_id = books.id
group by authors.id, authors.name
order by count(*) desc

Upvotes: 1

TarangP
TarangP

Reputation: 2738

First You have to learn Normalization. Database normalization, or simply normalization, is the process of organizing the columns (attributes) and tables (relations) of a relational database to reduce data redundancy and improve data integrity. ... Informally, a relational database relation is often described as "normalized" if it meets third normal form.

You can Also Try With this 

<?php

$myarr = "White McGregor Waine";

$myarr = explode(" ",$myarr);

foreach($myarr as $value){
    $sql = "SEECT title FROM table where authors = '$value'";
    echo $sql;

}

Upvotes: 2

Related Questions