how to update a field in mysql with txt file.

[basti]

Hi guys,

I'm very new to MySQL -- so, your help is very much appreciated.
I have a table that has 50 fields. 2 of the fields are primary (named: item_number and catalog_year). There are about 1200 different unique items on this table.

Now, what I basically need to update is the last field (field 50) which is called pkg_options.
I need to change all the entries inside this particular field(pkg_options) to a new entries.
I have created a text file with 2 columns separated with comma. The first column represent the Item Number and the second is for Packaging Options(pkg_options).

So it looks like this,

G1031,PkgPlastic
G1077,PkgMetal
G1039,PkgKeyTag
G1025,PkgPlastic
G1055,PkgPlastic
G3071,PkgFlashLight
K1079,PkgMetal

and so on...

How do I update this field in phpmyadmin? How do I import this text file I created so I dont
have to change each row at a time? Imagine this could take me a month or so... pls help.

A step by step tutorial would be much appreciated. Thanks very much!

best regards,
sebastian

[Shaffer]

Hello,
in what form do you need to enter them? I really don't understand... It seems like you need a different table for the pkgoptions, I really don't know how you want the different packages to be entered...

Also, there is no way to use phpmyadmin for it. You need to learn how to use PHP for MySQL.
Connection:

mysql_connect($host,$username,$password);

Database selection:

mysql_select_db($database);

Querying:

$result = mysql_query($query);

More about MySQL in PHP: http://www.php.net/mysql

Well, assuming you want to enter them into the field pkg_options just as they are in the file, with | signs seperating them...

<?php
if(@mysql_query("UPDATE table_name SET pkg_options='".implode(file("pkgoptions.txt"),"|")."' WHERE Ihavenoidea='alsoIhavenoidea';")) {
  echo "Success!";
}
else {
  echo "Failure! Error is: ".mysql_query();
}
?>

I really don't understand why you are making this a field, and not a column.

Anyhow, I hope this helps you.
If not, write otherwise.

Happy Jewish New Year!
Shaffer.

[basti]

Hi there Shaffer,

Thanks very much for the quick reply. Here, let me explain it again...

Im looking for a way to update the field/column "pkg_options" in a much faster way by using text file since I have about 1200 items on this table. I can update each item at a time but as you have known, there are about 1200 items. Each item has different pkg_options.

here take a look at my table...
http://www.ugatha.com/table1.jpg

here is view when browse...
http://www.ugatha.com/table2.jpg

The text file is written down as I had shown earlier... Im not sure if this the right way because for instance, the item number(G1031) has to have the pkg Option (PkgPlasticPens) but not PkgMetalPens...

G1031,PkgPlasticPens
G1079,PkgPlasticPens
G3094,PkgMetalPens

I just need to populate the pkg_options with the same Item number that it's already there.

Hope this helps. Thanks very much again!

regards,
sebastian

[Shaffer]

Hello,
thanks, I think I have a better understanding now of what you want.
Do you want to replace a Pkg(...) with it's code throughout the table?

If so...

$i=0;
foreach(file("pkgoptions.txt") as $key => $value) {
   if($temp=explode(",",$value)) {
     if(@mysql_query("UPDATE table_name SET ".$temp[1]."='".$temp[0]."';") {
       $i++;
     }
   }
}
if($i==sizeof(file("pkgoptions.txt")) echo "Works.";
else echo "Nup. Too bad.";

I hope this helps you. :)
Shaffer.

[basti]

$i=0;
foreach(file("pkgoptions.txt") as $key => $value) {
   if($temp=explode(",",$value)) {
     if(@mysql_query("UPDATE table_name SET ".$temp[1]."='".$temp[0]."';") {
       $i++;
     }
   }
}
if($i==sizeof(file("pkgoptions.txt")) echo "Works.";
else echo "Nup. Too bad.";

Hello Shaffer, Thanks again! I appreciate your time... however, could you a little bit explain how would I run this script? Im a new to php as well and can only write basic basic php.

A complete script and a instruction would be nice. I was hoping to get this done in phpmyadmin but I guess a PHP script would do this job easily as you suggested here.
Great stuff Shaffer, Thank you very much again.

regards,
sebastian

[Shaffer]

Hello,
I will explain the script as you wish...

I am setting the variable $i to be 0, so that I can increase it in the future for comparison.

$i=0;

Now, I am looping through the file "pkgoptions.txt", using the function file(), which splits the given file's lines into an array.
In-order to loop through an array, I used the foreach() loop, eventhough the for() loop can do it as well, just messier and with less options.
Thebasic syntax of the foreach() loop is: foreach($array as $key => $value){}, where $key and $value are variable names that I chose (they can boo $jupiter and $venus for all I care).
With this array: $fruit = array(1=>"apple",5=>"lemon","peach"=>11), 1,5, & peach are keys, and apple,lemon, & 11, are values.

foreach(file("pkgoptions.txt") as $key => $value) {

The following line uses the function explode() to split a string into an array, every time it finds a certain character. This time it was a comma. The function literally explodes the string. :)
So that $temp[0] is the code and $temp[1] is the pkgoption.

if($temp=explode(",",$value)) {

Finally, the mysql query! The function mysql_query() is used to... erm... send a query to mysql.
The @ sign before it, is used to have the function either return true on success or false on failure, rather an error, which the user can see (no good if it's public).
The query updates your table, and replaces each pkgoption with it's code. Because this is a loop, each pkgoption will get it's turn. Now, you can have as many pkgoptions as youd like. ;)

if(@mysql_query("UPDATE table_name SET ".$temp[1]."='".$temp[0]."';") {

The following cute little piece of code increments $i every time a query succeeds. Explanation will come in a bit..

$i++;

This closes the mysql_query() if.

}

This closes the explode() if.

}

This closes the foreach() loop.

}

Now, see if all of the pkgoptions were replaced... Or if they weren't...

if($i==sizeof(file("pkgoptions.txt")) echo "Works.";
else echo "Nup. Too bad.";

Shaffer.

[basti]

hello Shaffer,

Thanks very much for your explaination here. Great tutorial, I deffinitely learn something about your code.

However, Im still missing something, how exactly I would run this script? should I name it first to something like update.php then run this script from the server? or just paste this script in phpmyadmin? Im sorry Shaff but Im kind of lost and I dont know where to start. I desperately need your help. I hope im not taking much of your time.

Anyway, I did a backup on my table and here is one of the row looks like... now, there's a lot of info on this item, I dont want to lost its info when I update because all I really want to do add is to add this pkg_options which is the last column. I hope your getting the picture here.

here it is (in csv format);
"G645";"2007";;"0";"259";;"G3097 & G8019";;;;"Y";;;;;;;;;;;"60.00";"C";"30.00";"C";"0.30";"C";"0.00";"A";"P900BK";"blk_displa";"50";"500";"C";"50";"16.00";"C";"150";"15.50";"C";"250";"15.25";"C";"500";"15.00";"A";"0";"0.00";"Colorplay Maxine Pen & Luggage Tag Set";"Ensemble Colorplay avec stylo Maxine et porte-clés";"Colorplay 2-piece gift set with G3097 maxine twist-action ballpoint metal pen and G8019 luggage tag.";"Ensemble-cadeau Colorplay 2 pièces avec étiquette de bagage G8019 et stylo à bille G3097 Maxine.";;;"n";"n";;;;;;;"G8019";"G8019";;;;;"G3097";"G3097";;;;;"Silk Screen(Pen)/Debossed(Tag)";"Sérigraphie (stylos)/Gaufrage en relief";"Additional location (Pen)";"Surface additionnelle (stylos)";"(per location)";"(ch.surface)";;;;;;;"P900 - Blk ink";"P900 encre noire";"P900 - Blue ink: $0.15(C) ea.";"P900 - encre bleue:$0.15(C)ch";;;"Black display gift box";"Boîte de présentation noire.";"Silk Screened";"Sérigraphie";"in Line with Clip (G3097)";"Enligné avec l'agrafe (G3097)";"1 1/4\" x 1 1/8\"";"1 1/4\" x 1 1/8\"";"Debossed";"Gaufrage en relief";"Centered on back";"Centré Arrière";"2\" x 1 1/4\"";"2\" x 1 1/4\"";;;;;;;;;;;;;;;;;;;;;;;;;NULL;NULL;NULL

if you notice the last part is NULL, thats where the pkg_options. The first line there is the item_number and next is the
catalog year and so on.... http://www.ugatha.com/table2.jpg

Also, should the text file be the same format as what I have posted here?
G1031,PkgPlasticPens

or

should it be like this...
G1031,,,,,,,,,,,,,,,,,,,etc ,PkgPlasticPens

Pls, I really need your help...

Thanks again!

regards,
sebastian

[Shaffer]

Hello,
you should paste the code between the <?php ?> braces into a file of any name, of the php file tppe (.php), and then run it from the server, as you've said. :)
As for the text file; the format should be:
code,pkgoption (new line)
code,pkgoption (new line)
...
Of course, do not include the "(new line)".

Please run it and tell meif it works.

Shaffer. ;-)

p.s
You're not taking from my time, it's good to help. Plus, it happens a lot that I need to go to reference when I help people so that I remember codes better, and techniques better, so thanks to you too. :)

[basti]

Hello Shaffer,

Thanks again for your reply. Unfortunately, it didn't work. Im not sure if I have the correct code but here it is anyway and the error after I runned the script.

<?php
// Make a MySQL Connection
mysql_connect("localhost", "basti_test", "test") or die(mysql_error());
mysql_select_db("basti_test") or die(mysql_error());

$result = mysql_query($query);

$i=0;
foreach(file("run.txt") as $key => $value) {
if($temp=explode(",",$value)) {
if(@mysql_query("UPDATE test_table SET ".$temp[1]."='".$temp[0]."';") {
$i++;
}
}
}
if($i==sizeof(file("run.txt")) echo "Works.";
else echo "Nup. Too bad.";

?>

browser error:
Parse error: syntax error, unexpected '{' in /home/basti/public_html/test/run-update.php on line 13

Thanks again!

regards,
sebastian

[basti]

Hi Shaffer, by the way, line 13 is this line;
if(@mysql_query("UPDATE test_table SET ".$temp[1]."='".$temp[0]."';") {

thanks

[Shaffer]

Hello,
that is quite a really stupid error by me, and something too unfortunate to hold you back for.
Parse errors are a type of syntax errors. It told you that you have an unexpected '{'. This usually means that you didn't connect a string correctly ("Hello"."World"), or that you didn't close parenthasis correctly.
I made an "if" condition opening one clause ("(") and then a function, mysql_query, resulting in opening another clause ("("). Tha thing is, I only closed one (")") so that the curly brace ("{") was unexpected, instead of another closing parenthasis (")").

So, the correction is to be:
if(@mysql_query("UPDATE test_table SET ".$temp[1]."='".$temp[0]."';")) {

Good night!
Shaffer.