Aaaaagh SQL!!!

Anon

Can anyone help,

I am trying to run this query:

$query = \"SELECT $table_basket.PROD_ID, PROD_CODE, PROD_DESC, PROD_PRICE, QUANTITY
FROM $table_basket, $table_products, $table_users
WHERE $table_basket.PROD_ID = $table_products.PROD_ID
AND $table_basket.CUST_ID = $table_users.CUST_ID
AND $table_users.USERNAME = \\"$User\\"\";

but keep getting this error:

Error: Column: \'PROD_CODE\' in field list is ambiguous

Does anyone know what this means?

Cheers

Foggy

Anon

Foggy,

It means that it doesn't understand which table you're trying to get the PROD_CODE column in the select statement! There must be more than one table with a PROD_CODE column in the db?

Just write table_name.PROD_CODE like you have for $table_basket.PROD_ID

Regards,

Clarissa

fleymingmasc

When you are doing a join between tables it's always safest to be explicit about which table each field comes from. One very useful shorthand that makes this easier is table aliasing. For example:

SELECT tb.PROD_ID, tp.PROD_CODE, tp.PROD_DESC, tp.PROD_PRICE, tb.QUANTITY
FROM $table_basket tb, $table_products tp, $table_users tu
WHERE tb.PROD_ID = tp.PROD_ID
AND tb.CUST_ID = tu.CUST_ID
AND tu.USERNAME = \"$User\";

As you can see, we only need to define the correlation between a table and its alias once (in the FROM clause) and then we can refer to the alias anytime we specify a table anywhere else in the SQL statement.