How to store name of select option instead of value in database

Question

I'm trying to store the name of the select option instead of the value in the MySQL database. The current code is storing customer id instead of customer name in the database. I know some of you will say that why don't you use $customer_result['name'] in the name attribute but If I did this, then I wouldn't be able to filter the record using id

MYSQL and PHP

      <?php
      $select_customer = "select * from customer";
      $select_customer_query = mysqli_query($connection, $select_customer); // Customer Data

      if(isset($_POST['add-sale-btn'])) {
    
    $customer_name = mysqli_real_escape_string($connection, $_POST['customer_name']); 
    $customer_address = mysqli_real_escape_string($connection, $_POST['customer_address']); 


    
    $insert_sale = "insert into sale(customer_name, customer_address) values('$customer_name','$customer_address')";
    $insert_sale_query = mysqli_query($connection, $insert_sale);

}

      ?>

      <form method="post" action="">
      <div class="form-group">
      <select class="form-control" id="customer_name" name="customer_name">
      <option>Customer Name</option>
      <?php while($customer_result = mysqli_fetch_assoc($select_customer_query)) { ?>
      <option value="<?php echo $customer_result['id']; ?>"><?php echo $customer_result['name']; ?></option>
      <?php } ?>
      </select>
      </div>
       <div class="form-group" style="margin-top:10px;">
      <input type="submit" class="btn btn-primary" name="add-sale-btn" value="Save">  
      </div>
      </form>

JQuery Code

      <script>
      $(document).ready(function(){
         $('#customer_name').on('change', function(){
             var customer_name = $('#customer_name').val();
             $.ajax({
               url: 'customer-detail.php',
               type: 'POST',
               data: {id : customer_name},
               success: function(customer_data){
                   $('#customer_detail').html(customer_data);
               }
             });
         }); 
      });
      </script>

customer-detail.php

$select_customer_detail = "select * from customer where id={$_POST['id']}";
$customer_detail_query = mysqli_query($connection, $select_customer_detail);
$customer_detail_result = mysqli_fetch_assoc($customer_detail_query);

<div class="form-group">
<input type="text" class="form-control" name="customer_address" value="<?php echo $customer_detail_result['address']; ?>">
</div>

This smells like an XY problem - what’s the issue with storing the ID in this particular scenario...? Why store a mutable attribute as opposed to storing the (ostensibly) immutable ID? — esqew, Apr 05 '21 at 20:44
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — hppycoder, Apr 05 '21 at 20:50
This is in reference to `$select_customer_detail = "select * from customer where id={$_POST['id']}";` which takes whatever is sent in the POST and sends directly to the database — hppycoder, Apr 05 '21 at 20:51
Why would I store id in the customer_name column? I'm passing id in the value (select) attribute because I need to pass it to the server via ajax and then filter the record @esqew — shakti goyal, Apr 05 '21 at 20:57
@shaktigoyal It's really becoming more unclear what you're actually trying to achieve here. Your question states that you're "*trying to store the name of the select option instead of the value in the MySQL database*", but nothing in your code is actually "storing" anything. Can you elaborate on what *exactly* you're trying to do (inputs/outputs)? If you're truly trying to store information into your database, can you also edit in your attempt(s) at this, along with why those attempts didn't meet your requirements (expected vs. actual behaviors, error messages, etc.)? — esqew, Apr 05 '21 at 21:20
None of this makes a lot of sense. If you have the customer id you can query for the name any time you need it — charlietfl, Apr 05 '21 at 21:32
@esqew I updated the code. I forgot to mention the "INSERT INTO" statement in the question. So now you can clearly see I'm storing customer name into the database but Instead of storing customer name, it is storing customer-id — shakti goyal, Apr 05 '21 at 21:41
If you're just getting started with PHP and want to build applications, I'd strongly recommend looking at various [development frameworks](https://www.cloudways.com/blog/best-php-frameworks/) to see if you can find one that fits your style and needs. They come in various flavors from lightweight like [Fat-Free Framework](https://fatfreeframework.com/) to far more comprehensive like [Laravel](https://laravel.com/). These give you concrete examples to work from and guidance on how to write your code and organize your project's files. — tadman, Apr 05 '21 at 21:43
The code you added furthers my initial conclusion that this is definitely an XY problem. You should research what the "*R*" in *RDBMS* stands for, as well as what *third normal form (3NF)* entails. Essentially, you shouldn't store the customer's name in your *sale* table - names change all the time, what if "Customer A" decides to go by "Customer B" tomorrow? How would you relate that sale back to that customer if there's no more "Customer A"? Instead, to adhere to 3NF, `sale` should store the customer's immutable ID value as a foreign key, relating back to your `customer` table. — esqew, Apr 05 '21 at 22:45
The foreign key method worked perfectly! Everything has been sorted out @esqew — shakti goyal, Apr 06 '21 at 03:05

score 0 · Answer 1 · answered Apr 06 '21 at 03:59

As sorted in the comments, the question is quite indicative of an XY problem.

The OP's original design revolved around being able to store the name of a customer that a sale was made for within their sale table. However, this design violates the relational database principle of 3rd normal form in that there would be unnecessary duplication of data across tables, which could lead to various unintended consequences as time passes. From my comment:

[N]ames change all the time, what if "Customer A" decides to go by "Customer B" tomorrow? How would you relate that sale back to that customer if there's no more "Customer A"?

Instead, the solution was to modify the database schema such that the sale table instead had a column to store the ID of the customer alongside other order attributes, and link that customer ID back to the customer table properly as a foreign key.

How to store name of select option instead of value in database

1 Answers1