CODEWITHSUNDEEP
phpmysql
shelum
shelum

Reputation: 163

PHP/SQL Query Returns Nothing

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.

Upvotes: 0

Views: 568

Answers (1)

Martin
Martin

Reputation: 22760

 $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.

Upvotes: 4

Related Questions