1. Home
  2. Forums Index
  3. Server Side / PHP Server Side Scripting 3:16 am Apr 28, 2026

Forum Moderators: coopster

Message Too Old, No Replies

Adding 4 arrays into query string to be submitted to DB

adding 4 arrays into DB through function

         

gholiath

5:44 pm on Nov 22, 2011 (gmt 0)

10+ Year Member



Hello Everyone,

I have a problem where im trying to take 4 arrays, and add each element 1 at a time to a DB query string, then once the string is finished being built, it adds the information to the DB, and then repeats the process for as many times as there are values in the array.

Each array will have the same # of items in each one, though each on has different information.

Everything works as intended, up until my array function build, its building the string but incorrectly, and i think that is the reason why it will not insert into my DB. Can anyone help?

I removed the log in information blank, though it is correct on my local doc. And works, as you can see the 1st section where i enter info into a different DB table, that part works just fine, refer to the //Problem area starts here. 



<?php

//import variables


$dishname = $_POST['recipename'];
$ingcount = $_POST['ingcount'];
$dishid = $_POST['dishid'];
$rcat = $_POST['rcat'];
$cenviro = $_POST['cenviro'];
$ctemp = $_POST['ctemp'];
$ptime = $_POST['ptime'];
$ctime = $_POST['ctime'];
$stime = $_POST['stime'];

// connect to DB

$conn1 = @mysql_connect("localhost", "UID", "UPW") or die("cannot connect to the database!" . mysql_error() );

mysql_select_db("recipes", $conn1);


// add info to Dish DB table "rtable"


$insrinfo = "update rtable set cenviro='$cenviro', ctemp='$ctemp', ptime='$ptime', ctime='$ctime', stime='$stime' where recipename='$dishname'";
if (!mysql_query($insrinfo,$conn1))
 {
 die('Error: Inserting Recipe fields ' . mysql_error());
}

$idata = mysql_real_escape_string($_POST['instruction']);
$idata = htmlspecialchars($idata);
$idata = nl2br($idata);
$isql = "update rtable set instruction = '$idata' where recipename='$dishname'";
if (!mysql_query($isql,$conn1))
 {
 die('Error: Inserting Recipe Instructions ' . mysql_error());
}

mysql_close($conn1);

?>


<?php 
//import variables
$dishname = $_POST['recipename'];
$dishid = $_POST['dishid'];
$inga = $_POST['inga'];
$ingq = $_POST['ingq'];
$ingn = $_POST['ingn'];
$ingc = $_POST['ingc'];
$ingcount = $_POST['ingcount'];

// connect to DB

$conn2 = @mysql_connect("localhost", "UID", "UPW") or die("cannot connect to the database!" . mysql_error() );

mysql_select_db("recipes", $conn2);



//create ingredients funtion

function ext_ing() {

$dishid = $_POST['dishid'];
$inga = $_POST['inga'];
$ingq = $_POST['ingq'];
$ingn = $_POST['ingn'];
$ingc = $_POST['ingc'];
$ingcount = $_POST['ingcount'];

$i=1;
do
 {

$inginfo = "insert into rlist (id, dishid, inga, ingq, ingn, ingc) values ('', '$dishid'";

 
foreach($inga as $v1)
 {
 
 
 foreach($ingq as $v2)
 {

 
 foreach($ingn as $v3)
  {
  

  foreach($ingc as $v4)
  {
  $inginfo .= "'$v1', ";
  $inginfo .= "'$v2', ";  
  $inginfo .= "'$v3', ";  
  $inginfo .= "'$v4');";
  }
  } 
 }
 }

  $inginfo = "insert into rlist set (dishid = '$dishid', inga = '$v1', ingq = '$v2', ingn = '$v3', ingc = '$v4')";
  
  echo "$inginfo" . "\n\n<br><br>";
  

  $i++; 
  
}
while ($i<=$ingcount); 


 
}

mysql_close($conn2);

/* - Current Semi working
function ext_ing() {

$dishid = $_POST['dishid'];
$inga = $_POST['inga'];
$ingq = $_POST['ingq'];
$ingn = $_POST['ingn'];
$ingc = $_POST['ingc'];
$ingcount = $_POST['ingcount'];

$i=0;
do
 {

 
foreach($inga as $v1)
 {
 foreach($ingq as $v2)
 {
 foreach($ingn as $v3)
  {
  foreach($ingc as $v4)
  {
  
//   $inginfo = "insert into rlist set (dishid = '$dishid', inga = '$v1', ingq = '$v2', ingn = '$v3', ingc = '$v4')";
  $inginfo = "insert into rlist (id, dishid, inga, ingq, ingn, ingc) 
  values ('', '$dishid', '$v1', '$v2', '$v3', '$v4')";
  echo "$inginfo" . "\n\n<br><br>";
  

  $i++; 
  
  }
  }
 }
 }
 
}
while ($i<=$ingcount); 


 
}

mysql_close($conn2);
*/

?>

<!DOCTYPE html>
<html lang="en">
 <head>
 <meta charset="utf-8" />
 <title></title>
 </head>
 <body>
<?php 

// display recipe

echo "Welcome: Below is what you submited to the DB" . "<br>\n\n";
echo "$dishname" . "\n<br>";
print_r($ingn);
echo "\n\n<br>";
echo "# of ingredients in recipe: $ingcount \n\n<br><br>";


ext_ing();
echo "\n<br>";
echo "Ingredients added" . "<br>\n\n";

?>
 
 </body>
</html>

[edited by: gholiath at 5:55 pm (utc) on Nov 22, 2011]

Demaestro

5:47 pm on Nov 22, 2011 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month



You should really remove your root password from this example. If we knew the domain name it could be trouble.

Will the length of those arrays always be the same?

gholiath

5:55 pm on Nov 22, 2011 (gmt 0)

10+ Year Member



Thanks forgot i had that extra connection hidden setup in there :D

Luckily its just a local project not online.

[edited by: gholiath at 5:58 pm (utc) on Nov 22, 2011]

gholiath

5:57 pm on Nov 22, 2011 (gmt 0)

10+ Year Member



im not sure what you mean, by the length, the 4 arrays all have different information in them, so i guess the length would always be different.
array1 = int
array2 = varchar
array3 = varchar
array4 = varchar

httpwebwitch

7:29 pm on Nov 22, 2011 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member





From what I see in the code, you're getting (for example):

1 => [a,b,c],
2 => [d],
3 => [e,f],
4 => [g]

and you want the output to be:

a,d,e,g
a,d,f,g
b,d,e,g
b,d,f,g
c,d,e,g
c,d,f,g

is that correct?

And tell me, is this because you want to denormalize "substitution" ingredients in a recipe, like "2tsp of butter or margarine", so you're writing the recipe once with butter, and again with margarine?

I'm guessing

gholiath

8:50 pm on Nov 22, 2011 (gmt 0)

10+ Year Member



That is sort of what i am getting.


the ingredient arrays are like so
amount=int, ($a)
quantity=varchar, ($q)
name=varchar, ($n)
category=varchar ($c)

amount quantity name category
1 cup milk dairy
2 pounds ground beef Meat
etc....

each array has as many values as there are ingredients, so if there 4 ingredients there there will be 16 values. 4 for each array.

i want the output to be a concatenation of all arrays

using 4 ingredients
1. ($a, $q, $n, $c)
2. ($a, $q, $n, $c)
3. ($a, $q, $n, $c)
4. ($a, $q, $n, $c)

each one also coupled with a recipeid or dishid

I only want to output each value of each array 1 time,

so (dishid, $a, $q, $n, $c) gets inserted into the DB 1 time for each set of ingredients. The dishid remains the same for all ingredients.
but i want it to keep inserting into DB until all ingredients have been added

httpwebwitch

12:37 am on Nov 23, 2011 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Ok I get it now

you have 4 POST variables, and they're populated in the form like <input name='inga[]'/>

So, the $_POST array contains:

$_POST['inga'] = [1,2,3,4]
$_POST['ingq'] = ['tsp','tsp','cup','pinch']
$_POST['ingn'] = ['baking soda','flour','cream','nutmeg']
$_POST['ingc'] = ['powder','carbs','dairy','spice']

I'm making those up and that would be a terrible recipe. But is that basically what your $_POST looks like?

In that case, I'd loop only once, with a "for" instead of nested foreaches

<?php
for($i=0;$i<count($_POST['inga'])){
$inga = $_POST['inga'][$i];
$ingq = $_POST['ingq'][$i];
$ingn = $_POST['ingn'][$i];
$ingc = $_POST['ingc'][$i];

// construct your SQL INSERT statement here, using $inga,$ingq,$ingn,$ingc...

}
?>

If you're really crafty, you'll build a large bulk INSERT statement and execute it all at once, and not do a mysql_query() inside the loop.

httpwebwitch

12:40 am on Nov 23, 2011 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



You should also review the syntax for the INSERT statement.

insert into rlist set (dishid = '$dishid', inga = '$v1', ingq = '$v2', ingn = '$v3', ingc = '$v4')


That's how you build an UPDATE. An INSERT should look like:

INSERT into tablename VALUES (1,'tsp','baking soda','powder');

see: [dev.mysql.com...]

gholiath

1:31 am on Nov 23, 2011 (gmt 0)

10+ Year Member



I like the idea of the bulk statement, but im not really sure how to do that, im still very new to all this.

I like what you have set up. Your absolutely right i was thinking of running the insert in the loop.

Yes, i was trying many different ways to try and get it to work, originally i had it setup in that fashion.

Going to try it, i think what you are suggesting will work. I found a way that worked (similar with what you have but in a do loop and just creating the insert query string) but my count method is screwed up, there is only 2 items but its counted as (0,1)m but when i run it im getting 3 different outputs 2 correct with the right info, but a 3rd blank.

Ill be back :)

Ty very much for your time and help on this!

gholiath

2:30 am on Nov 23, 2011 (gmt 0)

10+ Year Member



@ hhtpwebwitch

I tried what you suggested, and it is working, however im still running into the same issue where its adding an extra query string that is blank except for the dish id, all the other fields are blank.

I know it has something to do with the way it starts from 0 when it counts but it seems like its trying to reach the number of ingredients, well say 4, but its starting at 0 and thereby displaying 5, (0, 1, 2, 3, 4)

Argh!

Ty very much for your time and help on this!

httpwebwitch

4:24 am on Nov 23, 2011 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



you said you're using a "do" instead of a "for"...

they don't quite act the same way. In a "do" loop, the condition is checked at the end of the loop, in a "while" or "for" loop, the condition is checked at the beginning.

[php.net...]

also bear in mind that in an array with 4 elements, the count() is 4. But the ordinals of the array are [0,1,2,3]. So make sure your loop is using a less-than operator (<) and not a less-than-or-equal operator (<=).

It may help to echo out the iterator while you're looping, so you can see what it's at

echo $i;

Do a little light reading about loop structures... I'm sure you can figure this one out :)

gholiath

2:48 pm on Nov 23, 2011 (gmt 0)

10+ Year Member



Yea i did have it in a for loop and it was working, but still had the same issue. I redid everything in the for loop like you mentioned, but it was still giving me the extra blank on, so i ended creating some math that shrank it by 1, i could swear i tried just a < last night and it was giving me the extra blank field, however when i removed the extra math this morning and changed my <= to < now its working great!

Thanks again for all your help.

If you could, would you give me a small example of how to do the
"you'll build a large bulk INSERT statement and execute it all at once"

is it something like each run through put the variables into a new array or string and then save it and execute the whole thing after the loop but while still inside the function?

Thanks again!

httpwebwitch

3:07 pm on Nov 23, 2011 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



the syntax for INSERTing more than one row looks like this:

INSERT INTO tbl_name (a,b,c) VALUES (1,2,3) , (4,5,6) , (7,8,9);

see how each row is in parentheses (1,2,3) and the rows are separated by commas.

So, once you have everything else working, that's an optimization you can try.

gholiath

3:46 pm on Nov 23, 2011 (gmt 0)

10+ Year Member



Im confused since all the values come from the form in an array, how do i get the values from each loop through stored individually, since each array has the same name. And since the amount of ingredients can always be different, i cant exactly hard code the values into it. So i would have to pass the values to a variable first then insert the variable into the string.

INSERT INTO tbl_name (inga,ingc,ingn,ingc) VALUES ($inga, $ingq, $ingn, $ingc) , ($inga, $ingq, $ingn, $ingc) , ($inga, $ingq, $ingn, $ingc);

I would think something like that would end up just sticking in the same values into each one?
So i would need to assign the pulled values somehow. Drawing a blank atm :(

I believe that i would need to set up a build as well to add the extra , ($inga, $ingq, $ingn, $ingc) for each additional ingredient right?

Its all still kinda confusing for me, but it at least its coming along :)

eelixduppy

5:03 pm on Nov 23, 2011 (gmt 0) 




$q = "INSERT INTO `table_name` VALUES "; 
$len = count($_POST['inga']); 

for($i=0;$i<$len; $i++){ 
  $inga = $_POST['inga'][$i]; 
  $ingq = $_POST['ingq'][$i]; 
  $ingn = $_POST['ingn'][$i]; 
  $ingc = $_POST['ingc'][$i]; 

  $q += sprintf("(%d, '%s', '%s', '%s')", $inga, $ingq, $ingn, $ingc); 

  // append comma on all but last 
  if($i != $len-1) 
    $q += ', ';  
  } 
} 
// execute query


Note, this still isn't ideally coded, but it should be enough to give you the idea of how this should work.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

  1. Home
  2. Forums Index
  3. Server Side / PHP Server Side Scripting 3:16 am Apr 28, 2026