Page 1 of 1
problem with query (order clause is ambiguous)
Posted: Mon Mar 16, 2009 3:44 pm
by ziepe
Hello all,
I recently dived into PHP coding so my question may seem a bit 'simple',
so be gentle with the n00b here plz.
Anyway, my project is up and running and works like a jiffy, the only thing that
remains is to sort the query results from the mysql_query.
My query looks as follows:
$data = mysql_query("SELECT * FROM $dbtable JOIN genera_table ON $dbtable.genus = genera_table.genera_id") or die(mysql_error());
Which works fine, but if I want to sort my results by adding 'ORDER BY genus ASC' I get an error saying:
"Column 'genus' in order clause is ambiguous".
Anyone willing to help me out ?
Thanks in Advance,
Sven
Re: problem with query (order clause is ambiguous)
Posted: Mon Mar 16, 2009 4:47 pm
by socket1
I'm guessing by the meaning of "ambiguous" that MySQL doesn't want to sort the data formed by the two joined tables.
Are you sure the two tables $dbtable and genera_table have the column genus ?
Also did you just try ORDER BY genus ?
Re: problem with query (order clause is ambiguous)
Posted: Mon Mar 16, 2009 5:04 pm
by ziepe
socket1 wrote:I'm guessing by the meaning of "ambiguous" that MySQL doesn't want to sort the data formed by the two joined tables.
Are you sure the two tables $dbtable and genera_table have the column genus ?
Also did you just try ORDER BY genus ?
The variable $dbtable has the tablename 'species_table' inside it which I use as the 'main' table for all my querries. Inside 'species_table' there is a column called 'genus'. Data inside that column refer to another column called 'genera_id' inside table genera_table. (It's a number, referring to a name. To convert the number to the name I use the column 'genus' in 'genera_table').
Adding just ORDER BY also results in: 'Column 'genus' in order clause is ambiguous'.
Sven
Re: problem with query (order clause is ambiguous)
Posted: Mon Mar 16, 2009 5:10 pm
by ziepe
I'll post some of the code I wrote, maybe that can help.
After the connectionstrings I add the following:
Code: Select all
<?php
if (isset($_GET['genusSelect'])) $linkchoice=$_GET['genusSelect'];
else $linkchoice='';
switch($linkchoice){
default : $data = mysql_query("SELECT * FROM $dbtable JOIN genera_table ON $dbtable.genus = genera_table.genera_id") or die(mysql_error());
...
Everything works fine... until I add 'ORDER BY'.
Thanks,
Sven
Re: problem with query (order clause is ambiguous)
Posted: Mon Mar 16, 2009 5:20 pm
by socket1
I can't figure it out, sorry.
I'll probably get banned for posting to another site but take a look at this..... I can't spot the difference between your code and the resolved part of this post but have a look.
http://www.phpbuilder.com/board/showthr ... p=10721117
Re: problem with query (order clause is ambiguous)
Posted: Mon Mar 16, 2009 5:26 pm
by Benjamin
The reason for the error is due to the fact that you have a field with the same name in both tables. You need to specify which table to use by prefixing the field with either the table name or an alias.