Shevcenko Shevcenko -4 years ago 159
jQuery Question

retrieve multiple data from 2 tables with php and jquery

I got this code from Indonesian programmer, this code is retrieving multiple data from 1 table only.

my question is how to retrieve multiple data from 2 tables and save them to another table

result in this picture that what I want to achieve http://i58.photobucket.com/albums/g248/shevcenkoandry/11_zpsqbbkotcy.jpg

here are the table

**request**
prNo branch
pr03 odessa
pr04 kiev


**detail_request**
prNo productCode productName qty
pr03 111 soap 1200
pr03 112 tooth paste 1000


I want to save data on detail_request table to purchase table, but only the data that have check mark on the checkbox, and I'm adding price column to be fill manually http://i58.photobucket.com/albums/g248/shevcenkoandry/11_zpsqbbkotcy.jpg

**purchase**
prNo productCode productName qty price
- - - - -


here is the code

<html>
<head>
<title>Lookup Modal Bootstrap 3</title>
<link rel="stylesheet" href="bootstrap/css/bootstrap.css"/>
<link rel="stylesheet" href="datatables/dataTables.bootstrap.css"/>
<style>
body{
margin: 15px;
}
.pick:hover{
cursor: pointer;
}
</style>
</head>
<body>
<div class="row">
<div class="col-md-5">
<h2>&nbsp;</h2>
</div>
</div>
<form action="action" onsubmit="dummy();
return false">
<div class="form-group">
<label for="varchar">Request Number</label>
<div class="row">
<div class="col-md-4">
<input type="text" class="form-control" name="prNo" id="prNo" placeholder="Request Number" readonly />
<strong>Branch Name</strong><br>
<input type="text" class="form-control" name="branch" id="branch" placeholder="branch " readonly />

</div>

<div class="col-md-2">

<button type="button" class="btn btn-default" data-toggle="modal" data-target="#myModal">. . .</button>
</div>
</div>
</div>
<table width="446" border="1">
<tr>
<th scope="row">&nbsp;</th>
<th scope="row">Request Number</th>
<td><strong>Product Code</strong></td>
<td><strong>Product Name</strong></td>
<td><strong>QTY</strong></td>
<td><strong>Price</strong></td>
</tr>
<tr>
<th scope="row"><input type="checkbox" name="prNo" id="prNo"></th>
<th scope="row"><label for="Request Number"></label>
<input type="text" name="prNo" id="prNo"></th>
<td><label for="productCode"></label>
<input type="text" name="productCode" id="productCode"></td>
<td><label for="productName"></label>
<input type="text" name="productName" id="productName"></td>
<td><label for="qty"></label>
<input type="text" name="qty" id="qty"></td>
<td><input type="text" name="price" id="price"></td>
</tr>
<tr>
<th scope="row"><input type="checkbox" name="prNo4" id="prNo4"></th>
<th scope="row"><input type="text" name="prNo2" id="prNo2"></th>
<td><input type="text" name="productCode2" id="productCode2"></td>
<td><input type="text" name="productName2" id="productName2"></td>
<td><input type="text" name="qty2" id="qty2"></td>
<td><input type="text" name="price2" id="price2"></td>
</tr>
</table>


<input type="submit" value="Save" class="btn btn-primary" />


</form>

<!-- Modal -->
<div class="modal fade" id="myModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
<div class="modal-dialog" style="width:800px">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button>
<h4 class="modal-title" id="myModalLabel">Lookup </h4>
</div>
<div class="modal-body">
<table id="lookup" class="table table-bordered table-hover table-striped">
<thead>
<tr>
<th>Request Number</th>
<th>Branch Name</th>
</tr>
</thead>
<tbody>
<?php

$con = mysqli_connect('localhost', 'root', '', 'purchase');
$sql = mysqli_query($con,'select * from request ');
while ($r = mysqli_fetch_array($sql)) {
?>
<tr class="pick" no="<?php echo $r['prNo']; ?>", branch="<?php echo $r['branch'] ?>", code="<?php echo $r['productCode'] ?>">
<td><?php echo $r['prNo']; ?></td>
<td><?php echo $r['branch']; ?></td>
</tr>
<?php
}
?>
</tbody>
</table>
</div>
</div>
</div>
</div>
<script src="js/jquery-1.11.2.min.js"></script>
<script src="bootstrap/js/bootstrap.js"></script>
<script src="datatables/jquery.dataTables.js"></script>
<script src="datatables/dataTables.bootstrap.js"></script>
<script type="text/javascript">


$(document).on('click', '.pick', function (e) {
document.getElementById("prNo").value = $(this).attr('no');
document.getElementById("branch").value = $(this).attr('branch');

$('#myModal').modal('hide');
});



$(function () {
$("#lookup").dataTable();
});


</script>
</body>
</html>


need your help

thanks in advanced

Answer Source
<?php
if(isset($_POST['product_submit']))
{
    $check=$_POST['check'];
    foreach($check as $i)
    {
        $prno=$_POST['prNo'.$i];
        $prcode=$_POST['productCode'.$i];
        $prname=$_POST['productName'.$i];
        $qty=$_POST['qty'.$i];
        $price=$_POST['price'.$i];
        $query = mysqli_query($con,"insert into purchase (prNo,productCode,productName,qty,price) value ('$prno', '$prcode', '$prname', '$qty', '$price')");
    }
    if($query)
    {
    ?>
    <script>
    alert("success");
    </script>
    <?php
    }
}
?>

<html>
    <head>
        <title>Lookup Modal Bootstrap 3</title>
    </head>
    <body>
        <div class="row">
            <div class="col-md-5">
                <h2>&nbsp;</h2>
            </div>
        </div>
        <form method="post" id="my_form">
            <div class="form-group">
                <label for="varchar">Request Number</label>
                <div class="row">
                    <div class="col-md-4">
                        <input type="text" class="form-control" name="prNo" id="prNo" placeholder="Request Number" readonly />
                          <strong>Branch Name</strong><br>
                          <input type="text" class="form-control" name="branch" id="branch" placeholder="branch " readonly />

                    </div>

                    <div class="col-md-2">

                      <button type="button" class="btn btn-default" data-toggle="modal" data-target="#myModal">. . .</button>
                      <button type="button" class="btn btn-default" onclick="show_fun()">View</button>
                    </div>
                </div>
            </div>

<table width="446" border="1">
  <thead>
  <tr>
    <th scope="row">&nbsp;</th>
    <th scope="row">Request Number</th>
    <td><strong>Product Code</strong></td>
    <td><strong>Product Name</strong></td>
    <td><strong>QTY</strong></td>
    <td><strong>Price</strong></td>
    <td><strong>Total</strong></td>
  </tr>
  </thead>
  <tbody id="product_table">

  </tbody>
</table>


              <input type="submit" value="Save" name="product_submit" class="btn btn-primary" />


        </form>

        <!-- Modal -->
        <div class="modal fade" id="myModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
            <div class="modal-dialog" style="width:800px">
                <div class="modal-content">
                    <div class="modal-header">
                        <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button>
                        <h4 class="modal-title" id="myModalLabel">Lookup </h4>
                    </div>
                    <div class="modal-body">
                        <table id="lookup" class="table table-bordered table-hover table-striped">
                            <thead>
                                <tr>
                                    <th>Request Number</th>
                                    <th>Branch Name</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php

                                $con = mysqli_connect('localhost', 'root', '', 'purchase');
                                $sql = mysqli_query($con,'select * from request ');
                                while ($r = mysqli_fetch_array($sql)) {
                                    ?>
                                    <tr class="pick" no="<?php echo $r['prNo']; ?>", branch="<?php echo $r['branch'] ?>", code="<?php echo $r['productCode'] ?>">
                                        <td><?php echo $r['prNo']; ?></td>
                                        <td><?php echo $r['branch']; ?></td>
                                    </tr>
                                    <?php
                                }
                                ?>
                            </tbody>
                        </table>  
                    </div>
                </div>
            </div>
        </div>
        <script src="js/jquery-1.11.2.min.js"></script>
        <script src="bootstrap/js/bootstrap.js"></script>
        <script src="datatables/jquery.dataTables.js"></script>
        <script src="datatables/dataTables.bootstrap.js"></script>
        <script type="text/javascript">


            $(document).on('click', '.pick', function (e) {
                document.getElementById("prNo").value = $(this).attr('no');
                document.getElementById("branch").value = $(this).attr('branch');

                var no=$(this).attr('no');

                $.ajax({
                    type:"post",
                    data:"&product_id=1&prno="+no,
                    url:"ajax.php",
                    success:function(result)
                    {
                        $("#product_table").html(result);
                    }
                });
                $('#myModal').modal('hide');
            });

            $(function () {
                $("#lookup").dataTable();
            });

            function calc_fun(i){
             var qty=$("#qty"+i).val();
             var price=$("#price"+i).val();
             var total=Number(qty)*Number(price);
             $("#total"+i).val(total);
            }

        </script>
    </body>
</html>

ajax.php

<?php
if(isset($_POST['product_id']))
{
    $prno=$_POST['prno'];
    $i=1;
    $sql = mysqli_query($con,"select * from detail_request where prNo='$prno'");
    while ($r = mysqli_fetch_array($sql)) {
        echo '<tr>
    <th scope="row"><input type="checkbox" name="check[]" id="check'.$i.'" value="'.$i.'"></th>
    <th scope="row"><label for="Request Number"></label>
      <input type="text" name="prNo'.$i.'" id="prNo'.$i.'" readonly value="'.$r["prNo"].'"></th>
    <td><label for="productCode"></label>
      <input type="text" name="productCode'.$i.'" id="productCode'.$i.'" readonly value="'.$r["productCode"].'"></td>
    <td><label for="productName"></label>
      <input type="text" name="productName'.$i.'" id="productName'.$i.'" readonly value="'.$r["productName"].'"></td>
    <td><label for="qty"></label>
      <input type="text" name="qty'.$i.'" id="qty'.$i.'" onchange="calc_fun('.$i.')" readonly value="'.$r["qty"].'"></td>
    <td><input type="text" name="price'.$i.'" onchange="calc_fun('.$i.')" id="price'.$i.'"></td>
    <td><input type="text" name="total'.$i.'" id="total'.$i.'"></td>
  </tr>';
  $i++;
    }
}
?>
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download