By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,830 Members | 2,271 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,830 IT Pros & Developers. It's quick & easy.

Updating multiple records

P: n/a
Hi,

I have a MySQL database where I want to updated multiple records. The
table has two columns:
product_number
product_price

I have a list with first entry product_price, second entry
product_number

@products_param = (

['11.90', '1034K'],
['22.90', '1034R'],
['43.90', '1034U']

); # no comma after last entry

I run the following from a file on the server:

$dbh = DBI->connect($dsn, $login_name, $password)
or die "(Can't connect to MySQL database: $DBI::errstr\n";

####### Start products_param FOR loop

$dbh = DBI->connect($dsn, $login_name, $password)
or die "(Can't connect to MySQL database: $DBI::errstr\n";

###### Start products_param FOR loop ######

$compiled = $dbh->prepare("

UPDATE products
SET products_price = ?
WHERE products_model = ?
VALUES (?,?)

");

foreach ($products_param as $products) {

$dbh->execute($compiled, $products);

};

This gives me an "Internal Server" error. I have several PHP and MySQL
books but cannot find the correct syntax.

Any help appreciated.

Al Moodie.
Sep 2 '08 #1
Share this Question
Share on Google+
6 Replies


P: n/a
On Sep 2, 11:37*am, Al Moodie <nos...@nospam.comwrote:
Hi,

I have a MySQL database where I want to updated multiple records. The
table has two columns:
product_number
product_price

I have a list with first entry product_price, second entry
product_number

@products_param = (

* * * * * * * * ['11.90', '1034K'],
* * * * * * * * ['22.90', '1034R'],
* * * * * * * * ['43.90', '1034U']

* * * * * * * * ); # no comma after last entry

I run the following from a file on the server: * * * * *

$dbh = DBI->connect($dsn, $login_name, $password)
or die "(Can't connect to MySQL database: $DBI::errstr\n";

####### Start products_param FOR loop

$dbh = DBI->connect($dsn, $login_name, $password)
or die "(Can't connect to MySQL database: $DBI::errstr\n";

###### Start products_param FOR loop ######

$compiled = $dbh->prepare("

* * * * UPDATE products
* * * * SET products_price = ?
* * * * WHERE products_model = ?
* * * * VALUES (?,?)

* * * * ");

foreach ($products_param as $products) {

$dbh->execute($compiled, $products);

};

This gives me an "Internal Server" error. I have several PHP and MySQL
books but cannot find the correct syntax.

Any help appreciated.

Al Moodie.
seems to be error in this
where have you declared this?

@products_param = (

['11.90', '1034K'],
['22.90', '1034R'],
['43.90', '1034U']

);

---------------------
http://www.webxan.com
---------------------
Sep 2 '08 #2

P: n/a
On 2 Sep, 16:37, Al Moodie <nos...@nospam.comwrote:
Hi,

I have a MySQL database where I want to updated multiple records. The
table has two columns:
product_number
product_price

I have a list with first entry product_price, second entry
product_number

@products_param = (

* * * * * * * * ['11.90', '1034K'],
* * * * * * * * ['22.90', '1034R'],
* * * * * * * * ['43.90', '1034U']

* * * * * * * * ); # no comma after last entry

I run the following from a file on the server: * * * * *

$dbh = DBI->connect($dsn, $login_name, $password)
or die "(Can't connect to MySQL database: $DBI::errstr\n";

####### Start products_param FOR loop

$dbh = DBI->connect($dsn, $login_name, $password)
or die "(Can't connect to MySQL database: $DBI::errstr\n";

###### Start products_param FOR loop ######

$compiled = $dbh->prepare("

* * * * UPDATE products
* * * * SET products_price = ?
* * * * WHERE products_model = ?
* * * * VALUES (?,?)

* * * * ");

foreach ($products_param as $products) {

$dbh->execute($compiled, $products);

};

This gives me an "Internal Server" error. I have several PHP and MySQL
books but cannot find the correct syntax.

Any help appreciated.

Al Moodie.
I have never seen that syntax for the execute method. Where are you
getting it from?
Sep 2 '08 #3

P: n/a
On Tue, 2 Sep 2008 09:00:05 -0700 (PDT), Captain Paralytic
<pa**********@yahoo.comwrote:
>
I have never seen that syntax for the execute method. Where are you
getting it from?
I "adapted" it from an INSERT placeholder query in a PHP book. If you
know the correct sytax please let me know.

Al Moodie.
Sep 2 '08 #4

P: n/a
$compiled = $dbh->prepare("
>
UPDATE products
SET products_price = ?
WHERE products_model = ?
VALUES (?,?)

");
I think the VALUES line is a problem anyhow. It does not belong in an
UPDATE query.

If you want to know what happens, enable the querylog (in my.cnf). I
assume you are developing on your local database, where enabling the
querylog is not a space/performance issue.
To check the log "live" there are quite nice log viewers, like multitail
(linux) or BareTail (windows or wine). You can then see what is really
sent to the server.

Best regards,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/
Sep 3 '08 #5

P: n/a
On Sep 2, 4:37 pm, Al Moodie <nos...@nospam.comwrote:
Hi,

I have a MySQL database where I want to updated multiple records. The
table has two columns:
product_number
product_price

I have a list with first entry product_price, second entry
product_number

@products_param = (

['11.90', '1034K'],
['22.90', '1034R'],
['43.90', '1034U']

); # no comma after last entry

I run the following from a file on the server:

$dbh = DBI->connect($dsn, $login_name, $password)
or die "(Can't connect to MySQL database: $DBI::errstr\n";

####### Start products_param FOR loop

$dbh = DBI->connect($dsn, $login_name, $password)
or die "(Can't connect to MySQL database: $DBI::errstr\n";

###### Start products_param FOR loop ######

$compiled = $dbh->prepare("

UPDATE products
SET products_price = ?
WHERE products_model = ?
VALUES (?,?)

");

foreach ($products_param as $products) {

$dbh->execute($compiled, $products);

};

This gives me an "Internal Server" error. I have several PHP and MySQL
books but cannot find the correct syntax.

Any help appreciated.

Al Moodie.
What's with the VALUES line? i've never seen that in a update
statement before.

If you want to update wveral rows at once so that a column holds the
same value you'd do UPDATE table SET column = ? WHERE key IN (?); KEY
would then take a list of keys seperated by commas. For example to
update rows with key values 3, 7 and 10 you'd pass 3,7,10 to the
prepared query.
Sep 3 '08 #6

P: n/a
On 2 Sep, 16:37, Al Moodie <nos...@nospam.comwrote:
Hi,

I have a MySQL database where I want to updated multiple records. The
table has two columns:
product_number
product_price

I have a list with first entry product_price, second entry
product_number
Hi Al,

I'd suggest re-writing your code in PHP and SQL - cos whatever you are
writing looks like neither:
@products_param = (

['11.90', '1034K'],
['22.90', '1034R'],
['43.90', '1034U']

); # no comma after last entry
I guess this is meant to be:

$products_param = array (
array ('11.90', '1034K'),
array ('22.90', '1034R'),
array ('43.90', '1034U')
); // no comma after last entry
(PHP will happily accomodate a trailing , in the array params - some
codingstandards even require it).

UPDATE products
SET products_price = ?
WHERE products_model = ?
VALUES (?,?)
WTF?

Do you mean
UPDATE products
SET products_price = ?
WHERE products_model = ?

C.
Sep 3 '08 #7

This discussion thread is closed

Replies have been disabled for this discussion.