I'm not sure if I'm asking this correctly, but I'm fairly new to this and can't make sense of the tutorials I've read so far [Edit - What I actually meant, was that I could not find any tutorials that showed me how to do this exactly, or at least something similar]. SQL isn't my strong point, and before you say 'go and learn it then you'll know', I'm in a tight spot and need to get it sorted ASAP.
A client wants me to adapt a job application form (which I don't have much experience with), and I need to change a field that requires the user to type the name of their nearby City, into a dropdown select that takes values from a separate table, that's linked to the column in the main application table. The City table is literally an ID and the city name (only 5 or 6 entries).
Here's what I have:
<td><label for="preferredCity">Preferred city of work :</label></td>
<td><input type="text" name="preferredCity" id="preferredCity" /></td>
In a table called 'team', one of the columns is a VARCHAR column called t_prefCity , and I want it to take values from a table called 'team_cities', with 2 columns - tC_ID and tC_city.
I've tried using the following as a template, but realised that being an integer-based column, it wouldn't work (I think?):
<td><label for="ownCar">Do you own a car?</label></td>
<td>
<select name="ownCar" id="ownCar">
<option value="0">No</option>
<option value="-1">Yes</option>
</select>
</td>
I want the name of the cities to be selectable, basically.
What do I need to do? I'm in a pickle with a deadline, and I'm more of a designer than I am a developer, so want to get this sorted as soon as I can.
Thanks for your help in advance!
(I've actually decided to change tack and go with a simpler version, which is to add the values in the actual PHP form, as opposed to extracting stuff from a different table. Check Ed Gibbs' answer below for the code)
If you'll be storing the city name (rather than the ID) in the team.t_prefCity
column, I'd just ignore the city ID. I'm not saying that's good practice - ideally the ID would be stored in test
if it fits the requirements, and if it doesn't then the city table shouldn't have the ID. But you're up against the wall on this so that's neither here nor there, at least for now.
At any rate, have your dropdown code look like this:
<td><label for="preferredCity">Preferred city of work :</label></td>
<td>
<select name="ownCar" id="ownCar">
<option value="Detroit">Detroit</option>
<option value="Chicago">Chicago</option>
<option value="Boston">Boston</option>
</select>
</td>
Do you need help with the MySQL query to retrieve this?