php - MYSQL Database Design/SELECT Statement assistance

442

I'm hoping someone may be able to provide some advice regarding a database schema I have created and a SELECT statement that I am using to query the database.

I am attempting to create a database of very old newspaper articles from the 1800's, storing such things as the date, title and full text of the article, an image of the article, the name of the newspaper the article came from, names of locations mentioned in the article and individuals mentioned within the article.

Basically below is the current structure I've created with tbArticle being the main table focus ("test" is the name of the database). I've normalised the name of the newspaper, image info, location info and individuals into their own tables and because it is assumed there will be many articles to many individuals, I've added a link table (lktbArticleIndividuals) of sorts between tbArticle & tbIndividual;

enter image description here

The reason for creating the database is to obviously make a focused set of newspaper articles searchable and store them in a logical format.

My issue or question is this ...

All I want to do is display a list of all the articles in the database, obviously including data from the other tables other than tbArticle and to do this I am using this SELECT query;

SELECT *
  FROM tbArticle a
     , tbLocation l
     , tbNewspapers n
     , tbIndividual i
     , lktbArticleIndividuals ai
     , tbImage m
 WHERE a.idLocation = l.idLocation 
   and a.idNewspaper = n.idNewspaper 
   and a.idArticle = ai.idArticle 
   and ai.idIndividual = i.idIndividual 
   and a.idImage = m.idImage;

Which does what I want ... except ... if more than one individual is listed as being in an article, then two (or more) instances of the whole article are returned with the only difference being the different individual's names being displayed.

If possible, I want to just list each article ONCE, but iterate through the two or more individuals to include them. Can this be done?

If I were to query the database in say PHP I suspect what I might have to do is some sort of loop within a loop to achieve the results I want, but this doesn't seem very efficient to me!!

Does any of this make sense to anyone?!

190

Answer

Solution:

Instead ofSELECT *, you could name the columns you're interested in, and for things such as individuals, useGROUP_CONCAT() to add them all into one field, and at the end of your query, useGROUP BY a.idArticle to limit each article to one row per article.

967

Answer

Solution:

Assuming you just want the first_name of each individual you could use a group by with a GROUP_CONCAT.

SELECT *,
       GROUP_CONCAT(i.firstname)
  FROM tbArticle a
     , tbLocation l
     , tbNewspapers n
     , tbIndividual i
     , lktbArticleIndividuals ai
     , tbImage m
 WHERE a.idLocation = l.idLocation 
   and a.idNewspaper = n.idNewspaper 
   and a.idArticle = ai.idArticle 
   and ai.idIndividual = i.idIndividual 
   and a.idImage = m.idImage;
GROUP BY a.idArticle

However, if you want to get many details of each individual I would encourage you to do two separate queries: one for the articles and another one to get the individuals of each article.

People are also looking for solutions to the problem: echo in php javascript variable single and double quotes

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.