Need script help to update mysql from array

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
St_Aug_Beach_BumAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TeRReFCommented:
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
dereshCommented:
$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
St_Aug_Beach_BumAuthor Commented:
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
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

TeRReFCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wakemupCommented:
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
St_Aug_Beach_BumAuthor Commented:
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
St_Aug_Beach_BumAuthor Commented:
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
dereshCommented:
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
dereshCommented:
and then also in second query:

$uqry = "UPDATE cellphones SET pricetoday = \"{$data[3]}\", rebates = \"{$data[4]}\", afterrebates = \"{$data[5]}\" WHERE id={$row['id']}";
0
St_Aug_Beach_BumAuthor Commented:
Thank you Desresh, I added that as you indicated, but still get the same error...
0
dereshCommented:
$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
St_Aug_Beach_BumAuthor Commented:
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
St_Aug_Beach_BumAuthor Commented:
CREATE TABLE cellphones1  (correction, that should be cellphones).
0
St_Aug_Beach_BumAuthor Commented:
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
TeRReFCommented:
you're welcome :)
0
dereshCommented:
np
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.