why is my sql doesn't work in checking row exist

Question

Here's my data structure of each table:

I have a problem that I need help with. I have a table student_votes and student. My problem is the second condition which is the checking the student if he/she already voted. Because when I run my codes it always go to the plsvote.php even when it's not supposed to.

student_votes table has:

(id(pri),candid(foreign),idno(foreign),syearid(foreign))

Here's my code:

<?php
//Start session
session_start();
//Array to store validation errors
//Connect to mysql server
include('connect.php');
//Function to sanitize values received from the form. Prevents SQL injection
//Sanitize the POST values
if (isset($_POST['login'])){
$idno = mysql_real_escape_string($_POST['idno']);
$password = mysql_real_escape_string($_POST['password']);
$position =  mysql_real_escape_string($_POST['user_type']);
    $YearNow=Date('Y');
 //checking student idno and password
    $sql1 = "SELECT * FROM student,school_year WHERE idno = '$idno' AND password = '$password' AND school_year.syearid = student.syearid AND school_year.from_year like $YearNow" ;
    $result = mysql_query($sql1) or die();
    $row = mysql_fetch_array($result);
    $num_row = mysql_num_rows($result);
 //checking if the student has been voted
    $sql2 = "SELECT * FROM student,studentvotes WHERE student.idno = studentvotes.idno AND syearid = $YearNow" ;
    $result1 = mysql_query($sql2) or die();
    $row1 = mysql_fetch_array($result1);
    $num_row = mysql_num_rows($result1);
    if ($row['user_type'] == "1"){
        //  $query = mysql_query ("INSERT INTO user_log VALUES('','$idno',NOW(), 'Login') ") or die(mysql_error());
        header('location:admin/index.php');
    } else if ($row['user_type'] == "3") {
//here is the part where I would check if the student already voted or not
        if ($num_row > 0) {
            $sql_c = "SELECT * FROM student WHERE idno = '$idno' AND password='$password' ";
            $result1 = mysql_query($sql_c) or die(mysql_error());
            while($row2=mysql_fetch_array($result1)){
                $_SESSION['SESS_COURSE'] = $row2['progid'];   
                $_SESSION['SESS_MEMBER_ID'] = $idno;
                header('location: plsvote.php');
                //$query = mysql_query ("INSERT INTO user_log VALUES('$idno',NOW(), 'Login') ") or die(mysql_error());
            }
        } else {
            header('location: notification.php');
        }
    } else if ($row['user_type'] == "2"){
        //  $query = mysql_query ("INSERT INTO user_log VALUES('','$idno',NOW(), 'Login') ") or die(mysql_error());
        header('location:admin/officerpanel.php');
        //$_SESSION['admin'] = $idno;
    } else {
        echo "<script type='text/javascript'>\n";
        echo "alert('Username or Password incorrect!, Please try again.');\n";
        echo "window.location = 'index.php';";
        echo "</script>";
        exit();
    }
}
?>

Answer 1

Your $sql2 query is selecting all students that have voted in the year specified.

All you need to do is limit the rows returned in that query to the votes made by the student identified by $idno i.e. this student.

<?php
//Start session
session_start();
//Array to store validation errors
//Connect to mysql server
include('connect.php');
//Function to sanitize values received from the form. Prevents SQL injection
//Sanitize the POST values
if (isset($_POST['login'])){
$idno = mysql_real_escape_string($_POST['idno']);
$password = mysql_real_escape_string($_POST['password']);
$position =  mysql_real_escape_string($_POST['user_type']);
    $YearNow=Date('Y');

    $sql1 = "SELECT * 
             FROM student,school_year 
             WHERE idno = '$idno' 
               AND password = '$password' 
               AND school_year.syearid = student.syearid 
               AND school_year.from_year like $YearNow";

    $result = mysql_query($sql1) or die();
    $row = mysql_fetch_array($result);
    $num_row = mysql_num_rows($result);


    $sql2 = "SELECT * 
             FROM student,studentvotes 
             WHERE student.idno = studentvotes.idno 
               AND syearid = $YearNow
               AND student.idno = $idno";


    $result1 = mysql_query($sql2) or die();
    $row1 = mysql_fetch_array($result1);
    $num_row = mysql_num_rows($result1);
    if ($row['user_type'] == "1"){
        //  $query = mysql_query ("INSERT INTO user_log VALUES('','$idno',NOW(), 'Login') ") or die(mysql_error());
        header('location:admin/index.php');
    } else if ($row['user_type'] == "3") {
        if ($num_row > 0) {
            $sql_c = "SELECT * FROM student WHERE idno = '$idno' AND password='$password' ";
            $result1 = mysql_query($sql_c) or die(mysql_error());
            while($row2=mysql_fetch_array($result1)){
                $_SESSION['SESS_COURSE'] = $row2['progid'];   
                $_SESSION['SESS_MEMBER_ID'] = $idno;
                header('location: plsvote.php');
                //$query = mysql_query ("INSERT INTO user_log VALUES('$idno',NOW(), 'Login') ") or die(mysql_error());
            }
        } else {
            header('location: notification.php');
        }
    } else if ($row['user_type'] == "2"){
        //  $query = mysql_query ("INSERT INTO user_log VALUES('','$idno',NOW(), 'Login') ") or die(mysql_error());
        header('location:admin/officerpanel.php');
        //$_SESSION['admin'] = $idno;
    } else {
        echo "<script type='text/javascript'>\n";
        echo "alert('Username or Password incorrect!, Please try again.');\n";
        echo "window.location = 'index.php';";
        echo "</script>";
        exit();
    }
}
?>

It would also benefit you to learn about the JOIN Syntax of SQL

The query could be written like this

    $sql2 = "SELECT * 
             FROM student s
                JOIN studentvotes sv ON s.idno = sv.idno 
             WHERE 
               AND syearid = $YearNow
               AND student.idno = $idno";

Answer 2

Here is my PHP....

You are selecting all students from the table student and studentvotes and joining them on id. You're also selecting the syearid if it matches the current year i.e. 2016. I assume your design is that if a student record appears in studentvotes then the student has voted? If that's the case then you probably don't need to select the student table unless there's a real need for it. You can just do:

$sql2 = "SELECT * FROM studentvotes WHERE idno = $idno AND syearid = $YearNow"

Also do you need the year they voted or do you just want to know if they voted or not? If the latter then add a int column in studentvotes table called 'voted' which would be used to store any one of two pieces of data, a 0 (if student has voted) or 1 (if student has not voted). Once this is done modify the sql statement to this:

$sql2 = "SELECT * FROM studentvotes WHERE idno = $idno AND voted = 1

As you can see the above statment says: Select all students from studentvotes where idno matches the current idno ($idno) and who has not voted and send them to plsvote.php. If you really need data back from student table too then just do this:

$voted=1;
 $sql2 = "SELECT name,age,voted FROM student s,studentvotes sv WHERE s.idno = sv.idno AND voted = 1

With the above example you can do another parse to bring back the ints to English for your FrontEnd:

if($voted = 1)
{
  header('location:admin/plsvote.php');
}
else
{
header('location:admin/officerpanel.php');
}

Also I don't get why you're doing a check with user_types by getting the user type from the user input storing it in a variable then doing nothing with this variable? But go on and check user type again for each select? I don't think this is needed.