Question posted in
You can find the official documentation here.

Fetch data from db using select tag – Jquery ajax

So i’m doing this function that on the change event of the select box, it will trigger an ajax call and would fetch data from the database. That part’s done. I’m getting all values i need. I place it in a table. Problem is, all the data appears in only one data cell. Here’y my code guys.

stud-home.php

<?php

    include('includes.html');
    include('conn.php');
    
    $select_options =mysqli_query($conn, "SELECT DISTINCT sy from subjects");
?>


<!--Logic-->
<script>
    $(document).ready(function(){
        function get_subj_id() {
            var filter =$("#subj-filter").val();
            $.ajax({
                url:'neu-server.php',
                method:'POST',
                data:{filter:filter},
                success:function(data) {
                    $(".this").html(data);
                }   
            });
        }
        
        $("#subj-filter").change(function(){
            get_subj_id();
        });
    });
</script>

<div class='container bg-success my-2'>
    <div class='row'>

<!-- content here -->


<select class='form-control my-2 mx-2' id='subj-filter'>
    <option selected disabled> **Select school year**</option>
    <?php
        if(mysqli_num_rows($select_options) >0) {
            while ($row =mysqli_fetch_assoc($select_options)) {
                echo "<option value='".$row['sy']."'>".$row['sy']."</option>";
            }
        }
    ?>
</select>

<table class='table table-light table-sm'>
    <thead>
        <th>ID</th>
        <th>Code</th>
        <th>Description</th>
    </thead>
    <tbody>
        <tr class='this'></tr>
    </tbody>
    <!--End of container-->
    
    
</div>
</div>

server code

<?php
    include('includes.html');
    $conn =mysqli_connect('localhost','root','','neu');
    
    if (isset($_POST['filter'])) {
        $filter = mysqli_real_escape_string($conn,$_POST['filter']);
        
        $search_subject =mysqli_query($conn,"SELECT id,description,code from subjects where sy = '$filter' ");
        $result =mysqli_num_rows($search_subject);
        
        
        if ($result > 0) {
            while ($row =mysqli_fetch_assoc($search_subject)) {
                $data ="
                    <td>".$row['id']."</td>";
                    $data .="
                    <td>".$row['code']."</td>";
                    $data .="
                    <td>".$row['description']."</td>";
                    
                echo $data;
            }
        } 
    } 
?>
Tags:

2

Answers


  1. 0

    As you are not Ending each row with tag
    change this

    `$data ="
<td>".$row['id']."</td>";
$data .="
<td>".$row['code']."</td>";
$data .="
<td>".$row['description']."</td>";`

    TO This

        $data ="
<tr><td>".$row['id']."</td>";
$data .="
<td>".$row['code']."</td>";
$data .="
<td>".$row['description']."</td></tr>";
    Login or Signup to reply.
  2. 0

    I read your comment about actually closing the table so assuming the markup is now correct:

    If you move the class='this' to the tbody element each record from the db should be on it’s own row when inserted. The Javascript remains fairly much the same as the original.

    The closing </option> is and has always been entirely optional (weirdly) so perfectly safe to remove it.

    <?php

    include('includes.html');
    include('conn.php');
    
    $select_options =mysqli_query($conn, "SELECT DISTINCT sy from subjects");
?>
<!--Logic-->
<script>
    $(document).ready(function(){
        function get_subj_id() {
            $.ajax({
                url:'neu-server.php',
                method:'POST',
                data:{filter:$("#subj-filter").val()},
                success:function(data) {
                    $(".this").html(data);
                }   
            });
        }
        $("#subj-filter").change(function(){
            get_subj_id();
        });
    });
</script>

<div class='container bg-success my-2'>
    <div class='row'>
        <select class='form-control my-2 mx-2' id='subj-filter'>
            <option selected disabled>**Select school year**
            <?php
                if(mysqli_num_rows($select_options) >0) {
                    while($row =mysqli_fetch_assoc($select_options)) {
                        printf('<option value="%1$s">%1$s',$row['sy']);
                    }
                }
            ?>
        </select>
        <table class='table table-light table-sm'>
            <thead>
                <th>ID</th>
                <th>Code</th>
                <th>Description</th>
            </thead>
            <tbody class='this'><!-- new content here--></tbody>
        </table>
    </div>
</div>

    The PHP.

    The original SQL was vulnerable to SQL injection – always use prepared statements when dealing with user supplied input!

    <?php

    error_reporting( E_ALL );
    
    if( $_SERVER['REQUEST_METHOD']=='POST' && isset( $_POST['filter'] ) ) {
        
        ob_clean();// remove anything from response that is not of our making below
        
        include('includes.html');
        include('conn.php');
        
        mysqli_report( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
        $conn=mysqli_connect('localhost','root','','neu');
        
        try{
    
            $sql='select `id`, `description`, `code` from `subjects` where `sy`=?';
            
            $stmt=$conn->prepare($sql);
            $stmt->bind_param('s',$_POST['filter']);
            $stmt->execute();
            $stmt->bind_result( $id, $desc, $code );

            while( $stmt->fetch() ) {
                printf('
                    <tr>
                        <td>%s</td>
                        <td>%s</td>
                        <td>%s</td>
                    </tr>',
                    $id,
                    $code,
                    $desc
                );
            }
            $stmt->free_result();
            $stmt->close();
            
            exit();
            
        }catch( mysqli_sql_exception $e ){
            exit('error.. ');
        }
    } 
?>
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search