Printing of data

Question

I created 3 tables in my database "Colleges" in PhpMyAdmin. The names of the tables are "cool", "data" and "tab". The first table "cool" consists of the the names of the states of India. It has two columns : ID and Statename. From the data in this table, I created a drop down list in HTML form. Further the HTML form consists of the name, email id, contact and the address. The user has to fill in the details and select his/her state from the drop down list. Now, the user input consisting of name, email id, contact and the address goes into the second table "data" and the selected state from the drop down list goes into the 3rd table "tab", "tab" consists of 2 columns ID and stat where the state name gets stored here. I joined the above two tables "data" and "tab" using inner join of SQL. When I fetched the data in another web page, the name, email id, contact and address are getting printed but not the the statename. Instead of the state name, ID of the statename (as given in table cool) is getting printed. I want state name to get printed.

Here is the drop down list created using the data from my database :

<td>State :</td>
<td>
<?php
$mysqli = new mysqli('localhost', 'root', '', 'colleges');

$resultset = $mysqli->query("SELECT ID, Statename from cool");

?>

<select name="state">
<?php
    while($rows = $resultset->fetch_assoc())
{
    $ID = $rows['ID'];
    $Statename = $rows['Statename'];
    echo "<option value='$ID'>$Statename</option>";
}
?>
</select>
</td>
</tr>

And what are the changes to be done here to insert the selected dropdown state name into the table in my database ?

<?php
$connection = mysqli_connect("localhost", "root", "", "colleges");
if(isset($_POST['submit'])){ // Fetching variables of the form which travels in URL
$name = $_POST['name'];
$email = $_POST['email'];
$contact = $_POST['contact'];
$address = $_POST['address'];
$state = $_POST['state'];
if($name !=''||$email !=''){
//Insert Query of SQL
$insert = "INSERT Into data(student_name, student_email, student_contact, student_address) values ('$name', '$email', '$contact', '$address')";
$query = mysqli_query($connection, $insert);
$insert2 = "INSERT Into tab(stat) values ('$state')";
$query2 = mysqli_query($connection, $insert2);
echo "<br/><br/><span>Data has been inserted successfully</span>";
}
else{
echo "<p>Insertion Failed <br/> Some Fields are Blank</p>";
}
}
mysqli_close($connection); // Closing Connection with Server
?>

3). The printing part:

<?php
$hostname = "localhost";
$dbname = "colleges";
$username = "root";
$password = "";

$conn = mysqli_connect("$hostname","$username","","$dbname");

if(mysqli_connect_errno())
{
echo "Failed to Connect MySQL (phpmyadmin) Database: ".mysqli_connect_error();
}
$query = ("select student_name, student_email, student_contact, student_address, stat from data t2 inner join tab t3 on t2.ID=t3.ID");
$result = mysqli_query($conn, $query);
echo "<center>";
echo "<h1>Student list</h1>";
echo "<hr/>";
    echo"<table border = '1'>
    <tr>
    <th>Name</th>
    <th>Email</th>
    <th>Contact</th>
    <th>Address</th>
    <th>State</th>
    </tr>";

    while ($row = mysqli_fetch_array($result))
    {
    echo "<tr>";
    echo "<td>".$row['student_name']."</td>";
    echo "<td>".$row['student_email']."</td>";
    echo "<td>".$row['student_contact']."</td>";
    echo "<td>".$row['student_address']."</td>";
    echo "<td>".$row['stat']."</td>";
    echo "</tr>";
    }
    echo "</table>";
    echo "</center>";
    mysqli_close($conn);

    ?>

Answer 1

echo "<option value='$ID'>$Statename</option>";

and

$state = $_POST['state'];

and

$insert2 = "INSERT Into tab(stat) values ('$state')";

You are actually not inserting the name of the state, but it's id, as the select return the selected option's value, not it's content. You can either change the first line to

echo "<option value='$Statename'>$Statename</option>";

which is a weird solution, or just use a join in your mysql query to get the statename

JOIN ON cool.ID = tab.stat

Extention:

When you use select, the innerHTML of option is displayed, but the value is sent in POST. So if you write

<select name="state">
<option value="1">name</option>
</select>

You will see name name, but the 1 will be sent.

On the next page, $State = $_POST['state']; will have $State the value of '1'. In the SQL you put this value to your stat field in your database, with a generated ID. It means, your ID won't hold any data, but the state field will!

So when printing on the third page, you have to join the list of states by it's ID with the stored stateid in the 'state' field. Then you will be able to print the statenames.

"SELECT * FROM t2 LEFT JOIN t3 ON t2.?? = t3.?? LEFT JOIN t1 ON t1.ID = t3.state"

Or something similar. Do you have a field you can use to join the t2 and t3 tables? It seems you are losing data when inserting to your database.