Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New to the CF scene
    Join Date
    Jul 2006
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can a query alias be used as a URL parameter?

    Question: Is is possible to use an alias created in a MySQL query as a URL parameter? (I'm not sure if this is a MySQL issue, a PHP issue, both, or neither). My database table uses self-referencing ids to establish hierarchy. In the temporary example below flow is my PK, and both parent and top are self-foreign. (Top acts as a sort of super-parent)

    db structure
    Code:
    table: category
    -------------------------------------------------------------------------------------
    flow		name		parent		top
    10000		Fun		0		10000
    10100		Fun-Places	10000		10000
    10110		Nantucket	10100		10000
    10111		Main-Street	10110		10000
    10120		Beijing		10100		10000
    10121		Mein-Street	10120		10000
    I have queries to select:

    This row; (this_name=Nantucket)
    The parent of this row; (parent_name=Fun-Places)
    The top (superparent) of this row; (top_name=Fun)
    The siblings of this row; (sibling_name=Nantcucket, Beijing)
    The children of this row. (children_name=Main-Street)

    Now, I want a page on Nantucket (id=10110) to look like this:
    index.php?top_name=Fun&parent_name=Fun-Places&this_name=Nantucket

    Why would I actually want it to look like that?
    Because, using Apache's mod_rewrite, I can display the URL as:
    /Fun/Fun-Places/Nantucket
    which is both search-engine- and user-friendly.

    So, while I can use aliasing to return the fields this_name parent_name and top_name:
    Code:
    SELECT 	this.flow AS this_id,
    	this.name AS this_name,
    	parent.flow AS parent_id,
    	parent.name AS parent_name,
    	top.flow AS top_id,
    	top.name AS top_name
    FROM 	category AS this,
    	category AS parent,
    	category AS top
    and I can use:
    index.php?top_id=10000&parent_id=10100&this_name=Nantucket

    I can't make the leap to using the aliased name in the URL.

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    welcome here!

    not sure i understand, but isn't it just
    PHP Code:
    $sql "SELECT     this.flow AS this_id,
        this.name AS this_name,
        parent.flow AS parent_id,
        parent.name AS parent_name,
        top.flow AS top_id,
        top.name AS top_name
    FROM     category AS this,
        category AS parent,
        category AS top"
    ;
    $result mysql_query($sql) or die('Queryproblem');
    while (
    $row=mysql_fetch_assoc($result){
       echo 
    '<br /><a href="index.php?top_id=',  $row['top_id'] , '&parent_id=',  $row['parent_id'] , '&this_name='$row['this_name'] , '">' $row['this_name'] , '</a>';

    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #3
    New to the CF scene
    Join Date
    Jul 2006
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Aliases cannot be used in WHERE clauses

    This is a floow-up to my posted question. Although, if anyone can help with whether the HAVING vs. WHERE clause is a vaild, I'd appreciate it.

    After some research I isolated the problem: aliases cannot be use in WHERE clauses.

    This is from MySQL Dev Zone: A.5.4. Problems with Column Aliases (http://dev.mysql.com/doc/refman/4.1/...h-alias.html):

    Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This restriction is imposed because when the WHERE code is executed, the column value may not yet be determined.

    Apparently, although this example is listed as pertaining specifically to a GROUP BY clause, the problem is the same.

    Although I'm still researching the vaildity of the syntax, it appears that substituting HAVING for WHERE eliminates the problem, so whereas:
    Code:
    Code:
    WHERE top_name=%s AND parent_name=%s AND this_name=%s
    results in a #1054 syntax error and:
    Unknown column 'top_name' in 'where clause'
    Code:
    Code:
    HAVING top_name=%s AND parent_name=%s AND this_name=%s
    works exactly as intended.

  • #4
    New to the CF scene
    Join Date
    Jul 2006
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by raf
    welcome here!
    not sure i understand, but isn't it just
    I will try this a little later, but in the meantime I've posted a follow-up that appears to have solved my problem/question.
    Thanks for the response and the welcome!


  •  

    Posting Permissions

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