DaniWeb Home &gt; Forums &gt; Web Development &gt; Databases &gt; MySQL

Offline

Newbie Poster

Re: how to update a field in mysql with txt file.

Sep 26th, 2007

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.

•

Join Date: Sep 2007

Posts: 7

Reputation: basti is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

basti

Offline

Newbie Poster

Re: how to update a field in mysql with txt file.

Sep 26th, 2007

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

•

Join Date: Mar 2007

Location: Israel

Posts: 16

Reputation: Shaffer is an unknown quantity at this point

Rep Power: 2

Solved Threads: 1

Offline

Newbie Poster

Re: how to update a field in mysql with txt file.

Sep 27th, 2007

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.

•

Join Date: Sep 2007

Posts: 7

Reputation: basti is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

basti

Offline

Newbie Poster

Re: how to update a field in mysql with txt file.

Sep 27th, 2007

•

Originally Posted by Shaffer

Help with Code Tags

(Toggle Plain Text)
$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=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

•

Join Date: Mar 2007

Location: Israel

Posts: 16

Reputation: Shaffer is an unknown quantity at this point

Rep Power: 2

Solved Threads: 1

Offline

Newbie Poster

Re: how to update a field in mysql with txt file.

Sep 27th, 2007

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.
The basic 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.

•

Join Date: Sep 2007

Posts: 7

Reputation: basti is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

basti

Offline

Newbie Poster

Re: how to update a field in mysql with txt file.

Sep 27th, 2007

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

•

Join Date: Mar 2007

Location: Israel

Posts: 16

Reputation: Shaffer is an unknown quantity at this point

Rep Power: 2

Solved Threads: 1