...

View Full Version : Can a query alias be used as a URL parameter?



owlman
07-13-2006, 11:14 PM
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
07-14-2006, 12:22 AM
welcome here!

not sure i understand, but isn't it just


$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
07-14-2006, 03:27 PM
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://http://dev.mysql.com/doc/refman/4.1/en/problems-with-alias.html) (http://dev.mysql.com/doc/refman/4.1/en/problems-with-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
07-14-2006, 03:29 PM
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!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum