How to use the select AND

Question

I have been trying to use AND in my SELECT, but I got an error.

What is likely the correct way I should have written the code?

The code is:

$sel=mysql_query("SELECT  * from student, subject, studentmark
 where student.username='$name' AND studentmark.student_id='$name' AND studentmark.YEAR='$ya' AND studentmark.Term='FIRST' AND studentmark.Term='SECOND' AND studentmark.Term='THIRD' AND subject.code=studentmark.code AND student.username=studentmark.student_id");

studentmark.Term='FIRST' AND studentmark.Term='SECOND' AND studentmark.Term='THIRD' . This condition will never work & you'll get nothing in return. Also, you need to use JOIN instead of the following: student.username=studentmark.student_id — RyanB, Sep 03 '15 at 09:41
@jarlh Yeap. His query is a table joining query & as I think, he needs a query with at least 2 LEFT JOIN. — RyanB, Sep 03 '15 at 09:50
Also, please read this post about why you should not be using `mysql_query` - you really need to be using `mysqli_query` at the very least! http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — Nerdwood, Sep 03 '15 at 10:02

score 1 · Answer 1 · answered Sep 03 '15 at 10:35

A simple rule: Never use commas in the FROM clause. Always use explicit JOIN syntax. The result is something like this:

SELECT  *
from studentmark sm join
     student st 
     on sm.student_id = st.username join
     subject join
     on sm.code = su.code     
 where st.username='$name' AND sm.YEAR = '$ya' AND
       sm.Term in ('FIRST', 'SECOND', 'THIRD')

Also, learn to use table aliases. They make queries easier to write and to read.

score 0 · Answer 2 · answered Sep 03 '15 at 09:52

0

You must use OR instead of AND studentmark.Term='FIRST' AND studentmark.Term='SECOND' The Value of Term can only has ONE Value

answered Sep 03 '15 at 09:52

Bernd Buffen

14,525
2
24
39

score 0 · Answer 3 · answered Sep 03 '15 at 09:59

I don't know what columns do you want to get from student, subject & studentmark tables & also relationship of those tables. So here is my idea:

Use OR instead of AND on the same column check: studentmark.Term='FIRST' AND studentmark.Term='SECOND' AND studentmark.Term='THIRD'. This will return nothing.
Use table join query instead of: subject.code=studentmark.code AND student.username=studentmark.student_id, it is not correct. Take a look here: SQL Joins

How to use the select AND

3 Answers3