...

View Full Version : update db with each item in a loop



clunky
07-11-2007, 03:10 PM
Hi to everyone, this is my first post here and comes out of pure frustration and desperation :-|

I am trying to retreive all the items from a multi lined textarea on a form, then after selecting each one run a db update to that item in the db.
Sounds pretty straightforward to me ....
So far I am able to echo each item from my exploded list and they all show correctly.

Now the bit I am stuck with is getting each item in turn updated in the db.
It will only update the last item in the list no matter what I do.

I have tried while, foreach, do while, for, etc but no matter what I try I cannot get it to update more than the last one.....

I am presuming that my update statement cannot execute due to each item not being seperated enough so it is just selecting the last in the list.
If there is only one item then it works just fine and the update executes.
more than one and it just updates the last in the list!

If any of you people out there in the ether can point me in the right direction I would be eternally greatful.
Many thanks
clunky ;-)

timgolding
07-11-2007, 04:02 PM
Let me get this straight you have a textarea that you need to seperate each line of text and sort into an array.



<?PHP
$text=$_GET['name_of_textarea'];
$txt_array=explode (chr(13),$text);

foreach ($txt_array as $this_txt)
{//P.S im guessing sql table/entity/field names
mysql_query('insert into table (text) values ("'.$this_txt.'")');
}
?>

clunky
07-11-2007, 04:23 PM
Let me get this straight you have a textarea that you need to seperate each line of text and sort into an array.



<?PHP
$text=$_GET['name_of_textarea'];
$txt_array=explode (chr(13),$text);

foreach ($txt_array as $this_txt)
{//P.S im guessing sql table/entity/field names
mysql_query('insert into table (text) values ("'.$this_txt.'")');
}
?>


Hi Tim
Many thanks for your reply.

I am using a textarea called stock_in.
I can get it to produce an echo on the results page after the form submission, like so....
FOGG312 BLK26
FOGG805 BEI26
FOGG311 BLK26

each of these is a scanned in barcode which corresponds to items in stock.
I want to update another field in the row relative to the code.
ie. mysql_query("UPDATE " . PRODUCTS . " SET products_store='instock' where products_model='$a'");
$a would be any of the scanned in barcodes.
currently using the above example results it will only update FOGG311 BLK26

So I am presuming your solution would be something like this using my parameters....


<?PHP
$text=$_GET[stock_in];
$txt_array=explode (chr(13),$text);

foreach ($txt_array as $this_txt)
{
mysql_query("UPDATE " . PRODUCTS . " SET products_store='instock' where products_model='$this_txt");
}
?>

How does that sound?

OK an update....
It doesn't work :-(

to get the correct code format (which has spaces) I have used this...

$txt_array=explode("\n", $text);
It produces the correct codes with
echo "$this_txt...<br>";
FOGG312 BLK26 ...
FOGG311 BLK26 ...
FOGG805 BEI26...

It still updates just the last item in the list.

clunky
07-12-2007, 11:30 AM
This seems absurd.
Somewhere within some kind of loop, whether it be a for, while, foreach or whatever it must be possible to update a db and then move to the next item and update that.

why does this only update the db with the last item in the list?



my multi line list produced by the form POST is ...
FOGG312 BLK26
FOGG805 BEI26
FOGG311 BLK26

/////////PHP CODE////////
$prod = $_REQUEST[stock_in];//input from form
$wordChunks = explode("\n", $prod);//explode at the end of item name
foreach ($wordChunks as $newitems) //creates each new item name
{
echo "$newitems<br>";// prints out correctly as above, just to make sure
mysql_query("UPDATE " . PRODUCTS . " SET products_store='some value' where products_model ='$newitems'"); //update the db
}

How do I get the first items updated before the last one?
Or indeed how to update all of them in the list?
Why are the first two ignored and just the last processed?
Please help a poor lost soul :-(
Cheers

_Aerospace_Eng_
07-12-2007, 12:56 PM
You should be using or die(mysql_error()) on your mysql_query() statement because I'm guessing its likely returning an error because its trying to update an empty table.

foreach ($wordChunks as $newitems) //creates each new item name
{
echo "$newitems<br>";// prints out correctly as above, just to make sure
$sql = "UPDATE PRODUCTS SET products_store='some value' where products_model='$newitems'";
mysql_query($sql) or die('Error: '.mysql_error().'<br>The query was:'.$sql.'<br>'); //update the db
}

clunky
07-12-2007, 01:00 PM
Hi
The database table is not empty, the update only effects the last item in the string.
Lets say there are 4 items in the list, it ignores the first 3???
Its really wierd and I have now spent 2 days trying to work this out, driving me nuts......


And no or die(mysql_error()) errors produced

_Aerospace_Eng_
07-12-2007, 01:03 PM
Then where are you getting PRODUCTS from? Its not a variable so why are you trying to concatenate it? Did you even try my suggestion? I think you should. You are asking for help not us.

clunky
07-12-2007, 01:07 PM
Then where are you getting PRODUCTS from? Its not a variable so why are you trying to concatenate it? Did you even try my suggestion? I think you should. You are asking for help not us.

products is the database table (not PRODUCTS)
I didn't think I was trying to concatenate it? Where am I doing that?

All I am trying to do is use the items from the posted form and update the db with an new value using the item code in the where clause.

It does work to a degree by updating the last item in the list.
But it ignores the rest before it.
Here is the current script


$prod = $_REQUEST[stock_in];//input from form
$wordChunks = explode("\n", $prod);//explode at the end of item name /
for($i = 0; $i < count($wordChunks);$i++){
$sql = "UPDATE products SET products_store='some value' where products_model='$wordChunks[$i]'";
mysql_query($sql) or die('Error: '.mysql_error().'<br>The query was:'.$sql.'<br>'); //update the db
}

I am desperatley asking for help :-)

StupidRalph
07-12-2007, 01:11 PM
products is the database table (not PRODUCTS)
I didn't think I was trying to concatenate it? Where am I doing that?



mysql_query("UPDATE " . PRODUCTS . " SET products_store='some value' where products_model ='$newitems'"); //update the db
Right here.

clunky
07-12-2007, 01:15 PM
mysql_query("UPDATE " . PRODUCTS . " SET products_store='some value' where products_model ='$newitems'"); //update the db
Right here.

Hi That was from an earlier helpful suggestion I tried.
I have edited my earlier post above with the current code, it doesnt make any difference, neither work.

This would appear to be a really simple task, get the form data, seperate each bit, then use it as the where clause for an update of another field.

:-|

clunky
07-12-2007, 01:20 PM
Hi That was from an earlier helpful suggestion I tried.
I have edited my earlier post above with the current code, it doesnt make any difference, neither work.

This would appear to be a really simple task, get the form data, seperate each bit, then use it as the where clause for an update of another field.

:-|

$prod = $_REQUEST[stock_in];//input from form
$wordChunks = explode("\n", $prod);//explode at the end of item name /
for($i = 0; $i < count($wordChunks);$i++){
echo "$wordChunks[$i] ...<br>";
$sql = "UPDATE products SET products_store='some value' where products_model='$wordChunks[$i]'";
mysql_query($sql) or die('Error: '.mysql_error().'<br>The query was:'.$sql.'<br>'); //update the db
}


this is the current script, I have just added the echo to see what its getting...
FOSS312 BLK26
FOSS805 BEI26
FOSS311 BLK26

It ran the script but only updated
products_store where products_model=FOSS311 BLK26

I would really like some help here please

StupidRalph
07-12-2007, 01:29 PM
Well since you know its running one query successfully how about constructing your update string to look like the following



UPDATE products SET products_store='some value' WHERE products_model IN ('FOSS312 BLK26', 'FOSS805 BEI26', 'FOSS311 BLK26')

clunky
07-12-2007, 01:33 PM
Well since you know its running one query successfully how about constructing your update string to look like the following



UPDATE products SET products_store='some value' WHERE products_model IN ('FOSS312 BLK26', 'FOSS805 BEI26', 'FOSS311 BLK26')


That sounds very cool;-)
As I am not brill with arrays etc how would I get that in there?
Like using implode to rebuild the string?

StupidRalph
07-12-2007, 01:33 PM
One second let me code it.

StupidRalph
07-12-2007, 01:51 PM
$wordChunks = array('FOSS312 BLK26', 'FOSS805 BEI26', 'FOSS311 BLK26');
$wordChunks = implode("','", $wordChunks);
//echo $wordChunks;

$sql = "UPDATE products SET products_store='some value' WHERE products_model IN ('" .$wordChunks."');";
echo $sql;

I simply used implode() to add the comma and single quotes. I hardcoded the outer single quotes. You should be able how to do it from here.

LoL I didn't see that you seen to use the implode(). I'm taking it that you've gotten it too.

clunky
07-12-2007, 02:02 PM
$wordChunks = array('FOSS312 BLK26', 'FOSS805 BEI26', 'FOSS311 BLK26');
$wordChunks = implode("','", $wordChunks);
//echo $wordChunks;

$sql = "UPDATE products SET products_store='some value' WHERE products_model IN ('" .$wordChunks."');";
echo $sql;

I simply used implode() to add the comma and single quotes. I hardcoded the outer single quotes. You should be able how to do it from here.

cool.
OK question then if I may please.
If the form posts the items in a string as they do currently, I explode them with \n as thats the end of the line, do I need to go through the for(loop) to extract each one first?
i.e. do I put this inside the loop?
would $wordChunks = array($wordChunks[$i]) work?

StupidRalph
07-12-2007, 02:09 PM
$prod = $_REQUEST[stock_in];//input from form
$wordChunks = explode("\n", $prod);//explode at the end of item name /
$wordChunks = implode("','", $wordChunks);
$sql = "UPDATE products SET products_store='some value' WHERE products_model IN ('" .$wordChunks."');";
mysql_query($sql) or die('Error: '.mysql_error().'<br>The query was:'.$sql.'<br>'); //update the db


You shouldn't need a for loop should you?

clunky
07-12-2007, 02:12 PM
Hi
here is the sql for that... UPDATE products SET products_store='some value' WHERE products_model IN ('FOSS312 BLK26 ','FOSS805 BEI26 ','FOSS311 BLK26');
It only updated the FOSS311 BLK26 row, the other two left unchanged??
I used the code thus


$prod = $_REQUEST[stock_in];//input from form
$wordChunks = explode("\n", $prod);//explode at the end of item name /
//for($i = 0; $i < count($wordChunks);$i++){
//$wordChunks = array('FOSS312 BLK26', 'FOSS805 BEI26', 'FOSS311 BLK26');
$wordChunks = implode("','", $wordChunks);
//echo $wordChunks;

$sql = "UPDATE products SET products_store='some value' WHERE products_model IN ('" .$wordChunks."');";
echo $sql;

clunky
07-12-2007, 02:21 PM
DOH!
its still only updating the last item in the list....
I changed the order of the items in the form and it only updated the last in the list.

I am wondering if this is the way to go here??
I just dont understand why it won't update the others before it.

If there is just one item then it works fine, add another and the first is ignored.

Maybe you can suggest an alternative method to what I am trying to sort out.
The form textarea is multi line, and each item is scanned in by a barcode scanner, the curser is moved to the next line by the barcode scanner, ready for the next item to be scanned.
When all the items have been scanned in, manually backspace to remove the empty row and then the form is submitted and its supposed to alter the table accordingly.

Simple eh?? ;-)

StupidRalph
07-12-2007, 02:27 PM
IN is just another way of writing the statement so you don't have to use OR a lot of times. So what we are essentially saying is:


UPDATE products SET products_store='some value' WHERE products_model ='FOSS312 BLK26' OR products_model ='FOSS805 BEI26' OR products_model = 'FOSS311 BLK26');

Try running a SELECT statement on those other two values and see if its able to select them There might be something quirky with the values inside of your DB. I'm running a similar SQL statement that works fine for me. Also, I noticed that above your second value has a space just before the last single quote.
'FOSS805 BEI26 '


Okay WOW let me try and GOOGLE some things.

clunky
07-12-2007, 02:48 PM
IN is just another way of writing the statement so you don't have to use OR a lot of times. So what we are essentially saying is:


UPDATE products SET products_store='some value' WHERE products_model ='FOSS312 BLK26' OR products_model ='FOSS805 BEI26' OR products_model = 'FOSS311 BLK26');

Try running a SELECT statement on those other two values and see if its able to select them There might be something quirky with the values inside of your DB. I'm running a similar SQL statement that works fine for me. Also, I noticed that above your second value has a space just before the last single quote.
'FOSS805 BEI26 '


Okay WOW let me try and GOOGLE some things.

Hi
this is the echo of the items
FOSS312 BLK26 ','FOSS805 BEI26 ','FOSS311 BLK26
they now have no leading or ending '
I have no idea where the single whitespace is coming from, I have checked the form itself, all ok there?

Having just run some tests, it would that the number of whitespaces from the original scanned input have changed somewhere along the process.

I have done rather a lot of googling lately ;-)
You are willing to help, its really appreciated, believe me.

StupidRalph
07-12-2007, 04:36 PM
The beginning and ending single quote (') are actually hard coded into the $sql variable. If you look at the variable $wordChunk at the end you will see the beginning and ending single quote.

I know you are running the statements via PHP. Is there another way you can interface with the MySQL? Either the command line, SQLyog, PHPMyAdmin or something along those lines so we can test out the statements and work our way backwards. I've tested a statement nearly identical in my setup and its successfully working for me. What version MySQL and PHP are you using?

I'm using PHP 4.4.0 and MySQL 5.0.27.

clunky
07-12-2007, 05:16 PM
The beginning and ending single quote (') are actually hard coded into the $sql variable. If you look at the variable $wordChunk at the end you will see the beginning and ending single quote.

I know you are running the statements via PHP. Is there another way you can interface with the MySQL? Either the command line, SQLyog, PHPMyAdmin or something along those lines so we can test out the statements and work our way backwards. I've tested a statement nearly identical in my setup and its successfully working for me. What version MySQL and PHP are you using?

I'm using PHP 4.4.0 and MySQL 5.0.27.



$prod = $_REQUEST[stock_out];//input from form
$wordChunks = explode("\r\n", $prod);//explode at the end of item name /
trim($wordChunks);
$wordChunks = implode("','", $wordChunks);
rtrim($wordChunks);
mysql_query("UPDATE products SET products_store='In Transit' WHERE products_model IN ('" .$wordChunks."');");


works a treat.
Found the spaces were being caused by \r as well as \n, so altered that bit.
Also that there was a ) missing at the end of the update statement so altered that bit.
Also in order to get rid of the whitspace I had to trim() and rtrim().

WORKS A TREAT!!!!!

Many many thanks for all your help StupidRalph I really appreciate you getting me on the right tracks.

btw php5 mysql 5.0.27 same as you. I had to upgrade to php5 locally too as my host did.
If I could I would shake your hand, what a gentleman !!
Cheers.
I hope that this may help some other person who faces the same or a similar problem somewhere, sometime.

StupidRalph
07-12-2007, 05:26 PM
I'm sure if I were search this forum I can find myself in the same situation. :thumbsup:

You have no idea how bad I want to use PHP5. I have several people reluctant to change their hosts though so I haven't. One person is actually still using MySQL 4.0.23 :eek: .

clunky
07-12-2007, 05:36 PM
I'm sure if I were search this forum I can find myself in the same situation. :thumbsup:

You have no idea how bad I want to use PHP5. I have several people reluctant to change their hosts though so I haven't. One person is actually still using MySQL 4.0.23 :eek: .

Don't know if I am allowed to mention a very good hosting Co. ?

If I am then www."afamousalien"webhosting.com bit of a cryptic one that :)
They are terrific and have had no problems whatsoever :thumbsup:
Anyway if not then admin will delete that I'm sure lol.
Cheers Again



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum