By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,723 Members | 1,876 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,723 IT Pros & Developers. It's quick & easy.

how to insert rows to the database in a loop

P: 92
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("INSERT INTO items (item_code, description, quantity) VALUES ('$item_code1','$description1','$quantity1')");
if($query1 && $item_code2)
{
$query2 = mysql_query("INSERT INTO items (item_code, description, quantity) VALUES ('$item_code2','$description2','$quantity2')");
}
}
?>
[/PHP]

If you have any other solutions, please reply.

Thank you in advance.
Feb 18 '08 #1
Share this Question
Share on Google+
8 Replies


ronverdonk
Expert 2.5K+
P: 4,258
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($_POST)/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($sql);
if (!query)
{
echo "INSERT error in $sql. MySQL error: ".mysql_error();
//=================================================
// Do here whatever you have to do after this error
//=================================================
}
}
}
}
?>[/php]
Ronald
Feb 18 '08 #2

P: 92
Sanitize? How will I do that? Could you please explain.
Feb 18 '08 #3

Markus
Expert 5K+
P: 6,050
Sanitize? How will I do that? Could you please explain.
Google mysql injection.
:)
Feb 18 '08 #4

ronverdonk
Expert 2.5K+
P: 4,258
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
Feb 18 '08 #5

P: 92
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?
Feb 19 '08 #6

Markus
Expert 5K+
P: 6,050
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?
Feb 19 '08 #7

ronverdonk
Expert 2.5K+
P: 4,258
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
Feb 19 '08 #8

P: 92
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("INSERT INTO so_parts (part_id, qty, so_no) VALUES ('$part_no1','$pquantity1','$so_no')");
mysql_query("UPDATE part_inventory SET qty_in_hand = qty_in_hand - '$pquantity1' WHERE part_id = '$part_no1'");
mysql_query("UPDATE part_inventory_s SET qty_minus = qty_minus + '$pquantity1' WHERE part_id = '$part_no1'");

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

while($row=mysql_fetch_array($call_parts))
{
$parts_so_id = $row['parts_so_id'];
}
$queryma1 = mysql_query("INSERT INTO so_materials (mat_id, qty, so_no) VALUES ('$mid1','$mq1','$so_no')");
if($querypa1 || $queryma1)
{
if($part_no2!="" || $mid2!="")
{
$querypa2 = mysql_query("INSERT INTO so_parts (part_id, qty, so_no) VALUES ('$part_no2','$pquantity2','$so_no')");
mysql_query("UPDATE part_inventory SET qty_in_hand = qty_in_hand - '$pquantity2' WHERE part_id = '$part_no2'");
mysql_query("UPDATE part_inventory_s SET qty_minus = qty_minus + '$pquantity2' WHERE part_id = '$part_no2'");

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

while($row=mysql_fetch_array($call_parts))
{
$parts_so_id = $row['parts_so_id'];
}
$queryma2 = mysql_query("INSERT INTO so_materials (mat_id, qty, so_no) VALUES ('$mid2','$mq2','$so_no')");
if($querypa2 || $queryma2)
{
if($part_no3!="" || $mid3!="")
{
$querypa3 = mysql_query("INSERT INTO so_parts (part_id, qty, so_no) VALUES ('$part_no3','$pquantity3','$so_no')");
mysql_query("UPDATE part_inventory SET qty_in_hand = qty_in_hand - '$pquantity3' WHERE part_id = '$part_no3'");
mysql_query("UPDATE part_inventory_s SET qty_minus = qty_minus + '$pquantity3' WHERE part_id = '$part_no3'");

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

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

$queryma3 = mysql_query("INSERT 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.
Feb 19 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.