SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot jrickards's Avatar
    Join Date
    May 2003
    Location
    Sudbury
    Posts
    131
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiple results for one field

    Hi:

    I have a database of news releases that is created at work on our intranet. These news releases are identified as to which area they apply to. Some news releases apply to multiple areas (there are only 6 possible areas, the most so far has been 3 for a particular news release). All of this is set up properly in the database, my problem is how to handle the extraction of the data most efficiently. When a record is displayed of a news release, I also want the multiple areas to be displayed as a comma separated list (easily done). There are two possible methods of running the SQL: I tried both, couldn't get one working so I did the other. The one that I got to work was to query and extract the news releases, then for each news release, query for area. This creates many queries. If the person doing the search through the search form and 10 news releases are found, there are 11 queries - one to find all news releases, and 1 for each news release to query for the areas. This I feel may be inefficient. The other method is to create a query which extracts both the news releases and the areas one query so if there are 3 areas, then the news release will be displayed 3 times, once for each area. I then thought that I could PHP loop through the result set and display the news release once and then the areas individually - I couldn't get this to work for some reason.

    Which is the better method?

  2. #2
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You need to give us some idea of the table structures involved.

  3. #3
    SitePoint Zealot jrickards's Avatar
    Join Date
    May 2003
    Location
    Sudbury
    Posts
    131
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Briefly:

    Table: newsreleases
    newsrelID
    Title
    Area

    Table: areas
    areaID
    AreaName

    Table: newsrelAreas
    newsrelID
    areaID


    The newsrelAreas is a junction table.

    If I run a search and get two newsreleases as results each of which have three areas, this is what I currently do

    SELECT Title FROM newsreleases WHERE Title Like "%gold%";

    Results:

    Code:
    Title                                       Areas
    Gold found at 3,000 ft                East, Northeast, Northwest
    INCO to open new gold mine        North, Northeast, Northwest
    If I create a SELECT where both Title and Areas will be displayed, then I will get the following results:

    Gold found at 3,000 ft, East
    Gold found at 3,000 ft, Northeast
    Gold found at 3,000 ft, Northwest
    INCO to open new gold mine, North
    INCO to open new gold mine, Northeast
    INCO to open new gold mine, Northwest

    which has the advantage of one SELECT but I couldn't get the code to loop through the same newsreleases and display "East, Northeast, Northwest".

    So I have two different types of SELECTS whereby I get

    Gold found at 3,000 ft (pretend this is newsrelID = 1)

    and then SELECT AreaName from Areas WHERE newsrelAreas.areaID = Areas.areaID and newsrelAreas.newsrelID = newsrel.newsrelID

    which gave me:

    East
    Northeast
    Northwest

    which I then gathered together with commas separating them and placed them in one <td></td>.

    The issue I am trying to resolve is: should I try to create a single SELECT and then fix the PHP to cycle through the areas or is my current working method fine where there is a single SELECT to gather all the news releases then a SELECT within each to gather the areas. Which is more efficient?

  4. #4
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    add to the end of your title, areas, query the line
    group by title

    (I think)

  5. #5
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Or collect this data

    Gold found at 3,000 ft, East
    Gold found at 3,000 ft, Northeast
    Gold found at 3,000 ft, Northwest
    INCO to open new gold mine, North
    INCO to open new gold mine, Northeast
    INCO to open new gold mine, Northwest

    and assign to an array as you go along.
    Then use php to work through the array and display the results as you want them. I'm sure this can be done.

  6. #6
    SitePoint Zealot jrickards's Avatar
    Join Date
    May 2003
    Location
    Sudbury
    Posts
    131
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dr John
    Or collect this data

    Gold found at 3,000 ft, East
    Gold found at 3,000 ft, Northeast
    Gold found at 3,000 ft, Northwest
    INCO to open new gold mine, North
    INCO to open new gold mine, Northeast
    INCO to open new gold mine, Northwest

    and assign to an array as you go along.
    Then use php to work through the array and display the results as you want them. I'm sure this can be done.
    Yes, I can do that (and did do that in one iteration of my testing) but for some reason, I couldn't get it to "if title = previous, grab only area" - it would display only the first title, then display all areas for all publications. However, likely that is a PHP issue not a SQL issue.

    What I am wondering though is which SQL method is better: single SELECT or main SELECT with area SELECTs within each. If it is decidedly better to use the first, then I have to figure out my PHP issue. If it makes no difference, then I will stick with what I have done.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •