Displaying SQL Query results as dropdown list

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • sunegtheoverlord
    New Member
    • Feb 2010
    • 5
    #1

    Displaying SQL Query results as dropdown list

    Hello all,

    This is my first post and my first outing into the the world of web design and php so forgive me if i'm asking an obvious question.

    i have 2 php pages at the moment.

    a.php, calls a SQL query to retreive 4 fields (FirstName, Surname, PostCode and a Reference number) from a table based on an input from a form (Postcode). these fields are then displayed as a drop down menu (apart from the reference which i want to remain invisible to the user) from which you can select the one you want. *NOTE the query produces multiple results from which i want to use the drop down list to select the desired record*

    b.php, echos the 3 visible fields using echo $_GET["form_name"];

    this works fine but i want to be able to pass the Reference number for the record that has been selected from the drop down list seperatly to b.php so i can use that as an input into another table of the sql database.

    i'm sure this is possible but i just can't see how to do it. Maybe involving a global variable containing the reference number?

    The code i'm using for getting the results is below

    [CODE=php]//display the results
    echo "<form action=\"b.php\ " method=\"GET\"> ";
    echo "<select name=\"TheClien t\">";
    while($row = mssql_fetch_arr ay($client_resu lt))
    {
    echo "<option>" . $row["Surname"] . ", " . $row["Forenames"] . ($row["PartnerForenam es"] == NULL ? "" : " and " . $row["PartnerSurname "] . ", " . $row["PartnerForenam es"]) . " - " . $row["HomePostCo de"] . "</option>";
    }

    echo "</select>\n <input type=\"submit\" value=\"Submit\ " /></select></form>";[/CODE]
    Last edited by Atli; Feb 18 '10, 03:53 PM. Reason: Added [code] tags.
    Tags: None
    • Atli
      Recognized Expert Expert
      • Nov 2006
      • 5062
      #2
      Hey.

      It would probably be simplest to just pass the PK of the row as the value of the <option> box, and then use that on the receiving page to fetch the reference value from the database.
      [code=php]while($row = mssql_fetch_ass oc($result))
      {
      echo "<option value=\"{$resul t['pk']}\">...All the other stuff...</option>";
      }[/code]


      It would also be possible to use Sessions to store the data from the first page and retrieve it again in the second page, but that would require a lot of unnecessary data duplication. (Values from ever row of the result set would have to be stored for each request. Best to just use the database, in my opinion.)

      I suppose you could also use hidden <input> elements, but that would mean putting the entire result set into the HTML, which is just a horrible idea... In fact, forget I mentioned it xD

      P.S.
      A few notes on your code. Nothing crucial, just things you may find interesting.
      [code=php]<?php
      // If you enclose a string in single-quotes,
      // you can use double-quotes inside it
      // without escaping them.
      echo '<form action="b.php" method="GET">';
      echo '<select name="TheClient ">';

      while($row = mssql_fetch_arr ay($client_resu lt))
      {
      // Just to make the echo line a little easier on the eyes.
      $partner = $row["PartnerForenam es"] == NULL ? "" : " and " . $row["PartnerSurname "] . ", " . $row["PartnerForenam es"];

      // You don't have to break out of a
      // double quoted string to insert variables.
      // PHP automatically parses variables inside
      // double-quoted strings. If they are array
      // elements they need to be enclosed in
      // curly-brackets though.
      echo "<option>{$ row["Surname"]}, {$row["Forenames"]}$partner - {$row["HomePostCo de"]}</option>";
      }

      // If you need to echo a lot of text, using
      // the Herdoc syntax is often a lot easier.
      // Look out for white-spaces in the opening and
      // closing lines! There must not be any.
      echo <<<HTML
      </select>
      <input type="submit" value="Submit" />
      </form>
      HTML;
      ?>[/code]

        Comment

        • sunegtheoverlord
          New Member
          • Feb 2010
          • 5
          #3
          Thanks Atli,

          That definitly sounds like the way forward as the PK is the reference number i actually need.

          how do i access the reference number from the database after this? what would my T SQL statement look like?

          Also, thanks for the notes on making the code easier to read. it was getting a little complicated!

          S

            Comment

            • Atli
              Recognized Expert Expert
              • Nov 2006
              • 5062
              #4
              I would start with something like this:
              [code=php]<?php
              // Assuming the PK is an integer.
              if(isset($_POST['TheClient']) && is_numeric($_PO ST['TheClient']))
              {
              $pk = (int)$_POST['TheClient'];
              $sql = "SELECT [stuff] FROM [tbl] WHERE [pk_column] = $pk";
              // etc...
              }
              else
              {
              echo "Invalid client ID passed.";
              }
              ?>[/code]

                Comment

                • sunegtheoverlord
                  New Member
                  • Feb 2010
                  • 5
                  #5
                  Hello again,

                  Please ignore my last message. i realise how to do all that now. only thing is this code isn't sending anything to the server

                  "<option value=\"{$resul t['pk']}\">"

                  the URL just says http://database/b.php?TheClient=

                  Shouldn't "TheClient =" now show the PK (i.e. the reference number)?

                  is there any reason why? The PK is definitly set on the database.

                    Comment

                    • sunegtheoverlord
                      New Member
                      • Feb 2010
                      • 5
                      #6
                      Hello again!

                      I still can't get that to work but it does work if i replace

                      "<option value=\"{$resul t['pk']}\">"

                      with

                      "<option value=\"{$row["reference_numb er"]}\">"

                      Any idea why the 'pk' doesn't work?

                        Comment

                        • Atli
                          Recognized Expert Expert
                          • Nov 2006
                          • 5062
                          #7
                          The 'pk' value I used was just an example. I meant it to be replaced by the table's Primary Key (the reference number, in your case).

                          Sorry, should have explain myself a bit better.

                            Comment

                            • kovik
                              Recognized Expert Top Contributor
                              • Jun 2007
                              • 1044
                              #8
                              Originally posted by sunegtheoverlor d
                              I still can't get that to work but it does work if i replace

                              "<option value=\"{$resul t['pk']}\">"

                              with

                              "<option value=\"{$row["reference_numb er"]}\">"
                              The PK = primary key. This is, typically, the auto-incremented ID column.

                              Also, in your "replacemen t line," you have an inconsistency with quotation marks. This is the reason that Atli used single quotes around "pk" in his example.

                                Comment

                                • sunegtheoverlord
                                  New Member
                                  • Feb 2010
                                  • 5
                                  #9
                                  Hi Atli,

                                  Sorry i should have realised too.

                                  Thanks for all your help, i "get it" now!

                                  S

                                    Comment

                                    Previous template Next
                                    Working...