[Resolved] Join expression not supported ??

[cheese]

Hi All,

This is using an ODBC to an access database.

I am reworking a page from someone's old database and have got a bit stuck. The client wants an extra field added to the display. My existing query works, but does not have a join.

Here is what I have done;

$row = odbc_exec($link,"SELECT Consignments.ReceiverCode, Consignments.ShipRef, 'Shipping References.DateofShipment', 'Shipping References.Remarks' FROM 'Shipping References' INNER JOIN Consignments ON 'Shipping References.TransportNumber' = Consignments.ShipRef WHERE (((Consignments.ReceiverCode)='$HTTP_COOKIE_VARS[user]') AND ((Consignments.ShipRef)>'$nowt')) ORDER BY 'Consignments.ShipRef' DESC");

Please could someone explain what I am doing wrong.

Thanks,

Neil.

[bastien]

remove all the single quotes

$sql = "SELECT Consignments.ReceiverCode, Consignments.ShipRef, Shipping References.DateofShipment,
Shipping References.Remarks FROM Shipping References INNER JOIN Consignments ON
Shipping References.TransportNumber = Consignments.ShipRef WHERE (((Consignments.ReceiverCode)='{$HTTP_COOKIE_VARS['user']}') 
AND ((Consignments.ShipRef)>'$nowt')) ORDER BY Consignments.ShipRef DESC"

echo $sql;// test echo the select statement to see if its intpreted correctly.

$row = odbc_exec($link,$sql); //easier to manage

[cheese]

Originally posted by bastien
remove all the single quotes

$sql = "SELECT Consignments.ReceiverCode, Consignments.ShipRef, Shipping References.DateofShipment,
Shipping References.Remarks FROM Shipping References INNER JOIN Consignments ON
Shipping References.TransportNumber = Consignments.ShipRef WHERE (((Consignments.ReceiverCode)='{$HTTP_COOKIE_VARS['user']}') 
AND ((Consignments.ShipRef)>'$nowt')) ORDER BY Consignments.ShipRef DESC"

echo $sql;// test echo the select statement to see if its intpreted correctly.

$row = odbc_exec($link,$sql); //easier to manage

[/B]

Thanks for that Bastien,

I had already tried that option and it gave the following;

SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Shipping References.DateofShipment'., SQL state 37000 in SQLExecDirect in C:\Apache Group\Apache2\htdocs\booksellerpastshipments.php on line 36

I am guessing that the error is something to do with the Shipping Reference.DateofShipment having a space in the Column Name. This bit is not mine and without rewriting the old database to get rid of the space, I am stuck with it.

Any other suggestions would be appreciated.

Neil.

[bastien]

use backticks instead of quotes

$sql = "SELECT `Consignments`.`ReceiverCode`, `Consignments`.`ShipRef`, `Shipping References`.`DateofShipment`,
`Shipping References`.`Remarks` FROM `Shipping References` INNER JOIN `Consignments` ON
`Shipping References`.`TransportNumber` = `Consignments`.`ShipRef` WHERE `Consignments`.`ReceiverCode`='{$HTTP_COOKIE_VARS['user']}'
AND `Consignments.ShipRef`>'$nowt' ORDER BY `Consignments`.`ShipRef` DESC"

echo $sql;// test echo the select statement to see if its intpreted correctly.

$row = odbc_exec($link,$sql); //easier to manage

also:

bad idea to have space in column or table names...use underscore if your require spaces

got rid of all those brackets

consider using table alias names to reduce the amount you need to type next time

[Roger_Ramjet]

If you are talking to an Access db then why not use the inbuilt query designer to generate the SQL, that is what I do. It's drag and drop to pick the fields and then just copy-paste the generated SQL into your code. Saves an awful lot of typing. Of course, you will get all those brackets but I've not found them much of a problem.

[cheese]

Bastien,

Thanks for the help. I agree with the comment about the spaces in names..... I never do it, but this was done by someone else and I just have to live with it. I never even use underscores, I simply capitalise the first letter of each word.

Had to make a small change to get it to run, but now works well.

Tried aliasing. Worked well until my son couldn't understand what I was doing when he compared it back. He complained, I went back to not doing it. Must admit not doing it, I find that it is easier to reference back.

Roger RamJet,

Thanks to you also. I do exactly as you suggest. I find that sometimes a small amout of reworking is required, but the generally work well...... This one didn't !!

Just needed the gem about using ` around row titles.

Neil.