Solved

Need script help to update mysql from array

Posted on 2007-04-01
16
264 Views
Last Modified: 2012-08-13
Ok, in way over my head...

I have an array coming in like this:

[0] => <start_snip>,Samsung A670 (Camera Phone),Verizon Wireless,$0.00,$20.00,+$20.00,<end_snip>
[1] => <start_snip>,Audiovox PC5740 EV-DO PC Card,Verizon Wireless,$0.00,$30.00,+$30.00,<end_snip>
[2] => <start_snip>,Novatel V620 EV-DO PC Card,Verizon Wireless,$0.00,$0.00,Free,<end_snip>

I'm connected to the database:

//connect
include ("/home/sites/connect.inc");
mysql_select_db(labelers) or die("Unable to load update into mysql");

//now I need help to

//select row where 'name' = $data[1] (the info in the second column above) and 'carrier' = $data[2]

//update 'pricetoday' with $data[3],  'rebates' with $data[4] , 'afterrebates' with  $data[5]

//the names in quotes are the field names, the table name is 'cellphones'.  I don't know if I need to explode the current array into more arrays or if it's used as is.   I am using php4.3.3.

Thank you for any assistance!  Chris
0
Comment
Question by:St_Aug_Beach_Bum
  • 7
  • 5
  • 3
  • +1
16 Comments
 
LVL 29

Expert Comment

by:TeRReF
ID: 18834735
This is a start, if you need help with the queries, can you provide me with some table info...

<?php

include ("/home/sites/connect.inc");
mysql_select_db(labelers) or die("Unable to load update into mysql");

$rows = array('<start_snip>,Samsung A670 (Camera Phone),Verizon Wireless,$0.00,$20.00,+$20.00,<end_snip>',
           '<start_snip>,Audiovox PC5740 EV-DO PC Card,Verizon Wireless,$0.00,$30.00,+$30.00,<end_snip>',
           '<start_snip>,Novatel V620 EV-DO PC Card,Verizon Wireless,$0.00,$0.00,Free,<end_snip>');

foreach ($rows as $row) {
        $data = explode(',', $row);
        $res = mysql_query("SELECT * FROM your_table WHERE name=".$data[1]." AND carrier=".$data[2]);

}

?>
0
 
LVL 6

Expert Comment

by:deresh
ID: 18834746
$qry = 'SELECT * FROM cellphones WHERE name = "{$data[1]}" AND carrier = {$data[2]}";

$res = mysql_query($qry) or die(mysql_error());

$row = mysql_fetch_assoc($res);

// now you have required table row in array $row as associaed array like $row['name']


// this will update your database with new data if your primary key in table cellphones is id,
// otherwise replace $row['id'] with your primary or unique key

$uqry = "UPDATE cellphones SET pricetoday = {$data[3]}, rebates = {$data[4]}, afterrebates = {$data[5]} WHERE id={$row['id']}";


$res = mysql_query($uqry) or die(mysql_error());

0
 

Author Comment

by:St_Aug_Beach_Bum
ID: 18836141
Thank you both.

I think I would probably need to explode it as in TeRRiF's answer, but I'm not sure how to write the um, queries.  I tried incorperating deresh's answer into TeRRiF's foreach statement but I obviously had that wrong.

So far, I have:

$rows = array('<start_snip>,Samsung A670 (Camera Phone),Verizon Wireless,$0.00,$20.00,+$20.00,<end_snip>',
           '<start_snip>,Audiovox PC5740 EV-DO PC Card,Verizon Wireless,$0.00,$30.00,+$30.00,<end_snip>',
           '<start_snip>,Novatel V620 EV-DO PC Card,Verizon Wireless,$0.00,$0.00,Free,<end_snip>');

foreach ($rows as $row) {
        $data = explode(',', $row);
        $res = mysql_query("SELECT * FROM cellphones WHERE name=".$data[1]." AND carrier=".$data[2]);

/*
now the row in mysql needs to be updated, where name=".$data[1]." AND carrier=".$data[2], the 'pricetoday', 'rebates', 'afterrebates', needs to be set to $data[3], $data[4], and $data[5].  I did set up an 'id' column that is primary and autoincremented if we need to use that.
*/


}


0
 
LVL 29

Accepted Solution

by:
TeRReF earned 250 total points
ID: 18836763
Mmm, try this:

$rows = array('<start_snip>,Samsung A670 (Camera Phone),Verizon Wireless,$0.00,$20.00,+$20.00,<end_snip>',
           '<start_snip>,Audiovox PC5740 EV-DO PC Card,Verizon Wireless,$0.00,$30.00,+$30.00,<end_snip>',
           '<start_snip>,Novatel V620 EV-DO PC Card,Verizon Wireless,$0.00,$0.00,Free,<end_snip>');

foreach ($rows as $row) {
        $data = explode(',', $row);
        $res = mysql_query("SELECT * FROM cellphones WHERE name=".$data[1]." AND carrier=".$data[2]);
        if (mysql_num_rows($res) > 0) {
                mysql_query("UPDATE cellphones SET pricetoday='".$data[3]."',rebates='".$data[4]."',afterrebates='".$data[5]."'
                             WHERE name=".$data[1]." AND carrier=".$data[2]");
        }

}


If it doesn't work, can you post the generated error ?
0
 
LVL 2

Expert Comment

by:wakemup
ID: 18837812
Can you use a different query method, such as mysql_fetch_assoc, or mysql_fetch_array(blah, MYSQL_BOTH) where you can index the arrays with nice names like 'name', 'pricetoday' etc.

This will help you sort out your problem.

0
 

Author Comment

by:St_Aug_Beach_Bum
ID: 18840643
TeRRiF, thank you eminesly for your help.  Sorry it has taken me a day to get back to this thread, my four year old gets me off track sometimes ;)

Ok, right now, running your script, I get a 'parse error' on this line:

if (mysql_num_rows($res) > 0) {

I've played around with it and can't figure out what the problem is.

Wakemup, I don't know enough to understand what you are trying to tell me.
0
 

Author Comment

by:St_Aug_Beach_Bum
ID: 18841260
Or, in trying to go a different route for this, I combined the two scripts and I'm doing this:

-----------------------
//connect
include ("/home/sites/connect.inc");
mysql_select_db(awc) or die("Unable to load update into mysql");

$rows = array('<start_snip>,Samsung A670 (Camera Phone),Verizon Wireless,$0.00,$20.00,+$20.00,<end_snip>',
           '<start_snip>,Audiovox PC5740 EV-DO PC Card,Verizon Wireless,$0.00,$30.00,+$30.00,<end_snip>',
           '<start_snip>,Novatel V620 EV-DO PC Card,Verizon Wireless,$0.00,$0.00,Free,<end_snip>');

foreach ($rows as $row) {
        $data = explode(',', $row);
        $res = mysql_query("SELECT * FROM cellphones WHERE name=".$data[1]." AND carrier=".$data[2]);

$row = mysql_fetch_assoc($res);

$uqry = "UPDATE cellphones SET pricetoday = {$data[3]}, rebates = {$data[4]}, afterrebates = {$data[5]} WHERE id={$row['id']}";

$res = mysql_query($uqry) or die(mysql_error());

}
------------------------

but I get:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/virtual/site1/fst/var/www/html/adm/test.php on line 74
You have an error in your SQL syntax near '+$20.00 WHERE id=' at line 1

0
 
LVL 6

Expert Comment

by:deresh
ID: 18841685
You should add " or ' into your query like:

$res = mysql_query("SELECT * FROM cellphones WHERE name=\"".$data[1]."\" AND carrier=\"".$data[2]."\"") or die(mysql_error());


0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 6

Expert Comment

by:deresh
ID: 18841689
and then also in second query:

$uqry = "UPDATE cellphones SET pricetoday = \"{$data[3]}\", rebates = \"{$data[4]}\", afterrebates = \"{$data[5]}\" WHERE id={$row['id']}";
0
 

Author Comment

by:St_Aug_Beach_Bum
ID: 18843014
Thank you Desresh, I added that as you indicated, but still get the same error...
0
 
LVL 6

Assisted Solution

by:deresh
deresh earned 250 total points
ID: 18843125
$res = mysql_query("SELECT * FROM cellphones WHERE name=\"".$data[1]."\" AND carrier=\"".$data[2]."\"") or die(mysql_error());

it seems that your data isn't fetched from database, and then subsequently update fails afterwards.

you haven't addedd: or die(mysql_error()); at the end of first mysql_query call

please add:
  echo $qry."<br />".$uqry;

after mysql_fetch_assoc;
0
 

Author Comment

by:St_Aug_Beach_Bum
ID: 18845493
Ok, this is getting a bit confusing since I combined your scripts.  I had to put your echo under the $uqry line so I could get something from it.

This is the script I have right now:
--------------------------------------------
//connect
include ("/home/sites/connect.inc");
mysql_select_db(awc) or die("Unable to load update into mysql");

//the array is like
$rows = array('<start_snip>,Samsung A670 (Camera Phone),Verizon Wireless,$0.00,$20.00,+$20.00,<end_snip>',
           '<start_snip>,Audiovox PC5740 EV-DO PC Card,Verizon Wireless,$0.00,$30.00,+$30.00,<end_snip>',
           '<start_snip>,Novatel V620 EV-DO PC Card,Verizon Wireless,$0.00,$0.00,Free,<end_snip>');

foreach ($rows as $row) {
        $data = explode(',', $row);
        $res = mysql_query("SELECT * FROM cellphones WHERE name=\"".$data[1]."\" AND carrier=\"".$data[2]."\"") or die(mysql_error());

$row = mysql_fetch_assoc($res);

$uqry = "UPDATE cellphones SET pricetoday = {$data[3]}, rebates = {$data[4]}, afterrebates = {$data[5]} WHERE id={$row['id']}or die(mysql_error())";

echo $res."<br />".$uqry;

//for testing
//$uqry = "UPDATE cellphones SET pricetoday = {$data[3]}, rebates = {$data[4]}, afterrebates = {$data[5]} WHERE name ={$data[1]} AND carrier = {$data[2]}or die(mysql_error())";

$res = mysql_query($uqry) or die(mysql_error());

}

----------------------------------------------------

The result I get is:

Resource id #3
UPDATE cellphones SET pricetoday = $0.00, rebates = $20.00, afterrebates = +$20.00 WHERE id=or die(mysql_error())You have an error in your SQL syntax near '+$20.00 WHERE id=or die(mysql_error())' at line 1

This is my db if that is helpful:

CREATE TABLE cellphones1 (
  name text NOT NULL,
  id int(11) NOT NULL auto_increment,
  carrier text NOT NULL,
  pricetoday text NOT NULL,
  rebates text NOT NULL,
  afterrebates text NOT NULL,
  PRIMARY KEY  (id)
) TYPE=MyISAM;

(there are additional fields, but I removed those for simplifycation)

0
 

Author Comment

by:St_Aug_Beach_Bum
ID: 18845506
CREATE TABLE cellphones1  (correction, that should be cellphones).
0
 

Author Comment

by:St_Aug_Beach_Bum
ID: 18846212
In continueing to try to get this to work, I cut it down and added quotes.  This is what got it working:
----------------------------------------
foreach ($rows as $row) {
        $data = explode(',', $row);
        $uqry = "UPDATE cellphones SET pricetoday = '{$data[3]}', rebates = '{$data[4]}', afterrebates = '{$data[5]}' WHERE name ='{$data[1]}' AND carrier = '{$data[2]}'";

$res = mysql_query($uqry) or die(mysql_error());

}
---------------------------------

Seems to function ok now.

Thank you both for your assistance on this.    Chris
0
 
LVL 29

Expert Comment

by:TeRReF
ID: 18846407
you're welcome :)
0
 
LVL 6

Expert Comment

by:deresh
ID: 18849126
np
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now