Im sure some simple mistake

piznac · August 28, 2007

Hello again guys,...

I have wrote this function to cycle through an unlimitied amount of sql updates(with a bit of help ages ok from a member here):

<?php
//insert secondary order info
        function insert_rows($item_num,$item_desc,$item_qty,$emb_loc1,$num,$size,$artwork,$num2){


$flds = array( 'item_num' , 'item_desc' , 'item_qty' , 'emb_loc1', 'num', 'size', 'artwork');
for($i=0;$i<count($item_num);$i++) {
$qtmp = array();
    foreach($flds as $fld)
        if (trim(stripslashes($_POST[$fld][$i])) != '')
            $qtmp[] = $fld . " = '" . mysql_escape_string(trim(stripslashes($_POST[$fld][$i]))) . "'";
    foreach($num2 as $num3){

    $q = "UPDATE c_or_details SET " . implode(', ',$qtmp) . " WHERE `subitemnum` = '$num3'";
    $rs = mysql_query($q) or die ('Problem with 1 the query: ' . $q . '<br>' . mysql_error());
    
}
}
}
?>

So everything is working fine with this,.. so I decided to do it again with this:

<?php
//insert multi locations into database
        function insert_multi($item_num,$item_desc,$item_qty,$emb_loc1,$num,$size,$artwork1,$emb_loc2,$artwork2,$emb_loc3,$artwork3,$emb_loc4,$artwork4,$emb_loc5,$artwork5,$subnum){
        $flds = array( 'item_num', 'item_desc', 'emb_loc1', 'artwork1', 'emb_loc2', 'artwork2', 'emb_loc3', 'artwork3', 'emb_loc4', 'artwork4', 'emb_loc5', 'artwork5', 'size', 'item_qty', 'num');

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


$qtmp = array();
    foreach($flds as $fld)   
        if (trim(stripslashes($_POST[$fld][$i])) != '')
        	$qtmp[] = $fld . " = '" . mysql_escape_string(trim(stripslashes($_POST[$fld][$i]))) . "'";
        	
    foreach($subnum as $sub){
       
    $q = "UPDATE loc_logo_ex SET " . implode(', ',$qtmp) . " WHERE `subnum` = '$sub'"; 
    $rs = mysql_query($q) or die ('Problem with 1 the query: ' . $q . '<br>' . mysql_error()); 
     echo "$q<br /> <br />"; 
}
   
}
}
?>

This one though is double looping everything. Meaning that if thier are two records to update it runs the query 4 times and ends up with the wrong data. When run with two changes,.. eg $item_num = 1 & in the 2nd query $item_num = 4,.. this is what the output is:

UPDATE loc_logo_ex SET item_num = '1', item_desc = '1', emb_loc1 = 'NA', artwork1 = 'NA', emb_loc2 = 'NA', artwork2 = 'NA', emb_loc3 = 'NA', artwork3 = 'NA', emb_loc4 = 'NA', artwork4 = 'NA', emb_loc5 = 'NA', artwork5 = 'NA', size = 'ONE SIZE', item_qty = '1' WHERE `subnum` = '23'

UPDATE loc_logo_ex SET item_num = '1', item_desc = '1', emb_loc1 = 'NA', artwork1 = 'NA', emb_loc2 = 'NA', artwork2 = 'NA', emb_loc3 = 'NA', artwork3 = 'NA', emb_loc4 = 'NA', artwork4 = 'NA', emb_loc5 = 'NA', artwork5 = 'NA', size = 'ONE SIZE', item_qty = '1' WHERE `subnum` = '24'

UPDATE loc_logo_ex SET item_num = '4', item_desc = '1', emb_loc1 = 'NA', artwork1 = 'NA', emb_loc2 = 'NA', artwork2 = 'NA', emb_loc3 = 'NA', artwork3 = 'NA', emb_loc4 = 'NA', artwork4 = 'NA', emb_loc5 = 'NA', artwork5 = 'NA', size = 'ONE SIZE', item_qty = '1' WHERE `subnum` = '23'

UPDATE loc_logo_ex SET item_num = '4', item_desc = '1', emb_loc1 = 'NA', artwork1 = 'NA', emb_loc2 = 'NA', artwork2 = 'NA', emb_loc3 = 'NA', artwork3 = 'NA', emb_loc4 = 'NA', artwork4 = 'NA', emb_loc5 = 'NA', artwork5 = 'NA', size = 'ONE SIZE', item_qty = '1' WHERE `subnum` = '24'

??

piznac · August 28, 2007

Ok now the first one is doing the same,. lol... bumpty bump bump

Jessica · August 28, 2007

Well where does $subnum come from?

piznac · August 28, 2007

It's passed via a post var,...from the form associated with this. I can post the form code if that will help.

Jessica · August 28, 2007

do a print_r($subnum) and see if it has doubles of the numbers?

The form code won't help, it's where you set $subnum and what you do with it before this code that matters.

Somewhere you need to have

$subnum = $_POST['form field name here'];

piznac · August 28, 2007

Yeah I did a echo on that earlier,.. and its good,..

<?php
case "up_nmulti";
if(!isset($_POST['multi']) && $_POST['multi'] != "t"){
$num = $_GET['num'];
$item_num = $_POST['item_num'];
$item_desc = $_POST['item_desc'];
$item_qty = $_POST['item_qty'];
$emb_loc1 = $_POST['emb_loc1'];
$size = $_POST['size'];
$artwork = $_POST['artwork']; 
$num2 = $_POST['num2'];
require_once("/var/www/html/include/classes/confirm.inc.php");
$this = new Confirm();
$this->insert_rows($item_num,$item_desc,$item_qty,$emb_loc1,$num,$size,$artwork,$num2);
$this->dis_order($num);
}else{
$num = $_GET['num'];
$item_num = $_POST['item_num'];
$item_desc = $_POST['item_desc']; 
$item_qty = $_POST['item_qty'];
$emb_loc1 = $_POST['emb_loc1'];
$size = $_POST['size'];
$artwork1 = $_POST['artwork1'];
$emb_loc2 = $_POST['emb_loc2'];
$artwork2 = $_POST['artwork2'];
$emb_loc3 = $_POST['emb_loc3'];
$artwork3 = $_POST['artwork3'];
$emb_loc4 = $_POST['emb_loc4'];
$artwork4 = $_POST['artwork4'];
$emb_loc5 = $_POST['emb_loc5'];
$artwork5 = $_POST['artwork5'];
$subnum = $_POST['subnum'];   
require_once("/var/www/html/include/classes/confirm.inc.php");
$this = new Confirm();
$this->insert_multi($item_num,$item_desc,$item_qty,$emb_loc1,$num,$size,$artwork1,$emb_loc2,$artwork2,$emb_loc3,$artwork3,$emb_loc4,$artwork4,$emb_loc5,$artwork5,$subnum);
$this->dis_order($num);
}
break;
?>

Jessica · August 28, 2007

What did the print_r() display?

piznac · August 28, 2007

With one record =

Array ( [0] => ) Array ( [0] => )

Jessica · August 28, 2007

...it's basically an empty array ?

piznac · August 28, 2007

well on that particular one yes,.. most of the times it would be a array of numbers.

Here is one with actual records,.. this is for two records. Does this mean it is repeating that var?

Array ( [0] => 2 [1] => 3 ) Array ( [0] => 2 [1] => 3 )

Jessica · August 28, 2007

Yes, see how it has the same entries twice? That's why it's putting them in twice...

piznac · August 28, 2007

hmm,.. yeah ,.. wonder why though.

piznac · August 28, 2007

Ok when I run the first function,.. for the var $num2 I get this:

Array ( [0] => 216 [1] => 217 [2] => 218 [3] => 219 )

So it would appear that its only running it once,.. but it still does the sql statement times the amount of entries,.. twice. So if there are four records it runs it 16 times. Like this:

Array ( [0] => 216 [1] => 217 [2] => 218 [3] => 219 ) UPDATE c_or_details SET item_num = '1', item_desc = '2', item_qty = '2', emb_loc1 = 'Left Chest', size = 'ONE SIZE', artwork = 'Winter.jpg' WHERE `subitemnum` = '216'

UPDATE c_or_details SET item_num = '1', item_desc = '2', item_qty = '2', emb_loc1 = 'Left Chest', size = 'ONE SIZE', artwork = 'Winter.jpg' WHERE `subitemnum` = '217'

UPDATE c_or_details SET item_num = '1', item_desc = '2', item_qty = '2', emb_loc1 = 'Left Chest', size = 'ONE SIZE', artwork = 'Winter.jpg' WHERE `subitemnum` = '218'

UPDATE c_or_details SET item_num = '1', item_desc = '2', item_qty = '2', emb_loc1 = 'Left Chest', size = 'ONE SIZE', artwork = 'Winter.jpg' WHERE `subitemnum` = '219'

UPDATE c_or_details SET item_num = '2', item_desc = '3', item_qty = '3', emb_loc1 = 'Left Chest', size = 'ONE SIZE', artwork = 'Winter.jpg' WHERE `subitemnum` = '216'

UPDATE c_or_details SET item_num = '2', item_desc = '3', item_qty = '3', emb_loc1 = 'Left Chest', size = 'ONE SIZE', artwork = 'Winter.jpg' WHERE `subitemnum` = '217'

UPDATE c_or_details SET item_num = '2', item_desc = '3', item_qty = '3', emb_loc1 = 'Left Chest', size = 'ONE SIZE', artwork = 'Winter.jpg' WHERE `subitemnum` = '218'

UPDATE c_or_details SET item_num = '2', item_desc = '3', item_qty = '3', emb_loc1 = 'Left Chest', size = 'ONE SIZE', artwork = 'Winter.jpg' WHERE `subitemnum` = '219'

UPDATE c_or_details SET item_num = '3', item_desc = '2', item_qty = '2', emb_loc1 = 'Left Chest', size = 'ONE SIZE', artwork = 'Winter.jpg' WHERE `subitemnum` = '216'

UPDATE c_or_details SET item_num = '3', item_desc = '2', item_qty = '2', emb_loc1 = 'Left Chest', size = 'ONE SIZE', artwork = 'Winter.jpg' WHERE `subitemnum` = '217'

UPDATE c_or_details SET item_num = '3', item_desc = '2', item_qty = '2', emb_loc1 = 'Left Chest', size = 'ONE SIZE', artwork = 'Winter.jpg' WHERE `subitemnum` = '218'

UPDATE c_or_details SET item_num = '3', item_desc = '2', item_qty = '2', emb_loc1 = 'Left Chest', size = 'ONE SIZE', artwork = 'Winter.jpg' WHERE `subitemnum` = '219'

UPDATE c_or_details SET item_num = '4', item_desc = '3', item_qty = '3', emb_loc1 = 'Left Chest', size = 'ONE SIZE', artwork = 'Winter.jpg' WHERE `subitemnum` = '216'

UPDATE c_or_details SET item_num = '4', item_desc = '3', item_qty = '3', emb_loc1 = 'Left Chest', size = 'ONE SIZE', artwork = 'Winter.jpg' WHERE `subitemnum` = '217'

UPDATE c_or_details SET item_num = '4', item_desc = '3', item_qty = '3', emb_loc1 = 'Left Chest', size = 'ONE SIZE', artwork = 'Winter.jpg' WHERE `subitemnum` = '218'

UPDATE c_or_details SET item_num = '4', item_desc = '3', item_qty = '3', emb_loc1 = 'Left Chest', size = 'ONE SIZE', artwork = 'Winter.jpg' WHERE `subitemnum` = '219'

As you can see it's running it once perfect,.. and if it would stop there it would be great,.. but it continues to run it 4 more times. This has to be something with the double foreach statement right?

Jessica · August 28, 2007

Hm...I don't see it. Hope someone else can help.

piznac · August 28, 2007

Thanks for trying

Jessica · August 28, 2007

You can try adding brackets with your conditional statements.

Instead of

if($x==1)
$x=5;

do:

if($x==1){
$x=5;
}

Do that for your foreach too, so you know where each ends and stuff.

piznac · August 28, 2007

Man it gets all messed up when I try that,..lol

How would you go about updating multiople rows at once?

Like if you had item1[],item2[],item3[] ect,.. and a corresponding table?

lemmin · August 28, 2007

Yeah, This block shouldn't work:

foreach($flds as $fld)
       if (trim(stripslashes($_POST[$fld][$i])) != '')
           $qtmp[] = $fld . " = '" . mysql_escape_string(trim(stripslashes($_POST[$fld][$i]))) . "'";

If you leave out the curly braces, it only includes the next line in the statement. The if statement is fine, however, since it it is two lines, it doesn't get included in the foreach. This means that $qtmp[] is only going to have one element per item_num.

piznac · August 28, 2007

Not sure I follow?

lemmin · August 28, 2007

That block I posted needs to have curly braces to work, as jesirose suggested.

change it to:

foreach($flds as $fld){
        if (trim(stripslashes($_POST[$fld][$i])) != '')
            $qtmp[] = $fld . " = '" . mysql_escape_string(trim(stripslashes($_POST[$fld][$i]))) . "'";
}

Which I think you already did.

The $qtmp[] array is populated in that loop and used in the bottom loop. You should get a string like you just posted. You need to use the VALUES clause along with it. For each item in (item1, item2, item3,) you have to have a VALUES (value1, value2, value3).

Here is the documentation for the UPDATE clause: http://dev.mysql.com/doc/refman/5.1/en/update.html

Jessica · August 28, 2007

Change it to this:

foreach($flds as $fld){
        if (trim(stripslashes($_POST[$fld][$i])) != ''){
            $qtmp[] = $fld . " = '" . mysql_escape_string(trim(stripslashes($_POST[$fld][$i]))) . "'";
        }
}

piznac · August 28, 2007

with or without the curly braces,.. it produces the same results.

<?php
//insert secondary order info
        function insert_rows($item_num,$item_desc,$item_qty,$emb_loc1,$num,$size,$artwork,$num2){
print_r($num2);


$flds = array( 'item_num' , 'item_desc' , 'item_qty' , 'emb_loc1', 'num', 'size', 'artwork');
for($i=0;$i<count($num2);$i++) {
$qtmp = array();
    foreach($flds as $fld){
        if (trim(stripslashes($_POST[$fld][$i])) != ''){
            $qtmp[] = $fld . " = '" . mysql_escape_string(trim(stripslashes($_POST[$fld][$i]))) . "'";
        }
    }
    foreach($num2 as $num3){

    $q = "UPDATE c_or_details SET " . implode(', ',$qtmp) . " WHERE `subitemnum` = '$num3'";
    $rs = mysql_query($q) or die ('Problem with 1 the query: ' . $q . '<br>' . mysql_error());
    echo "$q<br /> <br />";
    
}   

}
}?>

<?php
//insert secondary order info
        function insert_rows($item_num,$item_desc,$item_qty,$emb_loc1,$num,$size,$artwork,$num2){
print_r($num2);


$flds = array( 'item_num' , 'item_desc' , 'item_qty' , 'emb_loc1', 'num', 'size', 'artwork');
for($i=0;$i<count($num2);$i++) {
$qtmp = array();
    foreach($flds as $fld)
        if (trim(stripslashes($_POST[$fld][$i])) != '')
            $qtmp[] = $fld . " = '" . mysql_escape_string(trim(stripslashes($_POST[$fld][$i]))) . "'";
        
    
    foreach($num2 as $num3){

    $q = "UPDATE c_or_details SET " . implode(', ',$qtmp) . " WHERE `subitemnum` = '$num3'";
    $rs = mysql_query($q) or die ('Problem with 1 the query: ' . $q . '<br>' . mysql_error());
    echo "$q<br /> <br />";
    
}   

}
}
?>

both produce this:

Array ( [0] => 234 [1] => 235 )

UPDATE c_or_details SET item_num = '1', item_desc = '2', item_qty = '2', emb_loc1 = 'Left Chest', size = 'ONE SIZE', artwork = 'DSCN3361.jpg' WHERE `subitemnum` = '234'

UPDATE c_or_details SET item_num = '1', item_desc = '2', item_qty = '2', emb_loc1 = 'Left Chest', size = 'ONE SIZE', artwork = 'DSCN3361.jpg' WHERE `subitemnum` = '235'

UPDATE c_or_details SET item_num = '2', item_desc = '2', item_qty = '2', emb_loc1 = 'Left Chest', size = 'ONE SIZE', artwork = 'DSCN3361.jpg' WHERE `subitemnum` = '234'

UPDATE c_or_details SET item_num = '2', item_desc = '2', item_qty = '2', emb_loc1 = 'Left Chest', size = 'ONE SIZE', artwork = 'DSCN3361.jpg' WHERE `subitemnum` = '235'

Jessica · August 28, 2007

Oh wait, what is $num2?

piznac · August 28, 2007

There are two different functions here,... the last example I gave is of the first function. $num2 is my primary key in that table. And its retrieved the same way the "$subnum" was.

Jessica · August 28, 2007

I'm confused. Sorry Good luck,

Sign In

Im sure some simple mistake

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information