how to insert rows to the database in a loop

[backups2007]

I'm using php/mysql to develop a program. What I need to do is create an insert query that would be able to insert at least 9 rows of data from input text boxes. But of course, before inserting the data checking which rows are empty.

I did something like this:

[PHP]
<?
$item_code1 = $_POST['item_code1'];
$description1 = $_POST['description1'];
$quantity1 = $_POST['quantity1'];

$item_code2 = $_POST['item_code2'];
$description2 = $_POST['description2'];
$quantity2 = $_POST['quantity2'];

if($item_code1! ="")
{
$query1 = mysql_query("IN SERT INTO items (item_code, description, quantity) VALUES ('$item_code1', '$description1' ,'$quantity1')" );
if($query1 && $item_code2)
{
$query2 = mysql_query("IN SERT INTO items (item_code, description, quantity) VALUES ('$item_code2', '$description2' ,'$quantity2')" );
}
}
?>
[/PHP]

If you have any other solutions, please reply.

Thank you in advance.

[ronverdonk]

For you current sample there is a shorter way.

Note: I must stress again that is is extremely important that you sanitize and validate any data that is entered by a user and stored in a database.

The sample here assumes that you have no other $_POST values but the ones shown. Since the POSTed names are identical except for the suffix number, you can read the values via a loop. Just append the counter (starting at 1) to the name and you can address the POST array keys. E.g.[php]<?php

for ($i=1; $i<=(count($_PO ST)/3);$i++) {
if (isset($_POST['item_code'.$i]) AND
isset($_POST['description'.$ i]) AND
isset($_POST['quantity'.$i])) {
//=============== =============== =============== ===========
// CAUTION:
//
// YOU MUST SANITIZE THE DATA WHICH YOU ARE GOING TO INSERT!!
//=============== =============== =============== ===========
$item_code = $_POST['item_code'.$i];
$description = $_POST['description'.$ i];
$quantity = $_POST['quantity'.$i];

if ($item_code!="" )
{
$sql = "INSERT INTO items (item_code, description, quantity) VALUES ('$item_code',' $description',' $quantity')";
$query = mysql_query($sq l);
if (!query)
{
echo "INSERT error in $sql. MySQL error: ".mysql_error() ;
//=============== =============== =============== ====
// Do here whatever you have to do after this error
//=============== =============== =============== ====
}
}
}
}
?>[/php]
Ronald

[backups2007]

Sanitize? How will I do that? Could you please explain.

[Markus]

Sanitize? How will I do that? Could you please explain.

Google mysql injection.
:)

[ronverdonk]

SQL injection is a technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. http://en.wikipedia.org/wiki/SQL_injection

There are zillions of tutorials and articles on the web addressing the dangers of code attacks and SQL injection. So I'll just point you to a couple of those.
MySQL - SQL Injection Prevention
Preventing MySQL Injection attacks with PHP
SQL Injection Walkthrough

Ronald

[backups2007]

I will be running my program over an intranet. Access will and can only be done within the company. Will I still be needing to sanitize it?

[Markus]

I will be running my program over an intranet. Access will and can only be done within the company. Will I still be needing to sanitize it?

It's good practice.
What if you had a naughty employee whom you had angered?

[ronverdonk]

I will be running my program over an intranet. Access will and can only be done within the company. Will I still be needing to sanitize it?

Believe me, over 40 years experience taught me:

never, ever, trust any data coming from any source, whether it's a person, an organization, a system, the Queen or a gift out of the sky!

Ronald

[backups2007]

One more thing, how can your code be enhanced in such a way that it can be applied to this source code?

[PHP]<?
if($part_no1!=" " || $mid1!="")
{
$querypa1 = mysql_query("IN SERT INTO so_parts (part_id, qty, so_no) VALUES ('$part_no1','$ pquantity1','$s o_no')");
mysql_query("UP DATE part_inventory SET qty_in_hand = qty_in_hand - '$pquantity1' WHERE part_id = '$part_no1'");
mysql_query("UP DATE part_inventory_ s SET qty_minus = qty_minus + '$pquantity1' WHERE part_id = '$part_no1'");

$call_parts1 = mysql_query("SE LECT parts_so_id FROM so_parts WHERE so_no = '$so_no'");

while($row=mysq l_fetch_array($ call_parts))
{
$parts_so_id = $row['parts_so_id'];
}
$queryma1 = mysql_query("IN SERT INTO so_materials (mat_id, qty, so_no) VALUES ('$mid1','$mq1' ,'$so_no')");
if($querypa1 || $queryma1)
{
if($part_no2!=" " || $mid2!="")
{
$querypa2 = mysql_query("IN SERT INTO so_parts (part_id, qty, so_no) VALUES ('$part_no2','$ pquantity2','$s o_no')");
mysql_query("UP DATE part_inventory SET qty_in_hand = qty_in_hand - '$pquantity2' WHERE part_id = '$part_no2'");
mysql_query("UP DATE part_inventory_ s SET qty_minus = qty_minus + '$pquantity2' WHERE part_id = '$part_no2'");

$call_parts2 = mysql_query("SE LECT parts_so_id FROM so_parts WHERE so_no = '$so_no'");

while($row=mysq l_fetch_array($ call_parts))
{
$parts_so_id = $row['parts_so_id'];
}
$queryma2 = mysql_query("IN SERT INTO so_materials (mat_id, qty, so_no) VALUES ('$mid2','$mq2' ,'$so_no')");
if($querypa2 || $queryma2)
{
if($part_no3!=" " || $mid3!="")
{
$querypa3 = mysql_query("IN SERT INTO so_parts (part_id, qty, so_no) VALUES ('$part_no3','$ pquantity3','$s o_no')");
mysql_query("UP DATE part_inventory SET qty_in_hand = qty_in_hand - '$pquantity3' WHERE part_id = '$part_no3'");
mysql_query("UP DATE part_inventory_ s SET qty_minus = qty_minus + '$pquantity3' WHERE part_id = '$part_no3'");

$call_parts3 = mysql_query("SE LECT parts_so_id FROM so_parts WHERE so_no = '$so_no'");

while($row=mysq l_fetch_array($ call_parts))
{
$parts_so_id = $row['parts_so_id'];
}

$queryma3 = mysql_query("IN SERT INTO so_materials (mat_id, qty, so_no) VALUES ('$mid3','$mq3' ,'$so_no')");
}
}
}
}
}
?>[/PHP]

I apologize for posting a code this long. Its just that I really really need help on this as this is my first time to develop a program using PHP. Thank you in advance.

This code basically has to insert the parts, then query it right away and insert the queried parts_so_id to the so_materials table.