MYSQL - Inserting multiple inputs into a table issue

Question

I have been using this exactly code (changed variables names), to input information into my database throughout my website and i have never had a problem. This is the most variables I've ever tired to insert into a db at once. There is no error messages the data just will not insert.

Is there a better way to do this? is this a known problem?

PHP

<?php
if (isset($_POST['Save'])) {
  $f_name = $_POST['franchise_name'];
  $f_email = $_POST['fran_email'];
  $f_mangn = $_POST['mang_name'];
  $f_addline_1 = $_POST['franc_address'];
  $f_addline_2 = $_POST['address2'];
  $f_city = $_POST['city'];
  $f_pcode = $_POST['pcode'];
  $f_phone = $_POST['franc_phone'];

  $insert_franc_dets = "INSERT INTO Franchise_manager_account(Area_Name,Franchise_email,Fran_Fname,Fran_business_add_line1,Fran_business_add_line2,Fran_City,fran_Postcode,Fran_Contact_Num) 
  VALUES (?,?,?,?,?,?,?,?)
  ON DUPLICATE KEY 
  UPDATE
  Area_Name = '$f_name',
  Franchise_email = '$f_email',
  Fran_Fname = '$f_mangn',
  Fran_business_add_line1 = '$f_addline_1',
  Fran_business_add_line2 = '$f_addline_2',
  Fran_City = '$f_city',
  fran_Postcode = '$f_pcode',
  Fran_Contact_Num = '$f_phone'";

  $c = mysqli_prepare($dbc, $insert_franc_dets);
  //new
  // $stmt = mysqli_prepare($dbc, $insert_c);
  //debugging
  //$c = mysqli_prepare($dbc, $insert_franc_dets)  or die(mysqli_error($dbc));

  mysqli_stmt_bind_param($c,'sssssssi', $f_name, $f_email, $f_mangn, $f_addline_1, $f_addline_2, $f_city, $f_pcode, $f_phone);

  /* execute query */
  $execute = mysqli_stmt_execute($c);

  // if inserted echo the following messges
  if ($execute) {
    echo "<script> alert('Addrrss Saved')</script>";
  } else {
    echo "<b>Oops! we have an issue </b>";
  }
}
$dbc->close();
?>

HTML

<form id="franchiseDets" action ="Franchise-Details.php" method="POST">


  <!--                franchise details form-->
  <div class="field">

      <input type="text" name="franchise_name" id="fran_name" placeholder="e.g One Delivery Leeds" pattern="[a-zA-Z]" 
             autofocus required tabindex="1">
      <br>

      <input type="email" name="fran_email" id="fran_email" placeholder="[email protected]" required tabindex="2">
      <br>

      <input type="text" name="mang_name" id="name" placeholder="Joe Blogs" required tabindex="3">
      <br>


      <input type="text" name="franc_address" id="address1" placeholder="Address Line 1" tanindex="4">
      <input type="text" name="address2" id="address2" placeholder="Address Line 2" tabindex="5">
      <input type="text" name="city" id="city" placeholder="Town/City" tabindex="6">
      <input type="text" name="pcode" id="pcode" placeholder="Postcode" tabindex="7">
      <br>


      <input type="tel" name="franc_phone" id="phone" placeholder="Customer service number" min="10" maxlength="11" pattern="[0-9]{3}[-][0-9]{4}[-][0-9]{4}" 
             required title="Please provide your customer service number in the following format: 000-0000-0000" tabindex="8">

<input type="submit" name="Save" value="Save">
</form>
      <br>
  </div>

TABLE (3 unique elements) Session_start(); is at top of the page. still working on sql injections.

spencer7593 · Accepted Answer · 2016-03-25 05:11:31Z

You're using bind placeholders for the insert. (That's a good thing)

But you've got literals in the SQL text of the update. (For the love of all that is good and beautiful in the world, why would you do that?)

The prepared statement with bind placeholders is a defense against SQL Injection. But that job is only half done.

Just use the VALUES() function to reference the value that would have been inserted to the column (if the insert had worked). I didn't look at which values you are assigning to which columns in the update portion, the example below assigns the the value that was supplied for the insert.

 INSERT INTO franchise_manager_account
      ( area_name
      , franchise_email
      , fran_fname
      , fran_business_add_line1
      , fran_business_add_line2
      , fran_city
      , fran_postcode
      , fran_contact_num
      ) VALUES
      ( ?
      , ?
      , ?
      , ?
      , ?
      , ?
      , ?
      , ?
      )
 ON DUPLICATE KEY 
 UPDATE area_name               = VALUES(area_name)
      , franchise_email         = VALUES(franchise_email)
      , fran_fname              = VALUES(fran_fname)
      , fran_business_add_line1 = VALUES(fran_business_add_line1)
      , fran_business_add_line2 = VALUES(fran_business_add_line2)
      , fran_city               = VALUES(fran_city)
      , fran_postcode           = VALUES(fran_postcode)
      , fran_contact_num        = VALUES(fran_contact_num)

For that UPDATE portion to execute, the INSERT would have to raise a "duplicate key exception". And for that error to occur, there needs to be at least one PRIMARY and/or UNIQUE KEY to be defined. (Its's not clear from the question which column or columns make up the primary and/or unique keys. Typically, we omit those columns from the UPDATE.)

If I need to supply a different value in the update, I'd use a SQL expression to perform any checks or manipulation (e.g. don't replace a non-NULL value, or add to the existing value, et al.

       , col7  = IF(col7 IS NULL, VALUES(col7), col7) 
       , col8  = col8 + VALUES(col8)
       , col9  = CONCAT(col9,',',VALUES(col9))
       , colA  = ?

If it's an entirely different value, then I'd supply it with a bind placeholder, just like in the INSERT.

As far as why a row isn't being inserted... if it's SQL Injection causing the issue, like if one of the values includes a single quote or something, the pattern above should address that.

The code should really check the return from both the prepare and the execute, and handle an error. Emitting mysqli_error text isn't the best practice for production ready code, but it's easy for development and debugging. At a minimum, your code should check for an error and have a code block. For development, at least echo/print mysqli_error.

If your code isn't doing that, then it's putting it's putting it's virtual pinky finger to the corner of it's virtual mouth, Dr.Evil style and saying "I just assume it will all go to plan. What?"

And make sure PHP error reporting is enabled.

Are you sure that your first script is even getting executed?

If you still can't find the problem, then start adding some additional debugging output.

How to debug small programs

Thank you. This worked, but a big thank you for explaining i know how i better understand and will go on to change my previous script also

Collectives™ on Stack Overflow

MYSQL - Inserting multiple inputs into a table issue

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related