title: PHP Form MySql Update Table with Foreign Keys
body: <p>I'm building a website that holds, laptop and customer information held in separate tables.
  The structure of the <strong>customer</strong> table is;</p>
  <pre><code>ID, 
  Name, 
  Manager, 
  Email 
  </code></pre>
  <p>The structure of the laptops table is;</p>
  <pre><code>ID, 
  Asset_No, 
  Make, 
  Model, 
  RAM, 
  CPU, 
  MAC_Address, 
  Serial_Number, 
  Product_Number, 
  User_ID, 
  Location, 
  Assigned, 
  Faulty, 
  Decommissioned, 
  History
  </code></pre>
  <p>Where customer.ID == laptops.user_id</p>
  <p>I have a separate page for displaying the laptops data, from updating the data.</p>
  <p>On my update page, I want to be able to have a text field that pulls the customer.name and customer.ID data, into 2 separate fields, so that if i want to update the user assigned, I can use a  drop down field populated with user name, which will live update the value for customer.ID so that I can update the laptops.user_id field.
  <a href="https://i.stack.imgur.com/sM3B8.png" rel="nofollow noreferrer">update snip1</a></p>
  <p><a href="https://i.stack.imgur.com/sitZR.png" rel="nofollow noreferrer">update snip2</a>
  <a href="https://i.stack.imgur.com/HcJVl.png" rel="nofollow noreferrer">laptops display</a></p>
  <p>Here is my updater page &quot;update.php&quot;</p>
  <pre><code>&lt;?php
  include 'functions.php';
  $pdo = pdo_connect_mysql();
  $con = mysqli_connect(&quot;localhost&quot;,&quot;root&quot;,&quot;&quot;,&quot;appollo&quot;);
  
  $sql = &quot;SELECT * FROM `category`&quot;;
  $sql2 = &quot;SELECT (Name) FROM bexstaff WHERE Name = ? &quot;;
  $sql3 = &quot;Select Name FROM Customer ORDER BY Name Asc&quot;;
  $sql4 = &quot;Select ID FROM Customer &quot;;
  $all_categories = mysqli_query($con,$sql3);
  $getIDs = mysqli_query($con,$sql4);
  
  $msg = '';
  
  if (isset($_GET['Asset_No'])) {
      if (!empty($_POST)) {
          // This part is similar to the create.php, but instead we update a record and not insert
          $Asset_No = isset($_POST['Asset_No']) ? $_POST['Asset_No'] : '';
          $Make = isset($_POST['Make']) ? $_POST['Make'] : '';
          $Model = isset($_POST['Model']) ? $_POST['Model'] : '';
          $RAM = isset($_POST['RAM']) ? $_POST['RAM'] : '';
          $CPU = isset($_POST['CPU']) ? $_POST['CPU'] : '';
          $MAC_Address = isset($_POST['MAC_Address']) ? $_POST['MAC_Address'] : '';
          $Serial_Number = isset($_POST['Serial_Number']) ? $_POST['Serial_Number'] : '';
          $Product_Number = isset($_POST['Product_Number']) ? $_POST['Product_Number'] : '';
          $User = isset($_POST['User_ID']) ? $_POST['User_ID'] : '';
          $UserID = isset($_POST['ID']) ? $_POST['ID'] : '';
          $Location = isset($_POST['Location']) ? $_POST['Location'] : '';
          $Assigned = isset($_POST['Assigned']) ? $_POST['Assigned'] : '';
          $Faulty = isset($_POST['Faulty']) ? $_POST['Faulty'] : '';
          $Decommissioned = isset($_POST['Decommissioned']) ? $_POST['Decommissioned'] : '';
          $History = isset($_POST['History']) ? $_POST['History'] : '';
          
          $stmt = $pdo-&gt;prepare('UPDATE Laptops SET Asset_No = ?, Make = ?, Model = ?, RAM = ?, CPU = ?, MAC_Address = ?, Serial_Number = ?, Product_Number = ?, User_ID = ?, Location = ?, Assigned = ?, Faulty = ?, Decommissioned = ?, History = ? WHERE Asset_No = ?');
          $stmt-&gt;execute([$Asset_No, $Make, $Model, $RAM, $CPU, $MAC_Address, $Serial_Number, $Product_Number, $UserID, $Location, $Assigned, $Faulty, $Decommissioned, $History, $_GET['Asset_No']]);
          $msg = 'Updated Successfully!';
  
      }
      $stmt = $pdo-&gt;prepare('SELECT `Asset_No`, `Make`, `Model`, `RAM`, `CPU`, `MAC_Address`, `Serial_Number`, `Product_Number`, `User_ID`, `Location`, `Assigned`, `Faulty`, `Decommissioned`, `History` FROM `laptops`s WHERE Asset_No = ?');
      $stmt-&gt;execute([$_GET['Asset_No']]);
      $laptops = $stmt-&gt;fetch(PDO::FETCH_ASSOC);
  
      if (!$laptops) {
          exit('Contact doesn\'t exist with that ID!');
      }
  } else {
      exit('No ID specified!');
  }
  
  
  
  ?&gt;
  
  &lt;head&gt;
             &lt;link rel=&quot;preconnect&quot; href=&quot;https://fonts.googleapis.com&quot;&gt;
             &lt;link rel=&quot;preconnect&quot; href=&quot;https://fonts.gstatic.com&quot; crossorigin&gt;
             &lt;link href=&quot;https://fonts.googleapis.com/css2?family=Glory:wght@300&amp;display=swap&quot; rel=&quot;stylesheet&quot;&gt;
             &lt;script type=&quot;text/javascript&quot; src=&quot;https://www.gstatic.com/charts/loader.js&quot;&gt;&lt;/script&gt;  
             &lt;script type=&quot;text/javascript&quot;&gt;&lt;/script&gt;
             &lt;script src=&quot;https://ajax.googleapis.com/ajax/jquery/3.1.0/jquery.min.js&quot;&gt;&lt;/script&gt;
  
  &lt;/head&gt;
  
  &lt;script type=&quot;text/javascript&quot;&gt;
      $(document).ready(function () {     
  $('select[name=&quot;User&quot;]').change(function(){
     var ID = $('option:selected', this).attr('ID');
     $(&quot;#ID&quot;).val(ID);
  });
  
  &lt;/script&gt;
  
  
  &lt;?=template_header('Read')?&gt;
  
  &lt;?php function get_id($con){
      $output = '';
      $functionsql = &quot;SELECT * FROM customer&quot;;
      $functionresult = mysqli_query($con, $functionsql);
  
      while ($row = mysqli_fetch_array($functionresult)){
          $output .= '&lt;option value=&quot;'.$row[&quot;ID&quot;].'&quot;&gt;'.$row[&quot;Name&quot;].'&lt;/option&gt;';
      }
      
      return $output;
  
  }
  
  function show_id($con){
      $output2 = '';
      $functionsql2 = &quot;SELECT ID FROM customer WHERE Name = '' &quot;;
      $functionresult2 = mysqli_query($con, $functionsql2);
  
      while ($row = mysqli_fetch_array($functionresult2)){
          $output2 .= ''.$row[&quot;ID&quot;].'';
      }
      
      return $output2;
  
  }
  
   ?&gt;
  
  &lt;div class=&quot;content update&quot;&gt;
      &lt;h2&gt;Update Asset &lt;?=$laptops['Asset_No']?&gt;&lt;/h2&gt;
      &lt;form action=&quot;update.php?Asset_No=&lt;?=$laptops['Asset_No']?&gt;&quot; method=&quot;post&quot;&gt;
          &lt;label&gt;Laptop Number&lt;/label&gt;&lt;input type=&quot;Asset_No&quot; name=&quot;Asset_No&quot; value=&quot;&lt;?=$laptops['Asset_No']?&gt;&quot; id=&quot;Asset_No&quot; readonly&gt;
          &lt;label&gt;Make&lt;/label&gt;&lt;input type=&quot;Make&quot; name=&quot;Make&quot; value=&quot;&lt;?=$laptops['Make']?&gt;&quot; id=&quot;Make&quot; readonly&gt;
          &lt;label&gt;Model&lt;/label&gt;&lt;input type=&quot;Model&quot; name=&quot;Model&quot; value=&quot;&lt;?=$laptops['Model']?&gt;&quot; id=&quot;Model&quot; readonly&gt;
          &lt;label&gt;RAM&lt;/label&gt;&lt;input type=&quot;RAM&quot; name=&quot;RAM&quot; value=&quot;&lt;?=$laptops['RAM']?&gt;&quot; id=&quot;RAM&quot;&gt;
          &lt;label&gt;CPU&lt;/label&gt;&lt;input type=&quot;CPU&quot; name=&quot;CPU&quot; value=&quot;&lt;?=$laptops['CPU']?&gt;&quot; id=&quot;CPU&quot; readonly&gt;
          &lt;label&gt;MAC Address&lt;/label&gt;&lt;input type=&quot;MAC_Address&quot; name=&quot;MAC_Address&quot; value=&quot;&lt;?=$laptops['MAC_Address']?&gt;&quot; id=&quot;MAC_Address&quot; &gt;
          &lt;label&gt;Serial Number&lt;/label&gt;&lt;input type=&quot;Serial_Number&quot; name=&quot;Serial_Number&quot; value=&quot;&lt;?=$laptops['Serial_Number']?&gt;&quot; id=&quot;Serial_Number&quot; readonly&gt;
          &lt;label&gt;Product Number&lt;/label&gt;&lt;input type=&quot;Product_Number&quot; name=&quot;Product_Number&quot; value=&quot;&lt;?=$laptops['Product_Number']?&gt;&quot; id=&quot;Product_Number&quot; readonly&gt;
  
  
          &lt;label&gt;User&lt;/label&gt; 
          &lt;select name=&quot;User&quot; id=&quot;User&quot;&gt;
              &lt;?php    
              ?&gt;
                  &lt;option value=&quot;&lt;?php echo get_id($con);
                  ?&gt;&quot;&gt;
                      &lt;?php   
                      ?&gt;
                  &lt;/option&gt;
              &lt;?php 
              ?&gt;
          &lt;/select&gt;
  
          
          &lt;label&gt;ID&lt;/label&gt;
          &lt;input type=&quot;ID&quot; name=&quot;ID&quot; value='&lt;?php echo show_id($con)?&gt;' id=&quot;ID&quot; &gt;
  
  
      
      
  
  
          &lt;label&gt;Location&lt;/label&gt;&lt;input type=&quot;Location&quot; name=&quot;Location&quot; value=&quot;&lt;?=$laptops['Location']?&gt;&quot; id=&quot;Location&quot; &gt;
          &lt;label&gt;Assigned&lt;/label&gt; 
          &lt;select name=&quot;Assigned&quot;&gt;
          &lt;option &lt;?php if($laptops['Assigned']==&quot;Assigned&quot;) echo &quot;selected&quot;; ?&gt; value=&quot;Assigned&quot;&gt;Assigned&lt;/option&gt;
          &lt;option &lt;?php if($laptops['Assigned']==&quot;Returned&quot;) echo &quot;selected&quot;; ?&gt; value=&quot;Returned&quot;&gt;Returned&lt;/option&gt;
          &lt;/select&gt;
          &lt;label&gt;Faulty&lt;/label&gt;    
          &lt;select name=&quot;Faulty&quot;&gt;
          &lt;option &lt;?php if($laptops['Faulty']==&quot;Faulty&quot;) echo &quot;selected&quot;; ?&gt; value=&quot;Faulty&quot;&gt;Faulty&lt;/option&gt;
          &lt;option &lt;?php if($laptops['Faulty']==&quot;Working&quot;) echo &quot;selected&quot;; ?&gt; value=&quot;Working&quot;&gt;Working&lt;/option&gt;
          &lt;/select&gt;
          &lt;label&gt;Decommissioned&lt;/label&gt;   
          &lt;select name=&quot;Decommissioned&quot;&gt;
          &lt;option &lt;?php if($laptops['Decommissioned']==&quot;Decommissioned&quot;) echo &quot;selected&quot;; ?&gt; value=&quot;Decommissioned&quot;&gt;Decommissioned&lt;/option&gt;
          &lt;option &lt;?php if($laptops['Decommissioned']==&quot;Functioning&quot;) echo &quot;selected&quot;; ?&gt; value=&quot;Functioning&quot;&gt;Functioning&lt;/option&gt;
          &lt;/select&gt;
          &lt;label&gt;History&lt;/label&gt;&lt;input type=&quot;History&quot;  name=&quot;History&quot; value=&quot;&lt;?=$laptops['History']?&gt;&quot; id=&quot;History&quot; rows=&quot;4&quot;&gt;
  
          &lt;input type=&quot;submit&quot; value=&quot;Update&quot;&gt;
          
      &lt;/form&gt;
  
  
  
     
      &lt;?php if ($msg): 
      
      ?&gt;
      &lt;p&gt;&lt;?=$msg?&gt;&lt;/p&gt;
      &lt;?php endif; ?&gt;
  &lt;/div&gt;
  
  &lt;?=template_footer()?&gt;
  </code></pre>
  <p>Here is my laptops display page code &quot;laptops.php&quot;</p>
  <pre><code>&lt;?php
  include 'functions.php';
  $pdo = pdo_connect_mysql();
  $page = isset($_GET['page']) &amp;&amp; is_numeric($_GET['page']) ? (int)$_GET['page'] : 1;
  $records_per_page = 5;
  
  
  if (isset($_GET['search'])) {
      $cmd = $pdo-&gt;prepare('SELECT * 
                              FROM laptops 
                              WHERE Asset_No LIKE ? 
                              OR Faulty LIKE ? 
                              OR Decommissioned LIKE ? 
                              OR Assigned LIKE ? 
                              OR User LIKE ? 
                              OR MAC_Address LIKE ? 
                          ORDER BY Asset_No ASC');
      $cmd-&gt;execute([&quot;%&quot;.$_GET['search'].&quot;%&quot;, &quot;%&quot;.$_GET['search'].&quot;%&quot;, &quot;%&quot;.$_GET['search'].&quot;%&quot;, &quot;%&quot;.$_GET['search'].&quot;%&quot;, &quot;%&quot;.$_GET['search'].&quot;%&quot;, &quot;%&quot;.$_GET['search'].&quot;%&quot;]);
      $laptop = $cmd-&gt;fetchAll(PDO::FETCH_ASSOC);
  } else {
      $stmt = $pdo-&gt;prepare('SELECT * FROM laptops ORDER BY Asset_No');
      $stmt-&gt;execute();
      $laptop = $stmt-&gt;fetchAll(PDO::FETCH_ASSOC);
      $num_laptops = $pdo-&gt;query('SELECT COUNT(*) FROM laptops')-&gt;fetchColumn();
  }
  ?&gt;
  &lt;head&gt;
      &lt;link rel=&quot;preconnect&quot; href=&quot;https://fonts.googleapis.com&quot;&gt;
      &lt;link rel=&quot;preconnect&quot; href=&quot;https://fonts.gstatic.com&quot; crossorigin&gt;
      &lt;link href=&quot;https://fonts.googleapis.com/css2?family=Glory:wght@300&amp;display=swap&quot; rel=&quot;stylesheet&quot;&gt;
  &lt;/head&gt;
  
  &lt;?=template_header('Read')?&gt;
  
  &lt;div class=&quot;content read&quot;&gt;
      &lt;h2&gt;Laptops&lt;/h2&gt;
      &lt;a href=&quot;create.php&quot; class=&quot;create-contact&quot;&gt;Add Device&lt;/a&gt;
      
      &lt;form action=&quot;&quot; method=&quot;GET&quot;&gt;
          &lt;input id=&quot;search&quot; name=&quot;search&quot; type=&quot;text&quot; placeholder=&quot;Type here&quot;&gt;
          &lt;input id=&quot;submit&quot; type=&quot;submit&quot; value=&quot;Search&quot;&gt;
      &lt;/form&gt;
      &lt;br&gt;&lt;/br&gt;
  
      &lt;table&gt;
          &lt;thead&gt;
              &lt;tr&gt;
                  &lt;td&gt;Asset_No&lt;/td&gt;
                  &lt;td&gt;Make&lt;/td&gt;
                  &lt;td&gt;Model&lt;/td&gt;
                  &lt;td&gt;RAM&lt;/td&gt;
                  &lt;td&gt;CPU&lt;/td&gt;
                  &lt;td&gt;MAC_Address&lt;/td&gt;
                  &lt;td&gt;Serial_Number&lt;/td&gt;
                  &lt;td&gt;Product_Number&lt;/td&gt;
                  &lt;td&gt;User&lt;/td&gt;
                  &lt;td&gt;Location&lt;/td&gt;
                  &lt;td&gt;Assigned&lt;/td&gt;
                  &lt;td&gt;Faulty&lt;/td&gt;
                  &lt;td&gt;Decommissioned&lt;/td&gt;
                  &lt;td&gt;History&lt;/td&gt;
                  &lt;td&gt;&lt;/td&gt;
              &lt;/tr&gt;
          &lt;/thead&gt;
          &lt;tbody&gt;
              &lt;?php foreach ($laptop as $laptops): ?&gt;
              &lt;tr&gt;
                  &lt;td&gt;&lt;?=$laptops['Asset_No']?&gt;&lt;/td&gt;
                  &lt;td&gt;&lt;?=$laptops['Make']?&gt;&lt;/td&gt;
                  &lt;td&gt;&lt;?=$laptops['Model']?&gt;&lt;/td&gt;
                  &lt;td&gt;&lt;?=$laptops['RAM']?&gt;&lt;/td&gt;
                  &lt;td&gt;&lt;?=$laptops['CPU']?&gt;&lt;/td&gt;
                  &lt;td&gt;&lt;?=$laptops['MAC_Address']?&gt;&lt;/td&gt;
                  &lt;td&gt;&lt;?=$laptops['Serial_Number']?&gt;&lt;/td&gt;
                  &lt;td&gt;&lt;?=$laptops['Product_Number']?&gt;&lt;/td&gt;
                  &lt;td&gt;&lt;?=$laptops['User_ID']?&gt;&lt;/td&gt;
                  &lt;td&gt;&lt;?=$laptops['Location']?&gt;&lt;/td&gt;
                  &lt;td&gt;&lt;?=$laptops['Assigned']?&gt;&lt;/td&gt;
                  &lt;td&gt;&lt;?=$laptops['Faulty']?&gt;&lt;/td&gt;
                  &lt;td&gt;&lt;?=$laptops['Decommissioned']?&gt;&lt;/td&gt;
                  &lt;td&gt;&lt;?=$laptops['History']?&gt;&lt;/td&gt;
                  &lt;td class=&quot;actions&quot;&gt;
                      &lt;a href=&quot;update.php?Asset_No=&lt;?=$laptops['Asset_No']?&gt;&quot; class=&quot;edit&quot;&gt;&lt;i class=&quot;fas fa-pen fa-xs&quot;&gt;&lt;/i&gt;&lt;/a&gt; 
                    &lt;!--  &lt;a href=&quot;delete.php?Asset_No=&lt;?=$laptops['Asset_No']?&gt;&quot; class=&quot;trash&quot;&gt;&lt;i class=&quot;fas fa-trash fa-xs&quot;&gt;&lt;/i&gt;&lt;/a&gt;  --&gt;
                  &lt;/td&gt;
              &lt;/tr&gt;
              &lt;?php endforeach; ?&gt;
          &lt;/tbody&gt;
      &lt;/table&gt;
  
  
          &lt;?php if ($page &gt; 1): ?&gt;
          &lt;a href=&quot;read.php?page=&lt;?=$page-1?&gt;&quot;&gt;&lt;i class=&quot;fas fa-angle-double-left fa-sm&quot;&gt;&lt;/i&gt;&lt;/a&gt;
          &lt;?php endif; ?&gt;
          &lt;?php if ($page*$records_per_page &lt; $num_laptops): ?&gt;
          &lt;a href=&quot;read.php?page=&lt;?=$page+1?&gt;&quot;&gt;&lt;i class=&quot;fas fa-angle-double-right fa-sm&quot;&gt;&lt;/i&gt;&lt;/a&gt;
          &lt;?php endif; ?&gt;
      &lt;/div&gt;--&gt;
  &lt;/div&gt;
  
  &lt;?=template_footer()?&gt;
  </code></pre>