SQL Help Please

jadehill2001

I need some help because after a whole day of trying I cannot get this to work:

//Get the username of the user logged in
$student=$_SESSION["authenticatedUser"];

//Get the ID of the course the user is studying
$course="SELECT course_id FROM student WHERE student_user='$student' ";

//Get all the announcements for the users course
$query = "SELECT * FROM announcements WHERE course_id = '$course' ";

This is the error I'm getting:

Error in query: SELECT * FROM announcements WHERE course_id = 'SELECT course_id FROM student WHERE student_user='gordon_brown' ' . You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'gordon_brown' '' at line 1

Am I in too deep here? Am I making it more complicated for myself?

laserlight

The first problem is that you are not escaping your strings for use in SQL. The second problem is that your SQL is simply wrong. Assuming that you are using the MySQL extension, you might write:

//Get the username of the user logged in
$student = mysql_real_escape_string($_SESSION["authenticatedUser"]);

//Get all the announcements for the users course
$query = "SELECT announcements.* FROM announcements, student
          WHERE announcements.course_id=student.course_id
              AND student.student_user='$student'";

jadehill2001

Great Thanks....Can you explain the join you did for me please in case I have to do something like this again please?

laserlight

Can you explain the join you did for me please in case I have to do something like this again please?

Actually, you might have to tweak it yourself. I selected announcements.*, but you should name those columns that you want to select.

Basically, in your original example, you wanted to select the course_id of a given student, then select all the announcements associated with that course_id. So, both the student and announcements tables have a common course_id. We can then join them:

SELECT * FROM announcements, student WHERE announcements.course_id=student.course_id;

But since we actually want only the announcements for the particular student, we add a AND student.student_user='$student'.