SQL statement integrity constraint violation

I am currently trying to fetch comments from the database table employee_trip_comments, however i am running into some problems and since i am not that familiar with SQL statements i am having a hard time fixing it.

In the SQL statement, i am using two INNER JOIN clauses to select records that have matching values in both tables.

The SQL statement is being called right away when the page loads/refreshes to be sure the data is always visible. When refreshing the page i get the following error: SQLSTATE[23000]: Integrity constraint violation: 1052 Column ‘week_end_date’ in where clause is ambiguous.

     // get trip comments from the database
        $stmt = $pdo->prepare("SELECT comment_id, employee_trip_comments.employee_id, employee_trip_comments.trip_id, comment_datetime, comment_text,
            author_id, employees.full_name as 'author_name' FROM employee_trip_comments 
            INNER JOIN employees ON employees.employee_id = employee_trip_comments.author_id
            INNER JOIN employee_trips ON employee_trips.trip_id = employee_trip_comments.trip_id
            WHERE employee_trip_comments.employee_id = :employeeID AND employee_trip_comments.trip_id = :tripID AND week_end_date = :weekEndDate ORDER BY comment_id DESC");
        $stmt->execute([':employeeID' => $employeeID, 'tripID' => $tripID, ':weekEndDate' => $weekEndDate]);
        $tripComments = $stmt->fetchAll(PDO::FETCH_ASSOC);

I am not completely sure what exactly is going wrong here so i hope i can get some help from the community, Thanks in advance!

What is the constraints for this column?

If you submit the raw query using other tool (phpMyAdmin?), do you get the samer error?

Thanks for your reply!

week_end_date is using a primary key and index

When submitting the query using phpMyAdmin i get the following: #1064 - There is something wrong in the syntax used for ‘:employeeID AND week_end_date = :weekEndDate ORDER BY comment_id DESC LIMIT 0…’ in line 4

If you want to test the query in phpmyadmin, you’ll need to remove all the placeholders and put actual values into the query instead.

Is there a column named week_end_date in more than one of the tables that you are referencing in that query? I’ve seen similar messages when mysql isn’t sure which table you want to use that column from.

Yes! there are now that i think of it, there are more tables with a column called week_end_date, i specified which table it needs to grab the column from and this fixed the SQL error.

I find a date as only primary key sort of unusual. What is the name of the table? Are there other primary keys in this table?

You might want to consider laying out queries better, using the query in the OP as an example:

// get trip comments from the database
        $stmt = $pdo->prepare(
		
		"SELECT
			  comment_id
			, employee_trip_comments.employee_id
			, employee_trip_comments.trip_id
			, comment_datetime
			, comment_text
			, author_id
			, employees.full_name as 'author_name'
		FROM
			employee_trip_comments 
		INNER JOIN
			employees
				ON employees.employee_id = employee_trip_comments.author_id
		INNER JOIN
			employee_trips
				ON employee_trips.trip_id = employee_trip_comments.trip_id
		WHERE
			employee_trip_comments.employee_id = :employeeID 
		AND
			employee_trip_comments.trip_id = :tripID
		AND
			week_end_date = :weekEndDate
		ORDER BY
			comment_id DESC"
			
			);
        $stmt->execute([':employeeID' => $employeeID, 'tripID' => $tripID, ':weekEndDate' => $weekEndDate]);
        $tripComments = $stmt->fetchAll(PDO::FETCH_ASSOC);

It makes the query much easier to read and to spot any errors like typos

Thanks for the suggestion :wave:!, this is indeed a lot more readable and makes it easier to spot typos

There is another table called employee_comments which is being used for general comments, this one is also using week_end_date. Other colums with a primary key are: trip_id, comment_id, author_id and employee_id. employee_id is coming from another table called employees

I do not quite follow. Normally you have only ONE primary key. And often this is a SERIAL number. Other keys are normally “foreign keys” linking to other tables. Or just indexed.

There are also two ways to name columns (unique or not unique). Like id or employee_id. Using id or week_end_date in several tables is possible, but I prefer emp_week_of_date to avoid conflicts by omitting the table name by mistake. But this is a personal preference.

It should be more clear if you showed the table construction of joined tables with PK and FK for each column.

Seems everyone is dancing around the answer instead of actually identifying the problem. The error message tells you exactly what is wrong.

		AND week_end_date = :weekEndDate

The error is with this portion and the error means that there is a field called week_end_date on multiple tables. A guess would be you have it on employee_trip (which would make sense) and employee_trip_comments (which probably doesn’t since comments should relate to a specific trip). To resolve it, you have two choices.

  • Fully qualify the where clause (like you’ve done with all the other fields on the where clause)
  • Remove one of the two fields from the table if one of them doesn’t make sense

Personally, I’d probably do both. The first for readability and code maintenance later, and the second for database integrity purposes.

That shouldn’t make any difference to your original problem, because you don’t reference that table in the query you posted. As far as I am aware, it only becomes ambiguous if there are columns with the same name in the tables you use in the query.

1 Like

@droopsnoot (sorry, missed that in the discussion thread) is correct.

The only way you’d get the ambiguous error is if a reference column in the query is found on multiple tables. If you do a SHOW TABLE for those three tables (employees, employee_trips, and employee_trips_comments), week_end_date will be on two of those tables. Perhaps on employees for people who don’t work a “standard” M-F job?

I fully qualified the where clause for week_end_date now, i will probably consider removing the column since a comment will be linked to a trip using the trip_id anyway

Ah i see now, i checked my database tables again and employee_trips (which is being referenced in the query) also makes use of the week_end_date column, so that’s probably where things went wrong in the first place. i fully qualified the where clause and specified which table the query has to grab the column from.

The week_end_date column is being used on the employee_trip_comments and employee_trips table. the reason for this being is that it makes comments, trips, etc, only visible to the specific work week it has been set to.

Indeed - by having the week_end_date in both tables, you’re duplicating data which is generally “A Bad Thing”. (BTW it’s a column that you’d be removing.)

1 Like

(BTW it’s a column that you’d be removing.)

I still get row and column mixed up together sometimes :sweat_smile:. However the error has been resolved by specifying what table it has to get the column week_end_date from and it’s now pretty clear to me what the error means and what i can do to fix it! Thanks to everyone who helped me out!