Issue Storing Multiple orders in mysql with same first name

Question

I'm trying to have it where multiple orders from the same user, but when I go to create another order with the same first name as the other order it comes up with an error, But if you change the first name to something that is admin like admin1 it will store the order in the MySQL its only when its two different names :

Here is the error:

Error: INSERT INTO orders (userid,username,firstname, lastname, orderdate , address, email, tires, oil, sparks, detailkit, total, tordered, phonenumber, storeid) VALUES ('101','bigfella','admin', '' ,'18:27, 29th January 2018', '', '', '', '','', '' , '0', '0', '','Choose...' ) Duplicate entry 'admin' for key 'name'

Here is the Order.php form

 <form action="process.php" method="post"> 
  <div class="form-row">
    <div class="form-group col-md-6">
      <label class="cblack" for="firstname">First Name:</label>
      <input type="text" class="form-control" id="firstname" name="firstname" placeholder="First Name">
    </div>
     <div class="form-group col-md-6">
      <label class="cblack" for="lastname">Last Name:</label>
      <input type="text" class="form-control" id="lastname" name="lastname" placeholder=" Last Name">
    </div>

    <div class="form-group col-md-6">
      <label class="cblack" for="email">Email Address:</label>
      <input type="email" class="form-control" id="email" name="email" placeholder="Email Address">
    </div>
      <div class="form-group col-md-6">
      <label class="cblack" for="phonenumber">Phone Number:</label>
      <input type="text" class="form-control" id="phonenumber" name="phonenumber" placeholder="503-555-0000">
    </div>
  </div>
  <div class="form-group">
    <label class="cblack" for="address">Shipping Address:</label>
    <input type="text" class="form-control" id="address" name="address" placeholder="455 Your Address Here ">
  </div>

  <div class="form-row">
    <div class="form-group col-md-2">
      <label class="cblack" for="tires">Tires:</label>

      <input type="text" class="form-control" name="tireqty" id="tires" placeholder="0">
    </div>

    <div class="form-group col-md-2">
      <label class="cblack" for="oil">Oil:</label>
      <input type="text" class="form-control"  name="oilqty" id="oil" placeholder="0">
    </div>

    <div class="form-group col-md-2">
      <label class="cblack" for="sparks">Spark Plugs:</label>
      <input type="text" class="form-control" name="sparkqty" id="sparks"  placeholder="0">
   </div>

    <div class="form-group col-md-2">
      <label class="cblack" for="detailkit">Detailing Kits:</label>
      <input type="text" class="form-control" name="detailkit" id="detailkit" placeholder="0">   
   </div>
  </div>
    </div>
 <button type="submit" id="submit" name="submit" class="btn btn-primary">Submit Your Order.</button>
</form>

Process.php (Stores into the Database)

 $phonenumber = $_POST['phonenumber'];
     $detailkit = $_POST['detailkit'];
     $firstname = $_POST['firstname'];
     $lastname = $_POST['lastname'];

      $totalqty = $tireqty + $oilqty + $sparkqty + $detailkit; 
      $totalamount = 0.00;

      define('TIREPRICE', 100);
      define('OILPRICE', 10);
      define('SPARKPRICE', 4);
      define('detailkit', 50);

      $totalamount = $tireqty * TIREPRICE
                   + $oilqty * OILPRICE
                   + $detailkit * detailkit
                   + $sparkqty * SPARKPRICE;
      $taxrate = 0.00;  // local sales tax is 10%
      $totalamount = $totalamount * (1 + $taxrate);

//Code Will Insert into database Table. 
$servername = "localhost";
$username = "xxxxxx";
$password = 'xxxxxx';
$dbname = "xxxx";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}


if (isset($_POST['submit'])) { //On Submit Run below code :) For Automotive and then insert into database - AB
    $address = $_POST['address'];
     $firstname = $_POST['firstname'];
        $lastname= $_POST['lastname'];
      $email = $_POST['email'];
        $tireqty = $_POST['tireqty'];
            $oilqty = $_POST['oilqty'];
                $sparkqty = $_POST['sparkqty'];
                    $total = $_POST['total'];
                          $phonenumber = $_POST['phonenumber'];
                                $detailkit = $_POST['detailkit'];
                                    $userid = $_SESSION['user']['id'];
                                        $username = $_SESSION['user']['username'];


$name = $_POST['storeaddress'];
foreach ($name as $storeid){ 

}
                                   $totalqty = $tireqty + $oilqty + $sparkqty + $detailkit; 
                        //$totalordered = $_POST['totalordered'];
    $sql = "INSERT INTO orders (userid,username,firstname, lastname, orderdate , address, email, tires, oil, sparks, detailkit, total, tordered, phonenumber, storeid)
                    VALUES ('$userid','$username','$firstname', '$lastname' ,'$date', '$address', '$email', '$tireqty', '$oilqty','$sparkqty', '$detailkit' , '$totalamount', '$totalqty', '$phonenumber','$storeid' )";



if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
}

?>

Any Ideas on why it's not wanting to store multiple users orders with the same first name? And how I might be able to fix this issue?

Database: Heres the Database

Info Stored in Database: Info store in database

I ran Describe orders in mysql heres the output

describe orders output

You seem to have a unique or primary key constraint on your `firstname` column. Can you show the output of `DESCRIBE orders`? — Matt Clark, Jan 29 '18 at 18:41
It is impossible to answer without knowing existing data and table structure — Ravi, Jan 29 '18 at 18:41
Dont put `username,firstname, lastname,` in the orders table. The `userid` is enough — RiggsFolly, Jan 29 '18 at 18:41
@RiggsFolly when it displays the users orders it shows their first name, last name, on the order. — Bigfella, Jan 29 '18 at 18:46
@RiggsFolly Like using an Associative Entity? By storing the ID in the entity and then retrieving the first name and last name from the other entity? — Bigfella, Jan 29 '18 at 18:48
I belive what @MattClark is referring to is to, shell into the database server, and in a command-line sql statement run `describe orders` and place that output here. the php code that generates it is not low level enough to work with. `mysql -u root -p` should do the trick, then `describe orders` — Fire Crow, Jan 29 '18 at 18:51
**WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST`, `$_GET` or **any** user data directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman, Jan 29 '18 at 18:56
A lot of problems can be detected and resolved by [enabling exceptions in `mysqli`](https://stackoverflow.com/questions/14578243/turning-query-errors-to-exceptions-in-mysqli) so mistakes aren't easily ignored. — tadman, Jan 29 '18 at 18:56
@Bigfella, since you have access to phpMyAdmin, click that `SQL` button in the top menu, and enter the raw SQL query, `describe orders`, the screen caps posted do not show the table indicies. — Matt Clark, Jan 29 '18 at 18:56
Note: Try and get out of the habit of declaring SQL statements in throw-away variables that are used only once. It's a lot easier to follow code where the query is supplied directly to the function, and there's no longer a chance of messing up and sending in `$sql3` instead of the visually similar `$sql8`. — tadman, Jan 29 '18 at 18:56
Do try and get out of the habit of cluttering up your code with needless things like `=== true`. Many functions are designed to return values that evaluate as logically true or false so that's redundant. — tadman, Jan 29 '18 at 18:56
I ran it in mysql and it looks like I have a Unique on first name. I updated it above with the output of what it ran. @FireCrow — Bigfella, Jan 29 '18 at 18:56
You also urgently need to [normalize](http://en.wikipedia.org/wiki/Database_normalization) this database properly. Storing hard "username" values in a table is asking for trouble. Those should be foreign key-type identifiers, usually `INT`, to other tables where that data is stored. This ensures that if someone changes their username you don't mix up who owns what data. — tadman, Jan 29 '18 at 18:58
Note to the OP: consider dropping the HTML and (to smaller part) PHP part of this question. It's all about database design. — raina77ow, Jan 29 '18 at 19:04

score 1 · Answer 1 · answered Jan 29 '18 at 18:53

1

You seem to have an index on the firstname column with a UNIQUE constraint. You have to remove the index and then recreate it without the unique constraint, something like this:

ALTER TABLE orders DROP INDEX `name`;
ALTER TABLE orders CREATE INDEX `name` ON customer(firstname);

answered Jan 29 '18 at 18:53

Petko Bossakov

510
2
10

1

They paid extra for that UNIQUE constraint. It's actually a feature. (totally joking) – Tarek Adam Jan 29 '18 at 18:57
This prevents you from having two customers with the first name "Bob", which is probably not a solution. – tadman Jan 29 '18 at 18:57
1

Bob the Second disagrees. – raina77ow Jan 29 '18 at 19:02

Issue Storing Multiple orders in mysql with same first name

1 Answers1