MySQL INSERT query

Hi!
I have a form which has some javascript dynamic fields. The number of fields can be as much as the user desires.
The code is:

<HTML>
<HEAD>
    <TITLE> Add/Remove dynamic rows in HTML table </TITLE>
    <SCRIPT language="javascript">
        function addRow(tableID) {
 
            var table = document.getElementById(tableID);
 
            var rowCount = table.rows.length;
            var row = table.insertRow(rowCount);
 
            var colCount = table.rows[0].cells.length;
 
            for(var i=0; i<colCount; i++) {
 
                var newcell = row.insertCell(i);
 
                newcell.innerHTML = table.rows[0].cells[i].innerHTML;
                //alert(newcell.childNodes);
                switch(newcell.childNodes[0].type) {
                    case "text":
                            newcell.childNodes[0].value = "";
                            break;
                    case "checkbox":
                            newcell.childNodes[0].checked = false;
                            break;
                    case "select-one":
                            newcell.childNodes[0].selectedIndex = 0;
                            break;
                }
            }
        }
 
        function deleteRow(tableID) {
            try {
            var table = document.getElementById(tableID);
            var rowCount = table.rows.length;
 
            for(var i=0; i<rowCount; i++) {
                var row = table.rows[i];
                var chkbox = row.cells[0].childNodes[0];
                if(null != chkbox && true == chkbox.checked) {
                    if(rowCount <= 1) {
                        alert("Cannot delete all the rows.");
                        break;
                    }
                    table.deleteRow(i);
                    rowCount--;
                    i--;
                }
 
            }
            }catch(e) {
                alert(e);
            }
        }
 
    </SCRIPT>
</HEAD>
<BODY>
 <form method="post" action="test.php">
   <tr align="center"><td colspan="2"> <INPUT type="button" value="Add Row" onClick="addRow('dataTable')" /></td>
        <td colspan="2"><input name="" type="submit" value="Submit" name="submit">
        </td>
        <td colspan="2">&nbsp;</td>
        </tr>


    <TABLE id="dataTable" width="350px" border="1" align="center">

        <TR>
        <td>Qty:</td>
            <TD><INPUT type="text" name="txt[]"></TD>
            <td>Qty:</td>
            <TD><INPUT type="text" name="txt1[]"/></TD>
            <td>Qty:</td>
            <TD>
                <INPUT type="text" name="txt2[]"/>
            </TD>
                        <td>Qty:</td>
            <TD>
                <INPUT type="text" name="txt3[]"/>
            </TD>

        </TR>
    </TABLE>
    </form>
 
</BODY>
</HTML>

and test.php code is:

<?php
$txtbox = $_POST['txt'];
$txtbox1 = $_POST['txt1'];
$txtbox2 = $_POST['txt2'];
$txtbox3 = $_POST['txt3'];
foreach($txtbox as $a => $b)
  echo "$txtbox[$a]";
  echo "<br />";
 foreach($txtbox1 as $a1 => $b1)
  echo "$txtbox1[$a1]";
  echo "<br />";
foreach($txtbox2 as $a2 => $b2)
  echo "$txtbox2[$a2]";
  echo "<br />";
foreach($txtbox3 as $a3 => $b3)
  echo "$txtbox3[$a3]";
  echo "<br />";
?>

The output is quite fine with the foreach loop.But (BIG) problem is I dont have an idea how I can insert the values in the database.

$query=mysql_query("INSERT INTO  existing_clients (exi_inv_qty) VALUES ('".$txtbox[$a]."')");

This query inserts only a single value in the database.Can anyone help me out in this matter?I want to insert a new row each time when the query is runned.

Use php code to make an insert query with multiple rows then use mysql_query to insert.

You will have to run multiple INSERT queries.

One question, why do you have each of your text inputs an array?

The simplest way to do this would be to give all of your inputs the same name, with afterward (so, for example, txt).

Then, in your PHP you can access the value with $_POST[‘txt’] which would be an array containing each string the user input.

Then you can do a for or foreach loop for each string and insert it.

Can you show me some example gentlemen?? I would be much obliged. :slight_smile:

inside form you like this:::


 <TABLE id="dataTable" width="350px" border="1" align="center">



        <TR>

        <td>Qty:</td>

            <TD><INPUT type="text" name="txt[]"></TD>

            <td>Qty:</td>

            <TD><INPUT type="text" name="txt[]"/></TD>

            <td>Qty:</td>

            <TD>

                <INPUT type="text" name="txt[]"/>

            </TD>

                        <td>Qty:</td>

            <TD>

                <INPUT type="text" name="txt[]"/>

            </TD>



        </TR>

    </TABLE>


In PHP:::


$txt=$_POST['txt'];
$one=$txt[0];//assign 1st value to some variable
.
.
.
.
and insert it into database...

The PHP would look something like:


$txt = $_POST['txt'];

foreach($txt as $line) {
    mysql_query("INSERT INTO my_table (my_column) VALUES ('$line')");
}

Ok I am getting the concept little bit.But still its not working.
My new mixed & matched code is:

<?php
$txt = $_POST[‘qty’];
foreach($txt as $line) {
$one=$line[0];
$two=$line[1];
mysql_query(“INSERT INTO test (qty) VALUES (‘$one’)”);
mysql_query(“INSERT INTO test (qty) VALUES (‘$two’)”);

print_r(“INSERT INTO test (qty) VALUES (‘$one’)”);
print_r(“INSERT INTO test (qty) VALUES (‘$two’)”);

}
?>

It is giving o/p:

INSERT INTO test (qty) VALUES (‘1’)INSERT INTO test (qty) VALUES (‘’)

:frowning: :frowning: :frowning:

I’m not a real PHP hero :frowning: but I think, like any other server side scripting language, in PHP you should be able to loop through the form fields. Maybe one of the PGP Gurus can shine a light on that.

A foreach loop automatically increments itself using an index starting at 0 so using $line[1] for instance wouldn’t do anything because no array key of 1 within $line exists.

Try the code below

HTML

<!DOCTYPE html>
<html>
<head>
<title>Add/Remove dynamic rows in HTML table</title>
<meta charset="utf-8">
<script language="javascript">
function addRow(tableID) {
    var table = document.getElementById(tableID);
    var rowCount = table.rows.length;
    var row = table.insertRow(rowCount);
    var colCount = table.rows[0].cells.length;

    for(var i=0; i<colCount; i++) {
        var newcell = row.insertCell(i);

        newcell.innerHTML = table.rows[0].cells[i].innerHTML;
        //alert(newcell.childNodes);
        
        switch(newcell.childNodes[0].type) {
            case "text":
                newcell.childNodes[0].value = "";
                break;
            case "checkbox":
                newcell.childNodes[0].checked = false;
                break;
            case "select-one":
                newcell.childNodes[0].selectedIndex = 0;
                break;
        }
    }
}

function deleteRow(tableID) {
    try {
        var table = document.getElementById(tableID);
        var rowCount = table.rows.length;

        for(var i=0; i<rowCount; i++) {
            var row = table.rows[i];
            var chkbox = row.cells[0].childNodes[0];
            
            if (null != chkbox && true == chkbox.checked) {
                if (rowCount <= 1) {
                    alert("Cannot delete all the rows.");
                    break;
                }
                
                table.deleteRow(i);
                rowCount--;
                i--;
            }

        }
    } catch(e) {
        alert(e);
    }
}
</script>
</head>
<body>

    <form method="post" action="test.php">
        <table id="dataTable" width="350px" border="1" align="center">
            <tr align="center">
                <td colspan="2">
                    <input type="button" value="Add Row" onClick="addRow('dataTable')">
                </td>
                <td colspan="2">
                    <input name="" type="submit" value="Submit" name="submit">
                </td>
                <td colspan="2">&nbsp;</td>
            </tr>
            <tr>
                <td>qty:</td>
                <td>
                    <input type="text" name="qty[]">
                </td>
                <td>qty:</td>
                <td>
                    <input type="text" name="qty[]">
                </td>
                <td>qty:</td>
                <td>
                    <input type="text" name="qty[]">
                </td>
                <td>qty:</td>
                <td>
                    <input type="text" name="qty[]">
                </td>
            </tr>
        </table>
    </form>
    
</body>
</html>

PHP

if (isset($_POST['qty']) && sizeof($_POST['qty']) > 0) {
    // Trim any white space
    $qty = array_map('trim', $_POST['qty']);
    
    foreach($qty as $quantity) {
        mysql_query("INSERT INTO test (qty) VALUES ('$quantity')");
        print_r("INSERT INTO test (qty) VALUES ('$quantity')");
    }
} else {
    exit('No values entered');
}

Dont know why, but when I edited the code as above, its not working.
If I use it as it is, it works fine.
I am using 4 feilds and if user press add button, 4 more rows with same name are inserted.I want to get those values and insert them to database.
Code is like:

<td><input type=“button” value=“Add Row” onClick=“addRow(‘dataTable’)” /></td>
<table align=“center” id=“dataTable”>
<tr>
<td>Qty: </td>
<td><input type=“text” name=“qty”></td>
<td>Description: </td>
<td><textarea name=“description” cols=“16” rows=“”></textarea></td>
<td>Unit Price: </td>
<td><input type=“text” name=“unit_price”></td>
<td>Line Total: </td>
<td><input type=“text” name=“line_total”></td>
</tr>
</table>

Please guide me in this regard.

Its because the foreach loop is designed to handle only 1 array, the code i wrote below is simple but can dynamically handle its own array keys and values.

$keys = array('qty', 'description', 'unit_price', 'line_total');

// Run through the array keys
foreach($keys as $key) {
    $temp = isset($_POST[$key]) ? array_map('trim', $_POST[$key]) : false;

    if ($temp && sizeof($temp) > 0) {
        foreach($temp as $value) {
            // Only insert the value if it has a length
            if (!empty($value)) {
                mysql_query("INSERT INTO test ($key) VALUES ('$value')");
                print_r("INSERT INTO test ($key) VALUES ('$value')");
            }
        }
    } else {
        echo 'The key ' . $key . ' hasn\\'t been declared or has a length of 0';
    }
}

“An expert knows all the answers - if you ask the right questions.” said Levi Strauss .
Thx again SgtLegend. I think this code will solve my problem.

No worries, im no expert but i try my best :stuck_out_tongue:

Thats very sweet of you.Can I bother you again??

Query is giving output:

INSERT INTO test (qty) VALUES (‘1’)INSERT INTO test (description) VALUES (‘2’)INSERT INTO test (unit_price) VALUES (‘3’)INSERT INTO test (line_total) VALUES (‘4’)

and table looks like
qty description unit_price line_total
1 - - -

  • 	2 	  -	            -
    
  •    - 	  	3 	    -
    
  •     -	  -	  -	    4
    

as I said, There are 4 rows and they can be extended to say, 3 as per user choice.
I want my table like:
qty description unit_price line_total
1 2 3 4
5 6 7 8
9 10 11 12
13 14 15 16
Is there any solution?? :frowning:

then instead of this –


INSERT INTO test (qty) VALUES ('1');
INSERT INTO test (description) VALUES ('2');
INSERT INTO test (unit_price) VALUES ('3');
INSERT INTO test (line_total) VALUES ('4');

you should do this instead –



INSERT INTO test (qty,description,unit_price,line_total) 
VALUES ( 1, '2', 3, 4);

note only values intended for string columns should have quotes

Try this

if (isset($_POST['qty']) && sizeof($_POST['qty']) > 0) {
    // Run through the "qty" array which allows us to check for
    // "description, unit_price and line_total" values
    for($i = 0, $maxi = count($_POST['qty']); $i < $maxi; $i++) {
        // Make sure the array values have a length
        // If they don't use 0 as the value
        //
        // If the array key has a value remove any harmful input by
        // using mysql_real_escape_string()
        $quantity    = (isset($_POST['qty'][$i]) && !empty($_POST['qty'][$i])) ? mysql_real_escape_string($_POST['qty'][$i]) : 0;
        $description = (isset($_POST['description'][$i]) && !empty($_POST['description'][$i])) ? mysql_real_escape_string($_POST['description'][$i]) : 0;
        $unit_price  = (isset($_POST['unit_price'][$i]) && !empty($_POST['unit_price'][$i])) ? mysql_real_escape_string($_POST['unit_price'][$i]) : 0;
        $line_total  = (isset($_POST['line_total'][$i]) && !empty($_POST['line_total'][$i])) ? mysql_real_escape_string($_POST['line_total'][$i]) : 0;
        
        $sql = "INSERT INTO test (qty,description,unit_price,line_total) VALUES ('$quantity','$description','$unit_price','$line_total')";
        mysql_query($sql);
        print_r($sql);
    }
} else {
    exit('No values entered');
}

This query is working very cool.
New O/p is:

INSERT INTO test (qty,description,unit_price,line_total) VALUES (‘1’,‘2’,‘3’,‘4’)

But still I am unable to insert all the values from other dynamic text feilds.
So I am here once again!! :slight_smile:

What error message being returned by MySQL?

No error message SpacePhoenix.
SgtLegend’s code works fine.
Just not getting desired result :frowning: