I am having a bit of a problem with a data populated drop down selection. I am populating a form with the data from 2 tables using to different queries and a nested while statement. The first gives the output of the contact information, the second is just populating the state field. They both work fine, but I am trying to use a ternary operator to change the selected option to the correct one from the data base, and I think I am not selecting the right data. If I try to have a join on the 2 tables it either outputs only the states that exist in contacts, or it outputs all the states plus doubles for any records in contacts. I feel like I am pretty close here. Here is the php;
$dialogValue = $_POST['dataOpen'];
$sql = "SELECT * FROM contacts prc
JOIN states as st
ON (prc.stateId = st.idStates)
WHERE (idContacts ='" . $selectionValue ."')";
$statesSQL = "SELECT * FROM states";
$prcresult = $conn->query($sql);
$statesResult = $conn->query($statesSQL);
if ($prcresult->num_rows > 0) {
// output data of each row
while($prcrow = $prcresult->fetch_assoc()) {
echo
"<form>"
."<fieldset>"
."<label for='name'>Name</label>"
."<input type='text' name='firstName' value='" . $prcrow["firstName"] ."'>"
."<input type='text' name='lastName' value='" . $prcrow["lastName"] ."'>"
."<input type='text' name='address1' value='" . $prcrow["address1"] ."'>"
."<input type='text' name='address2' value='" . $prcrow["address2"] ."'>"
."<input type='text' name='city' value='" . $prcrow["city"] ."'>";
if ($statesResult->num_rows > 0) {
echo "<select>";
// output data of each row
while($strow = $statesResult->fetch_assoc()) {
//echo '<option value="'.$value.'" '.(($value=='United States')?'selected="selected"':"").'>'.$value.'</option>';
echo "<option value='" .$strow["idStates"] ."'" .(($strow["idState"]==$prcrow["statesId"])?'selected="selected"':"").">" .$strow["state"] ."</option>";
}
}
}
} else {
echo "0 results";
}
/*if ($statesResult->num_rows > 0) {
// output data of each row
while($row = $statesResult->fetch_assoc()) {
echo
"<option value='" .$row["idStates"] ."'>" .$row["state"] ."</option>";
}
}else{
echo "o results";
}*/
echo
"</select></fieldset>
</form>";
$conn->close();
?>
The tables that I am working with have this structure;
this is contacts
idContacts
firstName
lastName
address1
address2
city
stateId
zip
phone1
phone2
email
this is states;
idStates
state
They are joined where contacts.stateId = states.idStates on the first query $sql not sure if I need the second query or not...
Any help on this is greatly appreciated!
I found the answer. The previous query had to be used first in the ternary operator. like this;
while($strow = $statesResult->fetch_assoc()) {
//echo '<option value="'.$value.'" '.(($value=='United States')?'selected="selected"':"").'>'.$value.'</option>';
echo "<option value='" .$strow["idStates"] ."'" .(($prcrow["statesId"]===$strow["idState"])?'selected="selected"':"").">" .$strow["state"] ."</option>";
}
}