Need example of inserting form data into a Mysql db

[elentz]

I have a form with a table with columns and many rows.  The form uses Post and submits to an insert.php  I am looking for an example of how I can insert the table info into a mysql table.  I know I should use a while loop but unsure how to step through the form info.

 

Can someone suggest or provide and example that I can follow, or a tutorial somewhere?

 

Thanks

[mac_gyver]

how many rows of data, where is this data coming from/what format is it in now, and how frequently is this going to be performed, since the answers to these questions determine the best method to use to get the data into a database table(s)?

 

if the data is already in the form of a file or some other computer based format, so that it could be uploaded as a csv formatted file, you can just run a single LOAD DATA LOCAL INFILE ... sql query to get the data into a database table.

 

if the data is something that you can copy/paste into a single form field, a php script could break it into rows to loop over them for inserting the data into a database table.

 

if you are going to have a set of form fields holding the data, you would use array names for the different form fields. this will cause the data to be submitted as arrays, that a php script can use a foreach(){} loop to loop over.

 

for the cases where you will have a loop in a php script, you would use a prepared query, prepared once before the start of any looping, with place-holders for the data values, then supply each set of data and execute the query inside of the loop.

[elentz]

mac_gyver,

 

The info is in a form that the user fills out. Your third comment is what I am looking for.  So an example of how to do that would be great!

 

Thanks

[Jacques1]

How about you post your form (or at least the relevant parts), the processing attempts you've made and the exact problems you have?

 

Nobody is going to write a PHP tutorial just for you, especially when it's entirely unclear what you even have trouble with.

[elentz]

Here is the form code:

<form action="insert.php" method="post">
<?php
error_reporting(E_ALL);
ini_set('display_errors','On');
$link = mysql_connect("localhost", "", "") or die ('Error connecting to mysql' . mysql_error());
mysql_select_db("cqadmin");
$sql2 = "SELECT extension, secret from extensions;";
$result2 = mysql_query($sql2) or die(mysql_error());
echo "<table border='3'>
    <tr>
        <th>Extension #</th>
        <th>Secret</th>
        <th>MAC Address</th>
        <th>Template</th>
    </tr>";

while($row = mysql_fetch_array($result2))
{
    $sql = "SELECT id , mac FROM phones order by mac;";
    $result = mysql_query($sql) or die(mysql_error());
    $sql1 = "SELECT id , templatename FROM templates order by templatename;";
    $result1 = mysql_query($sql1) or die(mysql_error());
    echo "<tr>";
    echo "<td>" . $row['extension'] . "</td>";
    echo "<td>" . $row['secret'] . "</td>";
    echo "<td> <select name='phone'>";
    while($rowA = mysql_fetch_array($result)) {
        echo '<option value="' . $rowA['id'] . '">' . $rowA['mac'] . '</option>';
    }
    echo "</select></td>";
    echo "<td><select name='template'>";
    while($rowB = mysql_fetch_array($result1)) {
        echo '<option value="' . $rowB['id'] . '">' . $rowB['templatename'] . '</option>';
    }
    echo "</select></td>";
    echo "</tr>";
}
echo "</table>";
?>


<input type="submit" value="Submit your selections">
</body>
</html>

Here is the insert.php file

<?php
echo "You got here";
//***********Get the Assignment information *************
$ext = $_POST['extension'];
$password= $_POST['secret'];
$macaddress = $_POST['mac'];
$template = $_POST['template'];
Echo "$ext";
Echo "$password";
Echo "$macaddress";
Echo "$template";
?>

When I hit the submit button I get "You got here"  I know big deal...   I know I need to use Mysqli instead of mysql as well. I am pretty sure I need to use a while loop but I do not know how to apply it to multiple rows.  There will be an unknown number of rows each time

this form is used.  There are 4 strings of data to be inserted into the DB table each time.  I put the other code in the insert file to see if I would get anything.  

[Jacques1]

PHP (unlike many other languages) doesn't support multiple form fields with the same name. You have to use a special array syntax instead:

<form method="post">
    <input type="text" name="foo[0]">
    <input type="text" name="foo[1]">
    <input type="text" name="foo[2]">
    <input type="submit">
</form>

This turns $_POST['foo'] into an array containing the three field values as elements.

 

To get the data you expect in your script, you'd use a layout like this:

<form action="insert.php" method="post">

    <!-- first entry -->
    <input type="hidden" name="data[0][extension]" value="this is where the escaped(!) extension goes">
    <input type="hidden" name="data[0][secret]" value="this is where the escaped(!) secret goes">
    <select name="data[0][mac]"></select>
    <select name="data[0][template]"></select>

    <!-- second entry -->
    <input type="hidden" name="data[1][extension]" value="this is where the escaped(!) extension goes">
    <input type="hidden" name="data[1][secret]" value="this is where the escaped(!) secret goes">
    <select name="data[1][mac]"></select>
    <select name="data[1][template]"></select>

    <!-- etc. -->

</form>

Now $_POST['data'] is an array of associative arrays which can be iterated like this:

foreach ($_POST['data'] as $entry)
{
    var_dump($entry['extension'], $entry['secret'], $entry['mac'], $entry['template']);
}

But before you do anything, you need to clean up your code. This is a mess of security vulnerabilities, obsolete features and useless repetitions. For example, the <option> lists are always identical, so they should only be created once and then reused for each entry. It doesn't make sense to send run the same query with the same results over and over again.

[pieterjandc]

Best answer ever ?

Here you go:

<?php
  // table with following columns:
  // id(Auto-Increment), naam, straatnummer, postcodestad
 
  if(!empty($_POST))
  {
    $db=mysqli_connect("localhost","root","sql_password","db_name");
    
    $keys = implode(',',array_keys($_POST));
    $values = implode(', ', array_map(
        function ($v) { return sprintf("'%s'", $v); }, $_POST
    ));
    
    $query = "INSERT INTO personen(" . $keys . ") VALUES(" . $values . ")";
    mysqli_query($db,$query);
    mysqli_close($db);
    echo($query);
  }
  else
  {
?>

<!DOCTYPE html>
<html>
  <head>
  </head>
  <body>
    <form action="<?php echo($_SERVER['PHP_SELF']); ?>" method="POST">
      <table>
        <tr>
          <td>Naam</td>
          <td><input type="text" name="naam"></td>
        </tr>
        <tr>
          <td>Straat & nummer</td>
          <td><input type="text" name="straatnummer"></td>
        </tr>
        <tr>
          <td>Postcode & stad</td>
          <td><input type="text" name="postcodestad"></td>
        </tr>
        <tr>
          <td colspan="2"><input type="submit" value="Opslaan"></td>
        </tr>
      </table>
    </form>
  </body>
</html>

<?php
  }
?>

Edited January 8, 2017 by pieterjandc

[benanamen]

You already lose "best answer ever" by using $_SERVER['PHP_SELF'] which is vulnerable to an XSS attack.

[Jacques1]

Best answer ever ?

 

I hope that was a joke. Do you have any idea what the OP even asked for?

 

Not to mention the big, fat SQL injection vulnerabilities. You realize that POST data comes from the user, right? It can be changed to absolutely anything, including SQL commands.

[mac_gyver]

There are 4 strings of data to be inserted into the DB table each time.

 

 

actually, no. based on the form you have posted, you are selecting a phone id and a template id to be associated with an extension number. you would store only these three numbers in a database table. if you are trying to edit the secret/password, that's a different concern from assigning a phone id and template id to an extension and should be handled separately. btw - passwords should be hashed when stored, so displaying the stored value doesn't serve any useful purpose.

 

next, you need to separate all the database specific code out of the html document, by fetching the data from each query into a php array variable, then just use the php array variables in the rest of the code. this will make it easier to write and test your code, your code will be less cluttered, and it will make it easier to switch to a different php database extension, since all the database specific code will be grouped together. as long as the same data is fetched into the php variables, you won't have to touch the rest of the code that's using the fetched data.

 

the form field names you are using will be what php uses for the submitted data. if you are using name='phone' and name='template' for the two select/option menus, the data will be in $_POST['phone'] and $_POST['template']. there will not be any - $_POST['extension'], $_POST['secret'], and $_POST['mac'] variables because you don't have any form fields with these names.

 

since the extension number is apparently what identifies the data being inserted, when you change the form fields to be array names, i would use the extension number as the array index. this will also support the next task you will likely have of being able to edit/update data that has already been inserted into the database table.

[pieterjandc]

I hope that was a joke. Do you have any idea what the OP even asked for?

 

Not to mention the big, fat SQL injection vulnerabilities. You realize that POST data comes from the user, right? It can be changed to absolutely anything, including SQL commands.

 

I hope the user will be smart enough to use mysqli_real_escape_string, so obvious cause everybody already uses it. He's just asking a way to itterate the post-data in an easy way

[benanamen]

@pieterjandc, we all know you don't know what your talking about. Just stop already. You're starting to look foolish.

[elentz]

Mac_gyver,

 

Thank you for your explanation.  I think I am starting to get it.  Although I am wondering if I have not thought this out enough and this is not the way to achieve what I ultimately need to do.  Ultimately, I need to take the extension number, and the secret and insert it into a text file that is named the template name and then rename that file the mac address.cfg.  There could be 5 files to be created/appended or 105 of them.  It will be dynamic.  I have already figured out creating the template files and how to append them.  The form I posted retrieves the extension, secret, MAC and template information from three DB tables.  The Mac and the Template are then on this form I have "assigned" to each other.  I thought it would be easier to insert all this into a new table.  Maybe you could advise me if I should pursue the help in this thread or go another way.

 

Thanks

[mac_gyver]

you need to define the 'work flow' (steps) and data for each step before you write any code. without knowing what you are ultimately trying to achieve and WHY, it's not possible to advise you based on the content in this thread. just went back and looked at this thread - https://forums.phpfreaks.com/topic/302608-large-number-of-fields-to-upload/

 

it would appear you are creating 'auto' provisioning files for a phone network, where the mac address is used to identify which configuration file to use for any phone. your phones db table is apparently a list of the mac addresses of the available phones? your templates db table, i would guess is a list of some typical button layouts/template file names? the extensions db table is apparently a list of extension numbers and predefined passwords?

 

if your goal is to edit/assign a phone and a template file name to an extension number, what has been suggested so far in this thread for the form (using an array name for the form field names) is correct. you would actually retrieve any current assignment and pre-select the options in the select/option menus. you would also need to have a way of un-provisioning the phone assigned to an extension number. when the form gets submitted, the form processing code would need to insert new data and update existing data (there a single query that can do this), and delete any data for phones that have been un-assigned (not sure if you need a configuration file in this case that points the phone to a nonexistent extension number or something else to disable it.)

 

the assignment data should be stored in a database table. this will let you edit the current assignments and will also let you produce reports based on the data.

 

the mac-address.cfg configuration files are the end result of the assignment. if the phones are on the same network where the web server/php is at, you can let a php script serve the configuration files, without having actual files. this will keep all the data in sync, since you won't have to create and maintain actual files. anytime a phone makes a request for its configuration, it will get the current values, because they will be retrieved from the database tables at the time they are requested.

[elentz]

mac_gyver

 

You are 100% correct, in what I want  to do.  It sounds like you have some history with this type of project.  This project will reside on every machine I sell and will be on the same network as the phones.  I am only familiar with using TFTP for provisioning the phones.  If I use the http provisioning I can turn off the tftp server.  There is an option to use HTTP but I do not know how it operates.  As you say it might be the better way to do this with the page creating the information "on the fly" so to speak.  I will have to look into how that works.  I have a couple of weeks before the powers that be at my company will be breathing down my neck to roll this out.  

 

I did not pursue putting all the configuration elements into a DB.  I thought it would be easier to create the files individually.  

 

I really appreciate your help in all this.