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

    Join Date
    Nov 2012
    Posts
    56
    Rep Power
    2

    Interpreting foreign key for details page


    Hi,

    I have two tables (items and categories) linked through a foreign key. I want the foreign key interpreted back to the actual category name so that when the user prints the items details page, the category name is displayed.

    Is it a INNER JOIN i should use in a case like this one? How can i joint the two tables to select the category name?

    Code:
    if ($stmt = $mysqli->prepare("
    
    SELECT 
    	id, 
    categoryid, 
    itemname,
    location 
    FROM items 
    WHERE id='$id'")) {
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by josephbupe
    Is it a INNER JOIN i should use in a case like this one?
    yes

    Code:
    SELECT categories.name AS category 
         , items.itemname
         , items.location 
      FROM items 
    INNER
      JOIN categories
        ON categories.id = items.categoryid
     WHERE items.id = $id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    56
    Rep Power
    2
    Originally Posted by r937
    yes

    Code:
    SELECT categories.name AS category 
         , items.itemname
         , items.location 
      FROM items 
    INNER
      JOIN categories
        ON categories.id = items.categoryid
     WHERE items.id = $id
    Hi,

    Thanx. I appreciate alot.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    56
    Rep Power
    2
    Hi,

    I am still having a problem with my query I mentioned earlier. My statement is as follows but still does not fetch the actual category name to print in the details page. Instead, what I am getting printed is the foreign key:

    Code:
    /* Create the prepared statement */
    	if ($stmt = $mysqli->prepare("SELECT categories.category AS category 
    										,collections.c_id								,collections.ctitle
    ,collections.csubject
    ,collections.creference
    ,collections.cyear
    ,collections.cmaterial
    ,collections.ctechnic
    ,collections.cwidth
    ,collections.cheight
    ,collections.clength
    ,collections.cdiameter
    ,collections.cperiod
    ,collections.cmarkings
    ,collections.cdescription
    ,collections.csource
    ,collections.cartist
    ,collections.cfilename
    										FROM collections									INNER JOIN categories
    ON categories.cat_id = collections.cat_id
    
    WHERE collections.c_id='$c_id'")) {	
    	
    /* Execute the prepared Statement */
    $stmt->execute();
    	
    /* Bind results to variables */
    $stmt->bind_result($c_id,$category,$ctitle,$csubject,$creference,$cyear,$cmaterial,$ctechnic,$cwidth,$cheight,$clength,$cdiameter,$cperiod,$cmarkings,$cdescription,$csource,$cartist,$cfilename);
    And the output like this:

    Code:
    <td>CATEGORY</td>
    	<td colspan="3" bgcolor="#eeeeee">
    			<?php  
    			echo $category;
    			?>
             </td>
    What am I missing?

    Joseph
    Last edited by josephbupe; March 26th, 2013 at 07:46 AM.
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by josephbupe
    What am I missing?
    FROM and INNER JOIN

    always test your query outside of php first

    Comments on this post

    • josephbupe agrees : Thank you. I have resolved it.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo