How to search teachers name in the database either the status is 0 or 1? I dont know if this is correct "Status"<=1
SELECT * FROM "Teachers" WHERE LOWER("Name") = LOWER($1) AND "Section"=$2 AND "Status"<=1 LIMIT $3 OFFSET $4'
CodePudding user response：
Your sql is okay but it will selected records if you have status like -1, -2 as @Luuk states. My recommendation to use IN for readability and select only status you specify
SELECT * FROM "Teachers" WHERE LOWER("Name") = LOWER($1) AND "Section"=$2 AND "Status" IN (0,1) LIMIT $3 OFFSET $4
CodePudding user response：
In MySQL the boolean values are usually stored in TINYINT(1) format. In your case, to retrieve the teacher where their status is either 0(false) or 1(true) the SQL query would be:
select * from Teachers where LOWER("Name") = LOWER($1) AND "Section" = $2 and "Status" = 0 or "Status" = 1 LIMIT $3 OFFSET $4
One more thing you should try to follow, try to retrieve the relevant columns from the database rather than all the columns, it is a lot more performant than getting all the columns. Assuming the Teachers table has the following columns:
- Id
- FirstName
- LastName
- FullName
- Salary
- Designation
- Status
- Section etc.
And, suppose you only want to get the FirstName, LastName, Designation, Section from the table. You should run the following query,
select FirstName,LastName,Designation,Section from Teachers where LOWER("Name") = LOWER($1) AND "Section" = $2 and "Status" = 0 or "Status" = 1 LIMIT $3 OFFSET $4
Hope this helps in learning SQL and best of luck!