0

As my title states, I'm trying to dynamically populate a select in html with PHP. I've found some useful code snippets on the internet, but can't figure out how to connect them together and implement them to my desire. I have these selects that should get populated, the first one determines what should be put into the second one:

<select id="linetime" name="line" onChange="dynSelect(this.id);">
<option value="L411">L411</option>
<option value="L412">L412</option>
<option value="L414">L414</option>
<option value="L421">L421</option>
<option value="L422A">L422A</option>
<option value="L422B">L422B</option>
<option value="L423">L423</option>
<option value="L424">L424</option>
</select>

<select id="scannertime" name="scanner">
</select>

and tried to populate it this way:

function dynSelect(i){
$.ajax({
type: 'post',
url: "ajax.php.",
data: { id:'i' }
}).done(function(data) {
while(data.length){
$temp = data['scanner'];
$("#scannertime").append($("<options>").text($temp).val($temp));
}
});
}

PHP:

$id = $_REQUEST['id']; 
$json = array();

$dsn = "mysql:dbname=faultcounter; host=127.0.0.1";
$user = "root";
$password = "";
$dbh = new PDO($dsn,$user,$password);

$sql = $dbh->prepare("select * from possiblefaults where line=$id");
$sql->execute();
while ($row = $sql->fetch(PDO::FETCH_ASSOC)) {
$json[] = $row;
}
echo json_encode($json);

My problem is, that I am fairly new to script languages and PHP and I don't quite know how things work as of now, I can't figure out how to debug properly as I've only ever written in C# and Java. I also don't quite understand if my PHP isn't returning my desired data or my use of it in the code above is wrong. I would be glad if someone can explain these things to me.

Note: First time using stackoverflow.

Improve this question
6
  • Welcome to Stackoverflow. You have provided a quite good question. First of all I think you should use value instead of id in your HTML-code, like this: dynSelect(this.value);. Another hint is to add console.log(<your debug message>) in your javascript to trace whatever you need. By pressing F12 in your web browser you will find your developer tools, where you also can find the "Console" (where your trace outputs are found). Good luck :) Commented Aug 5, 2021 at 7:01
  • Thanks for the quick help, if I assume correctly that my data should be an array, how do i log the specific values inside of data? Commented Aug 5, 2021 at 7:12
  • You don't need to return json-data at all. Personnaly I prefer to generate HTML-strings directly in PHP, so that the returned data is easy to append to your existing HTML-document. So instead of $json[] = $row I would write something like $html .= "<option value='something'>First Option... etc". It is more readable IMHO. Commented Aug 5, 2021 at 7:17
  • What exactly is not working with the code? What have you tried to make it work? Also, be warned that your query is qidely open for SQL injection - if you already use a prepared statement, please use it properly by not injecting the raw $id Commented Aug 5, 2021 at 7:25
  • @NicoHaase, well I want it to populate the second select i posted based on the selection of the first one, it seems that i either don't get the selection of the first select to my php or I got the population wrong. NOTE: I know that my queries are open to SQL injection, I planned on doing that after the code works as intented as the site is currently running on a local webserver.(thanks for pointing it out though.) Edit: As I am new to script languages in general I don't really know how to find errors that aren't present in a console or immediately thrown in my face like it's common in c#. Commented Aug 5, 2021 at 7:40

2 Answers 2

Reset to default
3

The PHP was flawed in that the prepared statement embedded user supplied data directly in the SQL command and thus negated the benefits of Prepared Statements

As you specifically need the ID to be available to run the sql you should use a logic test to ensure that it is set before proceeding.

The ID attributes of HTML elements could be removed if you were to use another means of selecting the particular select menus - querySelector is really useful for this and as I do not use jQuery I offer a possible solution using vanilla js.

<?php
    /*
        We can only process the request if the ID/line is present
    */
    if( isset( $_POST['id'] ) ){
    
        $id=filter_input( INPUT_POST, 'id', FILTER_SANITIZE_STRING );
        $data=array();
        
        $dsn = "mysql:dbname=faultcounter; host=127.0.0.1";
        $user = "root";
        $password = "";
        $dbh = new PDO($dsn,$user,$password);
        /*
            The `prepared statement` should use a `placeholder` rather
            than directly embedding variables in the SQL. Embedding variables
            opens the code to SQL injection vulnerabilities.
        */
        $sql='select `scanner` from `possiblefaults` where `line`=:id';
        $stmt=$dbh->prepare($sql);
        /*
            execute the statement with the bound variable
        */
        $res=$stmt->execute(array(
            ':id'   =>  $id
        ));
        
        while( $row = $stmt->fetch( PDO::FETCH_ASSOC ) ) $data[]=$row;
        exit( json_encode( $data ) );
    }
?>

Some slightly modified HTML:

<select name='line'>
    <option value='L411'>L411
    <option value='L412'>L412
    <option value='L414'>L414
    <option value='L421'>L421
    <option value='L422A'>L422A
    <option value='L422B'>L422B
    <option value='L423'>L423
    <option value='L424'>L424
</select>
<select name='scanner'></select>

Vanilla js using Fetch

<script>
    /*
        vanilla javascript using querySelector to target the `select` menus
        and bind the event listener externally as opposed to inline using `onChange`
    */
    document.querySelector('select[name="line"]').addEventListener('change',function(e){
        //find target Select menu
        let scanner=document.querySelector('select[name="scanner"]');
        
        //create payload to send to server
        let fd=new FormData();
            fd.set('id',this.value);
        
        // send the ajax request using fetch
        fetch('ajax.php',{method:'post',body:fd})
            .then( r=>r.json() )
            .then( json=>{
                scanner.innerHTML='';
                // process the response data and add new option for each record
                Object.keys( json ).forEach( key=>{
                    let obj=json[ key ];
                    scanner.append( new Option( obj.scanner, obj.scanner ) );
                })
            })
    });
</script>

update The following is exactly as tested - returning results for all items from initial select menu.

<?php

    error_reporting( E_ALL );
    
    # script is running from different directory to where db connection files are stored
    # set the include path to the directory 2 levels up
    chdir('../../');
    set_include_path( realpath( sprintf( '%s/dbo', getcwd() ) ) );
    
    require 'db-conn-details.php';
    require 'pdo-conn.php';
    

    if( $_SERVER['REQUEST_METHOD']=='POST' && isset( $_POST['id'] ) ){
        ob_clean();
        
        $id=filter_input( INPUT_POST, 'id', FILTER_SANITIZE_STRING );
        $data=array();
        
        # different table and columns but essentially the same query.
        # This returns a single record per line/id.
        $sql='select `seqid` as `scanner` from `account` where `line`=:id';
        $stmt=$dbh->prepare( $sql );

        $res=$stmt->execute(array(
            ':id'   =>  $id
        ));
        
        while( $row = $stmt->fetch( PDO::FETCH_ASSOC ) ) $data[]=$row;
        exit( json_encode( $data ) );
    }
?>
<!DOCTYPE html>
<html lang='en'>
    <head>
        <meta charset='utf-8' />
        <title></title>
    </head>
    <body>
        <select name='line'>
            <option value='L411'>L411
            <option value='L412'>L412
            <option value='L414'>L414
            <option value='L421'>L421
            <option value='L422A'>L422A
            <option value='L422B'>L422B
            <option value='L423'>L423
            <option value='L424'>L424
        </select>
        <select name='scanner'></select>
        <script>
            document.querySelector('select[name="line"]').addEventListener('change',function(e){
                let scanner=document.querySelector('select[name="scanner"]');
                let fd=new FormData();
                    fd.set('id',this.value);
                    
                fetch( location.href,{ method:'post', body:fd })
                    .then( r=>r.json() )
                    .then( json=>{
                        scanner.innerHTML='';
                        Object.keys( json ).forEach( key=>{
                            let obj=json[ key ];
                            scanner.append( new Option( obj.scanner, obj.scanner ) );
                        })
                    })
            });
        </script>
    </body>
</html>

The db table data:

mysql> select `line`, `seqid` as `scanner` from `account`;
+-------+----------------+
| line  | scanner        |
+-------+----------------+
| L411  | sri-0000001    |
| L412  | sri-0000002    |
| L413  | sri-0000003    |
| L414  | sri-0000004    |
| L421  | sri-00000014   |
| L422A | sri-00000015   |
| L422B | sri-00000016   |
| L423  | sri-00000017   |
| L425  | sri-00000018   |
|       | sri-00000019   |
|       | sri-00000020   |
|       | sri-00000021   |
|       | sri-00000022   |
|       | sri-00000023   |
|       | sri-0000003580 |
|       | sri-0000003581 |
|       | sri-0000003582 |
|       | sri-0000003583 |
|       | sri-0000003584 |
+-------+----------------+
19 rows in set (0.00 sec)
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

This seems to work until the JSON is parsed, it states that the parser has reached an unexpexted end of data. Any ideas why would be appreciated.
Can you capture and post a sample (from the console) of the data returned by the ajax query?
I don't quite understand the question, but I can copy the output of the console: Uncaught (in promise) SyntaxError: JSON.parse: unexpected end of data at line 1 column 1 of the JSON data EDIT: I guess this means my promise failed?
If you observe the network request you should(hopefully) see the response from the ajax request. Is that valid JSON? Please add what you can to help identify the issue. I ran a test here with a different db and table structure and this worked OK for me...
Is the initial dropdown (line) populated directly from db? If so then you can easily add the selected attribute in your PHP code. If the list is static (ie: not rendered using data from db ) you would need to add some extra Javascript that runs on page load to select one of the options in the select menu and then add the selected attribute. btw: no bothering going on!
|
0

you can try this for example:

HTML:

 <div class="col-sm-6" id="occupation"></div>

This is the div for your selection

Then next, you're trying to mix jquery and PHP, but I will give you an example for both

My data in a json file:

[{"beruf_id":"58","beruf_name":"A SCH 19-23 B"},{"beruf_id":"1","beruf_name":"Anlagen- und Apparatebauer EFZ"},{"beruf_id":"59","beruf_name":"Automobil-Assistent\/in EBA"},{"beruf_id":"60","beruf_name":"Automobil-Fachleute EFZ"},{"beruf_id":"61","beruf_name":"Automobil-Mechatroniker\/in EFZ"},{"beruf_id":"62","beruf_name":"Automobil-Mechatroniker\/in EFZ Am-Me 19-23"},{"beruf_id":"2","beruf_name":"B\u00e4cker-Konditor-Confiseur EBA BKA 20-22A"},{"beruf_id":"3","beruf_name":"B\u00e4cker-Konditor-Confiseur EFZ"},{"beruf_id":"4","beruf_name":"Baupraktiker\/in EBA BPA 20-22A"},{"beruf_id":"5","beruf_name":"Berufsmatura Informatik"},{"beruf_id":"6","beruf_name":"Berufsmaturit\u00e4t 3-j\u00e4hrig"},{"beruf_id":"7","beruf_name":"Berufsmaturit\u00e4t I 3-j\u00e4hrig"},{"beruf_id":"8","beruf_name":"Berufsmaturit\u00e4t I 3-j\u00e4hrig  DL"},{"beruf_id":"9","beruf_name":"Berufsmaturit\u00e4t I 3-j\u00e4hrig TAL"},{"beruf_id":"10","beruf_name":"Berufsmaturit\u00e4t I 4-j\u00e4hrig TAL"},{"beruf_id":"63","beruf_name":"Berufsmaturit\u00e4t II 1-j\u00e4hrig TAL"},{"beruf_id":"11","beruf_name":"Berufsmaturit\u00e4t II 2-j\u00e4hrig TAL 21-23 A"},{"beruf_id":"12","beruf_name":"Berufsmaturit\u00e4t II BBM 20-22 A"},{"beruf_id":"13","beruf_name":"Betriebsinformatiker\/in EFZ"},{"beruf_id":"64","beruf_name":"BP HW Hauswart\/in"},{"beruf_id":"65","beruf_name":"Coiffeuse\/Coiffeur EBA"},{"beruf_id":"66","beruf_name":"Coiffeuse\/Coiffeur EFZ"},{"beruf_id":"14","beruf_name":"E Lehr mit Kick"},{"beruf_id":"15","beruf_name":"E Lehr mit Kick 20-24"},{"beruf_id":"16","beruf_name":"Elektroinstallateur EFZ"},{"beruf_id":"67","beruf_name":"Fachkunde Distribution"},{"beruf_id":"68","beruf_name":"Fachkunde Distribution FKD 20-23"},{"beruf_id":"69","beruf_name":"Fachleute Betriebsunterhalt EFZ HD"},{"beruf_id":"70","beruf_name":"Fachleute Betriebsunterhalt EFZ HD FBH 21-24 B"},{"beruf_id":"71","beruf_name":"Fachleute Betriebsunterhalt EFZ WD"},{"beruf_id":"72","beruf_name":"Fachmann\/Fachfrau BP LOFA 19-22"},{"beruf_id":"17","beruf_name":"Fleischfachassistent\/in EBA"},{"beruf_id":"18","beruf_name":"Fleischfachleute EFZ"},{"beruf_id":"73","beruf_name":"F\u00f6rderkurs Kombi Dienstag"},{"beruf_id":"74","beruf_name":"F\u00f6rderkurs Kombi Donnerstag"},{"beruf_id":"75","beruf_name":"F\u00f6rderkurs Kombi Mittwoch"},{"beruf_id":"76","beruf_name":"F\u00f6rderkurs Kombi Montag"},{"beruf_id":"77","beruf_name":"Forstwart\/in EFZ"},{"beruf_id":"19","beruf_name":"G\u00e4rtner\/in EBA Landschaft"},{"beruf_id":"20","beruf_name":"G\u00e4rtner\/in EBA Produktion"},{"beruf_id":"21","beruf_name":"G\u00e4rtner\/in EFZ Landschaft"},{"beruf_id":"22","beruf_name":"G\u00e4rtner\/in EFZ Produktion"},{"beruf_id":"78","beruf_name":"Grundsch\u00fcler"},{"beruf_id":"79","beruf_name":"Haustechnikpraktiker\/in EBA"},{"beruf_id":"23","beruf_name":"hf-ict 19-22 B2"},{"beruf_id":"24","beruf_name":"hf-ict 20-23 B1"},{"beruf_id":"25","beruf_name":"HF-ICT 21-24 A"},{"beruf_id":"26","beruf_name":"HF-ICT 21-24 B"},{"beruf_id":"27","beruf_name":"Holzbearbeiter\/in EBA"},{"beruf_id":"80","beruf_name":"IG Fahrzeugrestaurator"},{"beruf_id":"28","beruf_name":"Informatiker Applikationsentwickler"},{"beruf_id":"29","beruf_name":"Informatiker Betriebsinformatik"},{"beruf_id":"30","beruf_name":"Informatiker System"},{"beruf_id":"31","beruf_name":"Informatiker\/in EFZ Applikation"},{"beruf_id":"32","beruf_name":"Informatiker\/in EFZ Plattformentwicklung 21-25 A"},{"beruf_id":"33","beruf_name":"Informatiker\/in EFZ Plattformentwicklung 21-25 B"},{"beruf_id":"34","beruf_name":"Koch\/K\u00f6chin EFZ"},{"beruf_id":"35","beruf_name":"Koch\/K\u00f6chin EFZ KOC 20-23 A"},{"beruf_id":"36","beruf_name":"Koch\/K\u00f6chin EFZ KOC 20-23 B"},{"beruf_id":"37","beruf_name":"Koch\/K\u00f6chin EFZ KOC 20-23 C"},{"beruf_id":"38","beruf_name":"K\u00fcchenangestellte EBA"},{"beruf_id":"81","beruf_name":"Landmaschinenmechaniker\/in EFZ"},{"beruf_id":"82","beruf_name":"Logistiker\/in EBA"},{"beruf_id":"83","beruf_name":"Logistiker\/in EFZ Art. 32"},{"beruf_id":"84","beruf_name":"Logistiker\/in EFZ LOG  19-22 C"},{"beruf_id":"85","beruf_name":"Logistiker\/in EFZ LOG 21-24 A"},{"beruf_id":"86","beruf_name":"Maler\/in EFZ"},{"beruf_id":"87","beruf_name":"Malerpraktiker\/in EBA"},{"beruf_id":"88","beruf_name":"Malerpraktiker\/in EBA MAA 21-23 A"},{"beruf_id":"39","beruf_name":"Maurer\/in EFZ"},{"beruf_id":"89","beruf_name":"Mechanikpraktiker\/in EBA"},{"beruf_id":"40","beruf_name":"Metallbauer\/in EFZ"},{"beruf_id":"41","beruf_name":"Metallbaupraktiker\/in EBA"},{"beruf_id":"42","beruf_name":"Montage-Elektriker\/in EFZ"},{"beruf_id":"90","beruf_name":"NHB Art. 32 alle Berufe ABU 20-22"},{"beruf_id":"91","beruf_name":"NHB Art. 32 alle Berufe ABU 21-23"},{"beruf_id":"92","beruf_name":"NHB Art. 32 LOG ILB 21-23"},{"beruf_id":"93","beruf_name":"Polym.\/Konstr. E EFZ"},{"beruf_id":"94","beruf_name":"Polym.\/Konstr. E EFZ PMK 21-25 A"},{"beruf_id":"95","beruf_name":"Polym.\/Konstr. EFZ"},{"beruf_id":"96","beruf_name":"Polym.\/Konstr. EFZ PMK 21-25 M"},{"beruf_id":"97","beruf_name":"Polym.\/Konstr. G EFZ"},{"beruf_id":"98","beruf_name":"Polym.\/Konstr. G EFZ PMK 21-25 B"},{"beruf_id":"99","beruf_name":"Produktionsmechaniker\/in EFZ"},{"beruf_id":"100","beruf_name":"Sanit\u00e4rinstallateur\/in EFZ"},{"beruf_id":"101","beruf_name":"Schreiner\/in EFZ"},{"beruf_id":"102","beruf_name":"Spengler\/in EFZ"},{"beruf_id":"43","beruf_name":"St\u00fctzkurs B\u00e4cker-Konditor-Confiseur"},{"beruf_id":"44","beruf_name":"St\u00fctzkurs Elektro DI"},{"beruf_id":"45","beruf_name":"St\u00fctzkurs Elektro MI"},{"beruf_id":"46","beruf_name":"St\u00fctzkurs G\u00e4rtner DO"},{"beruf_id":"47","beruf_name":"St\u00fctzkurs Koch DI"},{"beruf_id":"48","beruf_name":"St\u00fctzkurs Mathe\/Deutsch DI"},{"beruf_id":"49","beruf_name":"St\u00fctzkurs Mathe\/Deutsch DO"},{"beruf_id":"50","beruf_name":"St\u00fctzkurs Mathe\/Deutsch MI"},{"beruf_id":"51","beruf_name":"St\u00fctzkurs Mathe\/Deutsch MO"},{"beruf_id":"52","beruf_name":"St\u00fctzkurs Maurer DI"},{"beruf_id":"53","beruf_name":"St\u00fctzkurs Maurer\/Zimmerleute DO"},{"beruf_id":"54","beruf_name":"St\u00fctzkurs Metall DI"},{"beruf_id":"55","beruf_name":"St\u00fctzkurs Metall MI"},{"beruf_id":"56","beruf_name":"St\u00fctzkurs Zimmerleute DI"},{"beruf_id":"103","beruf_name":"Unterhaltspraktiker\/in EBA"},{"beruf_id":"104","beruf_name":"Vorlehre Metall"},{"beruf_id":"105","beruf_name":"Vorlehre VLB 21-22 C"},{"beruf_id":"106","beruf_name":"Zeichner\/in EFZ, Architektur"},{"beruf_id":"57","beruf_name":"Zimmermann\/Zimmerin EFZ"}]

jQuery solution:.

    function fetchOccupations() {
  var occupationSelectionCode = '<h2>Berufsauswahl</h2>' +
    '<select class="form-control" name="occupationSelection" id="occupationSelection">' +
    '<option value="">Please select an occupation...</option>' +
    '</select>' +
    '<br>';

  $('#occupation').append(occupationSelectionCode);
  $('#occupationSelection').on('change', function () {
    $
    console.log($('#occupationSelection').val());
    fetchClasses();
    $('#class').fadeTo("slow", 1);
  })

  $.getJSON(occupationApi)
    .done(function (occupationsData) {
      $.each(occupationsData, function (_occupationIndex, occupationsData) {
        $('#occupationSelection').append($('<option value="' + occupationsData.beruf_id + '">' + occupationsData.beruf_name + '</option>'));
      })
    })
    .fail(function (error) {
      console.log("Request Failed: " + error);
    })
}

PHP solution:

<?php
$connect = new mysqli("127.0.0.1","root","");

            if ($connect -> connect_errno)
                {
                    echo "Failed to connect to MySQL: " . $connect -> connect_error;
                    exit();
                }

                    $connect -> select_db("Your Database");
                if ($result = $connect -> query("Your query")) 
                {
                    
                }
                echo "<select>";
                while($row = mysqli_fetch_array($result)) 
                {
                    echo "<option>" . $row['Your columns'] . "</option>"
                }
                echo "</select>"
?>

Be aware, for the PHP solution you will need a locally setup database in PhpMyAdmin for example

I hope, this can help you:)

Improve this answer

2 Comments

As for the PHP solution, how would it change the options of the select in regard to the selected option of the first select? I guess it is not written as it is just an example. Anyway thank you for your answer.
@Simon I'm not a Php pro, but I think it should do it automatically when you select an option. It was a pleasure.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.