Dynamically display data based on drop down menu selection

Hey, need help from some php Ninja's.

I have some members details set-up in a database where a plugin has done the easy part and created a table and id for members. I now need to insert a drop down menu on each members page which will dynamically display their price based on the selected location. So the member id as well as location field/values will need to be taken into consideration.

Thanks in advance.

Show what you have done so far and tell us what is going wrong.

I'm new to php so i'm taking this in small steps. I can display the table names in a drop down menu with this:

<?php
$dbhost = 'localhost';
$dbname ='database';
$dbuser = 'user';
$dbpass = 'pass';

mysql_connect($dbhost, $dbuser, $dbpass) or die(mysql_error());
mysql_select_db($dbname) or die(mysql_error());

?>

<select name="selectname">

<?php
$result = mysql_query("SHOW TABLES FROM database")
or die(mysql_error());

while($nt=mysql_fetch_row($result)){
echo "<option value=" . $nt[0] . ">" . $nt[0] . "</option>";
}

?>

</select>


Ultimately this is not what I want, so i've then tried to get the column names for the locations with the below, which is not working for me. I Just get a blank drop down box, it seems to not be connecting to the table:


$sql = "SELECT column_name FROM table";

echo "<SELECT name="location">";

$result = mysql_query($sql);

while ($row = mysql_fetch_assoc($result)) {

echo '<option value="'.$row["column_name"].'">'
.$row["column_name"].'</option>';

}

echo "</SELECT>";

?>

I've been searching for hours just to get this part right and there's still a long way to go. Need help badly.

So I figured out how to display the column names from the table with

<?php
$dbhost = 'localhost';
$dbname ='database';
$dbuser = 'root';
$dbpass = 'pass';


mysql_connect($dbhost, $dbuser, $dbpass) or die(mysql_error());
mysql_select_db($dbname) or die(mysql_error());

?>


<select name="selectname">

<?php
$result = mysql_query("SHOW COLUMNS FROM my_database")
or die(mysql_error());

while($nt=mysql_fetch_row($result)){
echo "<option value=" . $nt[0] . ">" . $nt[0] . "</option>";
}

?>

</select>

How do I select only specific columns as I only want the ones with location names?

Stop!!!! Do not use the deprecated MySQL extensions. Immediately switch to PDO.

When you post PHP code use [ PHP ] tags. See the sticky at the top of this forum.

Finally to your question. Are the columns that contain location names fixed? If not then perhaps you need to rethink your database schema. This appears to have an obvious solution (just use those columns that have location names) so I am guessing there is more to this than you have indicated. If you clarify what you are trying to do, we can probably be of more help.

Okay, see if I can make it clearer. My database has workers who charge a different price based on locations. When a user goes to the workers profile page, i want them to be able to select a location from the drop-down menu and then below the drop-down list display a predefined price stored for that worker.

Where I have column/field names that are locations, the values are prices. So i want only the column names that are names of Locations in the list, as opposes to name, address, etc. And then once i've done this, how do I output the stored value (price) for a specific worker when the location is selected from the drop down list? I'm guessing their id/key would play a role here....?




<select name="selectname">



</select>

You really didn't answer my question. Are you saying your column names are the location names and you add columns as you add location names? If that is the case your database design is wrong. Show us your database schema and we can go from there.

Thanks for your reply btw.

Yes that's right. As I add locations, It creates a new column.

It's the way the plugin was designed. It's based on a form where i can add new fields.

You need to redesign your database and fire whoever developed that plug-in. It is just plain wrong.

Lol. Thanks, so back to the drawing board.

So your saying my column would have to be filled withe the locations and then I somehow have to connect another table with individual workers prices. setting up a new table is the easy part, just not sure how i'd connect that with another table with prices for each worker.

Without more detail I can only guess what you might need but making some guesses you seem to be on the right track. You create a table with all the location information and a unique ID for each. A second table does the same for your workers. I am guessing that you expect a many-to-many relationship between locations and workers. In that case you create a 3rd "join" table. That is where you have a column for location ID and one for worker ID. This gives you either all the workers associated with any number of locations or all the locations with any number of workers.