oxhey Posted February 27, 2014 Share Posted February 27, 2014 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 123555Table 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>"; } Link to comment https://forums.phpfreaks.com/topic/286573-need-help-creating-query/ Share on other sites More sharing options...
Barand Posted February 27, 2014 Share Posted February 27, 2014 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. Link to comment https://forums.phpfreaks.com/topic/286573-need-help-creating-query/#findComment-1470965 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.