By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,745 Members | 1,897 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,745 IT Pros & Developers. It's quick & easy.

Alternative of DLookup in PHP

P: 40
Please help me to fetch data from table B to table A:

Its my input form coding(insertdata.php):
Expand|Select|Wrap|Line Numbers
  1.     <?php include ( 'header.html');
  2.          include ('selectdriver.php'); 
  3.          include ('selectdestination.php'); 
  4.          include ('selectloading.php'); 
  5.  
  6.     ?>
  7.  
  8.     <head>
  9.     <form name="regisform" action="insertdata.php" method="POST" valign="top" >
  10.             <h3> ADD RECORDS</h3>
  11.  
  12.             &nbsp; &nbsp;Select Date :&nbsp; &nbsp; &nbsp;<input type="date" name="date" value=""  /> 
  13.             &nbsp; &nbsp;Select Driver :&nbsp; <select name="driver" style="width: 163px; align: left">
  14.                 <?php 
  15.                 foreach ($name as $value):
  16.                     echo '<option value="' . $value . '">' . $value . '</option>';
  17.                 endforeach;
  18.                 ?>
  19.                 </select><br/>  <br/>  
  20.  
  21.             &nbsp; &nbsp;Select Truck No:&nbsp; &nbsp;<input type="text"  name="truck_no" value="" style="width: 170px; align: left"  /><br/>  <br/>   
  22.  
  23.           &nbsp; &nbsp;Loading Point: <select name="loading" style="width: 163px; align: left">
  24.                 <?php 
  25.                 foreach ($loading as $value):
  26.                     echo '<option value="' . $value . '">' . $value . '</option>';
  27.                 endforeach;
  28.                 ?>
  29.                 </select><br/>  <br/>       
  30.  
  31.            &nbsp; &nbsp;Destination :&nbsp;&nbsp;&nbsp;  &nbsp;   &nbsp;   &nbsp;   &nbsp; <select name="destination" style="width: 163px; align: left">
  32.                 <?php 
  33.                 foreach ($destination as $value):
  34.                     echo '<option value="' . $value . '">' . $value . '</option>';
  35.                 endforeach;
  36.                 ?>
  37.                 </select><br/>  <br/>       
  38.  
  39.              &nbsp; &nbsp;1st Drop :&nbsp;&nbsp;  &nbsp;   &nbsp;   &nbsp;   &nbsp; <select name="dropone" style="width: 100px; align: left">
  40.                 <option value="KL">KL</option>
  41.                 <option value="Shah Alam">Shah Alam</option>
  42.               </select>
  43.  
  44.              &nbsp; &nbsp;2nd Drop :&nbsp;&nbsp;  &nbsp;   &nbsp;   &nbsp;   &nbsp; <select name="droptwo" style="width: 100px; align: left">
  45.                 <option value="KL">KL</option>
  46.                 <option value="Shah Alam">Shah Alam</option>
  47.  
  48.               </select>
  49.  
  50.              &nbsp; &nbsp;3rd Drop :&nbsp;&nbsp;  &nbsp;   &nbsp;   &nbsp;   &nbsp; <select name="dropthree" style="width: 100px; align: left">
  51.                 <option value="KL">KL</option>
  52.                 <option value="Shah Alam">Shah Alam</option>
  53.  
  54.               </select>
  55.  
  56.              &nbsp; &nbsp;4th Drop :&nbsp;&nbsp;  &nbsp;   &nbsp;   &nbsp;   &nbsp; <select name="dropfour" style="width: 100px; align: left">
  57.                 <option value="KL">KL</option>
  58.                 <option value="Shah Alam">Shah Alam</option>
  59.  
  60.               </select>
  61.              &nbsp; &nbsp;5th Drop :&nbsp;&nbsp;  &nbsp;   &nbsp;   &nbsp;   &nbsp; <select name="dropfive" style="width: 100px; align: left">
  62.                 <option value="KL">KL</option>
  63.                 <option value="Shah Alam">Shah Alam</option>
  64.  
  65.               </select>
  66.              &nbsp; &nbsp;6th Drop :&nbsp;&nbsp;  &nbsp;   &nbsp;   &nbsp;   &nbsp; <select name="dropsix" style="width: 100px; align: left">
  67.                 <option value="KL">KL</option>
  68.                 <option value="Shah Alam">Shah Alam</option>
  69.               </select>
  70.  
  71.              &nbsp; &nbsp;7th Drop :&nbsp;&nbsp;  &nbsp;   &nbsp;   &nbsp;   &nbsp; <select name="dropseven" style="width: 100px; align: left">
  72.                 <option value="KL">KL</option>
  73.                 <option value="Shah Alam">Shah Alam</option>
  74.                </select>       <br/>                  <br/>        
  75.  
  76.             &nbsp; &nbsp;DO No :&nbsp;&nbsp;&nbsp;  &nbsp;   &nbsp;   &nbsp;   &nbsp; <input type="number" name="do_no" value="" />  
  77.                   <br/> 
  78.             <br/> 
  79.             &nbsp; &nbsp;Select Drops :&nbsp;&nbsp;&nbsp;  &nbsp;  <select name="drops" style="width: 173px; align: left">
  80.                 <option value="1">1</option>
  81.                 <option value="2">2</option>
  82.                 <option value="3">3</option>
  83.                  <option value="4">4</option>
  84.                 <option value="5">5</option>
  85.                 <option value="6">6</option>
  86.                 <option value="7">7</option>
  87.               </select>
  88.  
  89.  
  90.                   <br/><br/> 
  91.             &nbsp; &nbsp;Commission :&nbsp;&nbsp;&nbsp;  &nbsp;   &nbsp;   &nbsp;   &nbsp; <input type="text" name="commission" value="" /> <br/>    
  92.                   <br/> 
  93.  
  94.     &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; <input type="submit" value="Save" name="Save" /> 
  95.      &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; <input type="submit" value="Delete" name="Delete" /><br/> <br/> <br/> <br/>  
  96.             </form>
  97.  
  98.             <?php
  99.  
  100.  
  101.             if($_SERVER['REQUEST_METHOD']=='POST')
  102.             {
  103.                 $erArr = array();             
  104.                 require 'connectdb.php';
  105.  
  106.                 $id= isset($_POST['id']) ? $_POST['id'] : '';
  107.                 $date= $_POST['date'];
  108.                 $driver= $_POST['driver']; 
  109.                 $truck_no= $_POST['truck_no'];
  110.                 $loading= isset($_POST['loading']) ? $_POST['loading'] : '';
  111.                 $destination= $_POST['destination'];
  112.                 $dropone= isset($_POST['dropone']) ? $_POST['dropone'] : '';
  113.                 $droptwo= isset($_POST['droptwo']) ? $_POST['droptwo'] : '';
  114.                 $dropthree= isset($_POST['dropthree']) ? $_POST['dropthree'] : '';
  115.                 $dropfour= isset($_POST['dropfour']) ? $_POST['dropfour'] : '';
  116.                 $dropfive= isset($_POST['dropfive']) ? $_POST['dropfive'] : '';
  117.                 $dropsix= isset($_POST['dropsix']) ? $_POST['dropsix'] : '';
  118.                 $dropseven= isset($_POST['dropseven']) ? $_POST['dropseven'] : '';
  119.                 $do_no= $_POST['do_no'];
  120.                 $drops= $_POST['drops'];
  121.                 $commission= $_POST['commission'];
  122.                 $total= ($drops*10 + $commission);
  123.                 if(empty($_POST['driver']))
  124.                 {
  125.                     $erArr[] ="Mandatory field 'Name' is missing <br/>";
  126.                 }
  127.  
  128.                 foreach ($erArr as $msg) //foreach is used here to process the array
  129.                     {
  130.                         echo $msg;
  131.                     }   
  132.               $sql = "INSERT INTO "
  133.                       . "main (id, date, driver, truck_no, loading, destination, dropone, droptwo, dropthree, dropfour, dropfive, dropsix, dropseven, do_no, drops, commission, total ) VALUES "
  134.                       . "('$id', '$date', '$driver', '$truck_no', '$loading', '$destination', '$dropone', '$droptwo', '$dropthree','$dropfour','$dropfive','$dropsix','$dropseven','$do_no', '$drops', '$commission', '$total' )"; 
  135.  
  136.               if(!empty($driver))
  137.                  {
  138.                     $insertdb = mysqli_query($dbc, $sql);
  139.                     echo mysqli_error($dbc);//for trobleshooting eror
  140.                     if($insertdb ==1)
  141.                     {
  142.                         echo"The Database has been updated successfully"  . "<br/>";
  143.                     }
  144.                         else 
  145.                             {
  146.                     echo "Please check the error";
  147.                     echo mysqli_error($dbc);
  148.                     }
  149.  
  150.                  } 
  151.             }
  152.  
  153.             ?>
  154.  
  155.     <?Php
  156.     include 'connectdb.php';
  157.  
  158.     $count="SELECT id, date, driver, truck_no, loading, destination, dropone,droptwo, dropthree, dropfour, dropfive, dropsix, dropseven, do_no, drops, commission, total from main group by id";
  159.  
  160.     echo "<table border =1  bgcolor=white width=1200px fontsize=10>";
  161.     echo "<tr ><th>id</th><th>Date</th><th>Driver</th><th>Truck No.</th><th>Loading</th><th>Destination</th><th>1st Drop</th><th>2nd Drop</th><th>3rd Drop</th><th>4th Drop</th><th>5th Drop</th><th>6th Drop</th><th>7th Drop</th><th>DO No/RM</th><th>Drops</th><th>Commission</th><th>Total</td></tr>";
  162.     foreach ($dbc->query($count) as $row) {
  163.     echo "<tr ><td>$row[id]</td><td>$row[date]</td><td>$row[driver]</td><td>$row[truck_no]</td><td>$row[loading]</td><td>$row[destination]</td><td>$row[dropone]</td><td>$row[droptwo]</td><td>$row[dropthree]</td><td>$row[dropfour]</td><td>$row[dropfive]</td><td>$row[dropsix]</td><td>$row[dropseven]</td><td>$row[do_no]</td><td>$row[drops]</td><td>$row[commission]</td><td>$row[total]</td></tr>";
  164.  
  165.     }
  166.     echo "</table>";
  167.     ?>
  168.  
  169.  
Now i have to put commission manually in commission field of table A (main). I want this commission field fetch the commission from table B (comm) when destination is selected.
Please check it and advice accordingly.
Table A name is: main
Table B name is: comm
May 8 '15 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 1,035
you have a select statement like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.   id, 
  3.   date, 
  4.   driver, 
  5.   truck_no, 
  6.   loading, 
  7.   destination, 
  8.   dropone,
  9.   droptwo, 
  10.   dropthree, 
  11.   dropfour, 
  12.   dropfive, 
  13.   dropsix, 
  14.   dropseven, 
  15.   do_no, 
  16.   drops, 
  17.   commission, 
  18.   total 
  19. from main 
  20. group by id
and you do not want to get commission from table main, but from table comm,

than you should do:
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.   id, 
  3.   date, 
  4.   driver, 
  5.   truck_no, 
  6.   loading, 
  7.   destination, 
  8.   dropone,
  9.   droptwo, 
  10.   dropthree, 
  11.   dropfour, 
  12.   dropfive, 
  13.   dropsix, 
  14.   dropseven, 
  15.   do_no, 
  16.   drops, 
  17.   comm.commission, 
  18.   total 
  19. from main
  20. join comm on <tell SQL how these tables are linked> 
  21. group by id
You only have to know with which selection criteria you can select the correct commission (the '<tell SQL how these tables are linked>'-part)
May 8 '15 #2

P: 40
Sorry for late reply.
Can you help me more please in writing sql query...
Do i need to join the tables as well?
plz plz help me.
Thanks in advance
May 12 '15 #3

Expert 100+
P: 1,035
I can help, but I don't know what help you need.... ;)

You only need to join tables when information is needed from more than one table.

If you want information from table A, you can do
SELECT a.id FROM main a

If you also need some info from table B, than:
SELECT a.id, b.somefield
FROM main a
JOIN comm b ON a.id = b.id


After the 'ON'-clause you should write the proper expression that gives information on how the tables 'main' and 'comm' are related to each other.
May 14 '15 #4

Post your reply

Sign in to post your reply or Sign up for a free account.