CODEWITHSUNDEEP
mysqlselect
SubZero
SubZero

Reputation: 87

Selecting certain data

I have a code below which contains an MySQL query:

// These gets the values entered in the drop-down lists and stores the values as
// variables.
$event   = $_GET['cmbEvent'];
$yrGroup = $_GET['cmbYrGroup'];
$gender  = $_GET['cmbGender'];

/* The query $eygQuery, where the 'eyg' stands for event, year group and gender, 
grabs the stud_fname, stud_sname, result_studpoints, result_position, studevent_result 
where the requirements of the specific stud_gender, stud_yrgroup and event_id is met */
$eygQuery = "
    SELECT  stud_id,
            stud_fname, 
            stud_sname, 
            result_studpoints, 
            result_position, 
            studevent_result 
    FROM 
        students s
            INNER JOIN 
        result r on 
        s.stud_id = r.stud_id
    WHERE 
        stud_gender  = '$gender'
    AND stud_yrgroup = '$yrGroup'
    AND r.event_id   = '$event'  
";

$result = mysqli_query($conn, $eygQuery);

The fails because of 'SELECT stud_id,' (which is a primary and foreign key). When I remove stud_id leaving the query to look like this: 

        SELECT  stud_fname, 
                stud_sname, 
                result_studpoints, 
                result_position, 
                studevent_result 
        FROM 
            students s
                INNER JOIN 
            result r on 
            s.stud_id = r.stud_id
        WHERE 
            stud_gender  = '$gender'
        AND stud_yrgroup = '$yrGroup'
        AND r.event_id   = '$event'

It works, but how can the query be modified to also output stud_id too?

Thanks in advance.

Upvotes: 1

Views: 31

Answers (4)

RomanPerekhrest
RomanPerekhrest

Reputation: 92854

stud_id is ambiguous in your query. Make(denote) the field to be more specific:

    SELECT  s.stud_id,
            stud_fname, 
            stud_sname, 
            result_studpoints, 
            result_position, 
            studevent_result 
    FROM 
        students s
            INNER JOIN 
        result r on 
        s.stud_id = r.stud_id
    WHERE 
        stud_gender  = '$gender'
    AND stud_yrgroup = '$yrGroup'
    AND r.event_id   = '$event'

EDIT : For more convenient result fetching I would suggest to use aliases for some selected fields:

SELECT  s.stud_id AS student_id,
        ....

Fetching data example(in PHP):

... 
while ($data = mysqli_fetch_assoc($result)) { 
    $studId = $data['student_id']; 
    echo $studId; 
}
...

Upvotes: 2

Mureinik
Mureinik

Reputation: 311073

stud_id is ambiguous - it's present in both the students and the result table. Just qualify it with a table alias, and you should be fine: 

SELECT   s.stud_fname, 
-- Here -^
         stud_sname, 
         result_studpoints, 
         result_position, 
         studevent_result 
 FROM 
     students s
 INNER JOIN 
     result r ON s.stud_id = r.stud_id
 WHERE 
     stud_gender  = '$gender'
 AND stud_yrgroup = '$yrGroup'
 AND r.event_id   = '$event'

Upvotes: 0

Reversal
Reversal

Reputation: 622

stud_id is a field set in both students and result tables. When you ask the database to retrieve that data, he don't know what value to select. It's enough to specify from what table it should take that data:

SELECT  s.stud_id.
        stud_fname, 
        stud_sname, 
        result_studpoints, 
        result_position, 
        ìstudevent_result 
FROM 
        students s
INNER JOIN 
        result r on 
        s.stud_id = r.stud_id
WHERE 
    stud_gender  = '$gender'
AND stud_yrgroup = '$yrGroup'
AND r.event_id   = '$event'

Upvotes: 0

baao
baao

Reputation: 73221

Most likely you'll get this error because the column name is ambigious, so you need to specify the table you want results from. Try to prefix the column name with the table's short name, e.g.:

SELECT  s.stud_id,
        stud_fname, 
        stud_sname, 
        result_studpoints, 
        result_position, 
        studevent_result 
FROM 
    students s
        INNER JOIN 
    result r on 
    s.stud_id = r.stud_id
WHERE 
    stud_gender  = '$gender'
AND stud_yrgroup = '$yrGroup'
AND r.event_id   = '$event'

Upvotes: 0

Related Questions