Looping multiple rows into Database

[gwpaul]

I have an application that is driving me nuts.  I have a form that is dynamically created from a query, it works fine.  The user may enter a quantity from a <select> .  Once they click the button to post, I want the results to be entered into a table.  There are 7 columns of data, the number of rows will be determined by the number <select>s the user chooses. (It is a list of products they can specify a quantity for each product)

 

I have been unable to get this to work completely.

 

I have tried this:

$query_Recordset3 = " INSERT  INTO temporder ( qnt, prod_id)  VALUES ";

foreach($_POST['qnt'] AS $prod_id  => $qnt )

{
mysql_real_escape_string($query_Recordset3);

  $query_Recordset3 .= "('".$qnt."','".$prod_id."'),"; 
}

$query_Recordset3 = substr($query_Recordset3,0,-1); // get rid of trailing comma, since we cant use implode  
$Recordset3 = mysql_query($query_Recordset3, $szabo) or die(mysql_error()); 

 

This works perfect except it will only insert the two column (qny, prod_id).  Once I try to add more columns, it fails.  I have tried adding new foreach statements, but the inserts begin to multiply.

 

Is there a way to have the $value ($qnt) contain all the Values that need to be inserted?

 

Next I tried this:

 

$qnt=$_POST['qnt'];
$prod_id=$_POST['prod_id'];
$ptype=$_POST['ptype'];

$git=mysql_query("SELECT prod_id FROM garyco WHERE prod_id>='1'");
$getf = mysql_fetch_array($git);
do{
echo  $getf['prod_id'].',';
}
while ($getf = mysql_fetch_array($git));

$q=mysql_num_rows($git);

$qi="INSERT INTO temporder (qnt, prod_id, ptype) VALUES";

for( $i=0;     $i<count($q);    $i++);   {

$qi .= "('".$qnt.'", "'.$prod_id.'", "'.$ptype."')$i,";

}

$qi= substr($qi, 0, -1);
$result=mysql_query($qi, $szabo);

 

This does not insert anything into the database.

 

So the issue is, how to insert mulitple rows of data into a table.  I am fine with rethinking the entire thing, but this is driving me nuts.

 

Can someone point me in the right direction?

 

Thank you

 

Gary

[Jessica]

You should have a quantity column rather than insert the same data a number of times.

[gwpaul]

Thank you for your reply, but I'm not sure what you mean, could you give me more specifics?

 

Thank you

 

Gary

[Jessica]

I misread your initial post, I guess "qnt" is your quantity column.

 

After your query, you need to check for errors. change the line to this

$result=mysql_query($qi, $szabo) or die(mysql_error().' SQL: '.$qi);

and see what it outputs.

[gwpaul]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1 SQL: INSERT INTO temporder (qnt, prod_id, ptype) VALUES('Array", "", "Radio')1

[Jessica]

So the values you are trying to insert are arrays.

$qi .= "('".$qnt.'", "'.$prod_id.'", "'.$ptype."')$i,";

This line puts the array in the string. You should use the value in the array at the current key.

I would use a foreach instead of a for loop.

[gwpaul]

I have a foreach loop in the first codebox, the issue is that I can only get two columns (qnt, prod_id).  How could I get the values into more than two columns. I had this

 

$query_Recordset3 = " INSERT  INTO temporder ( qnt, prod_id, ptype, mfg)      VALUES ";

foreach($_POST['qnt'] AS $prod_id  =>   $qnt)
foreach($_POST['ptype'] AS $prod_id  =>   $ptype)
foreach($_POST['mfg'] AS $prod_id  =>   $mfg){

mysql_real_escape_string($query_Recordset3);
      $query_Recordset3 .=                  "('".$qnt."','".$prod_id."','".$ptype."','".$mfg."'),"; 
}

 

But the values multiplied and jumbled.

 

Thank you for you help.

 

ps, is there some way to shut off the captcha and quiz?

[Jessica]

I don't think you understand how a foreach works... What do you think your code is doing?

[gwpaul]

I dont understand how to make foreach loops insert multiple rows of multiple columns into a table, I dont know what the code is doing.

 

Is a foreach loop the best choice to accomplish this?  If so, what do I need to change. If this is not the way to insert multiple rows of multiple columns, what in your opinion is?

 

Gary

[Jessica]

It's worrisome that you don't know what your code is doing.

 

$query_Recordset3 = " INSERT  INTO temporder ( qnt, prod_id, ptype, mfg)      VALUES ";

foreach($_POST['qnt'] AS $prod_id  =>   $qnt){
$ptype = $_POST['ptype'][$prod_id];
$mfg = $_POST['mfg'][$prod_id];
$query_Recordset3 .= "('".$qnt."','".$prod_id."','".$ptype."','".$mfg."'),"; 
}

etc...

[jazzman1]

@gwpaul, I've just written a very simple script to you, how to insert multiple values into DB.

Take a look at the example:

// array containing data
$array = array(
    'name' => array("John", "Abbi", "Barbara"),
    'email' => array("j.doe@intelligence.gov", "abbi@gmail.com", "barbara@yahoo.ca"),
    'created_at' => array("2011-08-13", "2012-01-11", "2012-05-05")
);


// build query...
$sql = "INSERT INTO `tbl_name`";
// implode keys of $array...
$sql .= " (`" . implode("`, `", array_keys($array)) . "`) VALUES";
// loops values of 
// multiple Dimensional Arrays
$i = 0;

while ($i < count($array)):
    
    $sql .="(";

    foreach ($array as $value) {
        $sql .= "'" . $value[$i] . "',";
    }
    // trim the last comma from a query string
    $sql = rtrim($sql, ',');
    
    $i++;
    
    $sql .= "),";
endwhile;
// trim the last comma from a query string
$sql = rtrim($sql, ',');
echo '<pre>' . print_r($sql, true) . '</pre>';

// proper output
// INSERT INTO `tbl_name` (`name`, `email`, `created_at`) VALUES('John','j.doe@intelligence.gov','2011-08-13'),('Abbi','abbi@gmail.com','2012-01-11'),('Barbara','barbara@yahoo.ca','2012-05-05')

// execute query...
$result = mysql_query($sql) or die(mysql_error());

 

If you don't understand something, don't  hesitate to asк in the forum.

[gwpaul]

jesirose

 

Thank you for your help, your solution worked.  I was a little put off by your need to add sniping comments that had no productive value.  People come to help forums because they don't understand something, it is the very nature of a help forum. 

 

Having said that, I appropriate your taking the time to help. (You may wish to add that to the manual, I was not able to find anything that covered the issue on the foreach page.)

 

jazzman1

 

Thank you for your reply.  You would not believe the amount of research I did on this problem, posted to several forums, read and reread the manual and dozens and dozens of pages on it.  It seemed to me that it should be a common issue and somehow I did not find any solutions.  Now I have two.

 

Again, thank you for your reply.

 

Gary

[Jessica]

It definitely is productive to point out that you need to understand what your own code is doing. If you don't, you end up writing code that doesn't work and not knowing how to fix it.

 

There's nothing to ADD to the manual related to this issue because it's already there. You simply don't understand how to use a foreach or other type of loop.

[gwpaul]

I don't really want to belabor the point, but we have already established that people come to help forums because they don't understand something.

 

Second, please feel free to point out where in the manual, specifically on the foreach page that it references using the loop for an insert.  I would repeat my suggestion that you consider adding it to the manual.

 

Last, please feel free to point out the productive value of your statement, and I'll quote the remarks section of your post in it's entirety.

"It's worrisome that you don't know what your code is doing."

 

Offering remarks that are meant to be rude or belittling rarely plays to the audience the way the author intended.

 

Again, thank you for your help, and please to visit the manual and consider offer your code, it would have saved us both some time.

 

Gary

[Christian F.]

She's not belittling you, nor is she being rude. It is quite worrisome that you don't know what your code does. Just the same as you'd be worried if you hired a carpenter to build your house, and he came up to you and confessed he really had no idea about what he he was doing.

 

Let me quote ManicDan's signature:

Think we're being rude?  Maybe you asked a bad question or you're a Help Vampire.  Trying to argue intelligently?  Please read this.

I highly recommend reading at least the first link.

[Jessica]

Are you seriously still suggesting *I* use my time to add that code to the manual? Why don't YOU do it??

It doesn't matter what you do with a for loop, you need to understand the syntax. The fact that you put 3 for each after one another indicates YOU don't understand the syntax and should learn it.

 

Read the links, they're good.