SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    Somewhere in the Middle East
    Posts
    109
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Trying to understand some PHP/MySQL code

    Hi, I received a long bit of code from a PHP programmer that I am trying to get my head around. I'd like to understand what, for example, SELECT m.url, sc.name, sc.title FROM menu m means. Why is it not just SELECT url, name, title FROM menu. What is .sc and m?

    Code:
    SELECT m.url, sc.name, sc.title
    FROM menu m
    INNER JOIN static_content sc
    ON m.name = sc.menu_name
    WHERE m.name = "' . $pageid . '"
    ORDER BY order_no ASC;
    Many, many thanks,

    Leao

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    those are table aliases

    the reason you use them is twofold, and both apply when the query contains more than one table

    1. if the tables share a column name, an alias is required when you use the column name, otherwise the database won't know which one you want and you'll get a syntax error

    2. identifying the table that a column belongs to even if the column name is unique among all the tables in the query is best practice because it (self-)documents the query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    rajug.replace('Raju Gautam'); bronze trophy Raju Gautam's Avatar
    Join Date
    Oct 2006
    Location
    Kathmandu, Nepal
    Posts
    4,013
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I see there is an inner join in the sql statement. So m or sc are just the aliases of respective tables to denote the table name. Instead of using those aliases you can use the table name itself too.
    Code mysql:
    SELECT menu.url, static_content.name, static_content.title
    FROM menu
    INNER JOIN static_content
    ON menu.name = static_content.menu_name
    WHERE menu.name = "' . $pageid . '"
    ORDER BY order_no ASC;
    Mistakes are proof that you are trying.....
    ------------------------------------------------------------------------
    PSD to HTML - SlicingArt.com | Personal Blog | ZCE - PHP 5

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i prefer table aliases to full table names, for the obvious reason that there is less "noise" and more "signal"

    also, one of my rules for good sql is to qualify ~every~ column used in a query that has more than one table

    here's a quick question: which table is the order_no column in?

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

  5. #5
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    Somewhere in the Middle East
    Posts
    109
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the great replies!!!!

    order_no is in column 4.

    Thanks again

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Leao View Post
    order_no is in column 4.
    huh?

    my point was, from looking at the query, you cannot tell whether order_no is in the menu table or the static_content table
    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
  •