problem with query (order clause is ambiguous)

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ziepe
Forum Newbie
Posts: 6
Joined: Mon Mar 16, 2009 3:32 pm

problem with query (order clause is ambiguous)

Post 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. :wink:

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
socket1
Forum Commoner
Posts: 82
Joined: Mon Dec 08, 2008 7:40 pm
Location: Shokan, New York

Re: problem with query (order clause is ambiguous)

Post 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 ?
ziepe
Forum Newbie
Posts: 6
Joined: Mon Mar 16, 2009 3:32 pm

Re: problem with query (order clause is ambiguous)

Post 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
ziepe
Forum Newbie
Posts: 6
Joined: Mon Mar 16, 2009 3:32 pm

Re: problem with query (order clause is ambiguous)

Post 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
socket1
Forum Commoner
Posts: 82
Joined: Mon Dec 08, 2008 7:40 pm
Location: Shokan, New York

Re: problem with query (order clause is ambiguous)

Post 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
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: problem with query (order clause is ambiguous)

Post 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.
Post Reply