April 15, 2012

AND condition not working in MySQL Query

Question by dotman14

Please i need your help with my script. Whenever i include

AND maintable.semester_name = '$semester_name'

in the MySQL Query, it returns 0 for both values of semester_name, when actually
only one is supposed to have a value of 0 when echo $nums is processed. When i
remove

AND maintable.semester_name = '$semester_name'

the query gives normal results as i expect.

Thanks.

$query = "SELECT *             
   FROM maintable 
   WHERE maintable.matric_no = '$matric_no'
   AND   maintable.session = '$session'
   AND   maintable.semester_name = '$semester_name'
   AND   maintable.level = '$level'";
$result = mysql_query($query);
$nums = mysql_numrows($result);
echo $nums ;                  

TABLE STRUCTURE

COURSES
  course_id int(100) 
  course_code varchar(100) 
  course_title varchar(100) 
  course_unit int(10) 

MAINTABLE
  maintable_id int(255) 
  matric_no int(10) 
  session varchar(10) 
  semester_name varchar(10) 
  course_code varchar(10) 
  level int(10) 
  score int(10) 
  grade varchar(4) 

RESULT_UPLOAD
  upload_id int(10) 
  session varchar(10) 
  semester_name  varchar(10) 
  course_code varchar(10) 
  level varchar(10) 

SEMESTER
  semester_id int(10) 
  semester_name varchar(10) 

STUDENT
  matric_no int(10) 
  first_name varchar(100) 
  last_name varchar(100) 
  other_name varchar(100) 
  level int(10) 

USERS
  users_id int(10) 
  title varchar(20) 
  first_name varchar(20) 
  last_name varchar(20) 
  username varchar(20) 
  password varchar(100) 
  register_date datetime 
  tmp_name varchar(100) 
  type varchar(20) 
  name varchar(20) 
  size int(10) 

YEAR
  level_id int(10) 
  level int(10) 

Answer by Andreas Linden

The query is correct. I guess your conditions really don’t match any rows in the table. Just check what values you have in the database and what your are passing into the conditions.

Answer by Starx

Your query is correct, but with bit of ambiguity. The following query is run as exactly you are doing, but with less words.

$query = "SELECT *             
   FROM maintable 
   WHERE matric_no = '$matric_no'
   AND   session = '$session'
   AND   semester_name = '$semester_name'
   AND   level = '$level'";

Now, about your problem, the only way this might be happening may be due to no record is matching the records in your database.

Try to get the query you are running with echo $query and run the query directly from phpmyadmin to see how many result set you will get.

Author: Nabin Nepal (Starx)

Hello, I am Nabin Nepal and you can call me Starx. This is my blog where write about my life and my involvements. I am a Software Developer, A Cyclist and a Realist. I hope you will find my blog interesting. Follow me on Google+

...

Please fill the form - I will response as fast as I can!