Need help creating query

[oxhey]

I have three tables tables :

Table 1 is called "card" . The fields that are important are:

 - "state_id"  - This table has 3 values (1,2,3)  
 - "associated_driver" - Has a text value e.g John Smith
 - "referred_as" - Has a value , normally some number e.g 123555

Table 2 is called "entrylog" . The fields that are important are:

 - "ID" - The auto incremented value of the table.
 - "driver_id" - The value that links it to the ID filed of the driver
   table.

Table 3 is called "driver". The fields that are important are:

 - "ID" - The auto incremented value of the table
 - "associated_card" - Has a value , normally some number e.g 123555
 - "referred_as" - The name of the driver

 
I already have a field where the user can enter data (the entrylog ID), and a way to do the query . I just need to get the actual query correct.

What I want the MySQL query to do:

When a user enters an ID (from one of the entry's in the entrylog table) , the query compares the driver_id value to the value of ID on the driver table.

When it finds a match , it then compares the value of the associated _card and referred_as  (from the driver table ) to  referred_as and associated_driver (from the card table).

When those are verified as being the same , it then looks to the state_id field for that entry that has just been verified . It returns the value of state id .
 
Its a bit confusing , I know. I have posted code for what I have now (the query is totally wrong , but it shows how the result is checked.  )

Thanks in advance.

        $id = $_POST['id'];
    
    
    $sql ="SELECT * FROM card WHERE id = '$id'" ;
    mysql_select_db('damp');
    $result = mysql_query( $sql, $conn );
    $row = mysql_fetch_assoc($result);
    switch($row['state_id'])
    {
    case "1":
       echo "<strong><font color=\"green\">Authorisation Granted!</font></strong>";
       break;
    case "2":
       echo "<strong><font color=\"red\">Your card has expired and authorisation is denied</font></strong>";
       break;
    case "3":
       echo "<strong><font color=\"red\">Your card has been cancelled and authorisation is denied</font></strong>";
       break;
    default:
       echo "<strong><font color=\"red\">The Card ID does not exist</font></strong>";
    }

[Barand]

I can see that the "driver_id" in the log would match with the "id" in the driver table but I am totally confused over the other matches.

 

It sounds as though the referred_as columns should match but one is numeric in one table and alpha in the other. Ditto the "associated" columns.