#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    5
    Rep Power
    0

    Pulling correct data for item


    I have a list of items, and each item has its own list of details.

    What I am trying to do is pull an item, and then display some details about the item. I have gotten the items to display as I want them, however I am only getting one detail about the item, and any other details will repeat the item again. I want to make it so that all details for the same item are in the same area and do not cause the item to repeat. My code is similar to:

    Code:
    <cfoutput query="qDetails">
    <h1><a href="">#qDetails.item_name#</a></h1>
    <li>#qDetails.detail#</li>
    </cfoutput>
    Would this be something better to do with cfloop?

    For the final product I would like to pull up to six random items, add a voting system to the details, and have the top 4 voted details be displayed with the item. Any help on any of this would also be appreciated.
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,264
    Rep Power
    968
    If you put a <cfdump var="#qDetails#"> on the page, does the query actually contain the data you think it does?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    5
    Rep Power
    0
    All of the information is there, but it is doing the same thing as the page. Any new details about the same item is creating a duplicate of the item. This is my cfquery:

    Code:
    <cfquery datasource="ITEMS" name="qDetails">
    	SELECT item_id, item_name, detail
    	FROM details
    </cfquery>
    Is there a way to give the h1 a value that can be picked up by another cfquery? Then I could use a query of queries to display the information based on what the h1 is.
    Last edited by dyoung29; October 4th, 2013 at 08:58 AM. Reason: added question
  6. #4
  7. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,264
    Rep Power
    968
    If the query is showing the same duplicate detail values, then something is wrong with the query or the actual data in the database.
  8. #5
  9. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,264
    Rep Power
    968
    Wait, are you saying you have results that have the same item_id and item_name, but different details? If item_id is a primary key, this is impossible.

    So it really looks like a database design issue. You shouldn't have multiple database rows where everything is the same except one column. That should be normalized so that one item points to an item details table containing the different details for that item.

    It would also help to see an example of the query result, and to explain what you want to see on the page given that data.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    5
    Rep Power
    0
    Currently I have two tables on the database, one table has all of the items, the other table has all of the details for all of the items. On the details table the primary key is a detail_id. If I read you correctly you are saying it would be better to have each individual item have its own table of details on the database? I have considered doing this but wasn't sure how that would effect the items on their individual page.

    I have changed the code a bit here is what it is now:
    Code:
    <cfquery datasource="ITEMS" name="qDetails">
      SELECT item_id, item_name, detail
      FROM details
      ORDER BY RAND()
    </cfquery>
    ...
    <cfoutput query="qDetails" maxRows="4">
      <h1><a href="">#qDetails.item_name#</a></h1>
      <li>#qDetails.detail#</li>
    </cfoutput>
    This is the type of result I am currently getting:
    Item 1
    Detail 1 of item 1
    Item 2
    Detail 1 of item 2
    Item 1
    Detail 2 of item 1

    What I would like to be get is:
    Item 1
    Detail 1 of item 1
    Detail 2 of item 1

    Item 2
    Detail 1 of item 2
  12. #7
  13. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,264
    Rep Power
    968
    Yes, this isn't a good schema design. Each row in the detail table is repeating the item name, for example. Which means if you change the name in the item table, you also have to go change all the related names in the detail table.

    But to get what you want here, you can just order the query by item_id or item_name (if the names are the same for rows with the same item_id), and then use the group attribute of cfoutput to group the results.

    Code:
    <cfquery datasource="ITEMS" name="qDetails">
      SELECT item_id, item_name, detail
      FROM details
      ORDER BY item_id
    </cfquery>
    
    <cfoutput query="qDetails" group="item_id">
      <h1><a href="">#qDetails.item_name#</a></h1>
      <cfoutput>
        <li>#qDetails.detail#</li>
      </cfoutput>
    </cfoutput>
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    5
    Rep Power
    0
    That didn't work for me either, it was still duplicating the item but they were at least near each other. I think if I would have moved or added another query it would have worked. However, I decided to just go back and make a table for each item, this might actually save me some trouble for when I add a rating system.

    I am having a bit of an issue creating a table though, not sure why. I am trying to create a table titled "Movie" and I get this error:

    An error occurred while trying to add the new table 'Movie'.
    MySQL said: Table 'ITEMS.Movie' doesn't exist
    Obviously the table "Movie" doesn't exist on my database, I am trying to create it. Any ideas on what to do here?
  16. #9
  17. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,264
    Rep Power
    968
    If you don't know how to create a database table, you really should stop right now and go read the MySQL documentation. http://dev.mysql.com/doc/refman/5.1/...ate-table.html.
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    5
    Rep Power
    0
    I know how to create the tables, but for some reason that specific table was throwing that error. I eventually gave up on it and came back to it the next day and was able to create it without any issue.

IMN logo majestic logo threadwatch logo seochat tools logo