Can a query alias be used as a URL parameter?

[owlman]

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

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:

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.

[raf]

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>';
} 


[owlman]

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:

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:

HAVING top_name=%s AND parent_name=%s AND this_name=%s

works exactly as intended.

[owlman]

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!