SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    114
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help write a MySQL SELECT statement

    Help me write a MySQL SELECT statement to grab something I wish to get into a dynamic page.

    This is a simple dynamic web page list of parts (about 20) with categories (about 3). It works as you would expect. When the list is formatted as a web page, there is a simple test if the item from the parts list is under a new category. If yes, then print the category title before continuing to print the list.

    This works well so far. However I'm missing a functionality I wish to have. I wish my list to print the category name even if there are no parts using under the category.

    I've used in_array() function to check if a category is grabbed with my SELECT statement when there are no members; it is not. I thought to reverse the order of the SELECT statement (below), but made no difference.

    Overview=
    Two MySQL tables=
    list_items (id, item, part_number, doc_url, cat_id)
    categories (id, cat_name, cat_desc)
    list_items.cat_id relates to categories.id

    In my PHP script is this query=
    $myResult = mysql_query('SELECT list_items.*, categories.* FROM list_items, categories WHERE list_items.cat_id=categories.id ORDER BY list_items.cat_id', $connectID) or die ("Unable to select from database");

    As I said, i reversed the order in this SELECT statement, but it made no difference.
    Dinopad R40 - F15/XP
    APACHE 2
    MYSQL 5
    PHP 5

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by xtiansimon View Post
    SELECT list_items.*, categories.* FROM list_items, categories WHERE list_items.cat_id=categories.id ORDER BY list_items.cat_id
    this is an inner join, using implicit join syntax (tables listed in the FROM clause, join conditions specified in the WHERE clause)

    you need to change it to an outer join, using explicit JOIN syntax
    Code:
    'SELECT list_items.*
          , categories.* 
      FROM list_items
    LEFT OUTER
      JOIN categories 
        ON categories.id = list_items.cat_id
    ORDER 
        BY categories.id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    114
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That worked to order the list but there is a serious disconnect with the PHP output. The ID values of the list items is supposed to be mapped to each item. Instead the category IDs are getting mapped to the items in the list.

    I add the contents of the SELECT to the mysql_fetch_array() function and use a while() loop to write the TDs of the table. The fetch array is held in a variable I'll call $row. When I use $row['id'] to grab the ID of the current item from the list_items table and assign this to the listed xhtml item, instead I'm getting the category ID! I tried to use MySQL AS statement to remap the categories.id to ref_cat_id like this:

    Code:
    SELECT list_items.*, categories.*, categories.id AS ref_cat_id FROM categories LEFT JOIN list_items ON list_items.cat_id=categories.id ORDER BY list_items.cat_id
    Thinking that this would ensure the row array would not confuse the two different id fields. It didn't work fully.

    Xtian
    Dinopad R40 - F15/XP
    APACHE 2
    MYSQL 5
    PHP 5

  4. #4
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    114
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I fiddled with it and had time to look up the AS statements usage. But the real clincher was to look at the results at the MySQL command line. This showed me I was using the AS statement incorrectly. I made the change and i'm aces. Thanks r9! You got me going in the right direction. Chris
    Dinopad R40 - F15/XP
    APACHE 2
    MYSQL 5
    PHP 5


Tags for this Thread

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
  •