SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 27
  1. #1
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Display fields from different tables.

    CREATE TABLE IF NOT EXISTS `products` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `product_name` varchar(225) NOT NULL,
    `price` varchar(16) NOT NULL,
    `details` text NOT NULL,
    `category` varchar(255) NOT NULL,
    `subcategory` varchar(225) NOT NULL,
    `date_added` date NOT NULL,
    `location` varchar(255) NOT NULL,
    `category_id` int(11) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `product_name` (`product_name`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;




    Table structure for table `categories`
    --

    CREATE TABLE IF NOT EXISTS `categories` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `category` varchar(25) CHARACTER SET utf8 NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=17 ;
    As you can see there are two tables categories and products, I am looking for a query to display the category from the categories table not the products table. So far I have the following but it will display the category field of the products table. How can I point in the query that I want to display the category field of categories table?

    Even If I call it different names one category and another p_category I still confused how can I structure the query to display in the code.

    PHP Code:
    "SELECT * FROM products, categories ORDER BY date_added DESC LIMIT 6" 

  2. #2
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This query made it

    SELECT * FROM products INNER JOIN categories ON category_id = categories.id

  3. #3
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I thought I had it but not, Now it is displaying a error when I specify the fields in the SELECT clause

    SELECT products.id, categories.id, category_id, name, product_name, price, subcategory, location, date_added FROM products INNER JOIN categories ON category_id = categories.id"

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"' at line 1

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    first of all, the query you posted does not have " anywhere in it, although it does have a dangling " right at the end

    secondly, it is important that in any query with more than one table, you should properly qualify each column with its table name, just like you did for products.id, categories.id

    also, it is important to use column aliases, so that when the result set comes into php, you can distinguish columns which have identical column names from each other using their aliases
    Code:
    SELECT products.id     AS products_id
         , products.category_id
         , categories.name AS category_name
         , products.product_name
         , products.price
         , products.subcategory
         , products.location
         , products.date_added 
      FROM products 
    INNER 
      JOIN categories 
        ON categories.id = products.category_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you r937,

    Yes, Something I was looking were columns aliases. So far before you have posted all I found were table aliases but I was not sure how to apply in the columns even though I knew it was possible from pass experiences. I have that query it works pretty good the only thing is that it is returning all the rows in categories even though there is only one product name ON that condition.

    For instance. If there is one product named "good plate" with one category_name assign on the row of the join, it supposed to return that category_name only but instead it will return all the category_name found on the categories table, repeating the product_name as it iterate all the category_name colums.

    PHP Code:
    <?php
    $dynamicList 
    "";
    $sqlmysql_query("SELECT products.id AS products_id
                                                     , categories.category_id
                             , categories.name AS category_name
                             , categories.id AS category_id
                             , product_name
                             , products.price
                             , products.subcategory
                             , products.location
                             , products.date_added 
                             FROM products 
                             INNER JOIN categories 
                             ON categories_id = category_id ORDER BY date_added DESC LIMIT 6"
    );
    $productCount mysql_num_rows($sql); 
    if (
    $productCount>)
         {
             while(
    $row mysql_fetch_array($sql))
                      {
                        
    $category_id$row["category_id"]; 
                        
    $name$row["category_name"]; 
                        
    $id$row["products_id"];
                        
    $product_name$row["product_name"];
                        
    $price $row["price"];
                        
    $subcategory $row["subcategory"];
                        
    $location $row["location"];
                        
    $date_added strftime("%b %d, %Y"strtotime($row["date_added"]));
                       
    $dynamicList .= '
        
        <div class="dynamicList">
        
        <div class="alone">
        <div class="dynamicListF"><a href="product.php?id=' 
    $id '"><strong>' $product_name '</strong></a></div>
        <a href="product.php?id=' 
    $id '">
        <img style="border:#666 1px solid;" src="inventory_images/' 
    $id '.jpg" alt="' $product_name '                   "align="left" width="178" height="230" border="1" /></a><br />
         
    <div class="Divinfo">
         <div >
           <p >Price:</p>
           <p >  $' 
    $price '</p> 
         </div>

    <div class="clear"></div>
         <div >
           <p >Category:</p>
           <p> ' 
    $name '</p> 
         </div>
        

         
         <div>
         <div class="clear"></div>
            <p >Sub:</p>
            <p> ' 
    $subcategory'</p> 
         </div>
        
           <div class="clear"></div>
          <div class="city">
            <p> ' 
    $location'</p>  
          </div>
         
         </div>
      </div>  


    </div>
     

    </div>
    '
    ;
    }} else {
    $dynamicList "We have no products listed in our store yet";
    }
    mysql_close();
    ?>

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    the php does me no good, sorry, i don't do php

    the problem is in your sql anyway

    remember i said you should be using properly qualified columns? and yet you coded this --
    Code:
    ON categories_id = category_id
    there's your problem
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In other words it is returning all the rows in column category_name and only on that column after that everything is returning normal.

  8. #8
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for that typo, still the problems persist I think it is in the while loop,

    PHP Code:
    <?php 
    $idc 
    $_GET['id'];
    "SELECT products.id AS products_id
                             ,products.category_id
                             ,categories.name AS categories_name
                             ,product_name
                             ,products.price
                             ,products.subcategory
                             ,products.location
                             ,products.date_added 
                             FROM products 
                             INNER JOIN categories 
                             ON "
    .$idc." = category_id"

    ?>
    This is another file containing the same query and there is where the problems is. The ".$idc. " variable contains the same value as categories.category_id Still the problems persist on this one.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i have a suggestion which i think will help you a great deal

    first, copy the SELECT statement out of your php code into a text editor

    choose some id value, which you will use instead of
    Code:
    $idc = $_GET['id'];
    and then substitute that value into the SELECT statement

    then run the query directly in mysql, through the mysql workbench or whatever frontend utility you have

    then please do share what happens, and i will help you understand it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thank you r937 for the advice.

    i did run it with the value of 1

    Code:
    SELECT products.id AS products_id, products.category_id, categories.name AS categories_name, product_name, products.price, products.subcategory, products.location, products.date_added
    FROM products
    INNER JOIN categories ON 1 = category_id
    LIMIT 0 , 30
    That's the query result or the numbers of rows it brought back.

    Now it seems to me that it is the SQL the problem and not the php according to what I want to achieve, doesn't mean the query we have now is wrong, it just that it is not displaying the results I want. The result set goes as fallows it repeated the products_id 1 by the total numbers of categories_name when I really looking to display how many categories_name rows there are that matches that critiria then after finding that then display the categories_name rows that match that together with the other values in the products table that I choose in the SELECT table.

  11. #11
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you need the dump or what we are discussing so far is ok?

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    here's your problem --
    Code:
    FROM products
    INNER JOIN categories ON 1 = category_id
    the ON condition is not appropriate for what you want

    your ON condition is true for only one category, but this category is then matched with every single product

    let me know if you don't understand why that's wrong
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I thought about that, before and I thought about putting WHERE condition. I will research about that...

    I want to be on for one category and then display that row based on the category it matched. Now it stills display more than one category_name.

    It seems the ON clause is not specific, Please explain I don't understand.

  14. #14
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I changed it to WHERE clause but it won't run on the Run SQL query Database script. It has an error in your SQL syntas Error number #1064.

    SELECT products.id AS products_id
    ,categories.id AS categori_id
    ,products.category_id
    ,categories.name AS categories_name
    ,product_name
    ,products.price
    ,products.subcategory
    ,products.location
    ,products.date_added
    FROM products,categories
    WHERE categories.id = category_id ORDER BY `products`.`location` ASC

    I used the WHERE clause instead and it worked really good. It only brought back the categori_id and Category_id matches....


    URRA!

    Now I haven't test it in the code don't know how its going to work to what we have discussed so far. I have a question why ON was returning all the rows?

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by co.ador View Post
    I have a question why ON was returning all the rows?
    because it was incorrect

    here's what you have --
    Code:
      FROM products
         , categories 
     WHERE categories.id = products.category_id
    here's the equivalent explicit JOIN --
    Code:
      FROM products
    INNER
      JOIN categories 
        ON categories.id = products.category_id 
    compare to what you tried --
    Code:
      FROM products
    INNER
      JOIN categories 
        ON 1 = products.category_id 
    see the difference?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I see it

    FROM products INNER JOIN categories


    Here,

    SELECT * FROM products, categories

    WHERE categories.id = products.category_id


    Select everything or the fields specify where 1=1 or this field match = this one.

    Definitely there is a different statement. They speak different things.

  17. #17
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For another page I was using the same query but it was not working because it needed to conditions so I added an AND as below.

    "SELECT products.id AS products_id
    , products.category_id AS category_id
    , categories.name AS category_name
    , categories.id
    , products.product_name
    , products.price
    , products.subcategory
    , products.location
    , products.date_added
    FROM products, categories
    WHERE category_id = categories.id AND category_id = ".$idc.""

    Before I only had WHERE category_id = ".$idc." Which was returning all the category_name even the ones I didn't want so I put to display only if those two conditions are met.

    I am glad I have started to learn to speak this language..

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by co.ador View Post
    I am glad I have started to learn to speak this language..
    me too

    don't forget -- use explicit JOIN syntax with ON conditions (not the comma-list style with WHERE conditions for the join), and also please remember to qualify your columns properly (products.category_id = categories.id, not category_id = categories.id)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok I will take those in mind specially qualifying the columns, which some how got confusing when I Alias categories.id AS something else and the query was saying there was not column as such then I had to take the Alias and it worked. Just for that column after the = sign.

  20. #20
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT products.id AS products_id
    , products.category_id AS category_id
    , categories.name AS category_name
    , categories.id AS categori_id
    , products.product_name
    , products.price
    , products.subcategory
    , products.location
    , products.date_added
    FROM products, categories
    WHERE category_id = categori_id


    if you notice I Alias categories.id AS categhori_id and put after the = sign that one query right there returned me an error saying there was not column call categori_id. thought if it happens to you before. In that case I took the alias out and left it as categories.id and it worked. It was funny because it ended resulting like that only for that column.

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    please allow me to make some suggestions

    1. always use explicit JOIN syntax, not the old style comma-list join with the join conditions in the WHERE clause

    2. always properly qualify all columns with their table names when there is more than one table in the query

    3. use column aliases in the SELECT clause, so that when the result set comes into php, you can distinguish columns which have identical column names from each other using their aliases

    4. if you write the query in a way which guarantees that two columns are always going to be equal, you do not really need to return both columns in the SELECT clause

    Code:
    SELECT products.id           AS products_id
         , products.category_id 
         , categories.name       AS category_name
         , products.product_name
         , products.price
         , products.subcategory
         , products.location
         , products.date_added 
      FROM products
    INNER
      JOIN categories 
        ON categories.id = products.category_id
    if you take a close look, you will see that his query is identical to the one i gave you in post #4

    please don't change anything back again
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The first point I don't understand you mean that to use
    this approach with JOIN
    FROM products
    INNER
    JOIN categories
    ON categories.id = products.category_id
    Instead of joining as below.

    FROM products, categories
    WHERE category_id = categories.id AND category_id = ".$idc."
    I don't know if it is my version of Mysql server or what but The INNER JOIN on this particular situation didn't work.

    Don't know why.

  23. #23
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    sorry, "didn't work" is not a mysql error message that i'm familiar with

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  24. #24
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    WEll, I know you were giving me a suggestion,

    Really don't know what you mean, but we have discuss why it didn't work because INNER JOIN would return all the rows as well. When WHERE is more specific. That's what I understood.

    Sorry if I miss understood you,

    I do think you are very knowledgable,

    Thank r937.

  25. #25
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    this --

    FROM products INNER JOIN categories
    ON categories.id = products.category_id

    is equivalent to this --

    FROM products, categories
    WHERE category_id = categories.id

    if you add this to the comma-style join query --

    AND category_id = ".$idc."

    then you must obviously also add it to the JOIN syntax query !!

    so this --

    FROM products, categories
    WHERE category_id = categories.id
    AND category_id = ".$idc."

    is equivalent to this --

    FROM products INNER JOIN categories
    ON categories.id = products.category_id
    WHERE category_id = ".$idc."

    doesn't that make more sense now?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •