Problem arises in a SQL statement

[heshan]

Hi All,

 

I have a small problem related to a specific query. It creates following error.

 

Unknown column 'attendance_state.state' in 'field list'

 

    $query="SELECT attendance_state.state,attendance_state.state_id from attendance_state inner join attendance on    attendance_state.state_id=attendance.state_id where attendance.admission_no='".$admission_id[$i]."' and attendance.date='$date'";
    $result=mysql_query($query) or die (mysql_error());

 

The table related to the query looks like this. "state_id" is set as auto increment field.

 

attendance_state (state_id, state)

attendance (admission_no, state_id, date)

[Barand]

run the query "SHOW CREATE TABLE attendance_state" and post the result

[heshan]

That query executed successfully...Following is the result..

 

Table Create Table

attendance_state CREATE TABLE `attendance_state` (

`state_id` int(20) NOT NULL AUTO_INCREMENT,

` state` varchar(50) NOT NULL,

KEY `state_id` (`state_id`)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1

[Barand]

You have a space at the beginning of the " state" column name.

 

Run

 

ALTER TABLE `attendance_state` CHANGE COLUMN ` state` `state` VARCHAR(50) NOT NULL  ;

[heshan]

ohh yes exactly..thanks Barand..

 

Now it fixed and working...