How to fetch ALL data from database by selecting ALL in dropdown list-CodePudding

I have a Database contain 3 Tables, the Tables are: (products, Directive and Standard) Each device is manufactured according to a Directive and Standards, I want to creat a webpage with a Dropdown list, where I can select a Device to see according to which directive and Standards made. I alreadz done these, but I can select only one Device. how can I show a list of all devices when I press at Select all ??

<?php
    $con = mysqli_connect("test", "test", "test", "test");
    $sql = "SELECT DISTINCT product_name from products";
    $res = mysqli_query($con, $sql);
?>




<!DOCTYP html>
<html>
    <title></title>
    <script type="text/javascript" src="fetchnDisp.js"></script>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
    <style type="text/css">
        table{
            border: 1px solid;
            border-collapse: collapse;
            padding: 10px;
        }
        th, td, tr{
            border: 1px solid;
        }
        tr:nth-child(even) {
  background-color: #D6EEEE;
}

    </style>
</head>
<body>
    select Device :
    <select id="products" onchange="selectDevice()">
    <option value="">Please Choose...</option>
    <option value="all">Select All...</option>
        <?php while( $rows = mysqli_fetch_array($res)){
            ?>
            <option value="<?php echo $rows['product_name'];   ?>   " > <?php echo $rows['product_name'];  ?> </option>
        <?php    
        } 
        ?>   
    
    <select>

        <table>
        <thead>
            <th style="width: 10%"> Article Number </th>
            <th style="width: 10%"> Directive </th>
            <th style="width: 10%"> Note </th>
            <th style="width: 10%"> Standard </th>
        </thead>
        <tbody id="ans">
        </tbody>
    </table>

</body>
</html>

here is the Javascript file

function selectDevice(){

    var x = document.getElementById("products").value;

    $.ajax({
        url:"showDevice.php",
        method: "POST",
        data:{
            id : x
        },
        success:function(data){
            $("#ans").html(data);
        }
    })
     
}

here is the PHP file

<?php
    $k = $_POST['id'];
    $k = trim($k);
    $con = mysqli_connect("localhost", "test", "test", "woehler");
    $sql = "SELECT art_nr, product_name, directive, products.note, standards.standard_name FROM woehler.products left join standards on products.standard_id=standards.id WHERE product_name='{$k}'";
    $res = mysqli_query($con, $sql);
    while($rows = mysqli_fetch_array($res)){
?>
    <tr>
        <td><?php echo $rows['art_nr'];  ?> </td>
        <td><?php echo $rows['directive'];  ?> </td>
        <td><?php echo $rows['note'];  ?> </td>
        <td><?php echo $rows['standard_name'];  ?> </td>
    </tr>

<?php
    }
    echo $sql;
?>

CodePudding user response：

in your PHP file rewove WHERE product_name='{$k}' if value of $k equal to "all"

 $sql = "SELECT art_nr, product_name, directive, products.note, standards.standard_name FROM woehler.products left join standards on products.standard_id=standards.id";

if($k != 'all'){
    $sql = "SELECT art_nr, product_name, directive, products.note, standards.standard_name FROM woehler.products left join standards on products.standard_id=standards.id WHERE product_name='{$k}'";
}

CodePudding user response：

I used if elseif, and that's how it works

if ($k !="") {
    $sql = "SELECT art_nr, product_name, directive, products.note, standards.standard_name FROM woehler.products left join standards on products.standard_id=standards.id WHERE product_name='{$k}'";}
elseif ($k != "all") {
    $sql = "SELECT art_nr, product_name, directive, products.note, standards.standard_name FROM woehler.products left join standards on products.standard_id=standards.id";}

CodePudding user response：

<?php
$k = $_POST['id'];
$k = trim($k);
$con = mysqli_connect("localhost", "test", "test", "woehler");
$sql = "SELECT art_nr, product_name, directive, products.note, standards.standard_name FROM woehler.products left join standards on products.standard_id=standards.id WHERE product_name='{$k}'";
$res = mysqli_query($con, $sql);
$html = '';
while($rows = mysqli_fetch_array($res)){
$html .= '<tr>
    <td>'.$rows['art_nr'].'</td>
    <td>'.$rows['directive'].'</td>
    <td>'.$rows['note'].'</td>
    <td>'.$rows['standard_name'].'</td>
</tr>';
}
echo $html;
?>

Happy Coding!