#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    7
    Rep Power
    0

    Exclamation 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.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,232
    Rep Power
    593
    Show what you have done so far and tell us what is going wrong.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    7
    Rep Power
    0
    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.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    7
    Rep Power
    0
    Originally Posted by gw1500se
    Show what you have done so far and tell us what is going wrong.
    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?
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,232
    Rep Power
    593
    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.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    7
    Rep Power
    0
    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....?

    PHP Code:
    mysql_connect('localhost''database''username''password') or die('Could not connect: ' mysql_error());; 

    <select name="selectname">

    PHP Code:
    $result mysql_query("SHOW COLUMNS FROM my_table"
    or die(
    mysql_error()); 

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

    </select>
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,232
    Rep Power
    593
    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.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    7
    Rep Power
    0
    Thanks for your reply btw.
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    7
    Rep Power
    0
    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.
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,232
    Rep Power
    593
    You need to redesign your database and fire whoever developed that plug-in. It is just plain wrong.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    7
    Rep Power
    0
    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.
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,232
    Rep Power
    593
    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.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.

IMN logo majestic logo threadwatch logo seochat tools logo