Unknown column in where clause

dcjones

Hi all.

I have been trying to solve the issue for three day without a result.

I have a query which is displaying the error "Unknown column in where clause" but I cannot see an error.

$updateSQL = "UPDATE flight_client SET  ClientAirline=".$row_FlightData['CarrierCode'].", ClientFlightNo='".$row_FlightData['FlightNumber']."', ClientFlightSuffix=".$row_FlightData['FlightSuffix'].", ClientTerminalCode=".$row_FlightData['TerminalCode'].", ClientGateNumber='".$row_FlightData['GateNumber']."', ClientFlightStatus='".$row_FlightData['FlightStatus']."', ClientFlightId=".$row_FlightData['AODBUniqueID'].", ClientStime='".$row_FlightData['ScheduledDateTime']."', ClientEtime='".$row_FlightData['EstimatedDateTime']."', ClientDepartLoc=".$row_FlightData['IATALocationCode'].", ClientArriveLoc='".$row_FlightData['PublicLocationName']."', ClientZone='".$row_FlightData['zone']."' WHERE ClientFlightNoComplete =". $row_FlightData['FlightNoComplete'];

If I display the content of the query this is the error:

Array ( [id] => 190747 [AODBUniqueID] => 4545802 [FIDSFlightActive] => Y [ArrivalOrDeparture] => A [CarrierCode] => TCX [FlightNumber] => 121 [FlightSuffix] => L [FlightNoComplete] => TCX121 [FlightDate] => 2010-01-30 [ScheduledDateTime] => 2010-01-30T01:30:00+01:00 [EstimatedDateTime] => [IATALocationCode] => SSH [PublicLocationName] => Sharm El Sheikh [FlightStatus] => Baggage in [GateNumber] => [TerminalCode] => S [type] => [reg] => [run] => [stand] => 20M [MessageSentDateTime] => [zone] => [timeflag] => 2010-01-30 08:53:00 )

Unknown column 'TCX121' in 'where clause'

Can anyone see the error.

halojoy

'Unknown column'
often can tell you have missed to put quotes around 'string' value.
As it is in WHERE
I should try to put in quotes like this
around
$row_FlightData['FlightNoComplete']

."' WHERE ClientFlightNoComplete ='". $row_FlightData['FlightNoComplete']."'";

dcjones

HI Halojoy,

Many thanks for your help.

I have made the adjustment but I still have the error.

$updateSQL = "UPDATE flight_client SET  ClientAirline=".$row_FlightData['CarrierCode'].", ClientFlightNo='".$row_FlightData['FlightNumber']."', ClientFlightSuffix=".$row_FlightData['FlightSuffix'].", ClientTerminalCode=".$row_FlightData['TerminalCode'].", ClientGateNumber='".$row_FlightData['GateNumber']."', ClientFlightStatus='".$row_FlightData['FlightStatus']."', ClientFlightId=".$row_FlightData['AODBUniqueID'].", ClientStime='".$row_FlightData['ScheduledDateTime']."', ClientEtime='".$row_FlightData['EstimatedDateTime']."', ClientDepartLoc=".$row_FlightData['IATALocationCode'].", ClientArriveLoc='".$row_FlightData['PublicLocationName']."', ClientZone='".$row_FlightData['zone']."' WHERE ClientFlightNoComplete ='". $row_FlightData['FlightNoComplete']."'";

Output:
Array ( [id] => 190747 [AODBUniqueID] => 4545802 [FIDSFlightActive] => Y [ArrivalOrDeparture] => A [CarrierCode] => TCX [FlightNumber] => 121 [FlightSuffix] => L [FlightNoComplete] => TCX121 [FlightDate] => 2010-01-30 [ScheduledDateTime] => 2010-01-30T01:30:00+01:00 [EstimatedDateTime] => [IATALocationCode] => SSH [PublicLocationName] => Sharm El Sheikh [FlightStatus] => Baggage in [GateNumber] => [TerminalCode] => S [type] => [reg] => [run] => [stand] => 20M [MessageSentDateTime] => [zone] => [timeflag] => 2010-01-30 08:53:00 )

Unknown column 'TCX' in 'field list'

This has me at a loss.

halojoy

[CarrierCode] => TCX

So we should put string quotes around 'CarrierCode'

Which means $row_FlightData['CarrierCode']
Like this

ClientAirline='".$row_FlightData['CarrierCode']."', ClientFlightNo

dcjones

Hi Halojoy,

I tested the script starting with putting single quotes around the CarrierCode which then produced another error of the same type. I went through the script putting single quotes around each VAR when it displayed the error.

I now have no errors it has produced another problem.

The script is written to update multiple rows with different data in the table "flight_client".

It appears that only the last record is getting up in the "flight_client" table is getting updated.

I have tried:

do {

$updateSQL = "UPDATE flight_client SET  ClientAirline='".$row_FlightData['CarrierCode']."', ClientFlightNo='".$row_FlightData['FlightNumber']."', ClientFlightSuffix='".$row_FlightData['FlightSuffix']."', ClientTerminalCode='".$row_FlightData['TerminalCode']."', ClientGateNumber='".$row_FlightData['GateNumber']."', ClientFlightStatus='".$row_FlightData['FlightStatus']."', ClientFlightId=".$row_FlightData['AODBUniqueID'].", ClientStime='".$row_FlightData['ScheduledDateTime']."', ClientEtime='".$row_FlightData['EstimatedDateTime']."', ClientDepartLoc='".$row_FlightData['IATALocationCode']."', ClientArriveLoc='".$row_FlightData['PublicLocationName']."', ClientZone='".$row_FlightData['zone']."' WHERE ClientFlightNoComplete ='". $row_FlightData['FlightNoComplete']."'"; 

} while ($row_FlightData = mysql_fetch_assoc($FlightData));

and

while ($row_FlightData = mysql_fetch_assoc($FlightData)){

$updateSQL = "UPDATE flight_client SET  ClientAirline='".$row_FlightData['CarrierCode']."', ClientFlightNo='".$row_FlightData['FlightNumber']."', ClientFlightSuffix='".$row_FlightData['FlightSuffix']."', ClientTerminalCode='".$row_FlightData['TerminalCode']."', ClientGateNumber='".$row_FlightData['GateNumber']."', ClientFlightStatus='".$row_FlightData['FlightStatus']."', ClientFlightId=".$row_FlightData['AODBUniqueID'].", ClientStime='".$row_FlightData['ScheduledDateTime']."', ClientEtime='".$row_FlightData['EstimatedDateTime']."', ClientDepartLoc='".$row_FlightData['IATALocationCode']."', ClientArriveLoc='".$row_FlightData['PublicLocationName']."', ClientZone='".$row_FlightData['zone']."' WHERE ClientFlightNoComplete ='". $row_FlightData['FlightNoComplete']."'"; 

}

Any ideas as to why all the record in "flight_client" are not getting updated.

Hey, many thanks for your help with this.

halojoy

You did a Good work to get it run without those errors.

About howto UPDATE several rows.
Can you start a new topic.
And do include more lines of your script CODE.
And describe a bit more your idea how you want it work.

Regards 🙂

Ashley_Sheridan

I've found that whenever I get errors with my queries, that writing out the full query to the screen I'll more often than not spot the problem right away. If you don't spot it at first, try running it in something like phpMyAdmin, which will give your more feedback on the error, or copy the query into a text editor that supports syntax highlighting for SQL. Notepad++ and Kate both have this, and will show you if you've got mismatched quotes, or no quotes where there ought to be some.