PHP Assign a specific Value from reference list to every occurence of a string

Question

I have two lists. The first list is a list of Book Authors where every name has his own ID
Example:

id - name
1 Mr Miagi
2 Mr Smith
3 Susan Sun
4 Mr Commodore

The Second list is a list of books with one or more writers per book
Example:

book - writer
King Solomons Mines - Mr Miagi
She - Mr Miagi
Paperboy - Mr Commodore and Susan Sun

So what I need in the resulting list is the ID of the writer (from the first list) at every occurrence
Example:

book - writer - id
King Solomons Mines - Mr Miagi - 1
She - Mr Miagi - 1
Paperboy - Mr Commodore and Susan Sun - 4 , 3

What is the best Approach in PHP to get this result?

It'd be helpful if you organized your list for readability but arrays are the key here.. — Devon Bessemer, Apr 13 '14 at 23:22
"Best Approach" is an impossible request, as there's too many possible answers. Furthermore, no one knows where you're getting this data. — Ohgodwhy, Apr 13 '14 at 23:22
Beyond arrays and comparisons in PHP, do you have this in a database? This would be a simple many to many relationship table. You maintain a table for books, authors, and then authors/books where the primary key is both the author id and the book id. — Devon Bessemer, Apr 13 '14 at 23:25
@Ohgodwhy Like I said. I have two lists here. One with Books and Authors and another with Author Names and their linked Author_ID. So why does it matter where I get this data from? — user1895954, Apr 13 '14 at 23:29
It makes every difference. Where are the lists stored? are they in a txt file? is this a hard coded array? database? json? xml? what are you expectnig to get back? an object? json string? xml? array? — Ohgodwhy, Apr 13 '14 at 23:37
@Devon Thank you for your feedback. That's another approach that I will look into but as it is a migration from list a to list b, I prefer a one time solution with PHP. Thanks anyway for your positive input! — user1895954, Apr 13 '14 at 23:41
@Ohgodwhy It's a migration from book-author model A to B so I have two database exports from two different tables. I need to combine the two and import it into the new database table. This new database table will only use the ID of the Book and the ID of the Author(s). — user1895954, Apr 13 '14 at 23:45
Are the names literally joined together like this: `Mr Commodore and Susan Sun - 4 , 3` where `and` is the join? — Ohgodwhy, Apr 13 '14 at 23:51
You have php arrays. I would use php function array_*. Do you have a performance problem or anything ? — Shire, Apr 14 '14 at 01:03
Do you really need this result: Paperboy - Mr Commodore and Susan Sun - 4 , 3 — Shire, Apr 14 '14 at 01:05
maybe this can get you to the right track [Link Here](http://stackoverflow.com/questions/13353231/how-write-down-to-database-multiple-authors-in-simple-books-table). After getting the right query (with the right table structure), then you manage the query result with PHP — user1978142, Apr 14 '14 at 02:28

score 0 · Accepted Answer · answered Apr 14 '14 at 13:53

I hope i did understand you correctly from reading your question and the comments.

What i think you want still is three database tables:

authors:
  id int(11) NOT NULL,
  name varchar(200) NOT NULL,
  PRIMARY KEY (`id`)

books: 
  id int(11) NOT NULL,
  title varchar(200) NOT NULL,
  PRIMARY KEY (`id`)

author2books: 
  id int(11) NOT NULL,
  author_id int(11) NOT NULL,
  book_id int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE (`author_id`, `book_id`)

Now iterate over your first list (author - id) and simply insert the results into the first authors table.

Then iterate over the second result (book, authors as string). In this iteration, check for each authors string. If it has more than one authors, use php_explode to seperate them.

I hope this helped.

PHP Assign a specific Value from reference list to every occurence of a string

1 Answers1