I have three tables namely student, result and subject but whenever i try to join them i get this error message in the browser
Database query failed: Unknown column 'student.student_code' in 'field list'
here is my query

$query = "SELECT student.student_code,
student.firstname, 
student.middlename, 
student.lastname, 
student.gender, 
result.student_code, 
result.subject_code, 
result.mark, 
subject.subject_code, 
subject.subject_name ";
$query .= " FROM student AS s ";
$query .= " INNER JOIN result AS r ON s.student_code=r.student_code";
$query .= " INNER JOIN subject AS m ON m.subject_code=r.subject_code";
$query .= " WHERE student_code = '{$session_id}' ";
$query .= " LIMIT 1";

And here are the table structure

    This is student table

    student_code    int(255) primary key
    firstname   varchar(250)
    middlename  varchar(250)
    lastname    varchar(250)        
    gender  varchar(250)
    school_code varchar(250)    

    This is result table

    student_code    int(255)
    subject_code    int(10)
    mark    int(10) 

    This is subject table  

    subject_code    int(10)
    subject_name    varchar(250)

Recommended Answers

All 20 Replies

Because you've chosen to name the table in the FROM clause ('FROM student AS s'), you need to use that name in your selection part of the query
SELECT s.student_code...

i still get the same problem

from mysql

SELECT t1.name, t2.salary
  FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name;

SELECT t1.name, t2.salary
  FROM employee t1 INNER JOIN info t2 ON t1.name = t2.name;

so maybe is the AS m part

well i tried and i got this error
Database query failed: Column 'student_code' in where clause is ambiguous

try s.student_code then

select desired_field1, desired_field1, desired_fieldn from table1 t1 inner join (tabl2 t2, table3 t3, table4 t4) on (t1.t2ID=t2.t2ID and t1.t3ID=t3.t3ID and t1.t4ID=t4.t4Id)

$query = "SELECT s.student_code,
    s.firstname, 
    s.middlename, 
    s.lastname, 
    s.gender, 
    r.student_code, 
    r.subject_code, 
    r.mark, 
    sb.subject_code, 
    sb.subject_name 
    FROM student s 
    INNER JOIN (result r, subject sb) on 
        (s.student_code =r.student_code and 
        r.subject_code = sb.subject_code)";

thanks jerimepatient i tried with your query it works but if i add the where clause it says Database query failed: Column 'student_code' in where clause is ambiguous

specify student_code, s.student_code

$query = "SELECT s.student_code,
    s.firstname, 
    s.middlename, 
    s.lastname, 
    s.gender, 
    r.student_code, 
    r.subject_code, 
    r.mark, 
    sb.subject_code, 
    sb.subject_name 
    FROM student s 
    INNER JOIN (result r, subject sb) on 
        (s.student_code =r.student_code and 
        r.subject_code = sb.subject_code)
        WHERE s.student_code = '". $student_code ."'";

ambiguous means you have two or more tables that contains student_code and those tables are student and result.
You didn't specified the table you want to get the data.

For this situation I use student so the where data for student_code is s.student_code.

well i did that but i get nothing, only table header, here is my full code

<strong><table align="center" border="1" width="100%">
<tr>
<th>Subject Code</th>
<th>Subject Name</th>
<th>Marks</th>
</tr>
<?php
$query = "SELECT s.student_code,
    s.firstname, 
    s.middlename, 
    s.lastname, 
    s.gender, 
    r.student_code, 
    r.subject_code, 
    r.mark, 
    sb.subject_code, 
    sb.subject_name 
    FROM student s 
    INNER JOIN (result r, subject sb) ON 
        (s.student_code =r.student_code AND 
        r.subject_code = sb.subject_code)";
$query .= " WHERE s.student_code = '{$student_code}' ";
$query .= " LIMIT 1";   
$sql= mysql_query($query);
while($row=mysql_fetch_array($sql))
{
    ?>
    <tr>
    <td><p><?php echo $row($_SESSION['subject_code']); ?></p></td>
    <td><p><?php echo $row($_SESSION['subject_name']); ?></p></td>
    <td><p><?php echo $row($_SESSION['mark']); ?></p></td>
    </tr>
    <?php
}
?>

line 22: $query .= " WHERE s.student_code = '{$student_code}' ";

change it to : $query .= " WHERE s.student_code ='" . $student_code . "'";

sorry brother did that but only table header appear on browser and no any error

line 29 - 31: 
<td><p><?php echo $row($_SESSION['subject_code']); ?></p></td>
<td><p><?php echo $row($_SESSION['subject_name']); ?></p></td>
<td><p><?php echo $row($_SESSION['mark']); ?></p></td>

change it to:
<td><p><?php echo $row['subject_code']; ?></p></td>
<td><p><?php echo $row['subject_name']; ?></p></td>
<td><p><?php echo $row['mark']; ?></p></td>

remove $_SESSION[]

sorry brother i have chenged it and still nothing

<strong><table align="center" border="1" width="100%">
<tr>
<th>Subject Code</th>
<th>Subject Name</th>
<th>Marks</th>
</tr>
<?php
$query = "SELECT s.student_code,
    s.firstname, 
    s.middlename, 
    s.lastname, 
    s.gender, 
    r.student_code, 
    r.subject_code, 
    r.mark, 
    sb.subject_code, 
    sb.subject_name 
    FROM student s 
    INNER JOIN (result r, subject sb) ON 
        (s.student_code =r.student_code AND 
        sb.subject_code = r.subject_code)";
$query .= " WHERE s.student_code = '".$student_code."'";
$query .= " LIMIT 1";   
$sql= mysql_query($query);
while($row=mysql_fetch_array($sql))
{
    ?>
    <tr>
    <td><p><?php echo $row['subject_code']; ?></p></td>
    <td><p><?php echo $row['subject_name']; ?></p></td>
    <td><p><?php echo $row['mark']; ?></p></td>
    </tr>
    <?php
}
?>

what is the value of $student_code?
or did you store a value in $student_code?

numbers, for now it's 1 to 101

try this:
$query .= " WHERE s.student_code = '1'";

yes student_code store student unique number which starts from 1 to 101

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.