Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Feb 2012
    Posts
    14

    Unanswered: mysql database tables

    I am having trouble to link two tables together on my database. I have an items table and a category table, I need to show which items are in which category, some items are also in 2 categories. I have made a links table with the item_id and category_id from the other tables, but don't quite know where to go from there and displaying it on the webpage within a table.

    Please help

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well it would be nice to show your table design, rather than let us guess it. I appreciate that some contributors like a more 'sporting' challenge of trying to work out what your design is...
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2012
    Posts
    14
    tables:

    item
    item_id(pk)
    Item_name
    item_description
    item_price
    item_image

    category
    category_id(pk)
    category_name

    links
    item_id(fk)
    category_id(fk)

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what is it that you want to display?

    if there's only one webpage, do you want to show all items in all categories, or all categories with their items?

    or will there be a category page, and you want to list only the items in that category?

    or will there be an item page, and you want to list the categories that the item belongs to?

    despite enjoying a sporting challenge, i am not about to write a half dozen queries for you on the hope that one of them is what you want
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2012
    Posts
    14
    There is going to be 3 different categories with a page for each. On these pages i want the items within that category to be displayed. However there is one item that needs to be placed within two categories.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, let's say on the category page you will have a variable called $cat that contains the id of the category

    this is your query to return the category name as well as all its items --
    Code:
    SELECT category.category_name
         , item.item_name       
         , item.item_description
         , item.item_price      
         , item.item_image      
      FROM category    
    INNER
      JOIN links
        ON links.category_id = category.category_id
    INNER
      JOIN item
        ON item.item_id = links.item_id
     WHERE category.category_id = $cat
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2012
    Posts
    14
    Thank you. Would that then make it so each item belongs in the correct category? I can't get my head around how each category will know what items to hold

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    did you test the query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2012
    Posts
    14
    Its for an Admin section and what will happen is the admin can add, edit and delete items found within each category but we are having problems making it so each item is in the correct category, also one item can be found within two categories.

    Once the admin have then edited, added or deleted content the web page for the specific category that has been changed will be updated. (Its not for a real website etc its just so I am able to grasp MySQL and PHP, finding it very difficult).

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you said you created the tables

    the next thing to do is put some sample data in them

    then please test the query i gave you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2012
    Posts
    14
    This error appears

    Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

    I'm not sure if I am putting it in the correct place or if I need to add more code :-/ I'm very lost! The code is what I have below, the commented out sections are what we originally had as we where not sure how to put all items within their categories.

    session_start();

    "SELECT category.category_name
    , item.item_name
    , item.item_description
    , item.item_price
    , item.item_image
    FROM category
    INNER
    JOIN links
    ON links.category_id = category.category_id
    INNER
    JOIN item
    ON item.item_id = links.item_id
    WHERE category.category_id = $cat";

    //$sql = "SELECT * FROM `item` WHERE `item_id` = '1'";

    mysql_select_db("vhs_ecom") or die(mysql_error());



    //("SELECT * FROM item_price, CONCAT('', item_price) as amount FROM item");

    //$result = mysql_query("SELECT * from item WHERE item_id > 27");

    echo "<table border='1'>
    <tr>
    <th>Item Name</th>
    <th>Item Description</th>
    <th>Item Price</th>
    <th>Item Image</th>
    </tr>";
    while($row = mysql_fetch_array($result)){
    echo "<tr>
    <td>" .$row['item_name'] ."</td>
    <td>" .$row['item_description'] ."</td>
    <td>" .$row['item_price'] ."</td>

    </tr>";
    }
    echo "</table>";
    ?>

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, i can't help you with the php -- this is the mysql forum

    try testing your query directly in mysql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2012
    Posts
    14
    I didn't think of doing it there sorry. I have done it and it says;

    #1054 - Unknown column '$cat' in 'where clause'

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, let me explain the step you missed

    the query i gave you anticipated that there would be a variable to specify which category

    that was based on my question to you about where this query was to be run, under what circumstances, and you answered a category page, and obviously the only variable in that situation is which category it is that's about to be displayed on that page

    when you test the query in mysql, you cannot, of course, use a php variable name, you have to use an actual category id value

    the error message makes sense now, doesn't it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Feb 2012
    Posts
    14
    Jewellery has a category ID of 1. I have replaced $cat with $1 but it still says the same error. Its me being think Or should i put the category name?

Posting Permissions

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