SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select from database based on a mysql populated dropdown list

    Hello again,

    I have managed to populate a dropdown list with information from one of my Tables on my MySQL database. Could anyone give me some advice on how to now select an item from the dropdown list and use it in a SELECT statemant? The code that populates the table is shown below:-

    PHP Code:
    <?php      
                
    $server
    ="localhost";
    $username="root";
    $password="";
    $link=mysql_connect($server$username$password) or die ("Cannot connect to mysql server: ".mysql_error());
                
    $dbname 'golf_society';
    mysql_select_db($dbname$link) or die ("Cannot connect to database: ".mysql_error());
                
    $query="SELECT competitionname,id, competitiondate FROM competition order by competitiondate";

    $result mysql_query ($query);
    echo 
    "<select name=competitionname value=' '>";


    while(
    $drop=mysql_fetch_array($result)){

    //data stored in $drop
    echo "<option value=$drop[id]>$drop[competitionname] $drop[competitiondate]</option>";

    }
    echo 
    "</select>";
    // Close list box 
    ?>echo "</select>";
    // Close list box 
    ?>
    Thanks a lot
    Last edited by SpacePhoenix; Jan 7, 2012 at 04:10. Reason: put php tags around php code

  2. #2
    Non-Member Max Height's Avatar
    Join Date
    Dec 2011
    Posts
    303
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)
    To some extent it depends on whether the <select> is part of a form submission or if you want an onchange event handler to send the selected option value to a server side script as an ajax request or not.

    In either case, the principle is much the same. You need to send the selected option value as a GET or POST to a server side script which then uses the sent value as input to an sql select query. Your server side script then process the returned query results accordingly.

    If this is for a real life application then you will have to handle the validation and security issues related to the sent value but for now you probably want to KISS , so don't worry about security/validation for now.

  3. #3
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for that Max,

    I wanted to do something along the lines of:-

    $result = mysql_query ("select * from competition where competitionname = 'the selected value from the dropdown list' ");

  4. #4
    Non-Member Max Height's Avatar
    Join Date
    Dec 2011
    Posts
    303
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)
    Yes that is the psuedo code for the eventual query, but how do you want to send the selected option value to the server side script? Is your<select> actually part of a form which is submitted to a server side script or do you want to send the selected value to the server side script as soon as an option is selected?

  5. #5
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would like to click on the list - select a competition name and then that to return the other information from the table. I suppose what I'm saying is can this value be used directly in the SELECT statement,and if so how?

    I can do it if I create a form and populate the form manually, but I can't get it when the dropdown list is populated directly from MySQL:-(

  6. #6
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    PHP Code:
    echo "<select name='competitionname'>";
    foreach(
    mysql_fetch_array($result) as $drop){

    //data stored in $drop
    echo "<option value=" $drop['id'] .">" $drop['competitionname'] . " " $drop['competitiondate'] . "</option>";

    }
    echo 
    "</select>"
    Try something like that, watch the quotes -- php does not expand variables inside double quotes when the var is an array -- hence many prefer to return an object instead.

    mysql_fetch_object()

    PHP Code:
    echo "<option value=$drop->id>$drop->competitionname $drop->competitiondate</option>"
    or you will also see {} s used to clearly de-mark where vars start and end

    PHP Code:
    echo "<option value={$drop->id}>{$drop->competitionname} {$drop->competitiondate}</option>"

  7. #7
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cups,

    Thank you very much for your response,

    When I use the existing code I get the following dropdown:-dropdown.jpg

    Inserting your first code I get :- dropdown_cups.jpg

    For the 2nd and third examples of code I get a dropdown list with no content!

  8. #8
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    What does the result of adding:

    PHP Code:
    $rows mysql_fetch_array($result);

    var_dump($rows
    give you? Anything?

  9. #9
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry Cups, I'm affraid not.

    I don't want to take up too much more of your time but if you can bear with me:--

    I have two files
    • tester.php - which populates the dropdown list from the database

    • index3.php - which tries to select all the competition details from the database


    tester.php


    <html>
    <head>
    </head>
    <body>
    <form action= "index3.php" method="post">

    <?php

    $server="localhost";
    $username="root";
    $password="";
    $link=mysql_connect($server, $username, $password) or die ("Cannot connect to mysql server: ".mysql_error());

    $dbname = 'golf_society';
    mysql_select_db($dbname, $link) or die ("Cannot connect to database: ".mysql_error());

    $query="SELECT competitionname, id, competitiondate FROM competition order by competitiondate";

    $result = mysql_query ($query) or die ('error submitting');

    echo "<select name='competitionname'>";
    while($drop=mysql_fetch_array($result)){

    //data stored in $drop
    echo "<option value=$drop[id]>$drop[competitionname] $drop[competitiondate]</option>";

    }
    echo "</select>";
    ?>
    <input type="submit" value="Submit">
    </form>
    </body>
    </html>


    index3.php


    ?php
    $competition = $_POST['competitionname'];


    $server="localhost";
    $username="root";
    $password="";
    $link=mysql_connect($server, $username, $password) or die ("Cannot connect to mysql server: ".mysql_error());

    $dbname = 'golf_society';
    mysql_select_db($dbname, $link) or die ("Cannot connect to database: ".mysql_error());


    $result = mysql_query("SELECT id, competitionname, competitiondate FROM competition where competitionname = '$competition' order by competitiondate");

    echo "<table border='0' align='center'>";
    echo "<tr><th>id</th> <th>name</th> <th>date</th> <th>start</th></tr>";
    // keeps getting the next row until there are no more to get
    while($row = mysql_fetch_array( $result )) {
    // Print out the contents of each row into a table
    echo "<tr><font size='2'><td align='justify'>";
    echo $row['id'];
    echo "</td><td align='justify'>";
    echo $row['competitionname'];
    echo "</td><td align='center'>";
    echo $row['competitiondate'];
    echo "</td><td align='center'>";
    echo $row['starttime'];
    echo "</td><td align='center'>";

    echo "</td></tr>";
    }
    echo "</table>";
    ?>

    Basically tester.php acts as required and displays a dropdown list with the competitions listed, but when I select one and submit it, all that is displayed in index3.php is the table with no results.

    It seems that the selected value is not getting passed to the index.php file!

    Am I making a mistake with the <select name='competitionname'>

  10. #10
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Both files must be in the same folder in this case as the action of the form is "tester3.php" NOT "../tester3.php" or anything like that.

    Making a tester, smart move!

    Even better, reduce your tester down and do some "debugging by checking" as you go.

    In effect, you'd start with a blank script and write these code blocks one at a time proving that what you just wrote actually does work.

    It is the only way to divide and conquer between errors in HTML (or JS) / PHP / MYSQL


    PHP Code:
    <?php 

    // is anything being passed to this page at all?
    // if not it might be a badly formed HTML issue

    var_dump($_POST);
    echo 
    '<hr />';


    $competition $_POST['competitionname']; 
    echo 
    $competition '<hr />';


    $server="localhost";
    $username="root";
    $password="";
    $link=mysql_connect($server$username$password) or die ("Cannot connect to mysql server: ".mysql_error());

    $dbname 'golf_society';
    mysql_select_db($dbname$link) or die ("Cannot connect to database: ".mysql_error());


    // build up your query string into a variable first
    $qry "SELECT id, competitionname, competitiondate FROM competition where competitionname = '$competition' order by competitiondate";

    //then echo that variable, copy it paste it into your database and see if you have any matching data
    echo $qry ' <hr />';

    $result mysql_query($qry);

    // keeps getting the next row until there are no more to get
    // dont too much care about your html tables, do that kind of
    // tidying up when you have the whole thing running smoothly

    while($row mysql_fetch_array$result )) {

    var_dump$row );


    ?>

  11. #11
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks very much for that, I'll give it a try.

    Thanks for all your help.

  12. #12
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Success!

    Cups,

    Thanks a lot, I now havie it returning the required values.

    Working from your last post I was able to see that it was the id of the Competition which was being sent to the index3.php page.

    I altered my code to read:-

    $result = mysql_query("SELECT id, competitionname, competitiondate FROM competition where id = '$competition' order by competitiondate");

    and it works.

    I need to fiddle about now with the formatting etc. but once again thanks for all your hard work.

  13. #13
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Great, glad we helped you out there.

    To take my point on a stage, when developing something like that postback handler, always do as I say, move slowly proving that things are happening as you expect them.

    I went through a stage where I had files develop with a debug flag at the top of them, it might help you...

    PHP Code:
    <?php
    // first line of your file
    // easy to find and turn off

    $debug 1// or 0 or false if you prefer

    // some scripting


    if($debug){
    var_dump($_POST);
    echo 
    '<hr />';
    }

    // more scripting building up a query string and so on

    if($debug){
    var_dump($sql);
    echo 
    '<hr />';
    }

    // and so on
    I mean, you will soon tire of it, but it does give you a bit of a "safety net" till you have the real basics nailed into your brain.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •