how can insert all table's rows from script to database at once

Question

I have a problem ... here the create table's row each time user press Add button , How I can send all the data of the table to database at once cuz when press submit only last row inserted into database but not all the table but it should submit all rows with all data

I'm new in php so I don't have a lot in php code this is the php code

<?php
include_once("dbinfo.php");
session_start();
$name= $_SESSION['user'];
if(isset($_POST['savepav'])){
     date_default_timezone_set("Asia/Riyadh");
  $pavdate= date("Y/m/d");
  $pavtime=date("h:i:sa");
  $pavloca=$_POST['pavlocation'];
  $pavtype=$_POST['ddlPassport'];
  $pavdist=$_POST['pavedist'];
  $pavplan=$_POST['pavplan'];
  $pavseve=$_POST['pavseverity'];
    echo "<script>alert(' Pavement data saved successfully ');</script>";
  $query="INSERT INTO `pevement`(`userName`, `plocation`, `pavType`, `padistr`, `pavplan`, `severity`, `pavdate`, `pavtime`) VALUES ('$name' ,'$pavloca', '$pavtype', '$pavdist' ,'$pavplan', '$pavseve', '$pavdate' ,'$pavtime')"; 
  $result_query=mysqli_query($conn,$query);
}
?>

this is script code

<script>
function AddData() {
  var rows = "";
  var name = document.getElementById("locapavm").value;
  var city = document.getElementById("sevepavm").value;
  var plan = document.getElementById("planpavm").value;

  rows += "<tr><td>" + name + "</td><td>" + city + "</td><td>" + plan + "</td><td><button onclick = deleterow(this)>Delete</button></td></tr>";
  $(rows).appendTo("#list tbody");
}

function ResetForm() {
  document.getElementById("person").reset();
}

function deleterow(el) {
  $(el).closest('tr').remove();
}
</script>

and HTML

<html>
<div id = "data">
    <form id = "person">
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
<div class="col-12" style="width: 1514px">
    <select id = "locapavm" name = "pavlocation" style="width: 26%">
            <option value="">- Location -</option>
            <option value="Runway 17">Runway 17</option>
            <option value="Runway 35">Runway 35</option>
            <option value="Runway 18">Runway 18</option>
            <option value="Runway 36">Runway 36</option>
</select><br>
<div class="col-12">
                                                    <select id = "ddlPassport" name = "ddlPassport" style="width: 26%" onchange = "ShowHideDiv()">
                                                        <option value="">- Pavement Type -</option>
                                                        <option value="Flexible Pavement (Asphalt)">Flexible Pavement (Asphalt)</option>
                                                        <option value="Rigid Pavement (Concrete)">Rigid Pavement (Concrete)</option>
                                                </select>
                                                </div><br/>
<div class="col-12" style="width: 1514px">
<select id="pavdistrees" name="pavedist" style="width: 26%">
    <option value="">- Distress selections - </option>
</select><br> </div>
 <div class="col-12" style="width: 1514px">
<select id="sevepavm" name="pavseverity" style="width: 26%">
    <option value="">- Severity -</option>
    <option value="Low">Low</option>
    <option value="Medium"> Medium</option>
    <option value="High">High</option>
</select><br> </div>
<!----------------------------------------------------------------------->
  <p class="auto-style1">Maintenance Plan:</p>  
   <textarea id="planpavm" name="pavplan" style="width: 572px; height: 129px" ></textarea><br> 
        <input id = "person"  type = "reset" value = " Reset " onclick = "ResetForm()">
        <input id = "button"  type = "button" value = " Add " onclick = "AddData()">

    </form>
</div>
<div id = "tab" style="width: 76%">
        <table style="width: 96%" id = "list" cellspacing = "0px" cellpadding = "20px" text-align = "center">
            <thead>
                <tr>
                    <td>Location</td>
                    <td>Pavement Type</td>
                    <td>Type Distrees</td>
                    <td>Severity</td>
                    <td style="width: 396px">Maintenance Plan</td>
                </tr>
            </thead>

            <tbody>

            </tbody>
        </table>
</div>
<br><input type="submit" name="savepav" value="Submit"/>
</html>

Possible duplicate of [PDO Prepared Inserts multiple rows in single query](https://stackoverflow.com/questions/1176352/pdo-prepared-inserts-multiple-rows-in-single-query) — Dean Van Greunen, Aug 30 '19 at 17:55
It appears you are open to SQL Injection attacks. You should investigate this and look into using Prepared Statements. — Booboo, Aug 30 '19 at 18:06
I don't know how many rows user will add I need code with script table — Berrytdm, Aug 30 '19 at 18:41

Jesse · Answer 1 · 2019-08-30T20:41:56.650

1

I see you are using JQuery, so just based on that I think XHR would be your best bet. If you are adding it to the table with the "add" button, then I don't really see the point of the "Submit" button other than to redirect to a different page, so I will omit it just for this example, and edit my answer later if you have a different plan for it.

Your PHP code looks fine, other than it is vulnerable to an SQL injection attack. To prevent this, I would call the mysql_real_escape_string method on all of the strings you are putting in the database like so:

  $pavloca=mysql_real_escape_string($_POST['pavlocation']);
  $pavtype=mysql_real_escape_string($_POST['ddlPassport']);
  $pavdist=mysql_real_escape_string($_POST['pavedist']);
  $pavplan=mysql_real_escape_string($_POST['pavplan']);
  $pavseve=mysql_real_escape_string($_POST['pavseverity']);

For the HTML, I would give your Add button the type of submit, then add the action and method attributes to your opening form tag like so:

<form id = "person" method = "POST" action = "/path/to/php/page">

where /path/to/php/page is the path to the PHP page that adds everything to the database.

And finally for the JavaScript.

Instead of having the onclick attribute on your Add button, I would remove that then just add this bit of JQuery to your script tag:

$("#person").submit(function(d){
d.preventDefault();
AddData(new FormData(d.target));
})

Then for your AddData function add an input parameter for the form:

function AddData(form)

P.S, a little trick after you've done that, you can actually get the values by the name attribute rather than an id like so:

var name = form["pavlocation"];
var city = form["pavseverity"];
var plan = form["pavplan"];

Then last but not least, add this little bit of code to the end of your AddData() function:

var xhr = new XMLHttpRequest();
xhr.open(form.method, form.action);
xhr.send();

And that should be it!

NOTE: I did not test any of this, so I apologize for any typos or syntax errors. If this does not work, I will edit my answer accordingly.

EDIT: There were a couple out-of-order tags in the HTML you posted, so I ran it through an IDE to clean it up a bit, and created a JSFiddle for it here: https://jsfiddle.net/djy9vget/2/

You will have to change the action="/path/to/php/page.php" bit to the actual path, but other than that this should work. I also noticed a typo in my original answer where XmlHttpRequest(); should be XMLHttpRequest();.

I also changed the ID of the Reset button from person (which is the same as the form), to reset.

edited Aug 30 '19 at 20:41

answered Aug 30 '19 at 18:34

Jesse

1,386
3
9
23

first of all very thanks to u for ur help .. the reason of not using Add button is when press it will refresh the page and losing data if there is the way to add row and submit data to the DB at the same time without reload the whole page help me to do it – Berrytdm Aug 30 '19 at 19:21
add i trying ur codes but also only last row will submitted to the db not all rows – Berrytdm Aug 30 '19 at 19:22
The `d.preventDefault();` function prevents the page from refreshing. Are you using the `Add` button with the code or the `Submit` button? You may have more HTML/JavaScript that you didn't share, but from what you did share the `Submit` button doesn't appear to do anything. – Jesse Aug 30 '19 at 19:32
I just realized that your PHP code checks for the existence of `savepav` before it does anything with the database. You may just be able to remove this and be fine. I might be missing something but from what I see, it looks like it may cause more harm than good. – Jesse Aug 30 '19 at 19:38
I have a local host database and Add button isn't submit but the Submit is the submit button and nothing else what I share I don't know why when submit reload the page , could u please explain how to use d.preventDefault(); again ? – Berrytdm Aug 30 '19 at 19:52
The `preventDefault();` function is a JQuery function for the object passed with the `submit` event. To call set the `submit` event, all you have to do is add this little snippet I put above: `$("#person").submit(function(d){ d.preventDefault(); AddData(new FormData(d.target)); })` The `Submit` button doesn't do anything because it is not in a form. Change `type` attribute for the `add` button to `submit` instead of `button`, and remove the `onclick` attribute and what I posted should work. – Jesse Aug 30 '19 at 19:57
I did all u want ,but Unfortunately it is reload the page without add any rows ... this is the Add button – Berrytdm Aug 30 '19 at 20:14
Would you mind sharing your updated code in a post edit or a gist link? – Jesse Aug 30 '19 at 20:14
I put it in the answer – Berrytdm Aug 30 '19 at 20:18
I have updated my answer with an edit at the bottom that includes a link to JSFiddle along with what was going wrong. This revision should work. – Jesse Aug 30 '19 at 20:44
it is the same problem when press Add reload the web page without add any new row is that only in local host ? and there is any ways to insert all data from tables at once? – Berrytdm Aug 30 '19 at 21:15
Did you take a look at the link I posted? It works fine in there and adds the row without refreshing the page. You could send them all at once, but that would be a little more tricky because you would have to create a custom `FormData();` and enumerate through all of the entries in your table before sending it. Another possible factor that this couldn't be working is your browser/version. What are you trying to test this on? – Jesse Aug 30 '19 at 21:20
yes I took the same code u have been wrote it in the link but it keep going make refresh the web .. I use local host database and google chrome web browser ... – Berrytdm Aug 31 '19 at 15:15
I try to use explorer and opera browsers also the same problem when sen data to local host database automatically reload the page with last row only inserted to database – Berrytdm Aug 31 '19 at 15:23

how can insert all table's rows from script to database at once

1 Answers1