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

    Join Date
    Jan 2014
    Posts
    1
    Rep Power
    0

    Populate a PHP Dropdown List from MySQL Database


    Hello,

    I am trying to write a script to display records from an SQL database, but based on three variables which are set by three dropdown boxes on the page which are auto-populated from the database.

    This is for a Learning Management System I am working on to provide effective feedback from online learning tests.

    The code I am currently working on is below, along with the pseudo code which I hope will explain my requirements. The problem I am having is I cannot get the dropdown boxes to populate based on the criteria I have set in my pseudo code.

    Any help is very much appreciated,
    Thank you

    John

    Code:
    // I used this article for the structure of the following script: 
    (URL address blocked: See forum rules) 
    
    // Dropdown Box 1 - Choose the course - Show entries from the column "Name" from table "mdl_scorm". Once an option has been selected set variable $coursechoice to the value in the "id" column of the "mdl_scorm" table 
    
    // Dropdown Box 2 - Choose the user - Show entries from the columns "firstname" + "lastname" from table "mdl_user" IF the number shown in the "id" column of table "mdl_user" is present in the "userid" column of table "mdl_scorm_scoes_track" AND IF $coursechoice is present in the "scormid" column of table "mdl_scorm_scoes_track". Once an option has been selected set variable $userchoice to the value in the "id" column of table "mdl_user" 
    
    // Dropdown Box 3 - Choose the attempt - Show entries from the column "attempt" from table "mdl_scorm_scoes_track" IF $coursechoice is present in the "scormid" column of the table "mdl_scorm_scoes_track" AND IF $userchoice is present in the "userid" column of table "mdl_scorm_scoes_track". Once an option has been selected set variable $attemptchoice to the value in the "attempt" column from table "mdl_scorm_scoes_track" 
    
    // Submit button displays the records from table "mdl_scorm_scoes_track" which have a value in the column "scormid" which matches $coursechoice AND have a value in the column "userid" which matches $userchoice AND have a value in the column "attempt" which matches $attemptchoice 
    
    $sql="SELECT name FROM mdl_scorm"; 
    $result=mysql_query($sql); 
    
    $options=""; 
    
    while ($row=mysql_fetch_array($result)) { 
    
    $id=$row["id"]; 
    $thing=$row["name"]; 
    $options.="<OPTION VALUE=\"$id\">".$thing; 
    } 
    ?> 
    <SELECT NAME=course> 
    <OPTION VALUE=0>Choose the course 
    <?=$options?> 
    </SELECT> 
    <?php 
    
    $sql="SELECT username FROM mdl_user"; 
    $result=mysql_query($sql); 
    
    $options=""; 
    
    while ($row=mysql_fetch_array($result)) { 
    
    $id=$row["id"]; 
    $thing=$row["name"]; 
    $options.="<OPTION VALUE=\"$id\">".$thing; 
    } 
    ?> 
    <SELECT NAME=user> 
    <OPTION VALUE=0>Choose the user 
    <?=$options?> 
    </SELECT> 
    <?php 
    
    $sql="SELECT attempt FROM mdl_scorm_scoes_track"; 
    $result=mysql_query($sql); 
    
    $options=""; 
    
    while ($row=mysql_fetch_array($result)) { 
    
    $id=$row["id"]; 
    $thing=$row["name"]; 
    $options.="<OPTION VALUE=\"$id\">".$thing; 
    } 
    ?> 
    <SELECT NAME=attempt> 
    <OPTION VALUE=0>Choose the attempt 
    <?=$options?> 
    </SELECT> 
    <?php 
    
    $finalresult = SELECT element, value FROM mdl_scorm_scoes_track WHERE scormid=$coursechoice AND userid=$userchoice AND attempt=$attemptchoice 
    while ($testrows = mysqli_fetch_array($finalresult)){ 
    echo $testrows['value'];
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,709
    Rep Power
    1959
    Regarding the html, you need the closing tag for each OPTION element

    You should at least use mysqli_query instead of mysql_query. Another method is to use PDO.

    $finalresult should be called with mysqli_query before you use mysqli_fetch_array.
    The variables used for the SQL is missing and it also make it vulnerable to SQL Injection when you put them directly into the SQL.
    Take a look at how to use prepared statements.
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    and that's only the tip of the iceberg.

    Your code has no security whatsoever. You just throw all PHPHTMLSQL together in the hopes that it will do something. If you put this script online, the only thing the users will learn is how to break into a webserver.

    You need to start thinking about security. When you create a website, it's accessible to everybody, not just a bunch of friendly studenty. Failing to protect it will cause massive damages.

    Learn the basics of security. Learn proper PHP. The manual is a good starting point for that. For example, when you look up those MySQL functions, you'll get a big red warning sign saying that they're long obsolete.

    And then write your own code. Do not copypaste stuff you found somewhere on the Internet.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  6. #4
  7. Web Developer/Musician
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Nov 2004
    Location
    Tennessee Mountains
    Posts
    2,409
    Rep Power
    1032
    Originally Posted by johnnygrr
    Hello,

    I am trying to write a script to display records from an SQL database, but based on three variables which are set by three dropdown boxes on the page which are auto-populated from the database.

    This is for a Learning Management System I am working on to provide effective feedback from online learning tests.

    The code I am currently working on is below, along with the pseudo code which I hope will explain my requirements. The problem I am having is I cannot get the dropdown boxes to populate based on the criteria I have set in my pseudo code.

    Any help is very much appreciated,
    Thank you

    John

    Code:
    // I used this article for the structure of the following script: 
    (URL address blocked: See forum rules) 
    
    // Dropdown Box 1 - Choose the course - Show entries from the column "Name" from table "mdl_scorm". Once an option has been selected set variable $coursechoice to the value in the "id" column of the "mdl_scorm" table 
    
    // Dropdown Box 2 - Choose the user - Show entries from the columns "firstname" + "lastname" from table "mdl_user" IF the number shown in the "id" column of table "mdl_user" is present in the "userid" column of table "mdl_scorm_scoes_track" AND IF $coursechoice is present in the "scormid" column of table "mdl_scorm_scoes_track". Once an option has been selected set variable $userchoice to the value in the "id" column of table "mdl_user" 
    
    // Dropdown Box 3 - Choose the attempt - Show entries from the column "attempt" from table "mdl_scorm_scoes_track" IF $coursechoice is present in the "scormid" column of the table "mdl_scorm_scoes_track" AND IF $userchoice is present in the "userid" column of table "mdl_scorm_scoes_track". Once an option has been selected set variable $attemptchoice to the value in the "attempt" column from table "mdl_scorm_scoes_track" 
    
    // Submit button displays the records from table "mdl_scorm_scoes_track" which have a value in the column "scormid" which matches $coursechoice AND have a value in the column "userid" which matches $userchoice AND have a value in the column "attempt" which matches $attemptchoice 
    
    $sql="SELECT name FROM mdl_scorm"; 
    $result=mysql_query($sql); 
    
    $options=""; 
    
    while ($row=mysql_fetch_array($result)) { 
    
    $id=$row["id"]; 
    $thing=$row["name"]; 
    $options.="<OPTION VALUE=\"$id\">".$thing; 
    } 
    ?> 
    <SELECT NAME=course> 
    <OPTION VALUE=0>Choose the course 
    <?=$options?> 
    </SELECT> 
    <?php 
    
    $sql="SELECT username FROM mdl_user"; 
    $result=mysql_query($sql); 
    
    $options=""; 
    
    while ($row=mysql_fetch_array($result)) { 
    
    $id=$row["id"]; 
    $thing=$row["name"]; 
    $options.="<OPTION VALUE=\"$id\">".$thing; 
    } 
    ?> 
    <SELECT NAME=user> 
    <OPTION VALUE=0>Choose the user 
    <?=$options?> 
    </SELECT> 
    <?php 
    
    $sql="SELECT attempt FROM mdl_scorm_scoes_track"; 
    $result=mysql_query($sql); 
    
    $options=""; 
    
    while ($row=mysql_fetch_array($result)) { 
    
    $id=$row["id"]; 
    $thing=$row["name"]; 
    $options.="<OPTION VALUE=\"$id\">".$thing; 
    } 
    ?> 
    <SELECT NAME=attempt> 
    <OPTION VALUE=0>Choose the attempt 
    <?=$options?> 
    </SELECT> 
    <?php 
    
    $finalresult = SELECT element, value FROM mdl_scorm_scoes_track WHERE scormid=$coursechoice AND userid=$userchoice AND attempt=$attemptchoice 
    while ($testrows = mysqli_fetch_array($finalresult)){ 
    echo $testrows['value'];
    Even if you aren't using some sort of MVC framework approach to development, I would highly recommend not putting your database code in with your presentation code. At the very least put together a helper function that implements the recommended security suggested above, which takes an array of data from any source and turns it into a select. Then feed your SQL derived dataset to that function. If you are going to do procedural code at least do it cleanly.

IMN logo majestic logo threadwatch logo seochat tools logo