Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need script help to update mysql from array

Posted on 2007-04-01
16
Medium Priority
?
280 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 29

Accepted Solution

by:
TeRReF earned 1000 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
 
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 1000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
This article discusses how to create an extensible mechanism for linked drop downs.
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 create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

688 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