0

Trying to query a list of classes, teachers, extracurricular, and a student's major but when I run the query I get no results.

//query to get the student ID if user enters "name" on the page

 $student_name= $_POST["name"];
 $studentid= "SELECT studentid FROM students WHERE name='$student_name'";
 $id_query = mysqli_query($dbcon, $studentid);
 $student_id= mysqli_fetch_array($id_query);

//should query stuff related to the student.

 $sqlstudentquery= "SELECT c.name, c.teacher, e.name, m.name
   FROM students AS s
   LEFT JOIN classes AS c ON s.studentid = c.stude_id 
   LEFT JOIN extracurricular AS e ON s.studentid = e.stude_id 
   LEFT JOIN majors AS m ON s.studentid = m.stude_id 
 WHERE s.studentid='$student_id[0]'";
 $student_data = mysqli_query($dbcon, $sqlstudentquery);

When I enter a search term, the page will attempt to load but doesn't return any data. I tried to enter the query manually on the database but it returns an empty query. Should I be using an inner join rather than left?

edit: I did find a syntax error in my php after posting this. I have fixed it. SQL query still isn't working.

edit2: sql query works now thanks.

shelum
  • 173
  • 1
  • 2
  • 14
  • `print_r( $student_id);` chk this – devpro Feb 28 '16 at 20:48
  • 2
    In the first query you refer to the ID of a student as `SELECT student FROM` and on the second query you are using `s.studentid` which is `students AS s` so what is the correct field? Either your first or your second query is wrong. – Jorge Campos Feb 28 '16 at 20:48
  • If the query doesn't work in the DB interface this isn't a PHP question, it is a SQL/mysql question. What does the DB look like, can you provide a sqlfiddle? – chris85 Feb 28 '16 at 20:49

1 Answers1

4
 $student_name= $_POST["name"];
 $studentid= "SELECT student FROM students WHERE name='$name'";
 $id_query = mysqli_query($dbcon, $studentid);

You are looking for name $name but you are only setting a varaible called $student_name

So in effect you are looking for values in the Students table that have name=''.

Also

 WHERE s.studentid='$student_id[0]'";

You are looking for a studentid column value but this is checked against the data from the student column in the student table. This looks like your trying to check a numerical index field (studentid) with a varchar-type character value (student).

Further details:

You have the line WHERE s.studentid='$student_id[0]'"; which in full taking the values from your current SQL queries is:

student table column named student_id is s.student_id and you are checking this against value $sudent_id[0] which is from the query above it the value of the student column in the student table. This means you will never find any results as it's comparing two columns that almost certainly contain different values.

You are wide open to SQL injection on this code and it is very highly advised to use PDO or otherwise fully and completely escape strings from user input and use prepared statments.

Community
  • 1
  • 1
Martin
  • 22,212
  • 11
  • 70
  • 132
  • Might want to add a note about SQL injection possibility as well. – chris85 Feb 28 '16 at 20:53
  • Silly, I realized the syntax error after I posted this. Thanks. – shelum Feb 28 '16 at 20:58
  • @shelum I'm pleased it now works, but the code still looks like it wouldn't work as your comparing values from the column `student` and `studentid` , can you explain this? Cheers – Martin Feb 28 '16 at 21:04
  • `students` is the table, `studentid` is the student id. I had a typo that I edited. That is what I fixed to get the query working. – shelum Feb 28 '16 at 21:56
  • Ok. It's a good idea when you discover the typo or fix to your problem to **NOT** just update your original question, as that in turn makes it unclear what you're asking and unclear what my answer is explaining, the best method is to *edit* your question and add a note at the bottom as to how you solved your issue, Cheers :-) @shelum – Martin Feb 28 '16 at 22:14